[postgis-tickets] r14848 - Minor cleanup, change to schema qualify tables,
Regina Obe
lr at pcorp.us
Sun Apr 17 13:38:14 PDT 2016
Author: robe
Date: 2016-04-17 13:38:14 -0700 (Sun, 17 Apr 2016)
New Revision: 14848
Modified:
branches/2.2/extras/tiger_geocoder/geocode/geocode_intersection.sql
Log:
Minor cleanup, change to schema qualify tables,
change to use tiger node ids overlap instead of intersects for determining intersections
Closes #3530 for PostGIS 2.2.3
References #3451
Modified: branches/2.2/extras/tiger_geocoder/geocode/geocode_intersection.sql
===================================================================
--- branches/2.2/extras/tiger_geocoder/geocode/geocode_intersection.sql 2016-04-17 20:30:29 UTC (rev 14847)
+++ branches/2.2/extras/tiger_geocoder/geocode/geocode_intersection.sql 2016-04-17 20:38:14 UTC (rev 14848)
@@ -1,6 +1,6 @@
/***
*
- * Copyright (C) 2011 Regina Obe and Leo Hsu (Paragon Corporation)
+ * Copyright (C) 2011-2016 Regina Obe and Leo Hsu (Paragon Corporation)
**/
-- This function given two roadways, state and optional city, zip
-- Will return addresses that are at the intersecton of those roadways
@@ -8,10 +8,17 @@
-- Use case example an address at the intersection of 2 streets:
-- SELECT pprint_addy(addy), st_astext(geomout),rating FROM geocode_intersection('School St', 'Washington St', 'MA', 'Boston','02117');
--DROP FUNCTION tiger.geocode_intersection(text,text,text,text,text,integer);
-CREATE OR REPLACE FUNCTION geocode_intersection(IN roadway1 text, IN roadway2 text, IN in_state text, IN in_city text DEFAULT '', IN in_zip text DEFAULT '',
-IN num_results integer DEFAULT 10, OUT ADDY NORM_ADDY,
- OUT GEOMOUT GEOMETRY,
- OUT RATING INTEGER) RETURNS SETOF record AS
+CREATE OR REPLACE FUNCTION geocode_intersection(
+ IN roadway1 text,
+ IN roadway2 text,
+ IN in_state text,
+ IN in_city text DEFAULT ''::text,
+ IN in_zip text DEFAULT ''::text,
+ IN num_results integer DEFAULT 10,
+ OUT addy norm_addy,
+ OUT geomout geometry,
+ OUT rating integer)
+ RETURNS SETOF record AS
$$
DECLARE
var_na_road norm_addy;
@@ -48,19 +55,19 @@
var_sql := '
WITH
a1 AS (SELECT f.*, addr.fromhn, addr.tohn, addr.side , addr.zip
- FROM (SELECT * FROM featnames
+ FROM (SELECT * FROM tiger.featnames
WHERE statefp = $1 AND ( lower(name) = $2 ' ||
CASE WHEN length(var_na_road.streetName) > 5 THEN ' or lower(fullname) LIKE $6 || ''%'' ' ELSE '' END || ')'
- || ') AS f LEFT JOIN (SELECT * FROM addr WHERE addr.statefp = $1) As addr ON (addr.tlid = f.tlid AND addr.statefp = f.statefp)
+ || ') AS f LEFT JOIN (SELECT * FROM tiger.addr As addr WHERE addr.statefp = $1) As addr ON (addr.tlid = f.tlid AND addr.statefp = f.statefp)
WHERE $5::text[] IS NULL OR addr.zip = ANY($5::text[]) OR addr.zip IS NULL
ORDER BY CASE WHEN lower(f.fullname) = $6 THEN 0 ELSE 1 END
LIMIT 50000
),
a2 AS (SELECT f.*, addr.fromhn, addr.tohn, addr.side , addr.zip
- FROM (SELECT * FROM featnames
+ FROM (SELECT * FROM tiger.featnames
WHERE statefp = $1 AND ( lower(name) = $4 ' ||
CASE WHEN length(var_na_inter1.streetName) > 5 THEN ' or lower(fullname) LIKE $7 || ''%'' ' ELSE '' END || ')'
- || ' ) AS f LEFT JOIN (SELECT * FROM addr WHERE addr.statefp = $1) AS addr ON (addr.tlid = f.tlid AND addr.statefp = f.statefp)
+ || ' ) AS f LEFT JOIN (SELECT * FROM tiger.addr As addr WHERE addr.statefp = $1) AS addr ON (addr.tlid = f.tlid AND addr.statefp = f.statefp)
WHERE $5::text[] IS NULL OR addr.zip = ANY($5::text[]) or addr.zip IS NULL
ORDER BY CASE WHEN lower(f.fullname) = $7 THEN 0 ELSE 1 END
LIMIT 50000
@@ -68,14 +75,14 @@
e1 AS (SELECT e.the_geom, e.tnidf, e.tnidt, a.*,
CASE WHEN a.side = ''L'' THEN e.tfidl ELSE e.tfidr END AS tfid
FROM a1 As a
- INNER JOIN edges AS e ON (e.statefp = a.statefp AND a.tlid = e.tlid)
+ INNER JOIN tiger.edges AS e ON (e.statefp = a.statefp AND a.tlid = e.tlid)
WHERE e.statefp = $1
ORDER BY CASE WHEN lower(a.name) = $4 THEN 0 ELSE 1 END + CASE WHEN lower(e.fullname) = $7 THEN 0 ELSE 1 END
LIMIT 5000) ,
e2 AS (SELECT e.the_geom, e.tnidf, e.tnidt, a.*,
CASE WHEN a.side = ''L'' THEN e.tfidl ELSE e.tfidr END AS tfid
- FROM (SELECT * FROM edges WHERE statefp = $1) AS e INNER JOIN a2 AS a ON (e.statefp = a.statefp AND a.tlid = e.tlid)
- INNER JOIN e1 ON (e.statefp = e1.statefp AND ST_Intersects(e.the_geom, e1.the_geom)
+ FROM (SELECT * FROM tiger.edges WHERE statefp = $1) AS e INNER JOIN a2 AS a ON (e.statefp = a.statefp AND a.tlid = e.tlid)
+ INNER JOIN e1 ON (e.statefp = e1.statefp
AND ARRAY[e.tnidf, e.tnidt] && ARRAY[e1.tnidf, e1.tnidt] )
WHERE (lower(e.fullname) = $7 or lower(a.name) LIKE $4 || ''%'')
@@ -100,9 +107,9 @@
AS a_rating
FROM e1
INNER JOIN e2 ON (
- ST_Intersects(e1.the_geom, e2.the_geom) )
- INNER JOIN (SELECT * FROM faces WHERE statefp = $1) As fa1 ON (e1.tfid = fa1.tfid )
- LEFT JOIN place AS p ON (fa1.placefp = p.placefp AND p.statefp = $1 )
+ ARRAY[e2.tnidf, e2.tnidt] && ARRAY[e1.tnidf, e1.tnidt] )
+ INNER JOIN (SELECT * FROM tiger.faces WHERE statefp = $1) As fa1 ON (e1.tfid = fa1.tfid )
+ LEFT JOIN tiger.place AS p ON (fa1.placefp = p.placefp AND p.statefp = $1 )
ORDER BY e1.tlid, e1.side, a_rating LIMIT $9*4 )
SELECT address, fedirp , fename, fetype,fedirs,place, zip , geom, a_rating
FROM segs ORDER BY a_rating LIMIT $9';
@@ -145,4 +152,4 @@
LANGUAGE plpgsql IMMUTABLE
COST 1000
ROWS 10;
-ALTER FUNCTION geocode_intersection(IN text, IN text, IN text, IN text, IN text, IN integer) SET join_collapse_limit='2';
+ALTER FUNCTION geocode_intersection(text, text, text, text, text, integer) SET join_collapse_limit='2';
More information about the postgis-tickets
mailing list