Räumliche Daten mit mssql-python verwenden

Microsoft SQL bietet zwei räumliche Datentypen, die man über den mssql-python-Treiber verwenden kann. Wählen Sie den Typ basierend darauf, was Ihre Koordinaten repräsentieren:

Räumliche Daten einfügen

Verwenden Sie Microsoft SQL-Konstruktorfunktionen wie geography::Point() oder übergeben Sie Well-Known Text (WKT)-Zeichenketten.

Geographiedaten (Punkte)

Fügen Sie geografische Punkte mit dem Punktkonstruktor mit Breite, Längengrad und SRID ein.

import mssql_python

connection_string = "Server=<server>.database.windows.net;Database=AdventureWorks2022;Authentication=ActiveDirectoryDefault;Encrypt=yes"

conn = mssql_python.connect(connection_string)
cursor = conn.cursor()

# Insert a geographic point (longitude, latitude)
# Note: Microsoft SQL uses (longitude, latitude) order
cursor.execute("CREATE TABLE #Locations (Name NVARCHAR(100), GeoLocation GEOGRAPHY)")
cursor.execute("""
    INSERT INTO #Locations (Name, GeoLocation)
    VALUES (%(name)s, geography::Point(%(lat)s, %(lon)s, 4326))
""", {"name": "Seattle", "lat": 47.6062, "lon": -122.3321})
conn.commit()

Geografie aus WKT (Well-Known Text)

Fügen Sie geografische Daten mit Well-Known Textformat ein, das Punkte, Linienstrings und Polygone unterstützt.

# Well-Known Text format
wkt_point = "POINT(-122.3321 47.6062)"
wkt_line = "LINESTRING(-122.3321 47.6062, -122.4194 37.7749)"
wkt_polygon = "POLYGON((-122.40 47.60, -122.30 47.60, -122.30 47.65, -122.40 47.65, -122.40 47.60))"

cursor.execute("DROP TABLE IF EXISTS #Locations")
cursor.execute("CREATE TABLE #Locations (Name NVARCHAR(100), GeoLocation GEOGRAPHY)")
cursor.execute("""
    INSERT INTO #Locations (Name, GeoLocation)
    VALUES (%(name)s, geography::STGeomFromText(%(wkt)s, 4326))
""", {"name": "Route", "wkt": wkt_line})
conn.commit()

Geometriedaten

Fügen Sie Flächengeometriedaten mit Punkten und Polygonen für CAD-Zeichnungen oder Grundrisse ein.

# Insert a geometry point (flat coordinate system)
cursor.execute("CREATE TABLE #FloorPlan (RoomName NVARCHAR(100), RoomShape GEOMETRY)")
cursor.execute("""
    INSERT INTO #FloorPlan (RoomName, RoomShape)
    VALUES (%(name)s, geometry::Point(%(x)s, %(y)s, 0))
""", {"name": "Office 101", "x": 50.0, "y": 100.0})

# Insert a geometry polygon
room_wkt = "POLYGON((0 0, 0 10, 20 10, 20 0, 0 0))"
cursor.execute("""
    INSERT INTO #FloorPlan (RoomName, RoomShape)
    VALUES (%(name)s, geometry::STGeomFromText(%(wkt)s, 0))
""", {"name": "Conference Room", "wkt": room_wkt})
conn.commit()

Abfrage räumlicher Daten

Verwenden Sie räumliche Methoden wie STAsText() und die Eigenschaften, Lat/Long um Koordinaten in einem lesbaren Format abzurufen.

Als Text abrufen

Räumliche Koordinaten zusammen mit Breiten- und Längengrad-Eigenschaften im Well-Known-Text-Format abrufen.

cursor.execute("""
    SELECT 
        City,
        SpatialLocation.STAsText() AS LocationWKT,
        SpatialLocation.Lat AS Latitude,
        SpatialLocation.Long AS Longitude
    FROM Person.Address
    WHERE SpatialLocation IS NOT NULL
""")

