Take the 2-minute tour ×

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.

share|improve this question

1 Answer 1

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.

share|improve this answer
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 at 10:39
I am sorry but i did not find a solution to the problem... –  Egidi Aug 22 at 11:23

Your Answer


