More

How to find nearest line from point using PostGIS?

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

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 ONremoves 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.