for row in cursor.fetchall()[:5]:
    print(f"{row.City}: {row.Latitude}, {row.Longitude}")
    print(f"  WKT: {row.LocationWKT}")

Abruf als GeoJSON

Microsoft SQL unterstützt die GeoJSON-Konvertierung. In SQL Server 2017+ können Sie String-Funktionen verwenden, um GeoJSON direkt zu bauen, oder in Python konvertieren, wie unten gezeigt:

cursor.execute("""
    SELECT 
        City,
        SpatialLocation.STAsText() AS WKT
    FROM Person.Address
    WHERE SpatialLocation IS NOT NULL AND City = %(city)s
""", {"city": "Seattle"})

for row in cursor:
    print(f"{row.City}: {row.WKT}")

Finde nahegelegene Punkte

Finde alle Orte innerhalb eines angegebenen Abstands zu einem Referenzpunkt mithilfe von räumlichen Abstandsfunktionen.

# Find locations within 10 km of Seattle
cursor.execute("""
    DECLARE @seattle geography = geography::Point(47.6062, -122.3321, 4326);
    
    SELECT 
        City,
        SpatialLocation.STDistance(@seattle) / 1000 AS DistanceKM
    FROM Person.Address
    WHERE SpatialLocation IS NOT NULL
      AND SpatialLocation.STDistance(@seattle) < 50000  -- 50 km in meters
    ORDER BY SpatialLocation.STDistance(@seattle)
""")

for row in cursor.fetchall()[:5]:
    print(f"{row.City}: {row.DistanceKM:.2f} km away")

Punkte innerhalb eines Polygons finden

Finden Sie alle Punkte, die sich mit einem geografischen Polygonbereich schneiden oder dort liegen.

# Find all addresses within a region
cursor.execute("""
    DECLARE @region geography = geography::STPolyFromText(
        'POLYGON((-122.5 47.5, -122.2 47.5, -122.2 47.7, -122.5 47.7, -122.5 47.5))',
        4326
    );
    
    SELECT City, SpatialLocation.STAsText() AS Location
    FROM Person.Address
    WHERE SpatialLocation IS NOT NULL
      AND @region.STIntersects(SpatialLocation) = 1
""")

Entfernungen berechnen

Die Methode von STDistance() Microsoft SQL liefert Entfernungen in Metern für geografische Typen zurück.

Abstand zwischen zwei Punkten

Erstellen Sie eine Hilfsfunktion, um die Entfernung in Kilometern zwischen zwei geografischen Punkten zu berechnen.

def get_distance_km(cursor, point1: tuple, point2: tuple) -> float:
    """Calculate distance between two points in kilometers."""
    cursor.execute("""
        DECLARE @point1 geography = geography::Point(%(lat1)s, %(lon1)s, 4326);
        DECLARE @point2 geography = geography::Point(%(lat2)s, %(lon2)s, 4326);
        SELECT @point1.STDistance(@point2) / 1000 AS DistanceKM;
    """, {
        "lat1": point1[0], "lon1": point1[1],
        "lat2": point2[0], "lon2": point2[1]
    })
    return cursor.fetchval()

# Seattle to San Francisco
distance = get_distance_km(cursor, (47.6062, -122.3321), (37.7749, -122.4194))
print(f"Distance: {distance:.2f} km")

Fläche berechnen

Berechnen Sie die Fläche einer geografischen Region in Quadratkilometern.

cursor.execute("""
    DECLARE @region geography = geography::STPolyFromText(
        'POLYGON((-122.5 47.5, -122.2 47.5, -122.2 47.7, -122.5 47.7, -122.5 47.5))',
        4326
    );
    SELECT 
        'Seattle Region' AS Name,
        @region.STArea() / 1000000 AS AreaSqKm
""")

row = cursor.fetchone()
print(f"{row.Name}: {row.AreaSqKm:.2f} sq km")

