From 00167d4158fb9b69bc2d4d831d3dd4da5262c403 Mon Sep 17 00:00:00 2001 From: Regina Obe Date: Tue, 15 Feb 2011 00:26:08 +0000 Subject: [PATCH] Journeying into street ranges now can tell which side of the street we are on and spits that out when include_strnum_range is true. Next (hopefully last) step will be to pinpoint address number git-svn-id: http://svn.osgeo.org/postgis/trunk@6818 b70326c6-7e19-0410-871a-916f4a2858ee --- .../tiger_2010/geocode/reverse_geocode.sql | 16 +++++++++++----- 1 file changed, 11 insertions(+), 5 deletions(-) diff --git a/extras/tiger_geocoder/tiger_2010/geocode/reverse_geocode.sql b/extras/tiger_geocoder/tiger_2010/geocode/reverse_geocode.sql index 62a7830bb..cd00565e9 100644 --- a/extras/tiger_geocoder/tiger_2010/geocode/reverse_geocode.sql +++ b/extras/tiger_geocoder/tiger_2010/geocode/reverse_geocode.sql @@ -1,4 +1,4 @@ ---$Id$ +--$Id$ /*** * * Copyright (C) 2011 Regina Obe and Leo Hsu (Paragon Corporation) @@ -33,18 +33,24 @@ BEGIN -- Find the street edges that this point is closest to with tolerance of 500 meters (width of road) FOR var_redge IN - SELECT DISTINCT ON(fullname) foo.gid, foo.fullname, foo.stusps, foo.zip, (SELECT z.place FROM zip_state_loc AS z WHERE z.zip = foo.zip and z.statefp = foo.statefp) As place, foo.intpt + SELECT DISTINCT ON(fullname) foo.gid, foo.fullname, foo.stusps, foo.zip, + (SELECT z.place FROM zip_state_loc AS z WHERE z.zip = foo.zip and z.statefp = foo.statefp LIMIT 1) As place, foo.intpt, + side, fromhn, tohn FROM - (SELECT e.gid, e.fullname, a.zip, s.stusps, ST_ClosestPoint(e.the_geom, pt) As intpt, e.statefp + (SELECT e.gid, e.fullname, a.zip, s.stusps, ST_ClosestPoint(e.the_geom, pt) As intpt, e.statefp, a.side, a.fromhn, a.tohn FROM edges AS e INNER JOIN state As s ON (e.statefp = s.statefp AND s.statefp = ANY(var_states) ) - INNER JOIN addr As a ON (e.tlid = a.tlid AND e.statefp = a.statefp) -- INNER JOIN zip_state_loc As z ON (a.statefp = z.statefp AND a.zip = z.zip) /** really slow with this join **/ + INNER JOIN faces As fl ON (e.tfidl = fl.tfid AND e.statefp = fl.statefp) + INNER JOIN faces As fr ON (e.tfidr = fr.tfid AND e.statefp = fr.statefp) + INNER JOIN addr As a ON ( e.tlid = a.tlid AND e.statefp = a.statefp AND + ( ( ST_Contains(fl.the_geom, pt) AND a.side = 'L') OR ( ST_Contains(fr.the_geom, pt) AND a.side = 'R' ) ) ) + -- INNER JOIN zip_state_loc As z ON (a.statefp = z.statefp AND a.zip = z.zip) /** really slow with this join **/ WHERE e.statefp = ANY(var_states) AND a.statefp = ANY(var_states) AND ST_DWithin(e.the_geom, pt, 0.005) ORDER BY ST_Distance(e.the_geom, pt) LIMIT 4) As foo WHERE ST_Distance_Sphere(foo.intpt, pt) < 150 --less than 150 m ORDER BY foo.fullname, ST_Distance_Sphere(foo.intpt, pt) LOOP intpt := array_append(intpt,var_redge.intpt); IF var_redge.fullname IS NOT NULL THEN - street := array_append(street,var_redge.fullname::text); + street := array_append(street, CASE WHEN include_strnum_range THEN COALESCE(var_redge.fromhn, '')::text || ' - ' || COALESCE(var_redge.tohn,'')::text || ' '::text ELSE '' END::text || var_redge.fullname::text); var_addy := normalize_address(var_redge.fullname || ', ' || var_redge.place || ', ' || var_redge.stusps || ' ' || var_redge.zip); addy := array_append(addy, var_addy); END IF; -- 2.50.1