Compute parallel lines along a center line in PostGIS

Question

I have street (road center line) and building polygon tables in my PostgreSQL database. The sample scenario is as follows:

Problem:

I need to compute parallel lines along street at the intersection of 50 meters buffer around street and nearest building polygon on both sides. The desired output scenario is:

What I have tried:

My approach was:

 1) Generate 50m buffer around street layer
 2) get the intersection of buffer and polygons
 3) Compute the distance
 4) Draw offset curves (parallel lines) at both sides of street layer
 5) Merge both curves to get parallel lines at the intersection

Here is my attempt:

    WITH street_buffer AS (
     SELECT
      street.gid street_id,
      street.geom street_geom,
      ST_Buffer(street.geom, 50, 'endcap=square join=round') geom1,
      building.geom  geom2  
     FROM street
     LEFT JOIN building on ST_DWithin(building.geom, street.geom, 50)
     ORDER BY street_id
    ),
    selected_buildings AS (
     SELECT
      street_id,
      street_geom,
      ST_Intersection(geom1, geom2) geom
     FROM street_buffer
    ),
    distance AS (
     SELECT 
      street_id,
      street_geom,
      ST_Distance(street_geom, geom) as dist
     FROM selected_buildings 
    ),
    curves AS (
     SELECT 
      street_id,
      ST_OffsetCurve(ST_LineMerge(street_geom), dist) as curve1,
      ST_OffsetCurve(ST_LineMerge(street_geom), -dist) as curve2
     FROM distance
     ORDER BY street_id
    )
    SELECT 
     street_id,
     ST_Union(curve1, curve2) geom 
    FROM curves
    ORDER BY street_id

The problem with above code is that it doesn't return parallel lines according to the desired output i.e., parallel lines at all polygons intersections are being generated instead of at the intersection of nearest polygons.

EDIT_1:

The actual output of above code is:

While, in the above output only yellow parallel lines (offset curves to nearest polygons on both sides of street) are required:

Can anyone suggest me how to get the desired output?

Can you add an image of the actual output as well? It helps in understanding the problem. — tilt, May 29 '17 at 12:26
@tilt: I have edited the question. Just added the actual output and required parallel lines in actual output. — khajlk, May 29 '17 at 12:35
The issue is more complex than you think. Thirst you have to find out on which side of the street the houses are. Only then you can find the closest on both sides. Here is a post that has example code for finding the correct side: https://gis.stackexchange.com/questions/156578/how-to-detect-if-point-is-on-the-left-or-right-side-of-the-line-in-postgis — tilt, May 29 '17 at 13:12
In reality, there can be cases when buildings are only on one side (I would call it exceptions). I could modify my code to handle exceptions once I am able to reach my desired output. In above, you still see building polygons on both sides. At this point, my requirement is to get parallel lines on both sides of the street (like the ones I showed in figure). Regarding your link, I could use the example code to improve my above code probably later. — khajlk, May 29 '17 at 14:28
One thing I see is that the buffer is redundant. You can just use stdwithin and use 50 as the distance. (Select streets, buildings where st_dwithin(streets, buildings, 50)) — jbalk, Aug 30 '17 at 06:27

score 1 · Answer 1 · answered Sep 29 '17 at 13:21

1

If you modified the distance CTE to be as follows:

distance AS (
 SELECT 
  street_id,
  street_geom,
  MIN(ST_Distance(street_geom, geom)) as dist
 FROM selected_buildings
 GROUP BY street_id, street_geom
)

then only the shortest distance would be returned for each street, and one pair of offset lines would be generated at that distance.

answered Sep 29 '17 at 13:21

Andy Harfoot

3,367
1
24
33

Thank you for the suggestion. I will test it and see if it gives expected result. I have been digging around to get this issue resolved. I discovered one crazy idea: To start with 1 m buffer around street and programmatically increment the buffer AND search for buildings on both sides until the count of buildings is 2, then return this buffer distance as street width. The ultimate aim of all above exercise. – khajlk Sep 29 '17 at 14:11
It's true that only one pair of offset is generated using your suggestion. However, the yellow paralle lines are shown above are still missing :( – khajlk Sep 29 '17 at 18:50

Compute parallel lines along a center line in PostGIS

1 Answers1