# Compute parallel lines along a center line in PostGIS

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, 2017 at 12:26
• @tilt: I have edited the question. Just added the actual output and required parallel lines in actual output. May 29, 2017 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: gis.stackexchange.com/questions/156578/…
– tilt
May 29, 2017 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. May 29, 2017 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)) Aug 30, 2017 at 6:27

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.

• 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. Sep 29, 2017 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 :( Sep 29, 2017 at 18:50