Back to blog
Mar 2018·3 min readAI-translated
Language:DeutschEnglishFrançaisÖsterreichischKlingonian Original: Deutsch

OpenGeoDB Hurdles

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

Preliminary thoughts

For a current project I needed a list of all postal codes in Germany including the associated city names. "The more data the better" applies, of course ;-) but these were at least the necessary minimum requirements. In the end the result should be an autocomplete input that shows us the postal code and the associated place. So the first task is getting the data, and Mr. Google led me to the quite promising project OpenGeoDB. It provides really every conceivable piece of data about a place that you could wish for:

  • Official municipality key
  • Sort name
  • Name
  • WGS84 coordinates
  • Latitude
  • Longitude
  • Administrative association
  • Sort name of an administrative association
  • Postal code
  • Telephone area code
  • Population
  • Approximate population (wtf!)
  • Exact population
  • Area
  • Vehicle registration code
  • Type (according to the entry local/specialized) level
  • Marked as invalid

So it was clear pretty quickly that I'd get the data I need here. I started by downloading the data as an SQL dump. Interestingly, there are 3 files:

  • opengeodb-begin.sql: a rather small file that only makes sure the tables are created.
  • DE.sql: the actual data. 60MB of insert statements phew
  • opengeodb-end.sql: the finishing. Titles are created and indexes generated.

The skeleton

Accordingly, we start with opengeodb-begin.sql to create the skeleton. Here we run into the hurdle aka error number 1.

[ERROR in query 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

So the database doesn't know TYPE=InnoDB. Too bad. That's because the dump was created with a version before MySQL 4. Since MySQL 4 the database no longer listens to TYPE but to ENGINE. Solution: open the dump in the editor of your choice and replace "TYPE=InnoDB" with "ENGINE=InnoDB". Import the dump again and enjoy the successful import.

The dataset

On to the dataset. Simply import the file DE.sql (which of course only covers Germany; Austrians take AT.sql, Swiss take CH.sql, etc.) and wait... wait... until the roughly 600,000 records have been imported ... and then get the error message that 0 is not a date (.. really?)

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

The waiting was for nothing. Yeah. So empty the table again and think about what went wrong. I basically assumed that the OpenGeoDB data itself is correct and that the problem lies in my MySQL configuration, which simply doesn't allow a 0. Maybe my database shouldn't be quite so strict and should allow a 0 as a date, just this once. The MySQL manual led me to the variable strict_mode, which I could read out with a quick SHOW VARIABLES LIKE 'sql_mode', and which returned

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

So "NO_ZERO_IN_DATE,NO_ZERO_DATE" had to go, and since this should happen persistently, I configured the my.cnf, which for me was at /usr/local/etc/my.cnf. There I had to append sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (= the output of SHOW VARIABLES without the values NO_ZERO_IN_DATE and NO_ZERO_DATE) and restart the server with a friendly brew services restart mysql. Afterwards check the state again with "SHOW VARIABLES LIKE 'sql_mode'" and see whether everything worked. Time for a new attempt - import DE.sql and wait. This time everything should work :)

Finishing

Step 3 is importing opengeodb-end.sql, which does the "finishing" - inserting titles and creating indexes. Everything ran without errors here phew. Now the data is imported and can be processed. If you only want the changes temporarily, you can do that with 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

  1. Download the datasets from opengeodb.giswiki.org/wiki/OpenGeoDB_Downloads: opengeodb-begin.sql, opengeodb-end.sql, DE.sql
  2. Open opengeodb-begin.sql in the editor and replace TYPE=InnoDB with ENGINE=InnoDB
  3. Import opengeodb-begin.sql
  4. Run sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
  5. Import DE.sql
  6. Import opengeodb-end.sql
NewerPHP Snippets