Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
    
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 Jan 21 '13 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? –  Ranjit Jan 21 '13 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. –  Ranjit Jan 21 '13 at 22:42
5  
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 Jan 21 '13 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 
DROP TABLE IF EXISTS 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

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 :

enter image description here

You just get a geometry you can then update with cartodb_id

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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