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

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.