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 |
