PostGIS 2.0 Tiger Geocoder Cheat Sheet

New in this release ¹ Enhanced in this release²

Tiger Geocoder
Drop_Indexes_Generate_Script¹ (param_schema=tiger_data) Generates a script that drops all non-primary key and non-unique indexes on tiger schema and user specified schema. Defaults schema to tiger_data if no schema is specified.
Drop_State_Tables_Generate_Script¹ (address, param_schema=tiger_data) Generates a script that drops all tables in the specified schema that are prefixed with the state abbreviation. Defaults schema to tiger_data if no schema is specified.
Geocode² Takes in an address as a string (or other normalized address) and outputs a set of possible locations which include a point geometry in NAD 83 long lat, a normalized address for each, and the rating. The lower the rating the more likely the match. Results are sorted by lowest rating first. Can optionally pass in maximum results, defaults to 10, and restrict_region (defaults to NULL) address, max_results=10, restrict_region=NULL, in_addy, max_results=10, restrict_region=NULL,
Geocode_Intersection¹ ( roadway1, roadway2, in_state, in_city, in_zip, max_results=10, ) Takes in 2 streets that intersect and a state, city, zip, and outputs a set of possible locations on the first cross street that is at the intersection, also includes a point geometry in NAD 83 long lat, a normalized address for each location, and the rating. The lower the rating the more likely the match. Results are sorted by lowest rating first. Can optionally pass in maximum results, defaults to 10
Install_Missing_Indexes¹ () Finds all tables with key columns used in geocoder joins and filter conditions that are missing used indexes on those columns and will add them.
Loader_Generate_Script¹ (param_states, os) Generates a shell script for the specified platform for the specified states that will download Tiger data, stage and load into tiger_data schema. Each state script is returned as a separate record. Latest version supports Tiger 2010 structural changes.
Missing_Indexes_Generate_Script¹ () Finds all tables with key columns used in geocoder joins that are missing indexes on those columns and will output the SQL DDL to define the index for those tables.
Normalize_Address (in_address) Given a textual street address, returns a composite norm_addy type that has road suffix, prefix and type standardized, street, streetname etc. broken into separate fields. This function will work with just the lookup data packaged with the tiger_geocoder (no need for tiger census data).
Pprint_Addy (in_addy) Given a norm_addy composite type object, returns a pretty print representation of it. Usually used in conjunction with normalize_address.
Reverse_Geocode¹ (pt, include_strnum_range=false, ) Takes a geometry point in a known spatial ref sys and returns a record containing an array of theoretically possible addresses and an array of cross streets. If include_strnum_range = true, includes the street range in the cross streets.
Topology_Load_Tiger¹ (topo_name, region_type, region_id) Loads a defined region of tiger data into a PostGIS Topology and transforming the tiger data to spatial reference of the topology and snapping to the precision tolerance of the topology.

