PostGIS 3.6 Tiger Geocoder Cheatsheet

Aggiunta in questo rilascio 1Migliorata in questo rilascio 2  
Standardizzatore di indirizzi
stdaddr    Un tipo composito che consiste negli elementi di un indirizzo. È il tipo di ritorno della funzione standardize_address.
rules table    La tabella delle regole contiene un insieme di regole che mappano i token della sequenza di input dell'indirizzo in una sequenza di output standardizzata. Una regola è definita come un insieme di token di ingresso seguiti da -1 (terminatore) seguito da un insieme di token di uscita seguiti da -1 seguito da un numero che denota il tipo di regola seguito da una classificazione della regola.
lex table    A lex table is used to classify alphanumeric input and associate that input with (a) input tokens ( See ) and (b) standardized representations.
gaz table    A gaz table is used to standardize place names and associate that input with (a) input tokens ( See ) and (b) standardized representations.
debug_standardize_address (lextab, gaztab, rultab, micro, macro=NULL)    Returns a json formatted text listing the parse tokens and standardizations
parse_address (address)    Takes a 1 line address and breaks into parts
standardize_address    Returns an stdaddr form of an input address utilizing lex, gaz, and rule tables.
  1. lextab, gaztab, rultab, address
  2. lextab, gaztab, rultab, micro, macro

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_Nation_Tables_Generate_Script (param_schema=tiger_data)    Generates a script that drops all tables in the specified schema that start with county_all, state_all or state code followed by county or state.
Drop_State_Tables_Generate_Script (param_state, 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)
  1. address, max_results=10, restrict_region=NULL,
  2. 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 geomout as the point location in NAD 83 long lat, a normalized_address (addy) 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. Uses Tiger data (edges, faces, addr), PostgreSQL fuzzy string matching (soundex, levenshtein).
Get_Geocode_Setting ( setting_name)    Returns value of specific setting stored in tiger.geocode_settings table.
Get_Tract ( loc_geom, output_field=name)    Returns census tract or field from tract table of where the geometry is located. Default to returning short name of tract.
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_Census_Script (param_states, os)    Generates a shell script for the specified platform for the specified states that will download Tiger census state tract, bg, and tabblocks data tables, stage and load into tiger_data schema. Each state script is returned as a separate record.
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 and also loads census tract, block groups, and blocks tables.
Loader_Generate_Nation_Script (os)    Generates a shell script for the specified platform that loads in the county and state lookup tables.
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).
Pagc_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). Requires address_standardizer extension.
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.
Set_Geocode_Setting ( setting_name, setting_value)    Sets a setting that affects behavior of geocoder functions.

Tiger Geocoder Examples
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', 1) As g;
 rating |        lon        |      lat       | stno | street | styp |  city  | st |  zip
--------+-------------------+----------------+------+--------+------+--------+----+-------
      0 | -71.0557505845646 | 42.35897920691 |   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
Get_Geocode_Setting
SELECT get_geocode_setting('debug_geocode_address) As result;
result
---------
false
Get_Tract
SELECT get_tract(ST_Point(-71.101375, 42.31376) ) As tract_name;
tract_name
---------
1203.01
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
-------------------+----------+-----------
Set_Geocode_Setting
SELECT set_geocode_setting('debug_geocode_address', 'true') As result;
result
---------
true