1

I am using the following datasets:

I want to generate 2 lists, the zip codes in or partially in each council district, and the council districts in or partially in each zip code.

So far I have tried this in Carto but I am open to other solutions.

I have found examples of intersecting a second layer of data with points, but nothing about intersecting 2 layers of data where both data sets are boundaries.

Is this possible?

1 Answer 1

1

You need to apply a SQL query similar to this one:

SELECT 
   d.coun_dist as district,  --- z.zipcode as zipcode
   array_agg(z.zipcode) as zipcodes --- array_agg(d.coun_dist) as districts
FROM 
  nyc_zipcodes z, nyc_districts d
WHERE 
  st_intersects(z.the_geom, d.the_geom)
GROUP BY 1

Result:

| district |                         zipcodes                   |
|----------|:--------------------------------------------------:|
|    48    |  ["11229","11235","11224","11210","11230","11223"] |
|    18    |  ["10460","10472","10473","10474","10462","10461"] |
|    ...   |                         ...                        |

Do not forget to replace nyc_zipcodes and nyc_districts with your dataset names. Also notice that you will need to run the query twice, replacing the columns selected with the code after the comments ---. You can learn more about PostGIS ST_Intersects operation and PostgreSQL array_agg method reading their documentation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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