17

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?

Improve this question

2 Answers 2

Reset to default
17 
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.

Improve this answer
7
  • 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
    Commented Jul 31, 2015 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
    Commented Jul 31, 2015 at 19:59
  • 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
    Commented May 30, 2016 at 17:36
  • 3
    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
    Commented Mar 17, 2017 at 17:50
  • 1
    I made a function with it and some additions for edge case: gist.github.com/jeaneric/fbc2a0a322cd4587e2f27d14d53cd227
    – jeb
    Commented Oct 4, 2023 at 18:24
3

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 and acknowledge you have read our privacy policy.

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