source: trunk/extras/tiger_geocoder/tiger_2010/upgrade_geocode.sql @ 8276

Last change on this file since 8276 was 8276, checked in by robe, 5 years ago

#1333 geocode_intersections: would help to actually include the function to.

  • Property svn:eol-style set to LF
  • Property svn:keywords set to Author Date Id Revision
File size: 9.2 KB
Line 
1--$Id: upgrade_geocode.sql 8276 2011-12-01 08:36:30Z robe $
2--
3-- PostGIS - Spatial Types for PostgreSQL
4-- http://www.postgis.org
5--
6-- Copyright (C) 2010, 2011 Regina Obe and Leo Hsu
7--
8-- This is free software; you can redistribute and/or modify it under
9-- the terms of the GNU General Public Licence. See the COPYING file.
10--
11-- Author: Regina Obe and Leo Hsu <lr@pcorp.us>
12-- 
13-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
14-- Tiger is where we're going to create the functions, but we need
15-- the PostGIS functions/types which are in public.
16SET search_path TO tiger,public;
17--this will fail if the column already exists which is fine
18ALTER TABLE state_lookup ADD COLUMN statefp char(2);
19UPDATE state_lookup SET statefp = lpad(st_code::text,2,'0') WHERE statefp IS NULL;
20ALTER TABLE state_lookup ADD CONSTRAINT state_lookup_statefp_key UNIQUE(statefp);
21CREATE INDEX idx_tiger_edges_countyfp ON edges USING btree(countyfp);
22CREATE INDEX idx_tiger_faces_countyfp ON faces USING btree(countyfp);
23CREATE INDEX tiger_place_the_geom_gist ON place USING gist(the_geom);
24CREATE INDEX tiger_edges_the_geom_gist ON edges USING gist(the_geom);
25CREATE INDEX tiger_state_the_geom_gist ON faces USING gist(the_geom);
26DROP FUNCTION IF EXISTS reverse_geocode(geometry); /** changed to use default parameters **/
27DROP FUNCTION IF EXISTS geocode_location(norm_addy); /** changed to include default parameter for restrict_geom**/
28DROP FUNCTION IF EXISTS geocode(varchar); /** changed to include default parameter for max_results and restrict_geom**/
29DROP FUNCTION IF EXISTS geocode(norm_addy); /** changed to include default parameter for max_results and restrict_geom **/
30DROP FUNCTION IF EXISTS geocode(varchar, integer); /** changed to include default parameter for max_results and restrict_geom **/
31DROP FUNCTION IF EXISTS geocode(norm_addy,integer); /** changed to include default parameter for max_results and restrict_geom **/
32DROP FUNCTION IF EXISTS geocode_address(norm_addy); /** changed to include default parameter for max_results **/
33DROP FUNCTION IF EXISTS geocode_address(norm_addy,integer); /** changed to include default parameter for max_results and restrict_geom **/
34DROP FUNCTION IF EXISTS interpolate_from_address(integer, character varying, character varying, geometry); /** changed to use default args and added offset and side **/
35DROP FUNCTION IF EXISTS interpolate_from_address(integer, integer, integer, geometry); /**don't need this since got collapes into varchar version **/
36
37-- TODO: Put in logic to update lookup tables as they change.  street_type_lookup has changed since initial release --
38CREATE TABLE zcta5
39(
40  gid serial NOT NULL,
41  statefp character varying(2),
42  zcta5ce character varying(5),
43  classfp character varying(2),
44  mtfcc character varying(5),
45  funcstat character varying(1),
46  aland double precision,
47  awater double precision,
48  intptlat character varying(11),
49  intptlon character varying(12),
50  partflg character varying(1),
51  the_geom geometry,
52  CONSTRAINT uidx_tiger_zcta5_gid UNIQUE (gid),
53  CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
54  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
55  CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269),
56  CONSTRAINT pk_tiger_zcta5_zcta5ce PRIMARY KEY (zcta5ce,statefp)
57 );
58
59ALTER TABLE street_type_lookup ALTER COLUMN abbrev  TYPE varchar(50);
60ALTER TABLE street_type_lookup ALTER COLUMN name  TYPE varchar(50);
61ALTER TABLE street_type_lookup ADD COLUMN is_hw boolean NOT NULL DEFAULT false;
62DROP FUNCTION IF EXISTS rate_attributes(character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying);
63DROP FUNCTION IF EXISTS rate_attributes(character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying);
64
65--ALTER TABLE tiger.addr ALTER tlid TYPE bigint;
66ALTER TABLE featnames ALTER COLUMN tlid SET NOT NULL;
67ALTER TABLE edges ALTER COLUMN tlid SET NOT NULL;
68ALTER TABLE addr ALTER COLUMN tlid SET NOT NULL;
69BEGIN;
70-- Type used to pass around a normalized address between functions
71-- This is s bit dangerous since it could potentially drop peoples tables
72-- TODO: put in logic to check if any tables have norm_addy and don't drop if they do
73-- Remarking this out for now since we aren't changing norm_addy anyway
74/*DROP TYPE IF EXISTS norm_addy CASCADE;
75CREATE TYPE norm_addy AS (
76    address INTEGER,
77    preDirAbbrev VARCHAR,
78    streetName VARCHAR,
79    streetTypeAbbrev VARCHAR,
80    postDirAbbrev VARCHAR,
81    internal VARCHAR,
82    location VARCHAR,
83    stateAbbrev VARCHAR,
84    zip VARCHAR,
85    parsed BOOLEAN); */
86-- prefix and suffix street names for numbered highways
87CREATE TEMPORARY TABLE temp_types AS
88SELECT name, abbrev
89    FROM  ( VALUES
90        ('CAM', 'Cam'),
91        ('CAM.', 'Cam'),
92        ('CAMINO', 'Cam'),
93        ('CO HWY', 'Co Hwy'),
94        ('COUNTY HWY', 'Co Hwy'),
95        ('COUNTY HIGHWAY', 'Co Hwy'),
96        ('COUNTY HIGH WAY', 'Co Hwy'),
97        ('COUNTY ROAD', 'Co Rd'),
98        ('CO RD', 'Co Rd'),
99        ('CORD', 'Co Rd'),
100        ('CO RTE', 'Co Rte'),
101        ('COUNTY ROUTE', 'Co Rte'),
102        ('CO ST AID HWY', 'Co St Aid Hwy'),
103        ('EXP', 'Expy'),
104        ('EXPR', 'Expy'),
105        ('EXPRESS', 'Expy'),
106        ('EXPRESSWAY', 'Expy'),
107        ('EXPW', 'Expy'),
108        ('EXPY', 'Expy'),
109        ('FARM RD', 'Farm Rd'),
110        ('FIRE RD', 'Fire Rd'),
111        ('FOREST RD', 'Forest Rd'),
112        ('FOREST ROAD', 'Forest Rd'),
113        ('FOREST RTE', 'Forest Rte'),
114        ('FOREST ROUTE', 'Forest Rte'),
115        ('FREEWAY', 'Fwy'),
116        ('FREEWY', 'Fwy'),
117        ('FRWAY', 'Fwy'),
118        ('FRWY', 'Fwy'),
119        ('FWY', 'Fwy'),
120        ('HIGHWAY', 'Hwy'),
121        ('HIGHWY', 'Hwy'),
122        ('HIWAY', 'Hwy'),
123        ('HIWY', 'Hwy'),
124        ('HWAY', 'Hwy'),
125        ('HWY', 'Hwy'),
126        ('I', 'I-'),
127        ('I-', 'I-'),
128        ('INTERSTATE', 'I-'),
129        ('ROUTE', 'Rte'),
130        ('RTE', 'Rte'),
131        ('RT', 'Rte'),
132        ('STATE HWY', 'State Hwy'),
133        ('STATE HIGHWAY', 'State Hwy'),
134        ('STATE HIGH WAY', 'State Hwy'),
135        ('STATE RD', 'State Rd'),
136        ('STATE ROAD', 'State Rd'),
137        ('STATE ROUTE', 'State Rte'),
138        ('STATE RTE', 'State Rte'),
139        ('TPK', 'Tpke'),
140        ('TPKE', 'Tpke'),
141        ('TRNPK', 'Tpke'),
142        ('TRPK', 'Tpke'),
143        ('TURNPIKE', 'Tpke'),
144        ('TURNPK', 'Tpke'),
145        ('US HWY', 'US Hwy'),
146        ('US HIGHWAY', 'US Hwy'),
147        ('US HIGH WAY', 'US Hwy'),
148        ('US RTE', 'US Rte'),
149        ('US ROUTE', 'US Rte'),
150        ('US RT', 'US Rte'),
151        ('USFS HWY', 'USFS Hwy'),
152        ('USFS HIGHWAY', 'USFS Hwy'),
153        ('USFS HIGH WAY', 'USFS Hwy'),
154        ('USFS RD', 'USFS Rd'),
155        ('USFS ROAD', 'USFS Rd')
156           ) t(name, abbrev);
157           
158DELETE FROM street_type_lookup WHERE name IN(SELECT name FROM temp_types);         
159INSERT INTO street_type_lookup (name, abbrev, is_hw) 
160SELECT name, abbrev, true
161    FROM temp_types As t
162           WHERE t.name NOT IN(SELECT name FROM street_type_lookup);
163DROP TABLE temp_types;           
164DELETE FROM street_type_lookup WHERE name = 'FOREST';
165UPDATE street_type_lookup SET is_hw = false WHERE abbrev = 'Loop';
166
167CREATE TEMPORARY TABLE temp_types AS
168SELECT name, abbrev
169    FROM (VALUES 
170 ('LOOP', 'Loop'),
171 ('SERVICE DRIVE', 'Svc Dr'),
172 ('SERVICE DR', 'Svc Dr'),
173 ('SERVICE ROAD', 'Svc Rd'),
174 ('SERVICE RD', 'Svc Rd') 
175    ) t(name, abbrev);
176 
177DELETE FROM street_type_lookup WHERE name IN(SELECT name FROM temp_types);         
178INSERT INTO street_type_lookup (name, abbrev, is_hw) 
179SELECT name, abbrev, false
180    FROM temp_types As t
181           WHERE t.name NOT IN(SELECT name FROM street_type_lookup);
182-- System/General helper functions
183\i utility/utmzone.sql
184\i utility/cull_null.sql
185\i utility/nullable_levenshtein.sql
186\i utility/levenshtein_ignore_case.sql
187
188---- Address normalizer
189-- General helpers
190\i normalize/end_soundex.sql
191\i normalize/count_words.sql
192\i normalize/state_extract.sql
193\i normalize/get_last_words.sql
194-- Location extraction/normalization helpers
195\i normalize/location_extract_countysub_exact.sql
196\i normalize/location_extract_countysub_fuzzy.sql
197\i normalize/location_extract_place_exact.sql
198\i normalize/location_extract_place_fuzzy.sql
199\i normalize/location_extract.sql
200-- Normalization API, called by geocode mainly.
201\i normalize/normalize_address.sql
202\i normalize/pprint_addy.sql
203
204---- Geocoder functions
205-- General helpers
206\i geocode/other_helper_functions.sql
207\i geocode/rate_attributes.sql
208\i geocode/includes_address.sql
209\i geocode/interpolate_from_address.sql
210-- Actual lookups/geocoder helpers
211\i geocode/geocode_address.sql
212\i geocode/geocode_location.sql
213\i geocode/geocode_intersection.sql
214-- Geocode API, called by user
215\i geocode/geocode.sql
216
217-- Reverse Geocode API, called by user
218\i geocode/reverse_geocode.sql
219COMMIT;
220-- Tiger to PostGIS Topology
221-- only useable if you have topology installed
222\i topology/tiger_topology_loader.sql
223
224-- install missing indexes
225\echo 'Installing missing indexes - this might take a while so be patient ..'
226SELECT install_missing_indexes();
227\a
228--\o 'drop_dup_feat_create_index.sql'
229--\i generate_drop_dupe_featnames.sql
230\o
231--\i drop_dup_feat_create_index.sql
232\echo 'Missing index Install completed'
Note: See TracBrowser for help on using the repository browser.