3

I have two shapefiles. One is the DMA and another of states

I know that some DMAs, such as Chicago will overlap between multiple states, for instance the Denver DMA overlaps states outside of CO and into UT.

Is there a way to recreate DMAs by states, so it would be something like Denver DMA - CO and Denver DMA - UT (to differeniate which states the DMA belong to)? Basically I'm thinking of a way to draw the state boundaries into the DMA maps and separating it accordingly.

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
Minh
  • 611
  • 2
  • 9
  • 16

2 Answers2

5

If you want to split the DMA's along the state borders you can do something like that:

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);

before: enter image description here

after: enter image description here

Useful links:

PostGIS Union Between Two Tables, Preserving Attributes

Acquiring ArcGIS-like speed in Postgis

Update1:

As answer to the comment below (..."white blanks in some parts of the map"...)

You can also filter your result as a next step to get only Polygons back. With this specific datasets here there are also some points generated that seem to make problems:

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'
;

before filtering(extracted single geometries): enter image description here

after filtering: enter image description here

Thomas B
  • 8,807
  • 1
  • 21
  • 62
  • thanks @Thomas B .This is exactly what I'm looking for, the only thing I see weird is that there are white blanks in some parts of the map (for example, it looks like Dallas is missing). I followd your query, adjusting for names 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 us_states b join us_dma a on st_intersects(a.geom, b.geom); Any thoughts? – Minh Oct 05 '15 at 14:48
  • just updated the answer above to show a solution ;) – Thomas B Oct 06 '15 at 07:54
  • I have a quick question. I noticed that in your query, you have multiple Atlanta, GA for dma_1 and Alabama for state_name. Is there a way to join them all together into one geometry? – Minh Oct 16 '15 at 16:34
3

Depending on the exact result/output you want, you will be using an identity or union overlay.

No matter which one you use, you will have to peruse the resulting output table to see what happened and how you can use it to identify which DMAs are in which states, but that information will definitely be there.

Here is the documentation for doing a union or intersect in PostGIS:

http://postgis.net/docs/ST_Union.html

http://postgis.net/docs/ST_Intersection.html

Thomas
  • 1,477
  • 8
  • 19
  • I've tried doing SELECT ST_Union(us_dma.geom, us_states.geom)as newgeom from us_dma, us_states; but no luck. – Minh Oct 02 '15 at 21:53
  • Are the layers in the same projection? What happens when you have 'no luck?' – Thomas Oct 02 '15 at 22:45
  • Yes I have confirmed that they are. When I try it, it appears that PostGIS is making a combination of every DMA and State rather than just the DMAs within each state. For reference I updated my query to: SELECT us_dma.gid AS dmaid, us_states.name, us_dma.dma_1, us_states.gid AS statesid, ST_UNION(us_dma.geom, us_states.geom) AS newgeom FROM us_dma, us_states; – Minh Oct 03 '15 at 03:25