Térbeli adatok használata mssql-python-nal

A Microsoft SQL két térbeli adattípust kínál, amelyeket az mssql-python illesztőprogramon keresztül használhatsz. Válaszd ki a típust az alapján, hogy a koordinátáid mit képviselnek:

Típus	Leírás	Felhasználási eset
geography	Kör-Föld koordináta-rendszer	GPS koordináták, térképek, bármi a Föld felszínén. A távolságok méterekben vannak. Az SRID 4326 (WGS 84) szabvány a GPS-hez.
geometry	Sík koordináta-rendszer	Alaprajzok, CAD-rajzok, játékvilágok vagy bármilyen Descartes-féle koordináta-rendszer. A távolságok a koordináta-rendszer egységeiben vannak.

Térbeli adatok beszedése

Használja a Microsoft SQL konstruktorfüggvényeit, például a(z) geography::Point() elemet, vagy adjon át Well-Known Text (WKT) karakterláncokat.

Földrajzi adatok (pontok)

Szúrjon be földrajzi pontokat a Point konstruktor használatával, szélesség, hosszúság és SRID megadásával.

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

Geometria WKT-ből (Well-Known Text)

Földrajzi adatokat adj be Well-Known szövegformátummal, amely pontokat, vonalláncokat és sokszögeket támogat.

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

Geometriai adatok

Lapos sík geometriai adatokat adj be pontok és sokszögek segítségével CAD rajzokhoz vagy alaprajzokhoz.

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

Térbeli adatok lekérdezése

Használj olyan térbeli módszereket, mint STAsText() a és a Lat/Long tulajdonságok, hogy olvasható formátumban szerezd meg a koordinátákat.

Szövegként történő lekérés

Térbeli koordinátákat keress meg Well-Known szövegformátumban, valamint szélesség- és hosszúsági tulajdonságokkal.

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

Lekérés GeoJSON néven

A Microsoft SQL támogatja a GeoJSON átalakítást. Az SQL Server 2017+ verzióban string függvényekkel közvetlenül építhetsz GeoJSON-t, vagy Python-ban konvertálhatod az alábbiakban látható módon:

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

Találj közeli pontokat

Találj meg minden helyet egy meghatározott távolságon belül egy referenciaponttól a térbeli távolságfüggvényekkel.

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

Találj pontokat a sokszögen belül

Keresse meg az összes olyan pontot, amely metszi a földrajzi sokszöget, vagy annak belsejébe esik.

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

Távolságok kiszámítása

A Microsoft SQL STDistance() módszere földrajzi típusok esetén a távolságokat méterben adja vissza.

Két pont közötti távolság

Hozz létre egy segítő függvényt, amely kiszámítja a kilométeres távolságot két földrajzi pont között.

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

Terület számítása

Számold ki egy földrajzi terület területét négyzetkilométerben.

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

Térbeli műveletek

A Microsoft SQL támogatja a térbeli objektumokon végzett készletműveleteket, beleértve az uniót, metszést és puffert.

Formák egyesülése

Egyesítsd két földrajzi sokszöget egyetlen alakzattá, és számold ki a teljes területet.

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

Útkereszteződés

Keresd meg azt a átfedő területet, ahol két földrajzi régió metszik.

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 (terület kibővítése)

Hozz létre egy pufferzónát egy földrajzi pont körül, és keresd meg az összes helyszínt a puffersugaron belül.

# 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 integráció

Szerezze le a WKT stringeket a Microsoft SQL-ről, és parzálja őket a Shapely könyvtárral kliens oldali geometriai feldolgozáshoz.

Shapely könyvtárral

A telepítéshez futtassa a(z) pip install shapely elemet.

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

Térbeli adatok létrehozása a Shapely-vel

Készíts geometriai objektumokat a Shapely könyvtár segítségével, és konvertáld őket WKT formátumba, hogy beilleszthető legyen a Microsoft SQL-be.

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

Átkonvertálás GeoJSON-ra

Térbeli adatokat konvertálni WKT formátumból GeoJSON-ra webalapú alkalmazásokhoz és térképezési szolgáltatásokhoz.

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 integráció

Töltsd be a Microsoft SQL-ből származó térbeli adatokat egy GeoPandas GeoDataFrame-be a fejlett térbeli elemzéshez.

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

Térbeli indexek

Hozz létre térbeli indexeket Microsoft SQL-ben, hogy felgyorsítsd a lekérdezéseket nagy térbeli adathalmazoknál.

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

Teljesítménnyel kapcsolatos tippek

Alkalmazzuk ezeket a technikákat, hogy a térbeli lekérdezések hatékonyak maradjanak a nagyszabásban.

Használj térbeli indexeket

Használj térbeli indexeket a lekérdezések gyorsítására nagy térbeli adathalmazoknál.

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

Először szűrjük, aztán számoljuk ki

Optimalizáld a térbeli lekérdezéseket úgy, hogy először egy gyors határozó dobozszűrőt alkalmazz, majd pontos távolságszámításokat végezel.

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

Pontosság csökkentése megjelenítéshez

Egyszerűsítse a térbeli geometriákat a megjelenítéshez a koordináta pontosság csökkentésével.

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

Koordináta-referenciarendszerek

Az SRID határozza meg a koordinátarendszert, és befolyásolja, hogyan számít a Microsoft SQL távolságokat és területeket.

Gyakori SRID értékek

Az SRID (Térbeli Referenciaazonosító) határozza meg az adataid koordináta-rendszerét. A rossz SRID használata hibás távolság- és területszámításokat eredményez.

SRID	Név	Felhasználási eset
4326	WGS 84	GPS koordináták, webtérképezés
4269	NAD 83	Észak-amerikai felmérések
0	Nincs SRID	Sík geometria, helyi koordináták

SRID-ek közötti átalakítás

Kérje le a térbeli adatokat, és ellenőrizze azok SRID-jét a koordinátarendszer ellenőrzéséhez.

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

Bevált gyakorlatok

Alkalmazd ezeket az irányelveket, hogy helyesen és hatékonyan dolgozzon a térbeli adatokkal.

Validáld a geometriát

Ellenőrizd a térbeli adatok érvényességét, és azonosítsa az esetleges geometriai problémákat a feldolgozás előtt.

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

Legyen érvényes a geometria

Használd a MakeValid() módszert az érvénytelen geometriai alakzatok automatikus korrigálására.

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

Válaszd ki a megfelelő típust

A geography és a geometry közötti választás határozza meg, hogyan számítja ki a Microsoft SQL a távolságokat és a területeket:

• Földrajz: Valós helyszínek (GPS pontok), Föld felszíni számításai, távolságok méterben.
• geometria: Sík felületek (alaprajz, CAD), dekartézius koordináta-rendszerek, vagy amikor az SRID nem számít.

Kapcsolódó tartalom

• Adattípus-leképezések
• Microsoft SQL térbeli dokumentáció