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: