Querying OpenGeoDB

August 16, 2012

After importing OpenGeoDB Data into MS SQL Server, the next question is: How do we query relevant data?

Especially, how do we get all cities and municipalities for a given zip code?

The OpenGeoDB database contains locations and location-related data in various tables, each of them linked by the loc_id column. The geodb_textdata table contains typed text data for a location, and the type information is stored in geodb_type_names. Browsing the type names table soon reveals which information we need to query.

Additionally, the geodb_coordinates table may contain latitude and longitude information for a loc_id, so we want to retrieve this as well if available.

The resulting query is contained in a stored procedure (you can also implement a table-valued function with the same functionality), which looks like this:

create procedure [GeoDB_GetNamesFromZip](
    @zip nvarchar(10)
) as
    select t.loc_id as Loc_Id, 
        t.text_val as Name, e.text_val as Level, 
        c.lat as Lat, c.lon as Lon
    from geodb_textdata t 
    left outer join geodb_textdata e
        on t.loc_id = e.loc_id and e.text_type = '400200000'
    left outer join geodb_coordinates c 
        on t.loc_id = c.loc_id
    where t.loc_id in 
      (select loc_id from geodb_textdata 
       where text_val = @zip and text_type = '500300000')
    and t.text_type = '500100000'
    order by e.text_val desc, t.text_val

In the data set covering Austria, the execution of

EXEC GeoDB_GetNamesFromZip '3400'

returns the following records:

29569 Kierling 7 48,308 16,275
29602 Klosterneuburg 7 48,305 16,327
29634 Kritzendorf 7 48,328 16,303
66996 Maria Gugging 7 48,313 16,248
30358 Weidling 7 48,291 16,307
66997 Weidlingbach 7 48,2667 16,2533
66995 Klosterneuburg 6 48,305 16,327

Importing OpenGeoDB Data into MS SQL Server

August 15, 2012

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.