Importing OpenGeoDB Data into MS SQL Server

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.

About these ads