# How to find nearest line from point using PostGIS?

I have a table of Roads in PostGIS which geometry is "geometry(MultiLineString,4326)".

Its attribute are

gid osm_id name ref type oneway bridge maxspeed geom

Now i want to know the closest Road from latitude and longitude.

Also I want to set some distance from that Line/Road like 20m both side and if the point is within that distance then it should give the name of that line else not.

``````SELECT name,ref,type,ST_Distance(ST_Buffer(r.geom,20),ST_SetSRID(ST_MakePoint(lon, lat),4326))
ORDER BY 4 ASC
LIMIT 1;
``````

For point in lon/lat coordinates:

``````SELECT name,ST_Distance(r.geom,ST_SetSRID(ST_MakePoint(lon, lat),4326)) FROM roads r ORDER BY 2 ASC LIMIT 1;
``````
• @VldimirNaumov Both query are not working for me. Not giving the correct result. I am more interested in second query as i want to pass the co-ordinate directly into SQL Query as well as distance of 20m or less or much on lines both side. Does ST_ClosedPoint or ST_Intersects Query work? how to use it? Nov 25, 2012 at 9:04
• There is no such function as ST_ClosedPoint in PostGIS. Please provide MWE that reproduces the problem. Nov 25, 2012 at 9:10
• I mean ST_Closestpoint will work ? How to use it? What is MWE ? Nov 25, 2012 at 10:07
• Minimal working example, e.g. provide SQL code that you are trying to execute. ST_ClosestPoint returns geometry, consult postgis.org/docs/ST_ClosestPoint.html Nov 25, 2012 at 10:11
• SELECT name,ref,type,ST_Distance(r.geom,ST_SetSRID(ST_MakePoint(75.252398, 19.905008),4326)) FROM roads r ORDER BY 2 ASC LIMIT 1; name ref type st_distance NULL . tertiary 7.58381841503622 Its should give the "ref" ==> NH211 but giving the wrong result Nov 25, 2012 at 10:21

Maybe a little bit late. This should work for the first part of your answer:

``````WITH objects AS
(SELECT
name,
point AS
(SELECT
'SRID=4326;POINT(long lat)'::geometry AS point
);

SELECT DISTINCT ON
(ST_Distance(point, geometries)),
objects.name
FROM objects, point
ORDER BY ST_Distance(point, geometries)
LIMIT 1;
``````

At first you dump the objects from a multilinestring and then you calculate the distance between your point and the dumped linestring.`SELECT DISTINCT ON` removes duplicate rows from your result set. Ordering by the distance und limiting by 1 the name of your nearest road appears.

For long and lat you have to define coordinate values.

• `DISTINCT ON` and `LIMIT 1` are redundant, since the result will only be 1 row Dec 3, 2015 at 3:27