I have street (road center line) and building polygon tables in my PostgreSQL database. The sample scenario is as follows:
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.
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?