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 ASSELECT 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 geomFROM tl_2015_us_state_4326 bJOIN dma_boundary aON ST_Intersects(a.geom, b.geom);CREATE TABLE boundaries_polygons ASSELECT *FROM (SELECT id, state_name, dma_1, (ST_Dump(b.geom)).geomfrom boundaries b) as tempWHERE 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.
أكثر...
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 ASSELECT 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 geomFROM tl_2015_us_state_4326 bJOIN dma_boundary aON ST_Intersects(a.geom, b.geom);CREATE TABLE boundaries_polygons ASSELECT *FROM (SELECT id, state_name, dma_1, (ST_Dump(b.geom)).geomfrom boundaries b) as tempWHERE 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.
أكثر...