1

I'm trying to join two PostGIS table, one of DMAs and another of states.

After getting some help, I ran this query: 

CREATE TABLE combined 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)

However, the results I get have some gaps, for instance, it looks like the Dallas DMA in Texas is completely missing. I'm not familiar with PostGIS but I am unable to figure why the query will cause polygons to be missing. A picture is below:

Result of query

Community
  • 1
Minh
  • 611
  • 2
  • 9
  • 16
  • 1
    My initial thought would be to check those particular polygons for any inconsistencies. For example, are there loops or something in the polygons that cause them to error? – Get Spatial Oct 05 '15 at 20:58
  • @GetSpatial, not that I know of. The Dallas DMA looks pretty normal to me – Minh Oct 06 '15 at 02:20

1 Answers1

1

You might want to change your ST_Within(a.geom,b.geom) to ST_CoveredBy(a.geom, b.geom). If a.geom shares a border with b.geom, then ST_Within will return false, but ST_CoveredBy will return true.

Still the best article explaining this subtlety is this - http://lin-ear-th-inking.blogspot.com/2007/06/subtleties-of-ogc-covers-spatial.html

Regina Obe
  • 10,503
  • 1
  • 23
  • 28
  • I tried using St_CoveredBy and the results still ended up being exactly the same. Any other thoughts? – Minh Oct 06 '15 at 02:19
  • 1
    Did you verify they are valid with ST_IsValid? If they are not a lot of functions such as ST_Intersects, ST_CoveredBy will just return false. – Regina Obe Oct 06 '15 at 05:48