I am trying to do a spatial join on two tables, the join runs successfully but there's no data in the new table. what am i doing wrong?
CREATE TABLE us_countyparts_2008_with_count_v5 AS(
SELECT cp.statefp, cp.countyfp, cp.cd110fp, sum(bd.pop00)
FROM countyparts2008_with_cd AS cp
JOIN us_block_2000_with_data AS bd
ON ST_Contains(cp.geom, bd.geom)
GROUP BY cp.statefp, cp.countyfp, cp.cd110fp
)
I did IsValid on both and they both returned as valid geometries
This is some of the data from countyparts
and this is some data from us_blocks
I'm pretty sure the error is something to do with the extent this is the extent for the us_block file
"BOX(-2356113.74289801 -783964.164622752,1044933.24146018 845925.228214008)"
and this is the extent for the county file "BOX(-124.041983 24.3963080000001,-69.3723069999999 48.540437)"
If this is the problem then how do I fix the extent for the us_block file
SELECT *, ST_AsEWKT(geom) FROM countyparts2008_with_cd LIMIT 2
show? What doesSELECT *, ST_AsEWKT(geom) FROM us_block_2000_with_data LIMIT 2
show? Do you possibly have any invalid geometries in your data?