Having Trouble with ST_Buffer and ST_Union

Question

I am trying to build a coverage map, and measure how well a given set of points covers the target area.

I started by writing a query like this:

SELECT cartodb_id,
   ST_Transform(ST_Buffer(the_geom::geography,400)::geometry, 3857) as the_geom_webmercator
FROM set_of_points

That gave me a nice map of the areas covered by the set_of_points. But now I want to combine those buffers into a set of polygons where the overlapping buffers are merged into a single polygon.

What's the best way to do this?

Answer 1

You can also start from your selection and save it into a new table 'test' :

SELECT cartodb_id, ST_Transform(ST_Buffer(the_geom::geography,400)::geometry, 3857) as the_geom_webmercator INTO test FROM set_of_points

then you can try this SQL operations (which seem to be easier) :

I start with this layer :

A Get intersection union :

--create all intersections with table test
DROP TABLE IF EXISTS intersection;
SELECT a.cartodb_id as id_a, b.cartodb_id as id_b, st_intersection(a.geom, b.geom) AS geom_intersection
INTO intersection 
FROM test a, test b;

--delete auto_intersection with id (cartodb_id)
DELETE FROM intersection WHERE id_a = id_b;

-- create table intersection_union 
DROP TABLE IF EXISTS intersection_union;
SELECT st_union(geom_intersection) AS geom INTO intersection_union FROM intersection

;

result :

B Get Difference :

-- get difference 
DROP TABLE IF EXISTS difference;
SELECT st_difference(a.geom, b.geom) AS geom_difference
INTO difference 
FROM test a, intersection_union b ;

result :

C get final layer:

--create the final geom with difference and intersection_union

DROP TABLE IF EXISTS final_shape;
CREATE TABLE final_shape AS
(
select  st_union(iu.geom) AS geom FROM intersection_union iu
UNION
select d.geom_difference as geom FROM difference d
);

result :

You just get a geometry you can then update with cartodb_id

Answer 2

This is an normal cartesian product and grouping. I used Postgresql CTE to avoid retyping a lot of code in subqueries. I created the following example that can be adapted to several cases:

WITH
sample_data AS
(
  select column1 AS id, ST_Transform(ST_Buffer( column2::geography,400)::geometry, 3857) AS geom
  FROM
  (
  VALUES
   ( 1, st_makePoint( 10, 10 ) )
  ,( 2, st_makePoint( 20, 20 ) )
  ,( 3, st_makePoint( 10.001, 10 ) )
  ,( 4, st_makePoint( 10., 10.001 ) )
  ) AS tmp
)
,
data_to_union AS
(
  SELECT source.id AS id_to_unite
  FROM 
    sample_data AS source
  , sample_data AS cartesian_source
  WHERE
    st_intersects( source.geom, cartesian_source.geom )
    AND source.id != cartesian_source.id
  GROUP BY source.id
)
,
not_united_data AS
(
  SELECT id AS id_no_intersection
  FROM sample_data
  EXCEPT 
    SELECT * FROM data_to_union
)
SELECT id_no_intersection AS id, s.geom, st_geometrytype( s.geom ) AS typetest 
FROM not_united_data AS n, sample_data AS s
WHERE
  s.id = n.id_no_intersection
UNION
SELECT max( u.id_to_unite ), st_union( s.geom ) AS geom, st_geometrytype( st_union( s.geom ) ) AS typetest
FROM data_to_union AS u, sample_data AS s
WHERE
  s.id = u.id_to_unite
ORDER BY id