BI Services

Abfragen von geografischen Daten in SQL Server

Wie arbeitet man mit geografischen Daten zwecks Umkreissuche etc. in SQL Server?

Autor: Michael R. Friebel

Man sieht es sehr oft, die Möglichkeit, sich auf Hotel oder Reisebuchungsportalen anzeigen zu lassen, welche weiteren Hotels oder Städte es im Umkreis von soundso vielen km gibt.

Wie wird dies gemacht?

Zunächst erst einmal muss man wissen, dass SQL Server einen speziellen Datentyp zur Verfügung stellt, der in der Lage ist, eine Position zu speichern. Dieser Datentyp heißt geography and speichert Longitude und Latitude Daten.

Auf Basis dieses Datentyps kann man jetzt SQL Select Abfragen durchführen.

Ich möchte dies an folgendem einfachen Beispiel zeigen wie dies in SQL Server umgesetzt wird.

  1. Im ersten Schritt legen wir uns in einer Datenbank unserer Wahl auf SQL Server eine Tabelle an, die wichtige deutsche Städte mit den Daten ihrer Longitude und Latitude Position enthalten.
USE [AreaQuery]
GO
/****** Object:  Table [dbo].[city]    Script Date: 15.02.2024 15:07:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[city](
	[City] [nvarchar](50) NULL,
	[Country] [nvarchar](50) NULL,
	[Population] [int] NULL,
	[Longitude] [float] NULL,
	[Latitude] [float] NULL,
	[id] [int] 
) ON [PRIMARY]
GO

INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Berlin', N'D', 3472009, 13.3, 52.45, 1)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Hamburg', N'D', 1705872, 9.96667, 53.55, 2)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Hannover', N'D', 525763, 9.66667, 52.4, 3)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Munich', N'D', 1244676, 11.5667, 48.15, 4)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Mannheim', N'D', 316223, 8.46667, 49.5667, 5)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Saarbruecken', N'D', 183945, 6.6, 49.1, 6)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Muenchen', N'D', 1290079, 11.3, 48.1, 7)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Bremen', N'D', 549182, 8.5, 53.7, 8)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Stuttgart', N'D', 588482, 9.1, 48.7, 9)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Potsdam', N'D', 138268, NULL, NULL, 10)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Wiesbaden', N'D', 266081, 8.17, 50.07, 11)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Schwerin', N'D', 118291, NULL, NULL, 12)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Dusseldorf', N'D', 572638, NULL, NULL, 13)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Mainz', N'D', 184627, 8.1, 50, 14)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Saarbrucken', N'D', 189012, NULL, NULL, 15)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Dresden', N'D', 474443, NULL, NULL, 16)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Magdeburg', N'D', 265379, NULL, NULL, 17)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Kiel', N'D', 246586, 10.7, 54.2, 18)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Erfurt', N'D', 213472, NULL, NULL, 19)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Koln', N'D', 963817, NULL, NULL, 20)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Frankfurt am Main', N'D', 652412, NULL, NULL, 21)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Essen', N'D', 617955, NULL, NULL, 22)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Dortmund', N'D', 600918, NULL, NULL, 23)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Duisburg', N'D', 536106, NULL, NULL, 24)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Nurnberg', N'D', 495845, NULL, NULL, 25)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Leipzig', N'D', 481121, NULL, NULL, 26)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Bochum', N'D', 401129, NULL, NULL, 27)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Wuppertal', N'D', 383776, NULL, NULL, 28)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Bielefeld', N'D', 324067, NULL, NULL, 29)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Gelsenkirchen', N'D', 293542, NULL, NULL, 30)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Bonn', N'D', 293072, 7.5, 50.4, 31)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Halle', N'D', 290051, NULL, NULL, 32)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Karlsruhe', N'D', 277011, NULL, NULL, 33)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Chemnitz', N'D', 274162, NULL, NULL, 34)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Monchengladbach', N'D', 266073, NULL, NULL, 35)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Munster', N'D', 264887, NULL, NULL, 36)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Augsburg', N'D', 262110, NULL, NULL, 37)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Braunschweig', N'D', 254130, NULL, NULL, 38)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Krefeld', N'D', 249662, NULL, NULL, 39)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Aachen', N'D', 247113, NULL, NULL, 40)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Rostock', N'D', 232634, NULL, NULL, 41)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Oberhausen', N'D', 225443, NULL, NULL, 42)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Lubeck', N'D', 216854, NULL, NULL, 43)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Hagen', N'D', 213747, NULL, NULL, 44)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Kassel', N'D', 201789, NULL, NULL, 45)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Freiburg im Breisgau', N'D', 198496, NULL, NULL, 46)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Hamm', N'D', 184020, NULL, NULL, 47)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Herne', N'D', 180029, NULL, NULL, 48)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Mulheim an der Ruhr', N'D', 176513, NULL, NULL, 49)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Osnabruck', N'D', 168050, NULL, NULL, 50)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Ludwigshafen', N'D', 167883, NULL, NULL, 51)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Solingen', N'D', 165973, NULL, NULL, 52)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Leverkusen', N'D', 161832, NULL, NULL, 53)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Oldenburg', N'D', 149691, NULL, NULL, 54)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Neuss', N'D', 148870, NULL, NULL, 55)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Darmstadt', N'D', 139063, NULL, NULL, 56)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Heidelberg', N'D', 138964, NULL, NULL, 57)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Paderborn', N'D', 131513, NULL, NULL, 58)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Bremerhaven', N'D', 130847, NULL, NULL, 59)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Wurzburg', N'D', 127946, NULL, NULL, 60)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Gottingen', N'D', 127519, NULL, NULL, 61)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Recklinghausen', N'D', 127139, NULL, NULL, 62)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Wolfsburg', N'D', 126965, NULL, NULL, 63)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Gera', N'D', 126035, NULL, NULL, 64)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Cottbus', N'D', 125643, NULL, NULL, 65)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Regensburg', N'D', 125608, NULL, NULL, 66)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Remscheid', N'D', 123069, NULL, NULL, 67)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Heilbronn', N'D', 122253, NULL, NULL, 68)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Bottrop', N'D', 119669, NULL, NULL, 69)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Pforzheim', N'D', 117960, NULL, NULL, 70)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Salzgitter', N'D', 117842, NULL, NULL, 71)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Offenbach am Main', N'D', 116482, NULL, NULL, 72)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Ulm', N'D', 115123, NULL, NULL, 73)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Siegen', N'D', 111541, NULL, NULL, 74)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Ingolstadt', N'D', 110910, NULL, NULL, 75)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Koblenz', N'D', 109550, NULL, NULL, 76)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Furth', N'D', 107799, NULL, NULL, 77)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Reutlingen', N'D', 107782, NULL, NULL, 78)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Moers', N'D', 107011, NULL, NULL, 79)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Hildesheim', N'D', 106095, NULL, NULL, 80)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Witten', N'D', 105423, NULL, NULL, 81)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Bergisch Gladbach', N'D', 105122, NULL, NULL, 82)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Zwickau', N'D', 104921, NULL, NULL, 83)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Jena', N'D', 102204, NULL, NULL, 84)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Kaiserslautern', N'D', 101910, NULL, NULL, 85)
INSERT [dbo].[city] ([City], [Country], [Population], [Longitude], [Latitude], [id]) VALUES (N'Erlangen', N'D', 101450, NULL, NULL, 86)

Wenn Du nicht weißt was sich hinter den Begriffen Longitude und Latitude verbirgt, solltest Du Dich z.B. hier informieren https://www.google.de/search?q=was+ist+longitude+und+latitude

Bitte beachte, dass wir in der soeben angelegten Tabelle, noch keine Spalte mit dem Datentyp geography angelegt haben. Die Longitude und Latidude Daten, werden als einfache FLOAT Daten gespeichert und müssen erst umgewandelt werden.

Außerdem existieren für einige Städte keine Longitude und Latitude Informationen in den entsprechenden Spalten. Diese können dann natürlich im Rahmen einer Umkreissuche auch nicht gefunden werden.

Ich zeige Dir dies aus dem Grund in dieser Form, da es ein häufiges Szenario sein dürfte, dass Positionsdaten zwar vorhanden sind oder in die Datenbank eingetragen werden, dann allerdings in den Datentyp geography umgewandelt werden müssen, um die Möglichkeiten diesbezüglich von SQL Server zu nutzen.

  1. Im nächsten Schritt fügen wir eine Spalte position vom Datentyp geography zur Tabelle hinzu.
 alter table city add position geography
  1. Jetzt wird auf Basis der Daten die in den FLOAT Spalten Longitude und Latitude gespeichert sind, der Geography Datenwert in der gerade angelegten Spalte position hinzugefügt.

Dies kann, wie bereits erwähnt ein häufig benötigtes Anwendungszenario für Anwender sein, die zwar die Longitude und Latitude Daten haben, allerdings nicht wissen, wie sie diese in den für die Geographischen Auswertungen notwendigen geography Datentyp umwandeln.

Das folgende Update Kommando könnte z.b. in regelmäßigen Abständen ausgeführt werden oder gleich in einer DML-Triggerroutine beim ändern oder hinzufügen von Daten untergebracht werden.

 update city set position = Geography::Point(Latitude, Longitude, 4326) 
 where Longitude is not null and Latitude is not null
  1. Wenn all dies erledigt ist, können wir uns mit folgendem Befehl alle Städte ausgeben lassen, die im Umkreis von 300000 Metern = 300 km um Berlin zu finden sind.
-- deklariere eine Variable vom Typ geography in diesem Fall @act_position
declare @act_position geography;

-- speichere die Position von Berlin (befindet sich ja auch in der Tabelle city) in der Variablen @act_position
select @act_position = position from city Where city = 'Berlin';

-- gib alle Zeilen also Städte aus, die im Umkreis von 300 km zu finden sind (hier in Metern in der Where Klausel angegeben) 
select city, CAST(@act_position.STDistance(position) as numeric (10,2))
from city where @act_position.STDistance(position) < 300000

Natürlich kann die Abfrage jetzt um weitere Filterbedingungen mittels Where etc. ergänzt werden.

Der Datentyp geography besitzt noch weitere Möglichkeiten (Methoden). Informationen und Beispiele findest Du z. B. hier: https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/spatial-types-geography

Es werden die Städte Berlin, Hamburg, Hannover und Kiel ausgegeben.

  1. Das wars!

–Viel Spaß beim experimentieren mit geografischen Daten und vielen vielen Umkreissuchen in SQL Server–