# How to calculate nearest point to a linestring in PostGIS?

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

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,
``````poi                         closest_point               distance_km     line