I'm trying to find some wetland polygons that intersect a single US state, or a list of several states using SQL. I'm aware of how to find all polygons that intersect all of the geometries of another table, but how do I check on only a single geometry, or list of geometries in order to avoid creating layers for each of these subselections?
I have an unfortunate way of doing this with the following query, where the long multipolygon is a copy+paste geometry for the state of Texas:
SELECT wetland_id, acres
FROM wetlands
WHERE ST_Intersects(geom, st_geomfromtext('MULTIPOLYGON(((29334...........)))', 26914));
But I can't imagine this is the best way to do this. How do I select from the other layer called 'states_26914' to get just texas and then find wetlands that intersect Texas. This table has a 'name' column that can be queried. I'd rather not import another table with the lone row as Texas or any other state.
After I figure that out, I'd like a way to select wetlands that intersect several states: like Texas, Oklahoma, and Kansas, for example. I think the first question will make it easier for me to figure out the answer to the list of states question.
You can join the tables and include a condition, like with any other table (i.e. the fact that they contain geometries can be abstracted)
SELECT state.name, wetland.ID, wetland.geometry
FROM wetland
JOIN state
ON st_intersects(state.geometry, wetland,geometry)
AND state.name in ('abc','xyz');