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?

select (ST_Azimuth(h.vec) - ST_Azimuth(h.seg))
from (
        ST_MakeLine(cp.p, point.geom) vec,
                ST_LineLocatePoint(line.geom, cp.p) * 1.01)
        ) seg
        from (
                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

    --original point
    --find a point next to the closest point on line
         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.

| improve this answer | |
  • 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

An alternative method if you are using version 2.5.0 or later of PostGIS, you can supply a "side" argument to ST_Buffer. So an alternative method to the ST_LineInterpolatePoint with an scaled projection value is to use

ST_Contains(ST_Buffer(line.geom, 100, 'side=left'), point.geom)

One downside of this is the need to give a distance(in this case 100) to build the buffer on the left side of the LineString but in my case I already have a threshold of how close point.geom needs to be to be associated with line.geom.

| improve this answer | |

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.