I'm trying to clip a map of Oakland City Council Districts to the shoreline. Carto even has a nice walk through of how one could (theoretically) accomplish this, but I feel like I'm missing something basic here.
If I have a map of US States, and a map of Oakland city council districts, I want to do something like this:
SELECT oakland.*, states.the_geom FROM oakland_city_council_districts AS oakland, US_states_shapes AS states WHERE ST_Intersects(oakland.the_geom, states.the_geom)
But that particular query just gets me an error:
column reference the_geom is ambiguous -- if I pull out
states.the_geom I get the shapes, but they aren't cropped at all.
Realizing, with help in the answers here, that I should be using
ST_Intersects) I revised my query to:
SELECT oakland.councilmem, oakland.dist, oakland.fullname, ST_Intersection(oakland.the_geom, states.the_geom) AS the_geom FROM oakland_city_council_districts AS oakland, US_states_shapes AS states WHERE oakland.dist = 3
But that query finds nothing.
So how can I use the single California polygon in my
US_states_shapes table to trim the Oakland City Council Districts to the coastline?