How to find nearest line from point using PostGIS?

Question

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.

Answer 1

SELECT name,ref,type,ST_Distance(ST_Buffer(r.geom,20),ST_SetSRID(ST_MakePoint(lon, lat),4326)) 
FROM roads r 
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;

• sandeep: @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?
• Vladimir: There is no such function as ST_ClosedPoint in PostGIS. Please provide MWE that reproduces the problem.
• sandeep: I mean ST_Closestpoint will work ? How to use it? What is MWE ?
• Vladimir: 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
• sandeep: 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

Answer 2

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

WITH objects AS
 (SELECT
 name,
 (ST_Dump(roads.geom)).geom AS geometries
 FROM roads),
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.

• raphael: DISTINCT ON and LIMIT 1 are redundant, since the result will only be 1 row