I have 1.3 million points and 15 thousand polygons.
I'm trying to do a spatial join so I can find out how many points intersect with each polygon. Ultimately, I'll add up the values in some columns in the point data by each polygon.
So the point data is PCD4 and the polygon data is HCBG. I've verified that both tables have the same SRID. I tried this fairly simple spatial join query but the results are a null set which makes no sense!
select geoid, id from public."PCD4" join public."HCBG" ON ST_Intersects(public."HCBG".geom, public."PCD4".geom) group by id, public."PCD4".geom, geoid`
I've tried limiting the query to a single polygon but that also nulled out. I've checked the data in QGIS and there are definitely points in the polygons and specifically in the single test polygon I tried.
If I try to do the spatial join in QGIS, it errors out.
I've tested the integrity of the polygons. They're Census Tiger files and they checked out fine.
So I'm at a loss here. I'm sure the problem is something to do with the alignment of the data, either projection or something. But I am not sure how to troubleshoot it beyond checked for the SRID.