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.

  • try my answer below please, I was able to reproduce this case and get the expected result by using st_buffer and st_intersects.
    – kttii
    Commented Aug 31, 2016 at 14:10

2 Answers 2


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



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
    Commented Feb 1, 2013 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.vgeom)) 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
    Commented Feb 4, 2013 at 17:43
  • 1
    @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
    Commented Aug 14, 2014 at 10:39
  • I am sorry but i did not find a solution to the problem...
    – Egidi
    Commented Aug 22, 2014 at 11:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.