# How to obtain closest point of point over polylines layer in PostGIS 1.4 (without st_ClosestPoint)?

I´ve got a polylines layer (pipes) and a points layer (valves) and In Postgis 1.4 I would like to obtain the closest points but exactly on the closest polyline. In other words, i need to locate each valve over the closest point on a pipe but taking into account that there is not only one polyline but many and that i am using postgis 1.4 and St_ClosestPoint() that could help to solve this problem doesn't still exist on my postgis version.

If theres a way to add the St_ClosestPoint() function to my Postgis 1.4 could also help but i think i need to create a new function for this. Thanks in advance.

-

You might want to try out in Qgis the Hub-distance function of the plugin MMQGIS. (MMQGIS-Create-Hub-Distance). Measures the distance and draws a line from a point layer to the closest point on a polygon-layer, (I didn't check with a line layer) While not a postgis-solution, it might still work for you.

-
Thanks for your answer. I tested the MMQGIS-Create-Hub-Distance and and with polylines it doesn't work properly as it creates lines to the nearest polyline centroids... – Egidi Feb 1 '13 at 17:16
I paste the final query in Postgis that finally worked for me: SELECT ST_Line_Interpolate_Point(t2."GEOMETRY",ST_Line_Locate_Point(t2."GEOMETRY",C1.vg‌​eom)) AS valv_geom, t2."GEOMETRY" AS tube_geom FROM (SELECT v.id AS vid, st_centroid(v."GEOMETRY") AS vgeom, MIN(st_distance(st_centroid(v."GEOMETRY"),t."GEOMETRY")) AS min_dist FROM VALVES v, TUBES t WHERE v."GEOMETRY" && st_expand(t."GEOMETRY",50) GROUP BY v.id, v."GEOMETRY")AS C1 ,TUBES t2 WHERE st_distance(vgeom,t2."GEOMETRY")=min_dist ORDER BY C1.vid – Egidi Feb 4 '13 at 17:43
@Aitor, perhaps you can post your solution as an answer for the benefit of others...even though this post is a year and a half old now! – Joseph Aug 14 '14 at 10:39
I am sorry but i did not find a solution to the problem... – Egidi Aug 22 '14 at 11:23

Have you tried st_intersects? Below is a statement using a combination of st_intersects and st_buffer... st_buffer is providing for some error in alignment between the valve and the tube in my sample data. If you really want it precise then remove the st_buffer function.

select v.name from valves as v where st_intersects(v.geom ,st_buffer((select t.geom from tubes as t),.00005)) order by 1


http://postgis.net/docs/manual-1.4/ch08.html

-