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

1 Answer 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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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