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) )
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