309 lines
12 KiB
PL/PgSQL
309 lines
12 KiB
PL/PgSQL
--
|
|
-- PostGIS - Spatial Types for PostgreSQL
|
|
-- http://postgis.net
|
|
--
|
|
-- Copyright (C) 2010, 2011 Regina Obe and Leo Hsu
|
|
--
|
|
-- This is free software; you can redistribute and/or modify it under
|
|
-- the terms of the GNU General Public Licence. See the COPYING file.
|
|
--
|
|
-- Author: Regina Obe and Leo Hsu <lr@pcorp.us>
|
|
--
|
|
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|
|
--
|
|
\i utility/set_search_path.sql;
|
|
-- Tiger is where we're going to create the functions, but we need
|
|
-- the PostGIS functions/types which may be anywhere
|
|
-- we'll assume user has postgis functions and other contribs as part of search path
|
|
-- the below call will put tiger schema in front so all objects in this script
|
|
-- will get created in search path
|
|
SELECT tiger.SetSearchPathForInstall('tiger');
|
|
--this is used currently for debugging
|
|
\i geocode_settings.sql
|
|
--this will fail if the column already exists which is fine
|
|
ALTER TABLE state_lookup ADD COLUMN statefp char(2);
|
|
UPDATE state_lookup SET statefp = lpad(st_code::text,2,'0') WHERE statefp IS NULL;
|
|
ALTER TABLE state_lookup ADD CONSTRAINT state_lookup_statefp_key UNIQUE(statefp);
|
|
|
|
-- these introduced in PostGIS 2.4
|
|
DO language plpgsql
|
|
$$
|
|
BEGIN
|
|
ALTER TYPE tiger.norm_addy ADD ATTRIBUTE zip4 varchar;
|
|
ALTER TYPE tiger.norm_addy ADD ATTRIBUTE address_alphanumeric varchar;
|
|
EXCEPTION
|
|
WHEN others THEN -- ignore the error probably cause it already exists
|
|
END;
|
|
$$;
|
|
|
|
CREATE INDEX idx_tiger_edges_countyfp ON edges USING btree(countyfp);
|
|
CREATE INDEX idx_tiger_faces_countyfp ON faces USING btree(countyfp);
|
|
CREATE INDEX tiger_place_the_geom_gist ON place USING gist(the_geom);
|
|
CREATE INDEX tiger_edges_the_geom_gist ON edges USING gist(the_geom);
|
|
CREATE INDEX tiger_state_the_geom_gist ON faces USING gist(the_geom);
|
|
DROP FUNCTION IF EXISTS reverse_geocode(geometry); /** changed to use default parameters **/
|
|
DROP FUNCTION IF EXISTS geocode_location(norm_addy); /** changed to include default parameter for restrict_geom**/
|
|
DROP FUNCTION IF EXISTS geocode(varchar); /** changed to include default parameter for max_results and restrict_geom**/
|
|
DROP FUNCTION IF EXISTS geocode(norm_addy); /** changed to include default parameter for max_results and restrict_geom **/
|
|
DROP FUNCTION IF EXISTS geocode(varchar, integer); /** changed to include default parameter for max_results and restrict_geom **/
|
|
DROP FUNCTION IF EXISTS geocode(norm_addy,integer); /** changed to include default parameter for max_results and restrict_geom **/
|
|
DROP FUNCTION IF EXISTS geocode_address(norm_addy); /** changed to include default parameter for max_results **/
|
|
DROP FUNCTION IF EXISTS geocode_address(norm_addy,integer); /** changed to include default parameter for max_results and restrict_geom **/
|
|
DROP FUNCTION IF EXISTS interpolate_from_address(integer, character varying, character varying, geometry); /** changed to use default args and added offset and side **/
|
|
DROP FUNCTION IF EXISTS interpolate_from_address(integer, integer, integer, geometry); /**don't need this since got collapes into varchar version **/
|
|
|
|
-- this will fail if already exists, that is fine.
|
|
SELECT tiger.SetSearchPathForInstall('tiger');
|
|
CREATE TABLE IF NOT EXISTS addrfeat
|
|
(
|
|
gid serial not null primary key,
|
|
tlid bigint,
|
|
statefp character varying(2),
|
|
aridl character varying(22),
|
|
aridr character varying(22),
|
|
linearid character varying(22),
|
|
fullname character varying(100),
|
|
lfromhn character varying(12),
|
|
ltohn character varying(12),
|
|
rfromhn character varying(12),
|
|
rtohn character varying(12),
|
|
zipl character varying(5),
|
|
zipr character varying(5),
|
|
edge_mtfcc character varying(5),
|
|
parityl character varying(1),
|
|
parityr character varying(1),
|
|
plus4l character varying(4),
|
|
plus4r character varying(4),
|
|
lfromtyp character varying(1),
|
|
ltotyp character varying(1),
|
|
rfromtyp character varying(1),
|
|
rtotyp character varying(1),
|
|
offsetl character varying(1),
|
|
offsetr character varying(1),
|
|
the_geom geometry,
|
|
CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
|
|
CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'LINESTRING'::text OR the_geom IS NULL),
|
|
CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269)
|
|
);
|
|
CREATE INDEX idx_addrfeat_geom_gist ON addrfeat USING gist(geom );
|
|
CREATE INDEX idx_addrfeat_tlid ON addrfeat USING btree(tlid);
|
|
CREATE INDEX idx_addrfeat_zipl ON addrfeat USING btree(zipl);
|
|
CREATE INDEX idx_addrfeat_zipr ON addrfeat USING btree(zipr);
|
|
|
|
-- TODO: Put in logic to update lookup tables as they change. street_type_lookup has changed since initial release --
|
|
CREATE TABLE zcta5
|
|
(
|
|
gid serial NOT NULL,
|
|
statefp character varying(2),
|
|
zcta5ce character varying(5),
|
|
classfp character varying(2),
|
|
mtfcc character varying(5),
|
|
funcstat character varying(1),
|
|
aland double precision,
|
|
awater double precision,
|
|
intptlat character varying(11),
|
|
intptlon character varying(12),
|
|
partflg character varying(1),
|
|
the_geom geometry,
|
|
CONSTRAINT uidx_tiger_zcta5_gid UNIQUE (gid),
|
|
CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
|
|
CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
|
|
CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269),
|
|
CONSTRAINT pk_tiger_zcta5_zcta5ce PRIMARY KEY (zcta5ce,statefp)
|
|
);
|
|
|
|
ALTER TABLE street_type_lookup ALTER COLUMN abbrev TYPE varchar(50);
|
|
ALTER TABLE street_type_lookup ALTER COLUMN name TYPE varchar(50);
|
|
ALTER TABLE street_type_lookup ADD COLUMN is_hw boolean NOT NULL DEFAULT false;
|
|
DROP FUNCTION IF EXISTS rate_attributes(character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying);
|
|
DROP 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);
|
|
|
|
--ALTER TABLE tiger.addr ALTER tlid TYPE bigint;
|
|
ALTER TABLE featnames ALTER COLUMN tlid SET NOT NULL;
|
|
ALTER TABLE county ALTER COLUMN statefp SET NOT NULL;
|
|
ALTER TABLE edges ALTER COLUMN tlid SET NOT NULL;
|
|
ALTER TABLE addr ALTER COLUMN tlid SET NOT NULL;
|
|
BEGIN;
|
|
-- Type used to pass around a normalized address between functions
|
|
-- This is s bit dangerous since it could potentially drop peoples tables
|
|
-- TODO: put in logic to check if any tables have norm_addy and don't drop if they do
|
|
-- Remarking this out for now since we aren't changing norm_addy anyway
|
|
/*DROP TYPE IF EXISTS norm_addy CASCADE;
|
|
CREATE TYPE norm_addy AS (
|
|
address INTEGER,
|
|
preDirAbbrev VARCHAR,
|
|
streetName VARCHAR,
|
|
streetTypeAbbrev VARCHAR,
|
|
postDirAbbrev VARCHAR,
|
|
internal VARCHAR,
|
|
location VARCHAR,
|
|
stateAbbrev VARCHAR,
|
|
zip VARCHAR,
|
|
parsed BOOLEAN); */
|
|
-- prefix and suffix street names for numbered highways
|
|
CREATE TEMPORARY TABLE temp_types AS
|
|
SELECT name, abbrev, true
|
|
FROM (VALUES
|
|
('CAM', 'Cam'),
|
|
('CAM.', 'Cam'),
|
|
('CAMINO', 'Cam'),
|
|
('CO HWY', 'Co Hwy'),
|
|
('COUNTY HWY', 'Co Hwy'),
|
|
('COUNTY HIGHWAY', 'Co Hwy'),
|
|
('COUNTY HIGH WAY', 'Co Hwy'),
|
|
('COUNTY ROAD', 'Co Rd'),
|
|
('COUNTY RD', 'Co Rd'),
|
|
('CO RD', 'Co Rd'),
|
|
('CORD', 'Co Rd'),
|
|
('CO RTE', 'Co Rte'),
|
|
('COUNTY ROUTE', 'Co Rte'),
|
|
('CO ST AID HWY', 'Co St Aid Hwy'),
|
|
('EXP', 'Expy'),
|
|
('EXPR', 'Expy'),
|
|
('EXPRESS', 'Expy'),
|
|
('EXPRESSWAY', 'Expy'),
|
|
('EXPW', 'Expy'),
|
|
('EXPY', 'Expy'),
|
|
('FARM RD', 'Farm Rd'),
|
|
('FIRE RD', 'Fire Rd'),
|
|
('FOREST RD', 'Forest Rd'),
|
|
('FOREST ROAD', 'Forest Rd'),
|
|
('FOREST RTE', 'Forest Rte'),
|
|
('FOREST ROUTE', 'Forest Rte'),
|
|
('FREEWAY', 'Fwy'),
|
|
('FREEWY', 'Fwy'),
|
|
('FRWAY', 'Fwy'),
|
|
('FRWY', 'Fwy'),
|
|
('FWY', 'Fwy'),
|
|
('HIGHWAY', 'Hwy'),
|
|
('HIGHWY', 'Hwy'),
|
|
('HIWAY', 'Hwy'),
|
|
('HIWY', 'Hwy'),
|
|
('HWAY', 'Hwy'),
|
|
('HWY', 'Hwy'),
|
|
('I', 'I-'),
|
|
('I-', 'I-'),
|
|
('INTERSTATE', 'I-'),
|
|
('INTERSTATE ROUTE', 'I-'),
|
|
('INTERSTATE RTE', 'I-'),
|
|
('INTERSTATE RTE.', 'I-'),
|
|
('INTERSTATE RT', 'I-'),
|
|
('LOOP', 'Loop'),
|
|
('ROUTE', 'Rte'),
|
|
('RTE', 'Rte'),
|
|
('RT', 'Rte'),
|
|
('STATE HWY', 'State Hwy'),
|
|
('STATE HIGHWAY', 'State Hwy'),
|
|
('STATE HIGH WAY', 'State Hwy'),
|
|
('STATE RD', 'State Rd'),
|
|
('STATE ROAD', 'State Rd'),
|
|
('STATE ROUTE', 'State Rte'),
|
|
('STATE RTE', 'State Rte'),
|
|
('TPK', 'Tpke'),
|
|
('TPKE', 'Tpke'),
|
|
('TRNPK', 'Tpke'),
|
|
('TRPK', 'Tpke'),
|
|
('TURNPIKE', 'Tpke'),
|
|
('TURNPK', 'Tpke'),
|
|
('US HWY', 'US Hwy'),
|
|
('US HIGHWAY', 'US Hwy'),
|
|
('US HIGH WAY', 'US Hwy'),
|
|
('U.S.', 'US Hwy'),
|
|
('US RTE', 'US Rte'),
|
|
('US ROUTE', 'US Rte'),
|
|
('US RT', 'US Rte'),
|
|
('USFS HWY', 'USFS Hwy'),
|
|
('USFS HIGHWAY', 'USFS Hwy'),
|
|
('USFS HIGH WAY', 'USFS Hwy'),
|
|
('USFS RD', 'USFS Rd'),
|
|
('USFS ROAD', 'USFS Rd')
|
|
) t(name, abbrev)
|
|
WHERE t.name NOT IN(SELECT name FROM street_type_lookup);
|
|
|
|
DELETE FROM street_type_lookup WHERE name IN(SELECT name FROM temp_types);
|
|
INSERT INTO street_type_lookup (name, abbrev, is_hw)
|
|
SELECT name, abbrev, true
|
|
FROM temp_types As t
|
|
WHERE t.name NOT IN(SELECT name FROM street_type_lookup);
|
|
DROP TABLE temp_types;
|
|
DELETE FROM street_type_lookup WHERE name = 'FOREST';
|
|
UPDATE street_type_lookup SET is_hw = false WHERE abbrev = 'Loop';
|
|
|
|
CREATE TEMPORARY TABLE temp_types AS
|
|
SELECT name, abbrev
|
|
FROM (VALUES
|
|
('LOOP', 'Loop'),
|
|
('SERVICE DRIVE', 'Svc Dr'),
|
|
('SERVICE DR', 'Svc Dr'),
|
|
('SERVICE ROAD', 'Svc Rd'),
|
|
('SERVICE RD', 'Svc Rd')
|
|
) t(name, abbrev);
|
|
|
|
DELETE FROM street_type_lookup WHERE name IN(SELECT name FROM temp_types);
|
|
INSERT INTO street_type_lookup (name, abbrev, is_hw)
|
|
SELECT name, abbrev, false
|
|
FROM temp_types As t
|
|
WHERE t.name NOT IN(SELECT name FROM street_type_lookup);
|
|
|
|
SELECT tiger.SetSearchPathForInstall('tiger');
|
|
\i geocode_settings.sql
|
|
-- new census loader
|
|
\i census_loader.sql
|
|
--create parent tables for census
|
|
-- if they do not exist
|
|
SELECT create_census_base_tables();
|
|
-- System/General helper functions
|
|
\i utility/utmzone.sql
|
|
\i utility/cull_null.sql
|
|
\i utility/nullable_levenshtein.sql
|
|
\i utility/levenshtein_ignore_case.sql
|
|
|
|
---- Address normalizer
|
|
-- General helpers
|
|
\i normalize/end_soundex.sql
|
|
\i normalize/count_words.sql
|
|
\i normalize/state_extract.sql
|
|
\i normalize/get_last_words.sql
|
|
-- Location extraction/normalization helpers
|
|
\i normalize/location_extract_countysub_exact.sql
|
|
\i normalize/location_extract_countysub_fuzzy.sql
|
|
\i normalize/location_extract_place_exact.sql
|
|
\i normalize/location_extract_place_fuzzy.sql
|
|
\i normalize/location_extract.sql
|
|
-- Normalization API, called by geocode mainly.
|
|
\i normalize/normalize_address.sql
|
|
\i normalize/pprint_addy.sql
|
|
\i pagc_normalize/pagc_tables.sql
|
|
\i pagc_normalize/pagc_normalize_address.sql
|
|
|
|
---- Geocoder functions
|
|
-- General helpers
|
|
\i geocode/other_helper_functions.sql
|
|
\i geocode/rate_attributes.sql
|
|
\i geocode/includes_address.sql
|
|
\i geocode/interpolate_from_address.sql
|
|
-- Actual lookups/geocoder helpers
|
|
\i geocode/geocode_address.sql
|
|
\i geocode/geocode_location.sql
|
|
-- Geocode API, called by user
|
|
\i geocode/geocode.sql
|
|
|
|
-- 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
|
|
\i topology/tiger_topology_loader.sql
|
|
|
|
-- install missing indexes
|
|
\echo 'Installing missing indexes - this might take a while so be patient ..'
|
|
SELECT install_missing_indexes();
|
|
\a
|
|
--\o 'drop_dup_feat_create_index.sql'
|
|
--\i generate_drop_dupe_featnames.sql
|
|
\o
|
|
--\i drop_dup_feat_create_index.sql
|
|
\echo 'Missing index Install completed'
|