0

I have two datasets: 1. ZipCodes and 2. Neighborhoods (think of them as like counties).

I want to join each neighborhood with which zipcodes cover it. Most neighborhoods will only be within one zipcode, but in some cases neighborhoods will straddle two. So for example:

Neighborhood 1 is inside 20001
Neighborhood 2 is inside 20002
Neighborhood 3 is inside 20001,20002

Here is what I have so far:

SELECT name, zipcode  
FROM    
neighborhood_names nn, dc_zipcode_boundries dzb
WHERE ST_Intersects(nn.the_geom, dzb.the_geom);

Note: Updated to within based on comments, now getting an answer for each neighborhood but still not able to get the Array function to respond as expected.

Matt617
  • 458
  • 2
  • 14
  • 1
    You are not missing anything as such, though you might wants to use ST_Intersects, which will also pick up those zip codes that straddle a border. If you want one row of output for each neighbourhood, then you can use the array_agg function, along with GROUP by names, which will return something like 3, [20001, 20002] for your 3rd example row, rather than two rows. – John Powell Jun 14 '15 at 08:37
  • so i tried this:
    select nn.the_geom_webmercator, name, string_Agg(zipcode, ',') as zipcodeArr from
    neighborhood_names nn, dc_zipcode_boundries dzb where ST_Intersects(nn.the_geom,dzb.the_geom)
    and generally got errros: column "nn.the_geom_webmercator" must appear in the GROUP BY clause or be used in an aggregate function what am i doing wrong?     – Matt617 Jun 14 '15 at 12:45

1 Answers1

0

I figured it out. thanks to the help from John. My statement needed a group by (whcih is what the error said, just needed some time to digest before it clicked).

the snippet below worked for anyone following

SELECT name, array_to_string(array_agg(zipcode), ',') 
FROM    
neighborhood_names nn, dc_zipcode_boundries dzb
WHERE ST_Intersects(nn.the_geom, dzb.the_geom)
group by name
Matt617
  • 458
  • 2
  • 14