0

I have been able to use PostGIS and Tiger successfully to geocode addresses that are more formally typed out, however when I try with something like

"Queens, NY" or "Brooklyn" it doesn't work.

SELECT * FROM geocode('brooklyn, ny');

I have seen this done, however, so I wanted to ask if anyone had some ideas on how I could do this? I would ideally like to get the first point or something of that sort to just get an approximate area of Brooklyn (maybe the middle, etc).

1

Unfortunately tiger doesn't provide borough data. The only way it knows a place is in Brooklyn is by the zip code and even then it doesn't really know the place as brooklyn, just NY,NY.

Now what you could do is add entries to the tiger_data.ny_place tables.

Note how for example:

SELECT pprint_addy(addy), ST_AsText(geomout) FROM geocode('Bronxville, NY');

Gives you an answer.

You could get borough boundaries from here:

https://data.cityofnewyork.us/City-Government/Borough-Boundaries/tqmj-j8zm

I'd keep the data separate though just so in case you need to reload NY you don't mess up your extra data. Then create a table:

    CREATE TABLE tiger_data.extra_ny_place_borough( 
      CONSTRAINT uidx_extra_ny_place_boroughs_gid UNIQUE (gid),  
      CONSTRAINT chk_statefp CHECK (statefp = '36') ) 
    INHERITS (tiger.place);

Then the nycboroughs dataset you downloaded, load in a staging table say tiger_staging.nyc_boroughs.

Then do an insert:

  INSERT INTO 
tiger_data.extra_ny_place_borough(statefp, plcidfp, placefp, 
   name, namelsad, the_geom)
SELECT '36', 
       '36B' || lpad(boro_code::integer::text,4,'0'), 
       'B' || lpad(boro_code::integer::text,4,'0'), 
        boro_name, boro_name, ST_SetSRID(geom,4269)
FROM tiger_staging.ny_boroughs;

Now when you do:

SELECT pprint_addy(addy), ST_AsText(geomout), rating 
  FROM geocode('Brooklyn, NY',1);

You'll get an answer.

 pprint_addy  |                 st_astext                 | rating
--------------+-------------------------------------------+--------
 Brooklyn, NY | POINT(-73.9476763575345 40.6447341320196) |    100

(1 row)

The rating will be 100 which means its a pretty sucky answer, but best you can hope for given your address is not specific enough. That should be approximately the centroid of brooklyn.

  • I just realized all of these are in tiger_data.ny_cousub. Guess I'm not using that table in checking. So in theory the above work around should not be necessary. I've ticketed this issue and will try to resolve in upcoming PostGIS 2.4 trac.osgeo.org/postgis/ticket/3791 and backport to PostGIS 2.3.4 if it's not too major of a change. I guess I didn't notice because for MA neighborhoods (which is area I'm mostly concerned with) they are not in tiger, so I always have to do a work-around as stated above. – LR1234567 Jul 17 '17 at 19:32
  • this is extremely helpful, thanks! I see it in cousub. Now to get a census tract, I did a query like this. You think this is a good idea? SELECT get_tract(the_geom, 'tract_id') AS tract_id FROM cousub WHERE name = 'Brooklyn' LIMIT 1 – Mike-751031 Jul 18 '17 at 14:19
  • the only problem with my comment above, I guess, is you couldn't match on "Brooklyn, NY". – Mike-751031 Jul 18 '17 at 14:50

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.