12

I have a linestring table and a point table in postgis.

I know the closest line to any given point. What I need to know is on which "side" of that line is the point. I guess I have to do that by creating a perpendicular line from given point to the line (closest point on the line) and then compare the coordinates, but I don't know exactly how to do that, and if it's the proper way, since line changes it's direction.

I've made a picture to illustrate my task.

enter image description here

The line itself is black, its direction is shown with green arrows. I need to add a "side" column to the point table, so that red points should have value "right" and blue points should have value "left".

Can someone give an SQL code example of calculating a "side" value of a point?

10
select (ST_Azimuth(h.vec) - ST_Azimuth(h.seg))
from (
    select 
        ST_MakeLine(cp.p, point.geom) vec,
        ST_MakeLine(cp.p, 
            ST_LineInterpolatePoint(
                line.geom, 
                ST_LineLocatePoint(line.geom, cp.p) * 1.01)
        ) seg
        from (
            select 
                ST_ClosestPoint(line.geom, point.geom)
        ) p as cp
    ) as h

So the idea is to calculate angle between closest line segment, and vector from closest point on the line to your point.

get a closest point on a line

select ST_ClosestPoint(line.geom, point.geom)

create the vector from closest point to your point

ST_MakeLine(cp.p, point.geom) vec

create a vector among your line

ST_MakeLine(
    --original point
    cp.p, 
    --find a point next to the closest point on line
    ST_LineInterpolatePoint(line.geom, 
         ST_LineLocatePoint(line.geom, cp.p) * 1.01)) seg

get the difference between directions

ST_Azimuth(h.vec) - ST_Azimuth(h.seg)

So right and left will be greater than zero and lower than zero.

  • Thanks, it seems like a good solution, but I don't like the * 1.01 part. Can the next closest point of the line be selected to make this query more reliable? – mofoyoda Jul 31 '15 at 16:06
  • I was thinking about to get the closest segment but there us no such function. But this is more reliable solution because ST_LineInterpolate is directed so you will get next point onwards the line direction, not just nearest. It's possible to get actual next node, but it would urge you to iterate over all nodes and finding out are they next along the line or before the closest point on line. – dmitry.v.kiselev Jul 31 '15 at 19:59
  • Hi Dmitry. Will this work for a point that is beyond the line if you know what I mean. For example, the top left most red dot, if it were 1cm higher. In that case, the closestPoint and the point will not make a right angle with the original line. Will this algorithm work in that case? – Jenia Ivanov May 29 '16 at 19:32
  • 3
    ST_Azimuth(h.vec) - is a pseudocode. h.vec and h.seg are lines, so to be precise it should be something like ST_Azimuth(ST_StartPoint(h.vec), ST_EndPoint(h.vec)) – dmitry.v.kiselev May 30 '16 at 17:36
  • 2
    the above solution does not seem to work in cases where the line is east-west having a bearing of exactly 90 degrees for some reason. – user7543032 Mar 17 '17 at 17:50

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.