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

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

-

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

-

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

-

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

-