osm-search / tiger-data Goto Github PK

The current SQL format restricts how the data can be processed: in PostgrSQL only. Nominatim is currently moving in a direction where the data might be preprocessed in Python before being added to the database. Therefore I would like to move to a simple CSV output format with the following columns:

• from
• to
• interpolation
• street
• city
• state (as a two-letter abbreviation)
• postcode
• geometry (as a WKT)

I'll start with a converter from the existing SQL to this CSV format but eventually the TIGER conversion script needs to be changed to emit CSV natively.

Just note here: I read the Tiger data specification and it mentioned the best source for geocoding is the "addrfeat.shp" file instead of "Edge.shp".

I got some news from @lonvia "The address range data didn't exist when Tiger support was implemented, so that would count as feature request (i.e. somebody needs to program it)"

We don't have a documented process to generate https://www.nominatim.org/data/us_postcodes.csv.gz yet. The TIGER EDGE file can help calculate centroids. The TIGER ZCTA files aren't zip codes (https://en.wikipedia.org/wiki/ZIP_Code_Tabulation_Area)

From Nominatim's Postgresql logfile

WARNING:  Negative house number range (-201 to 499)
WARNING:  Negative house number range (-123 to 101)
WARNING:  Negative house number range (-498 to -200)
WARNING:  Negative house number range (-499 to -201)
WARNING:  Negative house number range (-101 to 199)

Hi,

last week i imported a new planet with all additional data (master branch / postgres12)

In tiger data sql files there are more than 33 million "select tiger_line_import" statements. After import the postgresql logfile had a lot of syntax errors for approx 1.5% of these statements

• 410.000 errors of type "invalid syntax for type integer"
  (second parameter should be integer) e.g.
  SELECT tiger_line_import(ST_GeomFromText('LINESTRING(-85.937244 31.384285,-85.937262 31.384142,-85.937319 31.383953,-85.937452 31.383749,-85.937794 31.383686,-85.938286 31.383649,-85.938534 31.383803,-85.938865 31.384175)',4326), 'UN98', 'UN00', 'all', 'Pearl St', 'Coffee, AL', '36351');

• 60.000 errors of type "Road too short for number range"
  e.g. Road too short for number range 424 to 5364 on E State St, Clarke, AL (4.0973280119849804e-07)
  PL/pgSQL-Funktion tiger_line_import(geometry,integer,integer,text,text,text,text) Zeile 44 bei RAISE

While this doesn't affect nominatim from working maybe we should leave out the statements not matching database structure of the tiger-preprocessed files ?

Thanks

Nominatim during import detects if the to housenumber is smaller than from (https://github.com/osm-search/Nominatim/blob/59fe74ddf6749d93c93e88b1aeff0eb59a8e03ec/lib-sql/tiger_import_start.sql#L20) but for clarity and easier debugging we should enforce that during converting already.