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 want to find out the nearest point on my road network which is a multiline string from a point with lat lon value. I have tried the following codes but it giving a error message that my road network is not a line.

SELECT ST_AsText(ST_Line_Interpolate_Point(foo.the_line, ST_Line_Locate_Point(foo.the_line, ST_GeomFromText('POINT(90.402 23.752)'))))
FROM (SELECT ST_GeomFromText(geom) As the_line from roads) As foo;

Where geom is my geometry column with multiLineString
roads is the table name
Long 90.402 and Lat 23.752
Projected Coordinate system UTM_Zone_46N

share|improve this question

3 Answers 3

up vote 4 down vote accepted

How about just using ST_ClosestPoint?

SELECT ST_AsText(ST_ClosestPoint(
    ST_GeomFromText('MULTILINESTRING ((10 10, 40 50), (20 20, 50 20, 50 60, 20 20))'),
    ST_GeomFromText('POINT(20 40)')
));

http://postgis.net/docs/manual-1.5/ST_ClosestPoint.html

share|improve this answer

I do it by finding the distance of that point (90.402 23.752) with road and arrange it in Ascending order to distance and limit it by 1.

SELECT asText(the_geom), distance(setsrid(the_geom,4326), 
       GeometryFromText('POINT(<longitude> <latitude>)', 4326)) AS dist 
FROM road 
ORDER BY dist LIMIT 1   

I agree that it is not the best or quickest (computationally) but will find the nearest point on your road network every time. Its best suited in cases where you have to find the nearest point on the road network no matter how far it is or if it is even out of your road bounds. Even that can be restricted by adding "where" clause on Distance column.

SELECT asText(the_geom), distance(setsrid(the_geom,4326), 
       GeometryFromText('POINT(<longitude> <latitude>)', 4326)) AS dist 
FROM road as r
WHERE ST_Within(setsrid(r.the_geom,4326), (Select ST_Buffer(GeometryFromText('POINT(<longitude> <latitude>)', 4326), 0.01))) 
ORDER BY dist LIMIT 1

This query will restrict the search within the buffer range i.e. 0.01

share|improve this answer

To my knowledge you cant. MULTILINESTRING does not quarantee that your LINE is collection of continous LINES. Your query works if you can change your multilines to linestrings ( you can use ST_LineMerge())

share|improve this answer

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.