Basically have 2 tables. 1 is buildings (1809555 rows called oregon). Another is flood zone (164 rows called oregonslr1). Want to see where they intersect to get number of buildings flooded. I am doing:
SELECT COUNT(*) FROM oregon, oregonslr1 WHERE ST_Intersects(oregonslr1.geom, oregon.wkb_geometry);
I want a count of the geometry in oregon that intersects with the oregonslr1 geom. Problem is it is taking forever. Ran for like 90 minutes got nowhere. The reason I switched to PostGIS was because this data was too big to work with in QGIS. I'm wondering if I am going about this wrong or need to make so optimizations because I may have to scale this for many more states and it would be incredibly time consuming at the rate it is going? Also using pgAdmin, not sure if makes slower....
Subdivided stuff with this command:
create table oregonslr2_sub as select st_subdivide(geom, 25) as geom from oregonslr2;