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