6

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.

2 Answers 2

7
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;
7
  • @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?
    – sandeep
    Nov 25, 2012 at 9:04
  • There is no such function as ST_ClosedPoint in PostGIS. Please provide MWE that reproduces the problem.
    – Vladimir
    Nov 25, 2012 at 9:10
  • I mean ST_Closestpoint will work ? How to use it? What is MWE ?
    – sandeep
    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
    – Vladimir
    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
    – sandeep
    Nov 25, 2012 at 10:21
3

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.