Räumliche Operationen

Microsoft SQL unterstützt Set-Operationen auf räumlichen Objekten, einschließlich Union, Intersection und Buffer.

Vereinigung der Formen

Kombinieren Sie zwei geografische Polygone zu einer einzigen Form und berechnen Sie die Gesamtfläche.

cursor.execute("""
    DECLARE @parcel1 geography = geography::STPolyFromText(
        'POLYGON((-122.35 47.60, -122.33 47.60, -122.33 47.62, -122.35 47.62, -122.35 47.60))',
        4326
    );
    DECLARE @parcel2 geography = geography::STPolyFromText(
        'POLYGON((-122.34 47.61, -122.32 47.61, -122.32 47.63, -122.34 47.63, -122.34 47.61))',
        4326
    );
    DECLARE @combined geography = @parcel1.STUnion(@parcel2);
    
    SELECT @combined.STAsText() AS CombinedWKT,
           @combined.STArea() / 1000000 AS TotalAreaKM
""")

row = cursor.fetchone()
print(f"Combined area: {row.TotalAreaKM:.2f} sq km")

Schnittpunkt

Ermitteln Sie den Überlappungsbereich, in dem sich zwei geografische Regionen überschneiden.

polygon1_wkt = "POLYGON((-122.35 47.60, -122.33 47.60, -122.33 47.62, -122.35 47.62, -122.35 47.60))"
polygon2_wkt = "POLYGON((-122.34 47.61, -122.32 47.61, -122.32 47.63, -122.34 47.63, -122.34 47.61))"

cursor.execute("""
    DECLARE @region1 geography = geography::STPolyFromText(%(wkt1)s, 4326);
    DECLARE @region2 geography = geography::STPolyFromText(%(wkt2)s, 4326);
    
    SELECT 
        @region1.STIntersection(@region2).STAsText() AS IntersectionWKT,
        @region1.STIntersection(@region2).STArea() / 1000000 AS AreaKM
""", {"wkt1": polygon1_wkt, "wkt2": polygon2_wkt})

Puffer (Fläche erweitern)

Erstelle eine Pufferzone um einen geografischen Punkt und finde alle Standorte innerhalb des Pufferradius.

# Find all addresses within 5km buffer of a point
cursor.execute("""
    DECLARE @center geography = geography::Point(47.6062, -122.3321, 4326);
    DECLARE @buffer geography = @center.STBuffer(5000);  -- 5 km buffer
    
    SELECT City
    FROM Person.Address
    WHERE SpatialLocation IS NOT NULL
      AND @buffer.STIntersects(SpatialLocation) = 1
""")

Python-Integration

Rufen Sie WKT-Strings aus Microsoft SQL ab und parsen Sie sie mit der Shapely-Bibliothek für die clientseitige Geometrieverarbeitung.

Mit der Shapely-Bibliothek

Installiere, indem du pip install shapely ausführst.

from shapely import wkt
from shapely.geometry import Point, Polygon

# Retrieve spatial data from Person.Address
cursor.execute("""
    SELECT City, SpatialLocation.STAsText() AS WKT
    FROM Person.Address
    WHERE SpatialLocation IS NOT NULL AND City IN ('Seattle', 'Redmond')
""")

for row in cursor:
    # Parse WKT into Shapely geometry
    geom = wkt.loads(row.WKT)
    
    if isinstance(geom, Point):
        print(f"{row.City}: Point at ({geom.x}, {geom.y})")
    elif isinstance(geom, Polygon):
        print(f"{row.City}: Polygon with area {geom.area}")

Räumliche Daten mit Shapely erstellen

Erstellen Sie geometrische Objekte mit der Shapely-Bibliothek und konvertieren Sie sie in das WKT-Format für die Einfügung in Microsoft SQL.

from shapely.geometry import Point, Polygon, LineString
from shapely import wkt

