2

I have two shapefiles. One is the DMA and another of states. What I'm trying to do is to draw a state boundary on DMAs (which can cross state boundaries).

I've asked this question and I recieved an answer, which solve the problem but then I noticed there are duplicates in the polygons. Below is the query that creates the state line boundaries.

CREATE SEQUENCE polyseq_1;


CREATE TABLE boundaries AS
SELECT
  nextval('polyseq_1') AS id,
  b.name as state_name,
  a.dma_1 as dma_1,
  CASE 
     WHEN ST_Within(a.geom,b.geom) 
     THEN a.geom
     ELSE ST_Multi(ST_Intersection(a.geom,b.geom)) 
  END AS geom
FROM tl_2015_us_state_4326 b
JOIN dma_boundary a
ON ST_Intersects(a.geom, b.geom);

CREATE TABLE boundaries_polygons AS
SELECT *
FROM (SELECT id, state_name, dma_1, (ST_Dump(b.geom)).geom
from boundaries b) as temp
WHERE ST_GeometryType(temp.geom) = 'ST_Polygon'
;

Below is the results, you can see that there are duplicates of the same DMA in the same state. Ideally, I'd like to merge all these polygons together into one by their name and state. For instance, combine all the Washington-DC DMA for Washignton, then all the Washington-DC DMAs for Virgina, and all the Washington-DC DMAs for Maryland into 3 distinct parts.

enter image description here

Community
  • 1
Minh
  • 611
  • 2
  • 9
  • 16

1 Answers1

2

Sounds like a simple aggregate operation. You can use ST_Union, grouping on state and name:

SELECT ST_Multi(ST_Union(geom)) FROM table GROUP BY state, name;

I use ST_Multi here because some unions may produce a single geometry, some may produce multis. You probably want a consistent output type, so you wrap the geometries into a multi using this call.

Rob Skelly
  • 3,101
  • 15
  • 14
  • Do you know if I should do it when creating the boundaries or boundaries_polygons table? Or is it a new tbale in itself? – Minh Oct 21 '15 at 22:17
  • Looking again at the code you posted: isn't the content of boundaries already what you want? It looks like the content of boundaries_polygons is just to remove degeneracies and break up multi* geometries. Anyway, the query I gave would run on boundaries_polygons and should result in a clean version of what's in boundaries (if I'm reading it correctly.) – Rob Skelly Oct 21 '15 at 23:28
  • The boundaries resulted in weird empty spaces, so the boundaries_polygons fixes that, but yields duplicates. You can take a look at the answer here. I'm sure there is an easier answer but I'm not sure what is it – Minh Oct 22 '15 at 17:33
  • So the answer to your question is to run it on boundaries_polygons to produce grouped output. – Rob Skelly Oct 22 '15 at 20:44