4

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.

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;
  • @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 '12 at 9:04
  • There is no such function as ST_ClosedPoint in PostGIS. Please provide MWE that reproduces the problem. – Vladimir Nov 25 '12 at 9:10
  • I mean ST_Closestpoint will work ? How to use it? What is MWE ? – sandeep Nov 25 '12 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 '12 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 '12 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
    DISTINCT ON and LIMIT 1 are redundant, since the result will only be 1 row – raphael Dec 3 '15 at 3:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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