# Create geometries in Python
seattle = Point(-122.3321, 47.6062)
seattle_wkt = wkt.dumps(seattle)

route = LineString([(-122.3321, 47.6062), (-122.4194, 37.7749)])
route_wkt = wkt.dumps(route)

# Insert into a temp table
cursor.execute("CREATE TABLE #Routes (Name NVARCHAR(100), Path NVARCHAR(MAX))")
cursor.execute("""
    INSERT INTO #Routes (Name, Path)
    VALUES (%(name)s, %(wkt)s)
""", {"name": "Seattle to SF", "wkt": route_wkt})

cursor.execute("SELECT Name, Path FROM #Routes")
row = cursor.fetchone()
print(f"{row.Name}: {row.Path[:40]}...")

Konvertiere zu GeoJSON

Konvertiere räumliche Daten vom WKT-Format in GeoJSON für webbasierte Anwendungen und Kartierungsdienste.

import json
from shapely import wkt
from shapely.geometry import mapping

cursor.execute("""
    SELECT City, SpatialLocation.STAsText() AS WKT
    FROM Person.Address
    WHERE SpatialLocation IS NOT NULL AND City = 'Seattle'
""")

row = cursor.fetchone()
geom = wkt.loads(row.WKT)

geojson = {
    "type": "Feature",
    "properties": {"name": row.City},
    "geometry": mapping(geom)
}

print(json.dumps(geojson, indent=2))

GeoDataFrame-Integration

Laden Sie räumliche Daten von Microsoft SQL in einen GeoPandas GeoDataFrame für fortgeschrittene geospatiale Analysen.

import geopandas as gpd
from shapely import wkt
import pandas as pd

cursor.execute("""
    SELECT AddressID, City, SpatialLocation.STAsText() AS WKT
    FROM Person.Address
    WHERE SpatialLocation IS NOT NULL AND City = 'Seattle'
""")

# Build DataFrame
rows = cursor.fetchall()
df = pd.DataFrame(
    [(r.AddressID, r.City, r.WKT) for r in rows],
    columns=['id', 'city', 'wkt']
)

# Convert to GeoDataFrame
df['geometry'] = df['wkt'].apply(wkt.loads)
gdf = gpd.GeoDataFrame(df, geometry='geometry', crs="EPSG:4326")

# Now use GeoPandas operations
print(gdf.head())

Räumliche Indizes

Erstellen Sie räumliche Indizes in Microsoft SQL, um Abfragen auf großen räumlichen Datensätzen zu beschleunigen.

-- Geography index on Person.Address
CREATE SPATIAL INDEX SIX_Address_SpatialLocation
ON Person.Address(SpatialLocation)
USING GEOGRAPHY_GRID
WITH (
    GRIDS = (LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM),
    CELLS_PER_OBJECT = 16
);

-- Geometry index (example with custom table)
CREATE SPATIAL INDEX SIX_FloorPlan_RoomShape
ON dbo.FloorPlan(RoomShape)
USING GEOMETRY_GRID
WITH (
    BOUNDING_BOX = (0, 0, 1000, 1000),
    GRIDS = (LEVEL_1 = HIGH, LEVEL_2 = HIGH, LEVEL_3 = HIGH, LEVEL_4 = HIGH)
);

Leistungstipps

Wenden Sie diese Techniken an, um räumliche Abfragen in großem Maßstab effizient zu halten.

Verwenden Sie räumliche Indexhinweise

Verwenden Sie räumliche Indizes, um Abfragen auf großen räumlichen Datensätzen zu beschleunigen.

cursor.execute("""
    DECLARE @region geography = geography::STPolyFromText(
        'POLYGON((-122.5 47.5, -122.2 47.5, -122.2 47.7, -122.5 47.7, -122.5 47.5))',
        4326
    );

    SELECT City
    FROM Person.Address
    WHERE SpatialLocation IS NOT NULL
      AND SpatialLocation.STIntersects(@region) = 1
""")

