Created
December 13, 2015 14:05
-
-
Save larsks/92e84999ccb535e4fc9d to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| gis=# select geocode('20 PREBLE GARDENS, BELMONT, MA 02478'); | |
| NOTICE: 2015-12-13 14:04:41.028434+00 input: 20 PREBLE GARDENS, BELMONT, MA 02478 | |
| CONTEXT: PL/pgSQL function geocode(character varying,integer,geometry) line 11 at assignment | |
| NOTICE: 2015-12-13 14:04:41.028519+00 addressString: 20 | |
| CONTEXT: PL/pgSQL function geocode(character varying,integer,geometry) line 11 at assignment | |
| NOTICE: 2015-12-13 14:04:41.028567+00 zipString: 02478 | |
| CONTEXT: PL/pgSQL function geocode(character varying,integer,geometry) line 11 at assignment | |
| NOTICE: 2015-12-13 14:04:41.028658+00 fullStreet: 20 PREBLE GARDENS, BELMONT, MA | |
| CONTEXT: PL/pgSQL function geocode(character varying,integer,geometry) line 11 at assignment | |
| NOTICE: 2015-12-13 14:04:41.029136+00 stateAbbrev: MA | |
| CONTEXT: PL/pgSQL function geocode(character varying,integer,geometry) line 11 at assignment | |
| NOTICE: 2015-12-13 14:04:41.029293+00 fullStreet: PREBLE GARDENS | |
| CONTEXT: PL/pgSQL function geocode(character varying,integer,geometry) line 11 at assignment | |
| NOTICE: 2015-12-13 14:04:41.029317+00 location: BELMONT | |
| CONTEXT: PL/pgSQL function geocode(character varying,integer,geometry) line 11 at assignment | |
| NOTICE: fullStreet: PREBLE GARDENS | |
| CONTEXT: PL/pgSQL function geocode(character varying,integer,geometry) line 11 at assignment | |
| NOTICE: location: BELMONT | |
| CONTEXT: PL/pgSQL function geocode(character varying,integer,geometry) line 11 at assignment | |
| NOTICE: internal: <NULL> | |
| CONTEXT: PL/pgSQL function geocode(character varying,integer,geometry) line 11 at assignment | |
| NOTICE: location: BELMONT | |
| CONTEXT: PL/pgSQL function geocode(character varying,integer,geometry) line 11 at assignment | |
| NOTICE: street Type: GARDENS, street Type abbrev: Gdns | |
| CONTEXT: PL/pgSQL function geocode(character varying,integer,geometry) line 11 at assignment | |
| NOTICE: 2015-12-13 14:04:41.030624+00 streetTypeAbbrev: Gdns | |
| CONTEXT: PL/pgSQL function geocode(character varying,integer,geometry) line 11 at assignment | |
| NOTICE: 2015-12-13 14:04:41.030783+00 reduced street: PREBLE | |
| CONTEXT: PL/pgSQL function geocode(character varying,integer,geometry) line 11 at assignment | |
| NOTICE: 2015-12-13 14:04:41.031282+00 location trying to extract postdir: BELMONT, tempstring: <NULL>, rawInput: 20 PREBLE GARDENS, BELMONT, MA 02478 | |
| CONTEXT: PL/pgSQL function geocode(character varying,integer,geometry) line 11 at assignment | |
| NOTICE: stmt: WITH a AS | |
| ( SELECT * | |
| FROM (SELECT f.*, ad.side, ad.zip, ad.fromhn, ad.tohn, | |
| RANK() OVER(ORDER BY diff_zip(ad.zip,'02478') + CASE WHEN lower(f.name) = lower('PREBLE') THEN 0 ELSE levenshtein_ignore_case(f.name, lower('PREBLE') ) END + | |
| levenshtein_ignore_case(f.fullname, lower('PREBLE' || ' ' || COALESCE('Gdns','')) ) | |
| + CASE WHEN (greatest_hn(ad.fromhn,ad.tohn) % 2)::integer = ('20' % 2)::integer THEN 0 ELSE 1 END | |
| + CASE WHEN '20'::integer BETWEEN least_hn(ad.fromhn,ad.tohn) AND greatest_hn(ad.fromhn, ad.tohn) | |
| THEN 0 ELSE 4 END | |
| + CASE WHEN lower('Gdns') = lower(f.suftypabrv) OR lower('Gdns') = lower(f.pretypabrv) THEN 0 ELSE 1 END | |
| + rate_attributes(NULL, f.predirabrv, 'PREBLE', f.name , 'Gdns', suftypabrv , NULL, sufdirabrv, prequalabr) | |
| ) | |
| As rank | |
| FROM featnames As f INNER JOIN addr As ad ON (f.tlid = ad.tlid) | |
| WHERE '25' = f.statefp AND '25' = ad.statefp | |
| AND (lower(f.fullname) LIKE (COALESCE(NULL || ' ','') || lower('PREBLE') || '%')::text OR lower(f.name) = lower('PREBLE') OR soundex(f.name) = soundex('PREBLE') ) AND ( ad.zip = ANY('{02477,02478,02479}'::varchar[]) ) ) AS foo ORDER BY rank LIMIT 30 ) | |
| SELECT * FROM ( | |
| SELECT DISTINCT ON (sub.predirabrv,sub.fename,COALESCE(sub.suftypabrv, sub.pretypabrv) ,sub.sufdirabrv,sub.place,s.stusps,sub.zip) sub.predirabrv as fedirp, sub.fename, COALESCE(sub.suftypabrv, sub.pretypabrv) as fetype, sub.sufdirabrv as fedirs, sub.place , s.stusps as state, sub.zip as zip, interpolate_from_address('20', sub.fromhn, sub.tohn, sub.the_geom, sub.side) as address_geom, sub.sub_rating + least(coalesce(diff_zip('02478' , sub.zip),0), 10)::integer + coalesce(levenshtein_ignore_case('BELMONT', sub.place),5) as sub_rating, sub.exact_address as exact_address, sub.tohn, sub.fromhn FROM ( SELECT tlid, predirabrv, COALESCE(b.prequalabr || ' ','' ) || b.name As fename, suftypabrv, sufdirabrv, fromhn, tohn, | |
| side, zip, rate_attributes(NULL, predirabrv, 'PREBLE', b.name , 'Gdns', suftypabrv , NULL, sufdirabrv, prequalabr) + CASE WHEN '20'::integer IS NULL OR b.fromhn IS NULL THEN 20 WHEN '20'::integer >= least_hn(b.fromhn, b.tohn) AND '20'::integer <= greatest_hn(b.fromhn,b.tohn) AND ('20'::integer % 2) = (to_number(b.fromhn,'99999999') % 2)::integer THEN 0 WHEN '20'::integer >= least_hn(b.fromhn,b.tohn) AND '20'::integer <= greatest_hn(b.fromhn,b.tohn) THEN 2 ELSE ((1.0 - (least_hn('20'::text,least_hn(b.fromhn,b.tohn)::text)::numeric / (greatest(1,greatest_hn('20'::text,greatest_hn(b.fromhn,b.tohn)::text))) )) * 5)::integer + 5 END as sub_rating,'20'::integer >= least_hn(b.fromhn,b.tohn) AND '20'::integer <= greatest_hn(b.fromhn,b.tohn) AND ('20' % 2)::numeric::integer = (to_number(b.fromhn,'99999999') % 2) as exact_address, b.name, b.prequalabr, b.pretypabrv, b.tfidr, b.tfidl, b.the_geom, b.place FROM | |
| (SELECT a.tlid, a.fullname, a.name, a.predirabrv, a.suftypabrv, a.sufdirabrv, a.prequalabr, a.pretypabrv, | |
| b.the_geom, tfidr, tfidl, | |
| a.side , | |
| a.fromhn, | |
| a.tohn, | |
| a.zip, | |
| p.name as place | |
| FROM a INNER JOIN edges As b ON (a.statefp = b.statefp AND a.tlid = b.tlid ) | |
| INNER JOIN faces AS f ON ('25' = f.statefp AND ( (b.tfidl = f.tfid AND a.side = 'L') OR (b.tfidr = f.tfid AND a.side = 'R' ) )) | |
| INNER JOIN place p ON ('25' = p.statefp AND f.placefp = p.placefp ) | |
| WHERE a.statefp = '25' AND b.statefp = '25' | |
| ) As b | |
| ORDER BY 10 , 11 DESC | |
| LIMIT 20 | |
| ) AS sub | |
| JOIN state s ON ('25' = s.statefp) | |
| ORDER BY 1,2,3,4,5,6,7,9 | |
| LIMIT 20) As foo ORDER BY sub_rating, exact_address DESC LIMIT 100 | |
| CONTEXT: PL/pgSQL function geocode(norm_addy,integer,geometry) line 14 at FOR over SELECT rows | |
| PL/pgSQL function geocode(character varying,integer,geometry) line 26 at RETURN QUERY | |
| NOTICE: SELECT DISTINCT ON (sub.predirabrv,sub.fename,COALESCE(sub.suftypabrv, sub.pretypabrv) ,sub.sufdirabrv,coalesce(p.name,zip.city,cs.name,co.name),s.stusps,sub.zip) sub.predirabrv as fedirp, sub.fename, COALESCE(sub.suftypabrv, sub.pretypabrv) as fetype, sub.sufdirabrv as fedirs, coalesce(p.name,zip.city,cs.name,co.name)::varchar as place, s.stusps as state, sub.zip as zip, interpolate_from_address($1, sub.fromhn, sub.tohn, e.the_geom, sub.side) as address_geom, sub.sub_rating + least((coalesce(diff_zip($7 , sub.zip),0) *1.00/2)::integer, coalesce(levenshtein_ignore_case($7, sub.zip),0) ) + coalesce(least(levenshtein_ignore_case($3, coalesce(p.name,zip.city,cs.name,co.name)), levenshtein_ignore_case($3, coalesce(cs.name,co.name))),5) as sub_rating, sub.exact_address as exact_address FROM ( SELECT a.tlid, predirabrv, COALESCE(a.prequalabr || ' ','' ) || a.name As fename, suftypabrv, sufdirabrv, fromhn, tohn, | |
| side, a.statefp, zip, rate_attributes($5, a.predirabrv, $2, a.name , $4, a.suftypabrv , $6, a.sufdirabrv, a.prequalabr) + CASE WHEN $1::integer IS NULL OR b.fromhn IS NULL THEN 20 WHEN $1::integer >= least_hn(b.fromhn, b.tohn) AND $1::integer <= greatest_hn(b.fromhn,b.tohn) AND ($1::integer % 2) = (to_number(b.fromhn,'99999999') % 2)::integer THEN 0 WHEN $1::integer >= least_hn(b.fromhn,b.tohn) AND $1::integer <= greatest_hn(b.fromhn,b.tohn) THEN 2 ELSE ((1.0 - (least_hn($1::text,least_hn(b.fromhn,b.tohn)::text)::numeric / greatest(1,greatest_hn($1::text,greatest_hn(b.fromhn,b.tohn)::text)))) * 5)::integer + 5 END as sub_rating,$1::integer >= least_hn(b.fromhn,b.tohn) AND $1::integer <= greatest_hn(b.fromhn,b.tohn) AND ($1 % 2)::numeric::integer = (to_number(b.fromhn,'99999999') % 2) as exact_address, a.name, a.prequalabr, a.pretypabrv FROM featnames a join addr b ON (a.tlid = b.tlid AND a.statefp = b.statefp ) WHERE a.statefp = '25' AND a.mtfcc LIKE 'S%' AND b.zip IN ('02478') AND ( lower($2) = lower(a.name) OR ( a.prequalabr > '' AND trim(lower($2), lower(a.prequalabr) || ' ') = lower(a.name) ) OR numeric_streets_equal($2, a.name) ) ORDER BY 11 LIMIT 20 ) AS sub JOIN edges e ON ('25' = e.statefp AND sub.tlid = e.tlid AND e.mtfcc LIKE 'S%' ) JOIN state s ON ('25' = s.statefp) JOIN faces f ON ('25' = f.statefp AND (e.tfidl = f.tfid OR e.tfidr = f.tfid)) LEFT JOIN zip_lookup_base zip ON (sub.zip = zip.zip AND zip.statefp='25') LEFT JOIN place p ON ('25' = p.statefp AND f.placefp = p.placefp) LEFT JOIN county co ON ('25' = co.statefp AND f.countyfp = co.countyfp) LEFT JOIN cousub cs ON ('25' = cs.statefp AND cs.cosbidfp = sub.statefp || co.countyfp || f.cousubfp) WHERE ( (sub.side = 'L' and e.tfidl = f.tfid) OR (sub.side = 'R' and e.tfidr = f.tfid) ) ORDER BY 1,2,3,4,5,6,7,9 LIMIT 10 | |
| CONTEXT: PL/pgSQL function geocode(norm_addy,integer,geometry) line 14 at FOR over SELECT rows | |
| PL/pgSQL function geocode(character varying,integer,geometry) line 26 at RETURN QUERY | |
| NOTICE: PREPARE query_base_geo(integer, varchar,varchar,varchar,varchar,varchar,varchar,geometry) As SELECT DISTINCT ON (sub.predirabrv,sub.fename,COALESCE(sub.suftypabrv, sub.pretypabrv) ,sub.sufdirabrv,coalesce(p.name,zip.city,cs.name,co.name),s.stusps,sub.zip) sub.predirabrv as fedirp, sub.fename, COALESCE(sub.suftypabrv, sub.pretypabrv) as fetype, sub.sufdirabrv as fedirs, coalesce(p.name,zip.city,cs.name,co.name)::varchar as place, s.stusps as state, sub.zip as zip, interpolate_from_address($1, sub.fromhn, sub.tohn, e.the_geom, sub.side) as address_geom, sub.sub_rating + least((coalesce(diff_zip($7 , sub.zip),0) *1.00/2)::integer, coalesce(levenshtein_ignore_case($7, sub.zip),0) ) + coalesce(least(levenshtein_ignore_case($3, coalesce(p.name,zip.city,cs.name,co.name)), levenshtein_ignore_case($3, coalesce(cs.name,co.name))),5) as sub_rating, sub.exact_address as exact_address FROM ( SELECT a.tlid, predirabrv, COALESCE(a.prequalabr || ' ','' ) || a.name As fename, suftypabrv, sufdirabrv, fromhn, tohn, | |
| side, a.statefp, zip, rate_attributes($5, a.predirabrv, $2, a.name , $4, a.suftypabrv , $6, a.sufdirabrv, a.prequalabr) + CASE WHEN $1::integer IS NULL OR b.fromhn IS NULL THEN 20 WHEN $1::integer >= least_hn(b.fromhn, b.tohn) AND $1::integer <= greatest_hn(b.fromhn,b.tohn) AND ($1::integer % 2) = (to_number(b.fromhn,'99999999') % 2)::integer THEN 0 WHEN $1::integer >= least_hn(b.fromhn,b.tohn) AND $1::integer <= greatest_hn(b.fromhn,b.tohn) THEN 2 ELSE ((1.0 - (least_hn($1::text,least_hn(b.fromhn,b.tohn)::text)::numeric / greatest(1,greatest_hn($1::text,greatest_hn(b.fromhn,b.tohn)::text)))) * 5)::integer + 5 END as sub_rating,$1::integer >= least_hn(b.fromhn,b.tohn) AND $1::integer <= greatest_hn(b.fromhn,b.tohn) AND ($1 % 2)::numeric::integer = (to_number(b.fromhn,'99999999') % 2) as exact_address, a.name, a.prequalabr, a.pretypabrv FROM featnames a join addr b ON (a.tlid = b.tlid AND a.statefp = b.statefp ) WHERE a.statefp = '25' AND a.mtfcc LIKE 'S%' AND b.zip IN ('02478') AND ( lower($2) = lower(a.name) OR ( a.prequalabr > '' AND trim(lower($2), lower(a.prequalabr) || ' ') = lower(a.name) ) OR numeric_streets_equal($2, a.name) ) ORDER BY 11 LIMIT 20 ) AS sub JOIN edges e ON ('25' = e.statefp AND sub.tlid = e.tlid AND e.mtfcc LIKE 'S%' ) JOIN state s ON ('25' = s.statefp) JOIN faces f ON ('25' = f.statefp AND (e.tfidl = f.tfid OR e.tfidr = f.tfid)) LEFT JOIN zip_lookup_base zip ON (sub.zip = zip.zip AND zip.statefp='25') LEFT JOIN place p ON ('25' = p.statefp AND f.placefp = p.placefp) LEFT JOIN county co ON ('25' = co.statefp AND f.countyfp = co.countyfp) LEFT JOIN cousub cs ON ('25' = cs.statefp AND cs.cosbidfp = sub.statefp || co.countyfp || f.cousubfp) WHERE ( (sub.side = 'L' and e.tfidl = f.tfid) OR (sub.side = 'R' and e.tfidr = f.tfid) ) ORDER BY 1,2,3,4,5,6,7,9 LIMIT 10 | |
| CONTEXT: PL/pgSQL function geocode(norm_addy,integer,geometry) line 14 at FOR over SELECT rows | |
| PL/pgSQL function geocode(character varying,integer,geometry) line 26 at RETURN QUERY | |
| NOTICE: EXECUTE query_base_geo(20,'PREBLE','BELMONT','Gdns',NULL,NULL,'02478',NULL); | |
| CONTEXT: PL/pgSQL function geocode(norm_addy,integer,geometry) line 14 at FOR over SELECT rows | |
| PL/pgSQL function geocode(character varying,integer,geometry) line 26 at RETURN QUERY | |
| NOTICE: DEALLOCATE query_base_geo; | |
| CONTEXT: PL/pgSQL function geocode(norm_addy,integer,geometry) line 14 at FOR over SELECT rows | |
| PL/pgSQL function geocode(character varying,integer,geometry) line 26 at RETURN QUERY | |
| NOTICE: SELECT DISTINCT ON (sub.predirabrv,sub.fename,COALESCE(sub.suftypabrv, sub.pretypabrv) ,sub.sufdirabrv,coalesce(p.name,zip.city,cs.name,co.name),s.stusps,sub.zip) sub.predirabrv as fedirp, sub.fename, COALESCE(sub.suftypabrv, sub.pretypabrv) as fetype, sub.sufdirabrv as fedirs, coalesce(p.name,zip.city,cs.name,co.name)::varchar as place, s.stusps as state, sub.zip as zip, interpolate_from_address($1, sub.fromhn, sub.tohn, e.the_geom, sub.side) as address_geom, sub.sub_rating + least((coalesce(diff_zip($7 , sub.zip),0) *1.00/2)::integer, coalesce(levenshtein_ignore_case($7, sub.zip),0) ) + coalesce(least(levenshtein_ignore_case($3, coalesce(p.name,zip.city,cs.name,co.name)), levenshtein_ignore_case($3, coalesce(cs.name,co.name))),5) as sub_rating, sub.exact_address as exact_address FROM ( SELECT a.tlid, predirabrv, COALESCE(a.prequalabr || ' ','' ) || a.name As fename, suftypabrv, sufdirabrv, fromhn, tohn, | |
| side, a.statefp, zip, rate_attributes($5, a.predirabrv, $2, a.name , $4, a.suftypabrv , $6, a.sufdirabrv, a.prequalabr) + CASE WHEN $1::integer IS NULL OR b.fromhn IS NULL THEN 20 WHEN $1::integer >= least_hn(b.fromhn, b.tohn) AND $1::integer <= greatest_hn(b.fromhn,b.tohn) AND ($1::integer % 2) = (to_number(b.fromhn,'99999999') % 2)::integer THEN 0 WHEN $1::integer >= least_hn(b.fromhn,b.tohn) AND $1::integer <= greatest_hn(b.fromhn,b.tohn) THEN 2 ELSE ((1.0 - (least_hn($1::text,least_hn(b.fromhn,b.tohn)::text)::numeric / greatest(1,greatest_hn($1::text,greatest_hn(b.fromhn,b.tohn)::text)))) * 5)::integer + 5 END as sub_rating,$1::integer >= least_hn(b.fromhn,b.tohn) AND $1::integer <= greatest_hn(b.fromhn,b.tohn) AND ($1 % 2)::numeric::integer = (to_number(b.fromhn,'99999999') % 2) as exact_address, a.name, a.prequalabr, a.pretypabrv FROM featnames a join addr b ON (a.tlid = b.tlid AND a.statefp = b.statefp ) WHERE a.statefp = '25' AND a.mtfcc LIKE 'S%' AND b.zip IN ('02474','02138','01773','02178','02472','02478','02476') AND ( soundex($2) = soundex(a.name) OR ( (length($2) > 15 or (length($2) > 7 AND a.prequalabr > '') ) AND lower(a.fullname) LIKE lower(substring($2,1,15)) || '%' ) OR numeric_streets_equal($2, a.name) ) ORDER BY 11 LIMIT 20 ) AS sub JOIN edges e ON ('25' = e.statefp AND sub.tlid = e.tlid AND e.mtfcc LIKE 'S%' ) JOIN state s ON ('25' = s.statefp) JOIN faces f ON ('25' = f.statefp AND (e.tfidl = f.tfid OR e.tfidr = f.tfid)) LEFT JOIN zip_lookup_base zip ON (sub.zip = zip.zip AND zip.statefp='25') LEFT JOIN place p ON ('25' = p.statefp AND f.placefp = p.placefp) LEFT JOIN county co ON ('25' = co.statefp AND f.countyfp = co.countyfp) LEFT JOIN cousub cs ON ('25' = cs.statefp AND cs.cosbidfp = sub.statefp || co.countyfp || f.cousubfp) WHERE ( (sub.side = 'L' and e.tfidl = f.tfid) OR (sub.side = 'R' and e.tfidr = f.tfid) ) ORDER BY 1,2,3,4,5,6,7,9 LIMIT 10 | |
| CONTEXT: PL/pgSQL function geocode(norm_addy,integer,geometry) line 14 at FOR over SELECT rows | |
| PL/pgSQL function geocode(character varying,integer,geometry) line 26 at RETURN QUERY | |
| NOTICE: PREPARE query_base_geo(integer, varchar,varchar,varchar,varchar,varchar,varchar,geometry) As SELECT DISTINCT ON (sub.predirabrv,sub.fename,COALESCE(sub.suftypabrv, sub.pretypabrv) ,sub.sufdirabrv,coalesce(p.name,zip.city,cs.name,co.name),s.stusps,sub.zip) sub.predirabrv as fedirp, sub.fename, COALESCE(sub.suftypabrv, sub.pretypabrv) as fetype, sub.sufdirabrv as fedirs, coalesce(p.name,zip.city,cs.name,co.name)::varchar as place, s.stusps as state, sub.zip as zip, interpolate_from_address($1, sub.fromhn, sub.tohn, e.the_geom, sub.side) as address_geom, sub.sub_rating + least((coalesce(diff_zip($7 , sub.zip),0) *1.00/2)::integer, coalesce(levenshtein_ignore_case($7, sub.zip),0) ) + coalesce(least(levenshtein_ignore_case($3, coalesce(p.name,zip.city,cs.name,co.name)), levenshtein_ignore_case($3, coalesce(cs.name,co.name))),5) as sub_rating, sub.exact_address as exact_address FROM ( SELECT a.tlid, predirabrv, COALESCE(a.prequalabr || ' ','' ) || a.name As fename, suftypabrv, sufdirabrv, fromhn, tohn, | |
| side, a.statefp, zip, rate_attributes($5, a.predirabrv, $2, a.name , $4, a.suftypabrv , $6, a.sufdirabrv, a.prequalabr) + CASE WHEN $1::integer IS NULL OR b.fromhn IS NULL THEN 20 WHEN $1::integer >= least_hn(b.fromhn, b.tohn) AND $1::integer <= greatest_hn(b.fromhn,b.tohn) AND ($1::integer % 2) = (to_number(b.fromhn,'99999999') % 2)::integer THEN 0 WHEN $1::integer >= least_hn(b.fromhn,b.tohn) AND $1::integer <= greatest_hn(b.fromhn,b.tohn) THEN 2 ELSE ((1.0 - (least_hn($1::text,least_hn(b.fromhn,b.tohn)::text)::numeric / greatest(1,greatest_hn($1::text,greatest_hn(b.fromhn,b.tohn)::text)))) * 5)::integer + 5 END as sub_rating,$1::integer >= least_hn(b.fromhn,b.tohn) AND $1::integer <= greatest_hn(b.fromhn,b.tohn) AND ($1 % 2)::numeric::integer = (to_number(b.fromhn,'99999999') % 2) as exact_address, a.name, a.prequalabr, a.pretypabrv FROM featnames a join addr b ON (a.tlid = b.tlid AND a.statefp = b.statefp ) WHERE a.statefp = '25' AND a.mtfcc LIKE 'S%' AND b.zip IN ('02474','02138','01773','02178','02472','02478','02476') AND ( soundex($2) = soundex(a.name) OR ( (length($2) > 15 or (length($2) > 7 AND a.prequalabr > '') ) AND lower(a.fullname) LIKE lower(substring($2,1,15)) || '%' ) OR numeric_streets_equal($2, a.name) ) ORDER BY 11 LIMIT 20 ) AS sub JOIN edges e ON ('25' = e.statefp AND sub.tlid = e.tlid AND e.mtfcc LIKE 'S%' ) JOIN state s ON ('25' = s.statefp) JOIN faces f ON ('25' = f.statefp AND (e.tfidl = f.tfid OR e.tfidr = f.tfid)) LEFT JOIN zip_lookup_base zip ON (sub.zip = zip.zip AND zip.statefp='25') LEFT JOIN place p ON ('25' = p.statefp AND f.placefp = p.placefp) LEFT JOIN county co ON ('25' = co.statefp AND f.countyfp = co.countyfp) LEFT JOIN cousub cs ON ('25' = cs.statefp AND cs.cosbidfp = sub.statefp || co.countyfp || f.cousubfp) WHERE ( (sub.side = 'L' and e.tfidl = f.tfid) OR (sub.side = 'R' and e.tfidr = f.tfid) ) ORDER BY 1,2,3,4,5,6,7,9 LIMIT 10 | |
| CONTEXT: PL/pgSQL function geocode(norm_addy,integer,geometry) line 14 at FOR over SELECT rows | |
| PL/pgSQL function geocode(character varying,integer,geometry) line 26 at RETURN QUERY | |
| NOTICE: EXECUTE query_base_geo(20,'PREBLE','BELMONT','Gdns',NULL,NULL,'02478',NULL); | |
| CONTEXT: PL/pgSQL function geocode(norm_addy,integer,geometry) line 14 at FOR over SELECT rows | |
| PL/pgSQL function geocode(character varying,integer,geometry) line 26 at RETURN QUERY | |
| NOTICE: DEALLOCATE query_base_geo; | |
| CONTEXT: PL/pgSQL function geocode(norm_addy,integer,geometry) line 14 at FOR over SELECT rows | |
| PL/pgSQL function geocode(character varying,integer,geometry) line 26 at RETURN QUERY | |
| gis=# |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment