OpenGeoDB obstacles

#geodb#opengeodb#plz#postleitzahlen#strassen#strassenverzeichnis

preliminary thoughts

current project vaD Germany Hoch postal codes list (city names rap) poQ. "more data better" ;-) 'ach minimum requirements. end: autocomplete input (postal code 'ej place show). first task: data obtain. Mr. Google promising project OpenGeoDB Daq jaH. place Daq conceivable data Hoch deliver:

official municipality key
sort name
name
WGS84 coordinates
latitude
longitude
administrative union
administrative union sort name
postal code
telephone area code
population
approximate population (wtf!)
exact population
area
vehicle registration
type (entry local/specialized) level
marked invalid

data jIH poQ tu' quickly clear. SQL dump download. 3 files:

opengeodb-begin.sql: small file (tables create neH).
DE.sql: actual data. 60 MB insert statements puh
opengeodb-end.sql: finishing. titles create, indexes generate.

basic structure

opengeodb-begin.sql wa'DIch. obstacle aka error number 1.

[FEHLER in Abfrage 3] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB CHARACTER SET utf8' at line 10

database TYPE=InnoDB Sovbe'. dump MySQL 4 qaSpa' version created. MySQL 4 qaSDI' TYPE pagh - ENGINE. solution: dump editor Daq poSmoH, "TYPE=InnoDB" "ENGINE=InnoDB" replace. dump again import.

data set

DE.sql import (Germany neH. Austrians AT.sql, Swiss CH.sql) 'ej wait... wait... ~600,000 records import... error message: 0 date pagh (.. really?)

Incorrect date value: '0' for column 'valid_since' at row 1

wait vain. table empty again 'ej what went wrong think. data OpenGeoDB correct assume; MySQL configuration 0 allow pagh. database strict pagh exception 0 as date allow? MySQL manual strict_mode variable: SHOW VARIABLES LIKE 'sql_mode':

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

"NO_ZERO_IN_DATE,NO_ZERO_DATE" remove, persistent my.cnf (/usr/local/etc/my.cnf) Daq configure. sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION append, server brew services restart mysql restart. "SHOW VARIABLES LIKE 'sql_mode'" check. new try - DE.SQL import 'ej wait. work :)

finishing

step 3: opengeodb-end.sql import ("finishing" - titles insert, indexes create). error pagh puh. data imported, process laH. changes temporary neH vaj set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'.

TL;DR

opengeodb.giswiki.org/wiki/OpenGeoDB_Downloads vo' data download: opengeodb-begin.sql, opengeodb-end.sql, DE.sql
opengeodb-begin.sql editor Daq poSmoH, TYPE=InnoDB ENGINE=InnoDB replace
opengeodb-begin.sql import
sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' execute
DE.sql import
opengeodb-end.sql import