1

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
2

You can use ST_ClosestPoint to return the projection of your point on the linestring.

SELECT 
  --poi.name,
  --poi.city,
  ST_AsText(poi.the_geog) AS poi,
  ST_AsText(ST_ClosestPoint(road.the_geog::geometry, poi.the_geog::geometry)::geography) AS closest_point,
  ST_Distance(road.the_geog, poi.the_geog)/1000.0 AS distance_km,
  ST_AsText(road.the_geog) AS line
FROM road
JOIN poi ON ST_DWithin(road.the_geog, poi.the_geog, 10000.0) 
WHERE road.id = 123 
ORDER BY ST_LineLocatePoint(road.the_geog::geometry, poi.the_geog::geometry), ST_Distance(road.the_geog, poi.the_geog);

Returns

poi                         closest_point               distance_km     line
POINT(85.280194 23.296728)  POINT(85.280194 23.296728)  0               LINESTRING(85.280194 23.296728,85.281572 23.297479)         
POINT(85.289673 23.291988)  POINT(85.281572 23.297479)  1.02790366894   LINESTRING(85.280194 23.296728,85.281572 23.297479)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.