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.
The SQL files needed to create the tables are available for download here. The data files need to be downloaded from their original location and modified as described above.
Pingback: Querying OpenGeoDB « devioblog
Pingback: Manu's Tech Blog | OpenGeoDB for MS SQL Server
Pingback: OpenGeoDB for MS SQL Server
Pingback: Parsing mysqldump files | devioblog