How to find closest point on multi line String from a point in PostGIS

Question

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

Answer 1

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

Answer 2

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())

Answer 3

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

Answer 4

with ids  as (
SELECT "PrimarykeyColumn", ST_Distance(
            ST_Transform(ST_GeomFromText('POINT(84.23171701760256 18.79346923714023)',4326),26986),
            ST_Transform(ST_GeomFromText(st_astext(the_geom), 4326),26986)
        )  from  TableName  order by st_distance limit 1

)
SELECT 
    ST_AsText(ST_ClosestPoint(the_geom ,'POINT(84.23171701760256 18.79346923714023)'::geometry)) As cp_line_pt
FROM  TableName where "PrimarykeyColumn" = (select "PrimarykeyColumn" from ids)

This way you will avoid confusion between different SRID ,and you will get the closet line Regardless of buffer size