Erst filtern, dann berechnen

Optimieren Sie räumliche Abfragen, indem Sie zunächst einen schnellen Bounding-Box-Filter anwenden und anschließend genaue Distanzberechnungen durchführen.

# Approximate filter with bounding box, then precise calculation
cursor.execute("""
    DECLARE @center geography = geography::Point(47.6062, -122.3321, 4326);
    
    SELECT City, SpatialLocation.STDistance(@center) AS Distance
    FROM Person.Address
    WHERE SpatialLocation IS NOT NULL
      AND SpatialLocation.Filter(@center.STBuffer(10000)) = 1  -- Fast bounding box filter
      AND SpatialLocation.STDistance(@center) < 10000         -- Precise distance check
    ORDER BY Distance
""")

Reduziere die Präzision bei der Darstellung

Vereinfachen Sie räumliche Geometrien für die Darstellung, indem Sie die Koordinatengenauigkeit reduzieren.

cursor.execute("""
    SELECT 
        City,
        SpatialLocation.Reduce(100).STAsText() AS SimplifiedWKT  -- 100 meter tolerance
    FROM Person.Address
    WHERE SpatialLocation IS NOT NULL AND City = 'Seattle'
""")

Koordinatenreferenzsysteme

Das SRID bestimmt das Koordinatensystem und beeinflusst, wie Microsoft SQL Entfernungen und Flächen berechnet.

Häufige SRID-Werte

Das SRID (Spatial Reference Identifier) definiert das Koordinatensystem für Ihre Daten. Die Verwendung des falschen SRID führt zu falschen Entfernungs- und Flächenberechnungen.

Konvertieren zwischen SRIDs

Räumliche Daten abrufen und deren SRID überprüfen, um das Koordinatensystem zu überprüfen.

cursor.execute("""
    -- Geography is always round-earth, but SRID defines datum
    SELECT SpatialLocation.STAsText() AS WKT,
           SpatialLocation.STSrid AS SRID
    FROM Person.Address
    WHERE SpatialLocation IS NOT NULL
""")

Bewährte Methoden

Wenden Sie diese Richtlinien an, um mit räumlichen Daten korrekt und effizient zu arbeiten.

Geometrie validieren

Überprüfen Sie räumliche Daten auf Gültigkeit und identifizieren Sie geometrische Probleme vor der Verarbeitung.

cursor.execute("""
    SELECT 
        City,
        SpatialLocation.STIsValid() AS IsValid,
        SpatialLocation.IsValidDetailed() AS InvalidReason
    FROM Person.Address
    WHERE SpatialLocation IS NOT NULL
      AND SpatialLocation.STIsValid() = 0
""")

for row in cursor:
    print(f"Invalid: {row.City} - {row.InvalidReason}")

Geometrie validieren

Verwenden Sie die Methode MakeValid() , um ungültige geometrische Formen automatisch zu korrigieren.

# Example with a temp table
cursor.execute("""
    CREATE TABLE #SpatialFix (Name NVARCHAR(100), GeoLocation GEOGRAPHY);
    INSERT INTO #SpatialFix VALUES ('Test', geography::STGeomFromText('POLYGON((0 0, 0 1, 1 0, 0 0))', 4326));
    UPDATE #SpatialFix
    SET GeoLocation = GeoLocation.MakeValid()
    WHERE GeoLocation.STIsValid() = 0
""")

Wählen Sie den richtigen Typ

Die Wahl zwischen geography und geometry bestimmt, wie Microsoft SQL Entfernungen und Flächen berechnet:

• Geografie: Reale Orte (GPS-Punkte), Berechnungen der Erdoberfläche, Entfernungen in Metern.
• Geometrie: Flache Flächen (Grundrisse, CAD), kartesische Koordinatensysteme oder wenn SRID keine Rolle spielt.

Verwandte Inhalte

• Datentypzuordnungen
• Microsoft SQL-Spatial-Dokumentation