How to create non-overlapping street polygons from centerlines?

Question

I want to create non-overlapping polygon layer from center line by applying the buffer to line layer in postgis. I am able to create polygon layer but how to avoid or resolve overlapped polygons.

select gid,st_multi(ST_union(ST_BUFFER(geom,10,'endcap=flat join=round'))) from roads group by gid

Using this query I am able to create polygon which is overlap at intersection
Due to 10 meter buffer distance polygon overlaps how to avoid or resolve this
I need expected output as non overlapping polygon with "gid" attribute. Any suggestion will always welcome. Thanks,Amit

Answer 1

If you don't have primary-secondary attribution to help you, you can still implement some basic logic to decide who's on the losing end of ST_Difference. For example, at a T-shaped intersection, you might specify that the road that passes through the intersection will retain its buffer, while the road that ends at the intersection will have its buffer clipped.

To start, it would be helpful to have a function to express the "passes through tee" relationship. We can say that A passes through the A-B tee if:

• A intersects B
• The intersection of A and B is an endpoint of B
• The intersection of A and B is not an endpoint of A

Turning this logic into a function:

CREATE OR REPLACE FUNCTION passes_through_tee(geom_a geometry, geom_b geometry)
RETURNS boolean AS
$$
SELECT  ST_Contains(endpoints(geom_b), ST_Intersection(geom_a, geom_b)) 
AND NOT ST_Contains(endpoints(geom_a), ST_Intersection(geom_a, geom_b))
$$ language 'sql';

However, endpoints isn't a PostGIS function, so we need to write it:

CREATE OR REPLACE FUNCTION endpoints(g geometry)
RETURNS geometry AS
$$ 
SELECT ST_Union(endpoints) FROM
  (SELECT ST_Collect(ST_EndPoint(geom), ST_StartPoint(geom)) AS endpoints 
     FROM 
      (SELECT (ST_Dump(g)).geom) dump) subquery
$$ language 'sql';

Note that the ST_Dump subquery allows the function to work on MultiLineString geometries.

To apply these functions to our roads, we can first save the buffered geometries in a temporary table:

CREATE TABLE buffers AS 
  SELECT gid, geom, ST_Buffer(geom, 0.0001, 'endcap=flat join=round') as buffered_geom
  FROM roads;
CREATE INDEX ON buffers USING GIST(geom);

Then, to pull out the clipped buffers:

SELECT gid, ST_Difference(buffered_geom, 
  COALESCE(
    (SELECT ST_Union(buffered_geom) FROM buffers b 
      WHERE ST_Intersects(a.geom, b.geom)
        AND a.gid != b.gid
         AND CASE WHEN passes_through_tee(a.geom, b.geom) THEN FALSE 
                  WHEN passes_through_tee(b.geom, a.geom) THEN TRUE                  
                  ELSE a.gid < b.gid END
     ), 'POLYGON EMPTY'::geometry)) 
FROM buffers a

Some example output from this query is below:

A couple of notes on the above:

• If we don't have a T-intersection (think end-to-end or four-way intersections) we fall back on gid to pick a winner.
• The COALESCE function substitutes POLYGON EMPTY for a null value, so that we get our original geometry back if there is nothing to clip against.
• Polygons will still overlap in cases where the buffers of two roads intersect but the roads themselves do not. This behaviour could be changed, but substituting buffered_geom for geom in the ST_Intersects call.

Answer 2

Do your centerlines have an attribute such as Primary road, Secondary road? If so, the Primary Road (the road almost horizontal)) buffer looks correct. You only need to erase or apply St_Difference to the Secondary buffers. This will remove the portion of the Secondary buffers that overlap the Primary buffer. You probably have intersections of 2 secondary roads, no primary. I'm unsure what logic you might use in this case.

Answer 3

As @klewis say, you can use an attribute such as "road order". When not have it, you can create with the method of this answer (ncolors attribute) and calculate order by "river order" method,