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

After getting some help, I ran this query:

     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

  • 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 5 '15 at 20:58
  • @GetSpatial, not that I know of. The Dallas DMA looks pretty normal to me – Minh Mai Oct 6 '15 at 2:20

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

  • I tried using St_CoveredBy and the results still ended up being exactly the same. Any other thoughts? – Minh Mai Oct 6 '15 at 2: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. – LR1234567 Oct 6 '15 at 5:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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