How to calculate nearest point to a linestring in PostGIS?

Question

I'm following this question to calculate POI nearest a road(linestring). I'm able to calculate nearest points in the linestring but I'm not able to find the distance from the POI to nearest point(vertex) on the linestring.

Here's my code for finding the nearest POI.

CREATE TABLE road(id serial PRIMARY KEY, the_geog geography(LINESTRING,4326) );

CREATE TABLE poi(gid serial PRIMARY KEY, name varchar, city varchar, the_geog geography(POINT,4326) )

Values are:

INSERT INTO road (id, the_geog) VALUES (123, ST_GeographyFromText('SRID=4326;LINESTRING(85.280194 23.296728,85.281572 23.297479)') );

To calculate nearest point :

SELECT 
    poi.name,
    poi.city,
    ST_AsTEXT(poi.the_geog),
    ST_Distance(road.the_geog, poi.the_geog)/1000.0 AS distance_km,
    ST_AsTEXT(road.the_geog::geometry)
FROM road, poi
WHERE
    road.id = 123
    AND ST_DWithin(road.the_geog, poi.the_geog, 10000.0)
ORDER BY
    ST_LineLocatePoint(road.the_geog::geometry, poi.the_geog::geometry),
    ST_Distance(road.the_geog, poi.the_geog)
;

If the linestring is represented by this: [85.280194 23.296728,85.281572 23.297479], I want the result like:

poi                   vertex                 distance_km
85.280194 23.296728   85.280001 23.299876    3
85.289673 23.291987   85.281572 23.297479    5

score 2 · Accepted Answer · answered Jun 21 '16 at 10:02

Use ST_DumpPoints() to extract the vertices of the line:

SELECT id,the_geog, (ST_dumppoints(road.the_geog::GEOMETRY)).geom AS vertex FROM road;

You have to cast GEOGRAPHY to GEOMETRY, otherwise st_dumppoints does not work.

Your whole query could look like so:

SELECT road.id AS road_id, 
       ST_Astext(poi.the_geog) AS poi, 
       ST_astext(road.vertex) AS vertex,
       ST_AsText(road.the_geog) AS line, 
       ST_Distance(vertex, poi.the_geog) AS distance
FROM (SELECT id,the_geog, (ST_dumppoints(road.the_geog::GEOMETRY)).geom AS vertex 
          FROM road
      ) as road, 
      poi 
WHERE road.id = 123
ORDER by ST_Distance(vertex, poi.the_geog);

How to calculate nearest point to a linestring in PostGIS?

1 Answers1