Tiger Geocoder Examples
Drop_Indexes_Generate_Script SELECT drop_indexes_generate_script() As actionsql; actionsql --------------------------------------------------------- DROP INDEX tiger.idx_tiger_countysub_lookup_lower_name; DROP INDEX tiger.idx_tiger_edges_countyfp; DROP INDEX tiger.idx_tiger_faces_countyfp; DROP INDEX tiger.tiger_place_the_geom_gist; DROP INDEX tiger.tiger_edges_the_geom_gist; DROP INDEX tiger.tiger_state_the_geom_gist; DROP INDEX tiger.idx_tiger_addr_least_address; DROP INDEX tiger.idx_tiger_addr_tlid; DROP INDEX tiger.idx_tiger_addr_zip; DROP INDEX tiger.idx_tiger_county_countyfp; DROP INDEX tiger.idx_tiger_county_lookup_lower_name; DROP INDEX tiger.idx_tiger_county_lookup_snd_name; DROP INDEX tiger.idx_tiger_county_lower_name; DROP INDEX tiger.idx_tiger_county_snd_name; DROP INDEX tiger.idx_tiger_county_the_geom_gist; DROP INDEX tiger.idx_tiger_countysub_lookup_snd_name; DROP INDEX tiger.idx_tiger_cousub_countyfp; DROP INDEX tiger.idx_tiger_cousub_cousubfp; DROP INDEX tiger.idx_tiger_cousub_lower_name; DROP INDEX tiger.idx_tiger_cousub_snd_name; DROP INDEX tiger.idx_tiger_cousub_the_geom_gist; DROP INDEX tiger_data.idx_tiger_data_ma_addr_least_address; DROP INDEX tiger_data.idx_tiger_data_ma_addr_tlid; DROP INDEX tiger_data.idx_tiger_data_ma_addr_zip; DROP INDEX tiger_data.idx_tiger_data_ma_county_countyfp; DROP INDEX tiger_data.idx_tiger_data_ma_county_lookup_lower_name; DROP INDEX tiger_data.idx_tiger_data_ma_county_lookup_snd_name; DROP INDEX tiger_data.idx_tiger_data_ma_county_lower_name; DROP INDEX tiger_data.idx_tiger_data_ma_county_snd_name; : :
Drop_State_Tables_Generate_Script `SELECT drop_state_tables_generate_script('PA'); DROP TABLE tiger_data.pa_addr; DROP TABLE tiger_data.pa_county; DROP TABLE tiger_data.pa_county_lookup; DROP TABLE tiger_data.pa_cousub; DROP TABLE tiger_data.pa_edges; DROP TABLE tiger_data.pa_faces; DROP TABLE tiger_data.pa_featnames; DROP TABLE tiger_data.pa_place; DROP TABLE tiger_data.pa_state; DROP TABLE tiger_data.pa_zip_lookup_base; DROP TABLE tiger_data.pa_zip_state; DROP TABLE tiger_data.pa_zip_state_loc;`
Geocode `SELECT g.rating, ST_X(g.geomout) As lon, ST_Y(g.geomout) As lat, (addy).address As stno, (addy).streetname As street, (addy).streettypeabbrev As styp, (addy).location As city, (addy).stateabbrev As st,(addy).zip FROM geocode('75 State Street, Boston MA 02109') As g; rating \| lon \| lat \| stno \| street \| styp \| city \| st \| zip --------+-------------------+------------------+------+--------+------+--------+----+------- 0 \| -71.0556722990239 \| 42.3589914927049 \| 75 \| State \| St \| Boston \| MA \| 02109`
Geocode_Intersection `SELECT pprint_addy(addy), st_astext(geomout),rating FROM geocode_intersection( 'Haverford St','Germania St', 'MA', 'Boston', '02130',1); pprint_addy \| st_astext \| rating ----------------------------------+----------------------------+-------- 98 Haverford St, Boston, MA 02130 \| POINT(-71.101375 42.31376) \| 0`
Install_Missing_Indexes `SELECT install_missing_indexes(); install_missing_indexes ------------------------- t`
Loader_Generate_Script SELECT loader_generate_script(ARRAY['MA','RI'], 'windows') AS result; -- result -- set STATEDIR="\gisdata\www2.census.gov\geo\pvs\tiger2010st\44_Rhode_Island" set TMPDIR=\gisdata\temp\ set UNZIPTOOL="C:\Program Files\7-Zip\7z.exe" set WGETTOOL="C:\wget\wget.exe" set PGBIN=C:\Program Files\PostgreSQL\8.4\bin\ set PGPORT=5432 set PGHOST=localhost set PGUSER=postgres set PGPASSWORD=yourpasswordhere set PGDATABASE=geocoder set PSQL="%PGBIN%psql" set SHP2PGSQL="%PGBIN%shp2pgsql" %WGETTOOL% http://www2.census.gov/geo/pvs/tiger2010st/44_Rhode_Island/ --no-parent --relative --recursive --level=2 --accept=zip,txt --mirror --reject=html : :
Missing_Indexes_Generate_Script SELECT missing_indexes_generate_script(); -- output: This was run on a database that was created before many corrections were made to the loading script --- CREATE INDEX idx_tiger_county_countyfp ON tiger.county USING btree(countyfp); CREATE INDEX idx_tiger_cousub_countyfp ON tiger.cousub USING btree(countyfp); CREATE INDEX idx_tiger_edges_tfidr ON tiger.edges USING btree(tfidr); CREATE INDEX idx_tiger_edges_tfidl ON tiger.edges USING btree(tfidl); CREATE INDEX idx_tiger_zip_lookup_all_zip ON tiger.zip_lookup_all USING btree(zip); CREATE INDEX idx_tiger_data_ma_county_countyfp ON tiger_data.ma_county USING btree(countyfp); CREATE INDEX idx_tiger_data_ma_cousub_countyfp ON tiger_data.ma_cousub USING btree(countyfp); CREATE INDEX idx_tiger_data_ma_edges_countyfp ON tiger_data.ma_edges USING btree(countyfp); CREATE INDEX idx_tiger_data_ma_faces_countyfp ON tiger_data.ma_faces USING btree(countyfp);
Normalize_Address SELECT address As orig, (g.na).streetname, (g.na).streettypeabbrev FROM (SELECT address, normalize_address(address) As na FROM addresses_to_geocode) As g; orig \| streetname \| streettypeabbrev -----------------------------------------------------+---------------+------------------ 28 Capen Street, Medford, MA \| Capen \| St 124 Mount Auburn St, Cambridge, Massachusetts 02138 \| Mount Auburn \| St 950 Main Street, Worcester, MA 01610 \| Main \| St 529 Main Street, Boston MA, 02129 \| Main \| St 77 Massachusetts Avenue, Cambridge, MA 02139 \| Massachusetts \| Ave 25 Wizard of Oz, Walaford, KS 99912323 \| Wizard of Oz \|
Pprint_Addy `SELECT pprint_addy(normalize_address('202 East Fremont Street, Las Vegas, Nevada 89101')) As pretty_address; pretty_address --------------------------------------- 202 E Fremont St, Las Vegas, NV 89101`
Reverse_Geocode `SELECT pprint_addy(r.addy[1]) As st1, pprint_addy(r.addy[2]) As st2, pprint_addy(r.addy[3]) As st3, array_to_string(r.street, ',') As cross_streets FROM reverse_geocode(ST_GeomFromText('POINT(-71.093902 42.359446)',4269),true) As r; result ------ st1 \| st2 \| st3 \| cross_streets -------------------------------------------+-----+-----+---------------------------------------------- 67 Massachusetts Ave, Cambridge, MA 02139 \| \| \| 67 - 127 Massachusetts Ave,32 - 88 Vassar St`
Topology_Load_Tiger SELECT topology.CreateTopology('topo_boston', 2249, 0.25); createtopology -------------- 15 -- 60,902 ms ~ 1 minute on windows 7 desktop running 9.1 (with 5 states tiger data loaded) SELECT tiger.topology_load_tiger('topo_boston', 'place', '2507000'); -- topology_loader_tiger -- 29722 edges holding in temporary. 11108 faces added. 1875 edges of faces added. 20576 nodes added. 19962 nodes contained in a face. 0 edge start end corrected. 31597 edges added. -- 41 ms -- SELECT topology.TopologySummary('topo_boston'); -- topologysummary-- Topology topo_boston (15), SRID 2249, precision 0.25 20576 nodes, 31597 edges, 11109 faces, 0 topogeoms in 0 layers -- 28,797 ms to validate yeh returned no errors -- SELECT * FROM topology.ValidateTopology('topo_boston'); error \| id1 \| id2 -------------------+----------+-----------

PostGIS 2.0 Tiger Geocoder Cheatsheet