Używaj danych przestrzennych z mssql-python

Microsoft SQL udostępnia dwa typy danych przestrzennych, które można wykorzystać za pomocą sterownika mssql-python. Wybierz typ na podstawie tego, co reprezentują twoje współrzędne:

Typ	Opis	Przypadek użycia
geography	Układ współrzędnych kulistej Ziemi	Współrzędne GPS, mapy, wszystko na powierzchni Ziemi. Odległości są w metrach. SRID 4326 (WGS 84) jest standardem dla GPS.
geometry	Układ współrzędnych płaskich	Plany pięter, rysunki CAD, światy gier lub dowolny kartezjański układ współrzędnych. Odległości są w jednostkach twojego układu współrzędnych.

Wstaw dane przestrzenne

Użyj funkcji konstruktora Microsoft SQL, takich jak geography::Point(), lub przekaż ciągi Well-Known Text (WKT).

Dane geograficzne (punkty)

Wstaw punkty geograficzne za pomocą konstruktora punktów z szerokością geograficzną, długością geograficzną i SRID.

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()

Geografia z WKT (Well-Known Text)

Wstaw dane geograficzne w formacie Well-Known Text, który obsługuje punkty, linie łamane i wielokąty.

# 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()

Dane geometryczne

Wstaw dane geometrii płaskiej za pomocą punktów i wielokątów do rysunków CAD lub planów pięter.

# 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()

Zapytanie o dane przestrzenne

Użyj metod przestrzennych takich jak STAsText() i Lat/Long właściwości, aby uzyskać współrzędne w czytelnym formacie.

Pobierz jako tekst

Pobierz współrzędne przestrzenne w formacie Well-Known Text wraz z właściwościami szerokości i długości geograficznej.

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}")

Pobierz jako GeoJSON

Microsoft SQL obsługuje konwersję GeoJSON. W SQL Server 2017+ możesz używać funkcji łańcuchowych do bezpośredniego budowania GeoJSON lub konwertować w Python, jak pokazano poniżej:

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}")

Znajdź pobliskie punkty

Znajdź wszystkie lokalizacje w określonej odległości od punktu odniesienia za pomocą funkcji odległości przestrzennej.

# 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")

Znajdź punkty wewnątrz wielokąta

Znajdź wszystkie punkty, które przecinają się z lub znajdują się w obrębie danego obszaru wielokąta geograficznego.

# 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
""")

Obliczanie odległości

Metoda Microsoft SQL STDistance() zwraca odległości w metrach dla typów geografii.

Odległość między dwoma punktami

Stwórz funkcję pomocniczą do obliczania odległości w kilometrach między dwoma punktami geograficznymi.

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")

Oblicz powierzchnię

Oblicz powierzchnię danego regionu geograficznego w kilometrach kwadratowych.

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")

Operacje przestrzenne

Microsoft SQL obsługuje operacje zbiorowe na obiektach przestrzennych, w tym union, intersection i buffer.

Zjednoczenie kształtów

Połącz dwa wielokąty geograficzne w jeden kształt i oblicz całkowity obszar.

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")

Skrzyżowanie

Znajdź nakładający się obszar, gdzie dwa regiony geograficzne się przecinają.

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})

Bufor (rozszerzenie obszaru)

Stwórz strefę buforową wokół punktu geograficznego i znajdź wszystkie lokalizacje w promieniu bufora.

# 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
""")

Integracja z Pythonem

Pobierz ciągi WKT z Microsoft SQL i analizuj je za pomocą biblioteki Shapely do przetwarzania geometrii po stronie klienta.

Dzięki bibliotece Shapely

Zainstaluj, uruchamiając pip install shapely.

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}")

Tworzenie danych przestrzennych za pomocą Shapely

Twórz obiekty geometryczne za pomocą biblioteki Shapely i konwertuj je do formatu WKT do wstawienia do 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]}...")

Przekonwertowanie na GeoJSON

Konwertowanie danych przestrzennych z formatu WKT do GeoJSON dla aplikacji internetowych i usług mapowych.

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))

Integracja z GeoDataFrame

Załaduj dane przestrzenne z Microsoft SQL do GeoPandas GeoDataFrame do zaawansowanej analizy geoprzestrzennej.

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())

Indeksy przestrzenne

Twórz indeksy przestrzenne w Microsoft SQL, aby przyspieszyć zapytania na dużych zbiorach danych przestrzennych.

-- 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)
);

Porady dotyczące wydajności

Stosuj te techniki, aby zapytania przestrzenne były wydajne na dużą skalę.

Używaj podpowiedzi indeksu przestrzennego

Używaj indeksów przestrzennych do przyspieszania zapytań na dużych zbiorach danych przestrzennych.

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
""")

Najpierw filtruj, potem obliczaj

Zoptymalizuj zapytania przestrzenne, najpierw stosując szybki filtr prostokąta ograniczającego, a następnie wykonując precyzyjne obliczenia odległości.

# 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
""")

Zmniejsz precyzję na potrzeby wyświetlania

Uproszczenie geometrii przestrzennych do prezentacji poprzez zmniejszenie precyzji współrzędnych.

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

Układy odniesienia współrzędnych

SRID określa układ współrzędnych i wpływa na sposób, w jaki Microsoft SQL oblicza odległości i powierzchnie.

Wspólne wartości SRID

SRID (Spatial Reference Identifier) definiuje układ współrzędnych dla Twoich danych. Użycie niewłaściwego SRID powoduje nieprawidłowe obliczenia odległości i powierzchni.

SRID	Nazwa	Przypadek użycia
4326	WGS 84	Współrzędne GPS, mapowanie internetowe
4269	NAD 83	Badania w Ameryce Północnej
0	Brak SRID	Geometria płaska, współrzędne lokalne

Konwertowanie między SRID-ami

Pobierz dane przestrzenne i sprawdź jego SRID, aby zweryfikować układ współrzędnych.

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
""")

Najlepsze rozwiązania

Stosuj te wytyczne, aby prawidłowo i efektywnie pracować z danymi przestrzennymi.

Walidacja geometrii

Sprawdź dane przestrzenne pod kątem poprawności i zidentyfikuj wszelkie problemy geometryczne przed przetworzeniem.

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}")

Uczyń geometrię poprawną

Użyj MakeValid() tej metody do automatycznej korekty nieprawidłowych kształtów geometrycznych.

# 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
""")

Wybierz odpowiedni typ

Wybór między geography i geometry decyduje o tym, jak Microsoft SQL oblicza odległości i obszary:

• Geografia: Rzeczywiste lokalizacje (punkty GPS), obliczenia powierzchni Ziemi, odległości w metrach.
• geometria: płaskie powierzchnie (plany pięter, CAD), układy współrzędnych kartezjańskich lub gdy SRID nie ma znaczenia.

Treści powiązane

• Mapowania typów danych
• Microsoft SQL spatial documentation