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?

  • Are you just looking for an example? Have you seen postgis.refractions.net/docs/ST_Collect.html and postgis.refractions.net/docs/ST_Union.html ?
    – BradHards
    Commented Jan 21, 2013 at 21:32
  • I have seen those. I tried adding ST_Union as another call in the select after applying ST_Transform, but that didn't seem to do anything. So I'm wondering if there's something I'm missing about the types?
    – 2daaa
    Commented Jan 21, 2013 at 22:14
  • Actually I just figured out my problem. I'm used to MySQL, and group by's have a very different behavior than in PostgreSQL.
    – 2daaa
    Commented Jan 21, 2013 at 22:42
  • 6
    If you come up with a statement that works, please post the code - I'm always interested in seeing the creative command chains people come up with.
    – L_Holcombe
    Commented Jan 21, 2013 at 23:09

2 Answers 2


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 :

enter image description here

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 :

enter image description here

B Get Difference :

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

result :

enter image description here

C get final layer:

--create the final geom with difference and intersection_union

CREATE TABLE final_shape AS
select  st_union(iu.geom) AS geom FROM intersection_union iu
select d.geom_difference as geom FROM difference d

result :

enter image description here

You just get a geometry you can then update with cartodb_id


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:

sample_data AS
  select column1 AS id, ST_Transform(ST_Buffer( column2::geography,400)::geometry, 3857) AS geom
   ( 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
    sample_data AS source
  , sample_data AS cartesian_source
    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
    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
  s.id = n.id_no_intersection
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
  s.id = u.id_to_unite

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.