OpenGeoDB provides geographical and hierarchical location data for a couple of European countries (currently .at, .be, .ch, .de, .li).
The data however is available for download only in MySQL format, and a couple of modifications are required to execute the import in a MS SQL Server database:
- opengeodb-begin.sql creates the required tables, and some data types and check constraints need to be adapted (see download)
AT.sql contains location data regarding Austria, such as latitude/longitude and zip codes for cities and communities. The file size is about 20MB, and could not be processed in SSMS.
I proceeded as follows to run the import:
- Using Notepad++, I separated each INSERT statement into a single batch command by replacing every ‘;’ with ‘;\nGO’ (in RegEx mode)
- Under the Encoding menu, invoke Convert to ANSI, and save the file.
- Open a command prompt (Start, Run, cmd.exe) and run the .sql file using sqlcmd:
sqlcmd -U [user] -P [pwd] -d [db] -i C:\path\to\AT.sql -e > at.sql.log
The AThier.sql file is small enough to be run from SSMS, but can also be executed via sqlcmd without modification:
sqlcmd -U [user] -P [pwd] -d [db] -i C:\path\to\AThier.sql -e > athier.sql.log
Make sure the log files do not contain any errors or warnings.
- Finally, opengeodb-end(-ms).sql inserts some type information and creates indexes.
In case anything goes wrong with the import, I also added a file delete_geodb.sql that truncates or deletes the geodb tables.