After execute (i renamed "name" to "namex", because phpmyadmin show me "name" as syntax error)
INSERT INTO zip_coordinates (zc_loc_id, zc_zip, zc_location_name, zc_lat, zc_lon) SELECT gl.loc_id, plz.text_val, namex.text_val, coord.lat, coord.lon FROM geodb_textdata plz LEFT JOIN geodb_textdata namex ON plz.loc_id = namex.loc_id LEFT JOIN geodb_locations gl ON plz.loc_id = gl.loc_id LEFT JOIN geodb_hierarchies tier ON plz.loc_id = tier.loc_id LEFT JOIN geodb_coordinates coord ON plz.loc_id = coord.loc_id WHERE plz.text_type = 500300000 / Postleitzahl / AND namex.text_type = 500100000 / Name / AND tier.id_lvl1 = 104 AND tier.id_lvl2 = 105 / Bundesrepublik Deutschland / AND namex.text_locale = "de" / deutschsprachige Version / AND gl.loc_type IN ( 100600000 / pol. Gliederung /, 100700000 / Ortschaft / )
Result: "37684 Datensätze eingefügt." and I get many many many warnings, this are only few lines as example:
Warning: #1048 Feld 'zc_lat' darf nicht NULL sein
Warning: #1265 Daten abgeschnitten für Feld 'zc_zip' in Zeile 4
Warning: #1265 Daten abgeschnitten für Feld 'zc_zip' in Zeile 20
Warning: #1265 Daten abgeschnitten für Feld 'zc_zip' in Zeile 11
Warning: #1265 Daten abgeschnitten für Feld 'zc_zip' in Zeile 29
Warning: #1265 Daten abgeschnitten für Feld 'zc_zip' in Zeile 35
The reason is, that field zc_zip get many zipcodes instead of a single code. The field have a VARCHAR length of 10, but its try to insert this too "10243,10244,10245" and cut its length to 10 = "10243,1024". There are lines that have 161 characters in this line and shorted to 10.
Here are 173 entries with "," in zc_zip field.
The data coming from
SELECT * FROM geodb_textdata WHERE text_type=500300000 AND text_val LIKE "%,%" ORDER BY Length(text_val) DESC
and 24 entries are empty for text_val
SELECT * FROM geodb_textdata WHERE text_type=500300000 ORDER BY text_val ASC
So in wiki example is a part missing that ignore empty entries and split entries with more as one zipcode.
UPDATE:
and its insert the same zipcode+city more times with different zc_lat and zc_lot. Example:
1 | 14308 | 19348 | Berge bei Perleberg | 53.23746 | 11.87077
2 | 14308 | 19348 | Berge bei Perleberg | 53.23746 | 53.2333
3 | 14308 | 19348 | Berge bei Perleberg | 11.8667 | 11.87077
Reason?
Last edit: Fintch 2019-01-04
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Here http://opengeodb.org/wiki/OpenGeoDB_-_Umkreissuche is a example to create and import zipcodes.
After execute (i renamed "name" to "namex", because phpmyadmin show me "name" as syntax error)
Result: "37684 Datensätze eingefügt." and I get many many many warnings, this are only few lines as example:
The reason is, that field zc_zip get many zipcodes instead of a single code. The field have a VARCHAR length of 10, but its try to insert this too "10243,10244,10245" and cut its length to 10 = "10243,1024". There are lines that have 161 characters in this line and shorted to 10.
Here are 173 entries with "," in zc_zip field.
The data coming from
and 24 entries are empty for text_val
So in wiki example is a part missing that ignore empty entries and split entries with more as one zipcode.
UPDATE:
and its insert the same zipcode+city more times with different zc_lat and zc_lot. Example:
1 | 14308 | 19348 | Berge bei Perleberg | 53.23746 | 11.87077
2 | 14308 | 19348 | Berge bei Perleberg | 53.23746 | 53.2333
3 | 14308 | 19348 | Berge bei Perleberg | 11.8667 | 11.87077
Reason?
Last edit: Fintch 2019-01-04