Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

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;
share|improve this answer
    
@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

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.

share|improve this answer

You can use ORDER BY, and LIMIT your result to 1 to find the closest road:

SELECT gid, ST_Distance(ST_Transform(ST_GeomFromText('POINT(39.240443 26.208803)',3857),4326),geom) AS myLineDistance 
FROM road
ORDER BY ST_Distance(ST_Transform(ST_GeomFromText('POINT(39.240443 26.208803)',3857),4326),geom) LIMIT 1

For the second query, you can convert the geometry to geography types to compute point-to-line distance in meters (as follows).

SELECT gid,  name 
FROM road
WHERE ST_Distance(ST_Transform(ST_GeomFromText('POINT(39.240443 26.208803)',3857),4326)::geography,geom::geography) < 20;
share|improve this answer
    
Those SRIDs don't look right. –  Mike T Nov 2 at 20:24
    
@MikeT this was supposed to answer another question gis.stackexchange.com/questions/120641/…, marked as duplicate of this. I don't know how it gets posted here. If you know of a fix, you may be able to help the guy there. I'll remove this answer after that. –  tlyx Nov 2 at 20:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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