I have point layer and line layer. I need to find the minimal distance between any point to any line. I'm a beginner with postgres and I'm guessing I need to start with the st_closestpoint function. My question - Is there alternative of writing all the coordinates values? the code example showing just a few of the coordinates, I Have to add many more to the function...

SELECT ST_AsText(ST_ClosestPoint(line,pt)) As closestpoint FROM (SELECT 'POINT(176684 660184)'::geometry As pt, 
    'LINESTRING (178647 661077,  178722 661022, 178745 661006, 178760 661259, 178793 660970, 178795 661249, 178797 661196)'::geometry As line
) As foo;

I think this is what you are looking for: this query will give you the shortest distance between every point + line combination.

ps* I have only tried this query on two line tables so I am unsure if this will work with points and lines. You may need to use the st_closestpoint function in place of the st_shortestline

point table = a

line table = b

select a.id,b2.id2,st_distance(a.geom,b2.geom) dist,st_shortestline(a.geom,b2.geom) geom
    from points a 
    cross join lateral
    (select b.id2,b.geom
        from lines b
        order by a.geom <-> b.geom)b2
order by dist;

In terms of writing out your point and linestring coordinates. You need to create the tables and know which columns you want there to be, then use an insert query to insert the coordinates into the respective point and line tables

|improve this answer|||||
  • ziggy, thank you. what is the 'b2' meaning? – Ofir Jul 27 '17 at 11:20
  • b2 is the name of the inner query. did this method work for you? – ziggy Jul 27 '17 at 13:38
  • No, I tried to write it in many ways but it didn't work. In th end I did it by inserting all the coordinates to the st_closest point and used another query with st_distance between the two points. – Ofir Jul 29 '17 at 20:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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