Querying OpenGeoDB

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
begin
    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
end

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

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.