search

bobosch / ods_osm

TYPO3 Extension OpenStreetMap
10 stars 20 forks source link

Increase length of housenumber? #194

Open sypets opened 3 months ago

sypets commented 3 months ago 
CREATE TABLE tx_odsosm_geocache (
       ....
    housenumber varchar(5) NOT NULL DEFAULT '',

https://github.com/bobosch/ods_osm/blob/master/ext_tables.sql#L40

When I add "Ammerländer Heerstraße 114-118" into the field "address", an exception is thrown:

An exception occurred while executing 
'INSERT INTO `tx_odsosm_geocache` (`search_city`, `country`, `state`, `city`, `zip`, `street`, `housenumber`, `tstamp`, `crdate`, `service_hit`, `lat`, `lon`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["Oldenburg", "De", "Niedersachsen", "Oldenburg", "26129", "Ammerl\u00e4nder Heerstra\u00dfe", "114-118", 1718702043, 1718702043, 1, "53.1488522", "8.1820344"]: 
Data too long for column 'housenumber' at row 1

114-118 is lengh 7, but field has length 5.

albig commented 2 months ago

Of course, we can rise this. in tt_address the equivalent would be building? It's varchar(255) for example. https://github.com/FriendsOfTYPO3/tt_address/blob/master/ext_tables.sql#L19C14-L19C26

sypets commented 2 months ago

@albig Do what you think is best. I am not so familiar with the internal DB structure and especially the mapping of tt_address and tx_odsosm_geocache.

The "114-118" was the only case I found in my DB which did not fit, I searched for housenumber with length 5 in DB.

albig commented 2 months ago

Nominatim (the address resolver service by OpenStreetMap which is used to georeference the address) uses TEXT for housenumber. So it's possible to get very long values.

https://nominatim.org/release-docs/latest/develop/Database-Layout/

So, we should increase the size in tx_odsosm_geocache.housenumber AND make sure, longer values get truncated before inserting in the cache database.