0

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_Intersection (not 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?

1

But that particular query just gets me an error: column reference the_geom is ambiguous

I think you are getting the error because you have two the_geom columns in your SELECT list. One from oakland.*, the other from states.the_geom. You can drop one of them or use an alias on one of them.

-- if I pull out states.the_geom I get the shapes, but they aren't cropped at all.

This is because you haven't actually performed the cropping. Your filter

WHERE ST_Intersects(oakland.the_geom, states.the_geom)

only filters out polygons that have no intersection with states.the_geom. To find and return the actual intersection between oakland.the_geom and states.the_geom, you will need e.g., ST_Intersection. Quoting documentation:

ST_Intersection — (T) Returns a geometry that represents the shared portion of geomA and geomB.

  • Ahh, ST_Intersection vs ST_Intersects is a great clue, but I'm still not all the way there. I'll keep trying to refine my query. – Amanda Nov 3 '17 at 3:17
1

You need to filter by the states, not the district no? You can try this query on this simple viewer where I used a world borders dataset to do the clip. You can compare with just selecting everything from oakland_districts.

select o.cartodb_id,
       ST_Intersection(
         w.the_geom_webmercator,
         o.the_geom_webmercator
       ) as the_geom_webmercator
  from oakland_districts o,
       world_borders_hd w
 where w.adm0_a3 = 'USA'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.