[GIS] Calculate distance between point and linestring and how to represent that on QGIS

distancelinestringpointpostgisqgis

I have loaded my data with osm2pgrouting and I have my network with ways (Linestring) and nodes (Point). The problem is : I have a database of doctors addresses (point) and I should calculate the distance between these points and my ways (Linestring).

Yellow points are Doctors : 1

I am using the following query :

SELECT ST_Distance ( 
ST_GeomFromText('POINT(55.4504545699355 -20.8752577759738)',4326),
ST_GeomFromText('LINESTRING(55.4748349 -27.1342723, 55.4747091 -21.1338699)',4326) )
As ST_DISTANCE;

But I don't think that works. I need to calculate the distance from all the doctors points and represent that in QGIS as a linestring.

Any help or advice?

Best Answer

You could use the Distance to nearest hub algorithm from the Processing plugin which you could add your points and your lines layer instead of going through a query.

Distance to nearest hub

From a couple of example layers that I have, you can get something like this:

Output player for Distance to nearest hub analysis

Then when you open up the Attributes Table for the output layer, you will be given the HubName and HubDist in whatever measurement scale you selected (in my case, meters). Hope this helps.

Related Solutions

[GIS] Simple PostGIS ST_Distance query for two points from the same geometry table

You need to reference your table twice, giving it different aliases:

SELECT ST_Distance(a.geom, b.geom)
FROM points_table a, points_table b
WHERE a.id='x' AND b.id='y';

[GIS] Calculate min distance between points in PostGIS

It's not a simple problem, it involves some kind of forced iteration over the set of candidate points. This chapter from the workshop shows a similar problem, but not exact (your problem is slightly easier)

http://postgis.net/workshops/postgis-intro/advanced_geometry_construction.html

The nearest neighbor searching chapter from the workshop shows the tools you might use to do an index-assisted approach with some external loop driving the query

http://postgis.net/workshops/postgis-intro/knn.html

If your points have a distinct id and you know a distance tolerance (9999) they will all fall within, a self-join and use of the "DISTINCT ON" filter will get you the answer in one go.

WITH unfiltered AS
(
  SELECT t1.id AS id1, t2.id AS id2, ST_Distance(t1.geom, t2.geom) as dist
  FROM t t1, t t2 WHERE ST_DWithin(t1.geom, t2.geom, 9999) AND t1.id <> t2.id
  ORDER BY t1.id, ST_Distance(t1.geom, t2.geom) ASC
)
SELECT DISTINCT ON (id1) id1, id2, dist FROM unfiltered;

It first gathers the candidates combinations of points, and sorts them by distance. Then the "distinct on" filter strips out just the first member of each candidate group, which conveniently is the closest, thanks to the pre-sorting.

Best Answer

Related Solutions

[GIS] Simple PostGIS ST_Distance query for two points from the same geometry table

[GIS] Calculate min distance between points in PostGIS

Related Question