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.

3 Responses to Importing OpenGeoDB Data into MS SQL Server

  1. […] importing OpenGeoDB Data into MS SQL Server, the next question is: How do we query relevant […]

  2. […] as a sql script for MySQL. After a search with my favorite search engine, I found the post from "devio" describing how to import the data into MSSQL. The bad point is, he provides only the schema […]

  3. […] as a sql script for MySQL. After a search with my favorite search engine, I found the post from “devio” describing how to import the data into MSSQL. The bad point is, he provides only the schema […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: