From: Don Harter <donharter@comcast.net>
To: 8417@debbugs.gnu.org
Subject: bug#8417: attached file
Date: Sun, 03 Apr 2011 14:13:26 -0500 [thread overview]
Message-ID: <4D98C6D6.2030005@comcast.net> (raw)
In-Reply-To: <4D9890C3.4040803@comcast.net>
[-- Attachment #1: Type: text/html, Size: 334 bytes --]
[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: reverse_geocode.sql --]
[-- Type: text/x-sql; name="reverse_geocode.sql", Size: 5274 bytes --]
--$Id: reverse_geocode.sql 6823 2011-02-15 13:49:06Z robe $
--
--
-- Copyright (C) 2011 Regina Obe and Leo Hsu (Paragon Corporation)
--
-- This function given a point try to determine the approximate street address (norm_addy form)
-- and array of cross streets, as well as interpolated points along the streets
--
ALTER DATABASE geocoder SET search_path TO tiger,public;
CREATE OR REPLACE FUNCTION public.reverse_geocode(
IN pt geometry,
IN include_strnum_range boolean,
OUT intpt geometry[],
OUT addy NORM_ADDY[],
OUT street varchar[]
) RETURNS RECORD
AS $_$
DECLARE
var_redge RECORD;
var_states text[];
var_addy NORM_ADDY;
var_strnum varchar;
var_nstrnum numeric(10);
var_primary_line geometry := NULL;
var_primary_dist numeric(10,2) ;
var_pt geometry;
BEGIN
IF pt IS NULL THEN
RETURN;
ELSE
IF ST_SRID(pt) = 4269 THEN
var_pt := pt;
ELSE
var_pt := ST_Transform(pt, 4269);
END IF;
END IF;
-- Determine state tables to check
-- this is needed to take advantage of constraint exclusion
var_states := ARRAY(SELECT statefp FROM state WHERE ST_Intersects(the_geom, var_pt) );
IF array_upper(var_states, 1) IS NULL THEN
-- We don't have any data for this state
RETURN;
END IF;
-- Find the street edges that this point is closest to with tolerance of 0.005 but only consider the edge if the point is contained in the right or left face
-- Then order addresses by proximity to road
FOR var_redge IN
SELECT *
FROM (SELECT DISTINCT ON(fullname) 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.center_pt,
side, to_number(fromhn, '999999') As fromhn, to_number(tohn, '999999') As tohn, ST_GeometryN(ST_Multi(line),1) As line, foo.dist
FROM
(SELECT e.the_geom As line, e.fullname, a.zip, s.stusps, ST_ClosestPoint(e.the_geom, var_pt) As center_pt, e.statefp, a.side, a.fromhn, a.tohn, ST_Distance_Sphere(e.the_geom, var_pt) As dist
FROM edges AS e INNER JOIN state As s ON (e.statefp = s.statefp AND s.statefp = ANY(var_states) )
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_Covers(fl.the_geom, var_pt) AND a.side = 'L') OR ( ST_Covers(fr.the_geom, var_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, var_pt, 0.005)
ORDER BY ST_Distance_Sphere(e.the_geom, var_pt) LIMIT 4) As foo
WHERE dist < 150 --less than 150 m
ORDER BY foo.fullname, foo.dist) As f ORDER BY f.dist LOOP
IF var_primary_line IS NULL THEN --this is the first time in the loop and our primary guess
var_primary_line := var_redge.line;
var_primary_dist := var_redge.dist;
END IF;
-- We only consider other edges as matches if they intersect our primary edge -- that would mean we are at a corner place
IF ST_Intersects(var_redge.line, var_primary_line) THEN
intpt := array_append(intpt,var_redge.center_pt);
IF var_redge.fullname IS NOT NULL THEN
street := array_append(street, (CASE WHEN include_strnum_range THEN COALESCE(var_redge.fromhn::varchar, '')::varchar || ' - ' || COALESCE(var_redge.tohn::varchar,'')::varchar || ' '::varchar ELSE '' END::varchar || var_redge.fullname::varchar)::varchar);
--interploate the number -- note that if fromhn > tohn we will be subtracting which is what we want
-- We only consider differential distances are reeally close from our primary pt
IF var_redge.dist < var_primary_dist*1.1 THEN
var_nstrnum := (var_redge.fromhn + ST_Line_Locate_Point(var_redge.line, var_pt)*(var_redge.tohn - var_redge.fromhn))::numeric(10);
-- The odd even street number side of street rule
IF (var_nstrnum % 2) != (var_redge.tohn % 2) THEN
var_nstrnum := CASE WHEN var_nstrnum + 1 NOT BETWEEN var_redge.fromhn AND var_redge.tohn THEN var_nstrnum - 1 ELSE var_nstrnum + 1 END;
END IF;
var_strnum := var_nstrnum::varchar;
var_addy := normalize_address( COALESCE(var_strnum::varchar || ' ', '') || var_redge.fullname || ', ' || var_redge.place || ', ' || var_redge.stusps || ' ' || var_redge.zip);
addy := array_append(addy, var_addy);
END IF;
END IF;
END IF;
END LOOP;
RETURN;
END;
$_$ LANGUAGE plpgsql STABLE;
CREATE OR REPLACE FUNCTION public.reverse_geocode(IN pt geometry, OUT intpt geometry[],
OUT addy NORM_ADDY[],
OUT street varchar[]) RETURNS RECORD
AS
$$
-- default to not including street range in cross streets
SELECT reverse_geocode($1,false);
$$
language sql STABLE;
prev parent reply other threads:[~2011-04-03 19:13 UTC|newest]
Thread overview: 9+ messages / expand[flat|nested] mbox.gz Atom feed top
2011-04-03 15:22 bug#8417: hexlify-buffer Don Harter
2011-04-03 18:09 ` Eli Zaretskii
[not found] ` <4D98C9FC.8010307@comcast.net>
2011-04-03 20:55 ` Eli Zaretskii
[not found] ` <4D98CB8C.4070007@comcast.net>
2011-04-03 21:06 ` Eli Zaretskii
[not found] ` <4D990F77.60008@comcast.net>
2011-04-04 2:59 ` Eli Zaretskii
[not found] ` <4D99704F.5000308@comcast.net>
2011-04-04 11:50 ` Eli Zaretskii
2011-04-04 18:43 ` Eli Zaretskii
2011-04-04 18:50 ` Eli Zaretskii
2011-04-03 19:13 ` Don Harter [this message]
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=4D98C6D6.2030005@comcast.net \
--to=donharter@comcast.net \
--cc=8417@debbugs.gnu.org \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
Code repositories for project(s) associated with this external index
https://git.savannah.gnu.org/cgit/emacs.git
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is an external index of several public inboxes,
see mirroring instructions on how to clone and mirror
all data and code used by this external index.