[postgis-tickets] [PostGIS] #2734: TIGER geocoder gives no results with omitted zipcode
PostGIS
trac at osgeo.org
Tue May 13 22:27:11 PDT 2014
#2734: TIGER geocoder gives no results with omitted zipcode
----------------------------+-----------------------------------------------
Reporter: futuraprime | Owner: robe
Type: defect | Status: new
Priority: medium | Milestone: PostGIS 2.2.0
Component: tiger geocoder | Version: 2.1.x
Keywords: |
----------------------------+-----------------------------------------------
Comment(by robe):
Evan,
So what I was saying -- might as well say it here too for completeness.
From the debugged output, it looks like the 10012 zip is in the check so
I'm feeling more and more that its the limits being set.
Try running the debugged sql output: Except change the LIMIT 5000 to
something like LIMIT 10000 and LIMIT 1000 to like LIMIT 5000 and LIMIT 100
to something like LIMIT 1000
So basically try running this query:
{{{
WITH
a1 AS (SELECT f.*, addr.fromhn, addr.tohn, addr.side , addr.zip
FROM (SELECT * FROM featnames
WHERE statefp =
'36' AND ( lower(name) = 'broadway' or lower(fullname) LIKE 'broadway '
|| '%' )) AS f LEFT JOIN (SELECT * FROM addr WHERE addr.statefp = '36')
As addr ON (addr.tlid = f.tlid AND addr.statefp = f.statefp)
WHERE
'{10001,10002,10003,10004,10005,10006,10007,10009,10010,10011,10012,10013,10014,10016,10017,10018,10019,10020,10021,10022,10023,10024,10025,10026,10027,10028,10029,10030,10031,10032,10033,10034,10035,10036,10037,10038,10039,10040,10044,10065,10069,10075,10107,10110,10112,10118,10119,10120,10123,10128,10166,10169,10175,10270,10278,10279,10280,10281,10282,10285,10301,10301,10302,10303,10304,10305,10306,10307,10308,10309,10310,10312,10314,10451,10452,10453,10454,10455,10456,10457,10458,10459,10460,10461,10462,10463,10463,10464,10464,10465,10466,10466,10467,10468,10469,10470,10470,10471,10472,10473,10474,10475,10550,10550,10704,10704,10705,10705,11001,11001,11003,11004,11040,11101,11102,11103,11104,11105,11106,11109,11201,11203,11204,11205,11206,11207,11207,11208,11208,11209,11210,11211,11212,11213,11214,11215,11216,11217,11218,11219,11219,11220,11221,11222,11223,11223,11224,11225,11226,11228,11229,11230,11231,11232,11233,11234,11235,11236,11237,11237,11238,11239,11249,11252,11354,11355,11356,11357,11358,11359,11360,11361,11362,11363,11364,11365,11366,11367,11368,11369,11370,11372,11373,11374,11375,11377,11378,11379,11385,11385,11411,11412,11413,11414,11414,11415,11416,11416,11417,11417,11418,11419,11420,11421,11421,11422,11422,11423,11426,11426,11427,11428,11428,11429,11429,11430,11432,11433,11434,11435,11436,11580,11580,11691,11691,11692,11693,11694,11695,11697}'::text[]
IS NULL OR addr.zip =
ANY('{10001,10002,10003,10004,10005,10006,10007,10009,10010,10011,10012,10013,10014,10016,10017,10018,10019,10020,10021,10022,10023,10024,10025,10026,10027,10028,10029,10030,10031,10032,10033,10034,10035,10036,10037,10038,10039,10040,10044,10065,10069,10075,10107,10110,10112,10118,10119,10120,10123,10128,10166,10169,10175,10270,10278,10279,10280,10281,10282,10285,10301,10301,10302,10303,10304,10305,10306,10307,10308,10309,10310,10312,10314,10451,10452,10453,10454,10455,10456,10457,10458,10459,10460,10461,10462,10463,10463,10464,10464,10465,10466,10466,10467,10468,10469,10470,10470,10471,10472,10473,10474,10475,10550,10550,10704,10704,10705,10705,11001,11001,11003,11004,11040,11101,11102,11103,11104,11105,11106,11109,11201,11203,11204,11205,11206,11207,11207,11208,11208,11209,11210,11211,11212,11213,11214,11215,11216,11217,11218,11219,11219,11220,11221,11222,11223,11223,11224,11225,11226,11228,11229,11230,11231,11232,11233,11234,11235,11236,11237,11237,11238,11239,11249,11252,11354,11355,11356,11357,11358,11359,11360,11361,11362,11363,11364,11365,11366,11367,11368,11369,11370,11372,11373,11374,11375,11377,11378,11379,11385,11385,11411,11412,11413,11414,11414,11415,11416,11416,11417,11417,11418,11419,11420,11421,11421,11422,11422,11423,11426,11426,11427,11428,11428,11429,11429,11430,11432,11433,11434,11435,11436,11580,11580,11691,11691,11692,11693,11694,11695,11697}'::text[])
OR addr.zip IS NULL
ORDER BY CASE WHEN lower(f.fullname) =
'broadway ' THEN 0 ELSE 1 END
LIMIT 10000
),
a2 AS (SELECT f.*, addr.fromhn, addr.tohn, addr.side , addr.zip
FROM (SELECT * FROM featnames
WHERE statefp =
'36' AND ( lower(name) = 'houston' or lower(fullname) LIKE 'houston st'
|| '%' ) ) AS f LEFT JOIN (SELECT * FROM addr WHERE addr.statefp = '36')
AS addr ON (addr.tlid = f.tlid AND addr.statefp = f.statefp)
WHERE
'{10001,10002,10003,10004,10005,10006,10007,10009,10010,10011,10012,10013,10014,10016,10017,10018,10019,10020,10021,10022,10023,10024,10025,10026,10027,10028,10029,10030,10031,10032,10033,10034,10035,10036,10037,10038,10039,10040,10044,10065,10069,10075,10107,10110,10112,10118,10119,10120,10123,10128,10166,10169,10175,10270,10278,10279,10280,10281,10282,10285,10301,10301,10302,10303,10304,10305,10306,10307,10308,10309,10310,10312,10314,10451,10452,10453,10454,10455,10456,10457,10458,10459,10460,10461,10462,10463,10463,10464,10464,10465,10466,10466,10467,10468,10469,10470,10470,10471,10472,10473,10474,10475,10550,10550,10704,10704,10705,10705,11001,11001,11003,11004,11040,11101,11102,11103,11104,11105,11106,11109,11201,11203,11204,11205,11206,11207,11207,11208,11208,11209,11210,11211,11212,11213,11214,11215,11216,11217,11218,11219,11219,11220,11221,11222,11223,11223,11224,11225,11226,11228,11229,11230,11231,11232,11233,11234,11235,11236,11237,11237,11238,11239,11249,11252,11354,11355,11356,11357,11358,11359,11360,11361,11362,11363,11364,11365,11366,11367,11368,11369,11370,11372,11373,11374,11375,11377,11378,11379,11385,11385,11411,11412,11413,11414,11414,11415,11416,11416,11417,11417,11418,11419,11420,11421,11421,11422,11422,11423,11426,11426,11427,11428,11428,11429,11429,11430,11432,11433,11434,11435,11436,11580,11580,11691,11691,11692,11693,11694,11695,11697}'::text[]
IS NULL OR addr.zip =
ANY('{10001,10002,10003,10004,10005,10006,10007,10009,10010,10011,10012,10013,10014,10016,10017,10018,10019,10020,10021,10022,10023,10024,10025,10026,10027,10028,10029,10030,10031,10032,10033,10034,10035,10036,10037,10038,10039,10040,10044,10065,10069,10075,10107,10110,10112,10118,10119,10120,10123,10128,10166,10169,10175,10270,10278,10279,10280,10281,10282,10285,10301,10301,10302,10303,10304,10305,10306,10307,10308,10309,10310,10312,10314,10451,10452,10453,10454,10455,10456,10457,10458,10459,10460,10461,10462,10463,10463,10464,10464,10465,10466,10466,10467,10468,10469,10470,10470,10471,10472,10473,10474,10475,10550,10550,10704,10704,10705,10705,11001,11001,11003,11004,11040,11101,11102,11103,11104,11105,11106,11109,11201,11203,11204,11205,11206,11207,11207,11208,11208,11209,11210,11211,11212,11213,11214,11215,11216,11217,11218,11219,11219,11220,11221,11222,11223,11223,11224,11225,11226,11228,11229,11230,11231,11232,11233,11234,11235,11236,11237,11237,11238,11239,11249,11252,11354,11355,11356,11357,11358,11359,11360,11361,11362,11363,11364,11365,11366,11367,11368,11369,11370,11372,11373,11374,11375,11377,11378,11379,11385,11385,11411,11412,11413,11414,11414,11415,11416,11416,11417,11417,11418,11419,11420,11421,11421,11422,11422,11423,11426,11426,11427,11428,11428,11429,11429,11430,11432,11433,11434,11435,11436,11580,11580,11691,11691,11692,11693,11694,11695,11697}'::text[])
or addr.zip IS NULL
ORDER BY CASE WHEN lower(f.fullname) = 'houston
st' THEN 0 ELSE 1 END
LIMIT 10000
),
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)
WHERE e.statefp = '36'
ORDER BY CASE WHEN lower(a.name) =
'houston' THEN 0 ELSE 1 END + CASE WHEN lower(e.fullname) = 'houston st'
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 =
'36') 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)
AND ARRAY[e.tnidf, e.tnidt] &&
ARRAY[e1.tnidf, e1.tnidt] )
WHERE (lower(e.fullname) = 'houston st' or
lower(a.name) LIKE 'houston' || '%')
ORDER BY CASE WHEN lower(a.name) =
'houston' THEN 0 ELSE 1 END + CASE WHEN lower(e.fullname) = 'houston st'
THEN 0 ELSE 1 END
LIMIT 1000
),
segs AS (SELECT DISTINCT ON(e1.tlid, e1.side)
CASE WHEN e1.tnidf = e2.tnidf OR e1.tnidf = e2.tnidt
THEN
e1.fromhn
ELSE
e1.tohn END As address, e1.predirabrv As
fedirp, COALESCE(e1.prequalabr || ' ','' ) || e1.name As fename,
COALESCE(e1.suftypabrv,e1.pretypabrv) As
fetype, e1.sufdirabrv AS fedirs,
p.name As place, e1.zip,
CASE WHEN e1.tnidf = e2.tnidf OR e1.tnidf =
e2.tnidt THEN
ST_StartPoint(ST_GeometryN(ST_Multi(e1.the_geom),1))
ELSE
ST_EndPoint(ST_GeometryN(ST_Multi(e1.the_geom),1)) END AS geom ,
CASE WHEN lower(p.name) = 'new york' THEN
0 ELSE 1 END
+ levenshtein_ignore_case(p.name, 'new
york')
+ levenshtein_ignore_case(e1.name ||
COALESCE(' ' || e1.sufqualabr, ''),'broadway') +
CASE WHEN e1.fullname = 'broadway ' THEN 0
ELSE levenshtein_ignore_case(e1.fullname, 'broadway ') END +
+ levenshtein_ignore_case(e2.name ||
COALESCE(' ' || e2.sufqualabr, ''),'houston')
AS a_rating
FROM e1
INNER JOIN e2 ON (
ST_Intersects(e1.the_geom,
e2.the_geom) )
INNER JOIN (SELECT * FROM faces WHERE statefp
= '36') As fa1 ON (e1.tfid = fa1.tfid )
LEFT JOIN place AS p ON (fa1.placefp = p.placefp
AND p.statefp = '36' )
ORDER BY e1.tlid, e1.side, a_rating LIMIT 10*4 )
SELECT address, fedirp , fename, fetype,fedirs,place, zip , geom,
a_rating
FROM segs ORDER BY a_rating LIMIT 10
}}}
--
Ticket URL: <http://trac.osgeo.org/postgis/ticket/2734#comment:2>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.
More information about the postgis-tickets
mailing list