From 6a0e9867e236d66fcca04a0ff5f70d7b232b5b8d Mon Sep 17 00:00:00 2001 From: Regina Obe Date: Tue, 14 Feb 2012 17:53:40 +0000 Subject: [PATCH] #1494: done with programming need to finish documenting and regress test. git-svn-id: http://svn.osgeo.org/postgis/trunk@9189 b70326c6-7e19-0410-871a-916f4a2858ee --- .../tiger_2010/census_loader.sql | 3 +- .../tiger_2010/create_geocode.sql | 1 + .../geocode/census_tracts_functions.sql | 57 +++++++++++++++++++ .../tiger_2010/upgrade_geocode.sql | 1 + 4 files changed, 61 insertions(+), 1 deletion(-) create mode 100644 extras/tiger_geocoder/tiger_2010/geocode/census_tracts_functions.sql diff --git a/extras/tiger_geocoder/tiger_2010/census_loader.sql b/extras/tiger_geocoder/tiger_2010/census_loader.sql index 3c76244cc..b3bd9efd4 100644 --- a/extras/tiger_geocoder/tiger_2010/census_loader.sql +++ b/extras/tiger_geocoder/tiger_2010/census_loader.sql @@ -99,7 +99,8 @@ $$ language 'plpgsql'; ALTER FUNCTION create_census_base_tables() SET search_path=tiger,public; -CREATE OR REPLACE FUNCTION loader_generate_census(param_states text[], os text) +DROP FUNCTION IF EXISTS loader_generate_census(text[], text); +CREATE OR REPLACE FUNCTION loader_generate_census_script(param_states text[], os text) RETURNS SETOF text AS $$ SELECT create_census_base_tables(); diff --git a/extras/tiger_geocoder/tiger_2010/create_geocode.sql b/extras/tiger_geocoder/tiger_2010/create_geocode.sql index d8cfaf51b..0fce8e311 100644 --- a/extras/tiger_geocoder/tiger_2010/create_geocode.sql +++ b/extras/tiger_geocoder/tiger_2010/create_geocode.sql @@ -70,6 +70,7 @@ CREATE TYPE norm_addy AS ( -- Reverse Geocode API, called by user \i geocode/reverse_geocode.sql +\i geocode/census_tracts_functions.sql COMMIT; -- Tiger to PostGIS Topology diff --git a/extras/tiger_geocoder/tiger_2010/geocode/census_tracts_functions.sql b/extras/tiger_geocoder/tiger_2010/geocode/census_tracts_functions.sql new file mode 100644 index 000000000..e9fba9f90 --- /dev/null +++ b/extras/tiger_geocoder/tiger_2010/geocode/census_tracts_functions.sql @@ -0,0 +1,57 @@ +--$Id: census_tracts_functions.sql 7996 2011-10-21 12:01:12Z robe $ + /*** + * + * Copyright (C) 2012 Regina Obe and Leo Hsu (Paragon Corporation) + **/ +-- This function given a geometry try will try to determine the tract. +-- It defaults to returning the tract name but can be changed to return track geoid id. +-- pass in 'tract_id' to get the full geoid, 'name' to get the short decimal name + +CREATE OR REPLACE FUNCTION get_tract(IN loc_geom geometry, output_field text DEFAULT 'name') + RETURNS text AS +$$ +DECLARE + var_state text := NULL; + var_stusps text := NULL; + var_result text := NULL; + var_loc_geom geometry; + var_stmt text; + var_debug boolean = false; +BEGIN + --$Id: census_tracts_functions.sql 7996 2011-10-21 12:01:12Z robe $ + IF loc_geom IS NULL THEN + RETURN null; + ELSE + IF ST_SRID(loc_geom) = 4269 THEN + var_loc_geom := loc_geom; + ELSIF ST_SRID(loc_geom) > 0 THEN + var_loc_geom := ST_Transform(loc_geom, 4269); + ELSE --If srid is unknown, assume its 4269 + var_loc_geom := ST_SetSRID(loc_geom, 4269); + END IF; + IF GeometryType(var_loc_geom) != 'POINT' THEN + var_loc_geom := ST_Centroid(var_loc_geom); + END IF; + END IF; + -- Determine state tables to check + -- this is needed to take advantage of constraint exclusion + IF var_debug THEN + RAISE NOTICE 'Get matching states start: %', clock_timestamp(); + END IF; + SELECT statefp, stusps INTO var_state, var_stusps FROM state WHERE ST_Intersects(the_geom, var_loc_geom) LIMIT 1; + IF var_debug THEN + RAISE NOTICE 'Get matching states end: % - %', var_state, clock_timestamp(); + END IF; + IF var_state IS NULL THEN + -- We don't have any data for this state + RAISE NOTICE 'No data for this state'; + RETURN NULL; + END IF; + -- locate county + var_stmt := 'SELECT ' || quote_ident(output_field) || ' FROM tract WHERE statefp = $1 AND ST_Intersects(the_geom, $2) LIMIT 1;'; + EXECUTE var_stmt INTO var_result USING var_state, var_loc_geom ; + RETURN var_result; +END; +$$ + LANGUAGE plpgsql IMMUTABLE + COST 500; diff --git a/extras/tiger_geocoder/tiger_2010/upgrade_geocode.sql b/extras/tiger_geocoder/tiger_2010/upgrade_geocode.sql index f3c693454..4e718d4a6 100644 --- a/extras/tiger_geocoder/tiger_2010/upgrade_geocode.sql +++ b/extras/tiger_geocoder/tiger_2010/upgrade_geocode.sql @@ -222,6 +222,7 @@ SELECT create_census_base_tables(); -- Reverse Geocode API, called by user \i geocode/geocode_intersection.sql \i geocode/reverse_geocode.sql +\i geocode/census_tracts_functions.sql COMMIT; -- Tiger to PostGIS Topology -- only useable if you have topology installed -- 2.50.1