PostGIS Spatial Join not working

Question

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

Answer 1

The query you created should work without any issues, as shown by doing the following simple query which uses known geometries.

-- Use a CTE to create some known data
WITH countyparts2008_with_cd AS (
  SELECT *
  FROM (VALUES
    (1,1,1,'srid=4326;MULTIPOLYGON(((0 0, 10 0, 10 10, 0 10, 0 0)))'::Geometry)
    )A(statefp, countyfp, cd110fp, geom)
  ),
  us_block_2000_with_data AS (
  SELECT *
  FROM (VALUES
    (10,'srid=4326;MULTIPOLYGON(((0 0, 5 0, 5 5, 0 5, 0 0)))'::Geometry)
    ,(10,'srid=4326;MULTIPOLYGON(((5 5, 10 5, 10 10, 5 10, 5 5)))'::Geometry)
    )A(pop00,geom)
  )
-- The select statement from the create table query
  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

The issue you are having is that the us_block table geometries are not in the same projection as the country file, which is in WGS84. I do not know which projection the us_block table is in.

There are a couple of questions that cover reprojecting the geometries in the table, here and here. You will need to determine what the projection is.

You could also use ST_Transform within your query. You will need to get the SRID set correctly against the geometries in the us_block table.

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, ST_Transform(bd.geom,4326))
  GROUP BY cp.statefp, cp.countyfp, cp.cd110fp
)

Answer 2

Did you try to join the tables with a CROSS JOIN and a WHERE?

Something like:

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 CROSS JOIN us_block_2000_with_data AS bd
  WHERE ST_CONTAINS(cp.geom, bd.geom)
  GROUP BY cp.statefp, cp.countyfp, cp.cd110fp
)

I learned it that way in school. I have never seen, in my limited experience with PostGIS, spatial joins the way you do it. This syntax is very interesting though and I will try it. CROSS JOIN operations are very time consuming for the server. Specially when you cross join hundred of thousands of rows from each relation!!

Answer 3

I would try to change the spatial predicate to ST_Intersects (just for sure). ST_Contains(cp.geom, ST_Buffer(bd.geom, -0.01)) might also help, I suppose the two datasets don't match on the edges.