[postgis-tickets] [PostGIS] #4826: Geocoder gives goofy resutls for 1 Main St, Hanover, MA

[PostGIS]

#4826: Geocoder gives goofy resutls for 1 Main St, Hanover, MA
-----------------------------+---------------------------
  Reporter:  robe            |      Owner:  robe
      Type:  defect          |     Status:  new
  Priority:  medium          |  Milestone:  PostGIS 3.1.1
 Component:  tiger geocoder  |    Version:  3.1.x
Resolution:                  |   Keywords:
-----------------------------+---------------------------

Comment (by robe):

 Okay this is a more generic query that should handle all these.  I put in
 a custom table to distinguish from main.  If this works well I'll
 incorporate into the main prep logic add add as part of the main state
 lookups.

 It basically adds in all the county sub areas zips that aren't already
 present. So should handle all cases where the place is a county sub.


 {{{
 CREATE TABLE tiger_data.custom_zip_lookup_base_custom( CONSTRAINT
 pk_custom_zip_state_loc_city PRIMARY KEY (zip, state, county, city,
 statefp)) INHERITS (tiger.zip_lookup_base);

 INSERT INTO tiger_data.custom_zip_lookup_base_custom(zip, state, county,
 city, statefp)
 WITH csub AS (SELECT DISTINCT COALESCE(me.zipl, me.zipr) AS zip, s.stusps
 AS state, c.name As  county, cs.name AS city, cs.statefp
 FROM tiger_data.ma_faces AS f INNER JOIN tiger.county AS c ON (c.countyfp
 = f.countyfp AND c.statefp = f.statefp)
                           INNER JOIN tiger_data.ma_cousub AS cs ON
 (f.cousubfp = cs.cousubfp AND cs.countyfp = c.countyfp AND cs.statefp =
 c.statefp)
         INNER JOIN tiger.state As s ON s.statefp = f.statefp
                         INNER JOIN tiger_data.ma_edges AS me ON me.tfidl =
 f.tfid
                 WHERE  (me.zipl IS NOT NULL or me.zipr IS NOT NULL) )
 SELECT csub.zip, csub.state, csub.county, csub.city, csub.statefp
 FROM csub LEFT JOIN tiger.zip_lookup_base AS zb ON (csub.city =zb.city AND
 csub.statefp = zb.statefp  AND zb.zip = csub.zip)
 WHERE zb.zip IS NULL;
 }}}

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4826#comment:3>
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.