[postgis-users] Adding a position (direction) attribute to a point feature

[Felix Kunde]

> I have a a point feature (representing street signs).
> I would like to do the following:
> 1. add street segment name to the point feature (I will create a buffer around my street segment and perform a spatial join)
> 2. add on which side of the street segment is the point feature located (options are left, right, centre), this is where I stuck - what would be the best approach to deal with my use case

I would try ST_OffsetCurve with ST_DWithin search on both sides. Leave some space in the middle to get the center position. I've written a test query, assuming there are not outliers and all points already know to which street they belong (can be tough at intersections)

WITH offset_lines AS (
  SELECT
    *
  FROM (
    VALUES
    ('left'::text, ST_OffsetCurve('LINESTRING(0 1,7 1)'::geometry(LineString),1)),
    ('right'::text, ST_OffsetCurve('LINESTRING(0 1,7 1)'::geometry(LineString),-1))
  ) AS ol (side, geom)
), points AS (
  SELECT
    *
  FROM (
    VALUES 
    (1, 'POINT(1 0)'::geometry(Point)),
    (2, 'POINT(2 1)'::geometry(Point)),
    (3, 'POINT(3 2)'::geometry(Point)),
    (4, 'POINT(4 0)'::geometry(Point)),
    (5, 'POINT(5 1)'::geometry(Point)),
    (6, 'POINT(6 2)'::geometry(Point))
  ) AS pt (id, geom)
)
SELECT
  p.id,
  COALESCE(l.side,'center'::text) AS position
FROM
  points p
LEFT JOIN
  offset_lines l ON _ST_DWithin(l.geom, p.geom, 0.9)
;

id|position
------------
1;"right"
2;"center"
3;"left"
4;"right"
5;"center"
6;"left"

Cheers,
Felix