Používejte prostorová data s mssql-python

Microsoft SQL poskytuje dva typy prostorových dat, které můžete použít pomocí ovladače mssql-python. Vyberte typ podle toho, co vaše souřadnice představují:

Typ	Popis	Případ použití
geography	Souřadnicový systém kulaté Země	GPS souřadnice, mapy, cokoli na povrchu Země. Vzdálenosti jsou v metrech. SRID 4326 (WGS 84) je standard pro GPS.
geometry	Souřadnicový systém s plochou rovinou	Půdorysy, CAD výkresy, herní světy nebo jakýkoli kartézský souřadnicový systém. Vzdálenosti jsou v jednotkách vašeho souřadnicového systému.

Vložte prostorová data

Použijte konstruktorové funkce Microsoft SQL, například geography::Point(), nebo předejte řetězce Well-Known Text (WKT).

Geografické údaje (body)

Vložte geografické body pomocí konstruktoru bodů s zeměpisnou šířkou, délkou a 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()

Geografie z WKT (Well-Known Text)

Vložte geografická data pomocí formátu Well-Known Text, který podporuje body, řádkové řetězce a polygony.

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

Geometrická data

Vkládejte data z ploché geometrie pomocí bodů a polygonů pro CAD výkresy nebo půdorysy.

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

Dotaz na prostorová data

Použijte prostorové metody jako STAsText() a vlastnosti Lat/Long k získání souřadnic v čitelném formátu.

Získat jako text

Načtěte prostorové souřadnice ve formátu Well-Known Text spolu s atributy zeměpisné šířky a délky.

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

Získat jako GeoJSON

Microsoft SQL podporuje konverzi na GeoJSON. V SQL Server 2017+ můžete použít stringové funkce přímo k vytvoření GeoJSON, nebo je převést v Python, jak je uvedeno níže:

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

Najděte blízká místa

Najděte všechna místa v rámci určené vzdálenosti od referenčního bodu pomocí funkcí prostorové vzdálenosti.

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

Najděte body uvnitř mnohoúhelníku

Najděte všechny body, které se protínají nebo spadají do geografické polygonální oblasti.

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

Výpočet vzdáleností

Metoda Microsoft SQL STDistance() vrací vzdálenosti v metrech pro typy geografie.

Vzdálenost mezi dvěma body

Vytvořte pomocnou funkci pro výpočet vzdálenosti v kilometrech mezi dvěma geografickými body.

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

Výpočet plochy

Spočítejte plochu geografického regionu v kilometrech čtverečních.

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

Prostorové operace

Microsoft SQL podporuje množinové operace s prostorovými objekty, včetně sjednocení, průniku a pásem.

Sjednocení tvarů

Spojte dva geografické polygony do jednoho tvaru a vypočítejte celkovou plochu.

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

Křižovatka

Najděte překrývající se oblast, kde se protínají dvě geografické oblasti.

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

Buffer (rozšiřování oblasti)

Vytvořte ochrannou zónu kolem geografického bodu a najděte všechna místa v rámci poloměru bufferu.

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

Integrace Pythonu

Získejte WKT řetězce z Microsoft SQL a parsujte je pomocí knihovny Shapely pro zpracování geometrie na straně klienta.

S knihovnou Shapely

Instalujte spuštěním 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}")

Vytvářejte prostorová data pomocí Shapely

Vytvářejte geometrické objekty pomocí knihovny Shapely a převádějte je do formátu WKT pro vložení 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]}...")

Převod na GeoJSON

Převádějte prostorová data z formátu WKT do GeoJSON pro webové aplikace a mapové služby.

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

Integrace s GeoDataFrame

Nahrát prostorová data z Microsoft SQL do GeoPandas GeoDataFrame pro pokročilou geoprostorovou analýzu.

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

Prostorové indexy

Vytvářejte prostorové indexy v Microsoft SQL pro urychlení dotazů na velkých prostorových datových sadách.

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

Tipy týkající se výkonu

Aplikujte tyto techniky k udržení prostorových dotazů efektivních ve velkém měřítku.

Používejte nápovědy prostorového indexu

Použijte prostorové indexy k urychlení dotazů na velkých prostorových datových sadách.

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

Nejprve filtrujte, pak vypočítejte

Optimalizujte prostorové dotazy nejprve aplikací filtru rychlého ohraničovacího rámečku a poté provedením přesných výpočtů vzdálenosti.

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

Snižte přesnost pro zobrazení

Zjednodušit prostorové geometrie pro zobrazení snížením přesnosti souřadnic.

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

Souřadnicové referenční systémy

SRID určuje souřadnicový systém a ovlivňuje, jak Microsoft SQL počítá vzdálenosti a plochy.

Běžné hodnoty SRID

SRID (Spatial Reference Identifier) definuje souřadnicový systém pro vaše data. Použití nesprávného SRID vede k nesprávným výpočtům vzdálenosti a plochy.

SRID	Jméno	Případ použití
4326	WGS 84	GPS souřadnice, webové mapování
4269	NAD 83	Severoamerické průzkumy
0	Žádný SRID	Plochá geometrie, lokální souřadnice

Převod mezi SRID

Získejte prostorová data a zkontrolujte jeho SRID pro ověření souřadnicového systému.

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

Osvědčené postupy

Aplikujte tyto pokyny k správné a efektivní práci s prostorovými daty.

Ověřit geometrii

Zkontrolujte platnost prostorových dat a identifikujte geometrické problémy před zpracováním.

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

Udělejte geometrii platnou

Použijte tuto MakeValid() metodu k automatické opravě neplatných geometrických tvarů.

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

Vyberte správný typ

Volba mezi geography a geometry určuje, jak Microsoft SQL počítá vzdálenosti a plochy:

• Geografie: Reálné polohy (GPS body), výpočty povrchu Země, vzdálenosti v metrech.
• geometrie: Rovné plochy (půdorysy, CAD), kartézské souřadnicové soustavy nebo když SRID není důležitý.

Související obsah

• Mapování datových typů
• Microsoft SQL prostorová dokumentace