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.

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
sandeep
  • 251
  • 3
  • 4
  • 9

2 Answers2

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;
Vladimir
  • 1,569
  • 7
  • 15
  • @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 09:04
  • There is no such function as ST_ClosedPoint in PostGIS. Please provide MWE that reproduces the problem. – Vladimir Nov 25 '12 at 09: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 http://www.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
  • Try this 'SELECT ref,ST_Distance(r.geom,ST_SetSRID(ST_MakePoint(75.252398, 19.905008),4326)) FROM roads r ORDER BY 2 ASC LIMIT 1;' – Vladimir Nov 25 '12 at 10:24
  • let us continue this discussion in chat – Vladimir Nov 25 '12 at 10:38
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.

Stefan
  • 4,414
  • 1
  • 33
  • 66