mssql-python ile mekansal veri kullanın

Microsoft SQL, mssql-python sürücüsü aracılığıyla kullanabileceğiniz iki uzamsal veri türü sunar. Koordinatlarınızın temsil ettiği tipe göre seçin:

Türü	Açıklama	Kullanım örneği
geography	Yuvarlak dünya koordinat sistemi	GPS koordinatları, haritalar, Dünya yüzeyindeki her şey. Mesafeler metre cinsindendir. SRID 4326 (WGS 84) GPS için standarttır.
geometry	Düz düzlem koordinat sistemi	Kat planları, CAD çizimleri, oyun dünyaları veya herhangi bir Kartezyen koordinat sistemi. Mesafeler, koordinat sisteminizin birimleri cinsindendir.

Mekansal veri ekle

geography::Point() gibi Microsoft SQL oluşturucu işlevlerini kullanın veya İyi Bilinen Metin (WKT) dizelerini geçirin.

Coğrafya verileri (puanlar)

Coğrafi noktaları enlem, boylam ve SRID ile Nokta yapıcısı ile ekleyin.

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

WKT’den Coğrafya (İyi Bilinen Metin)

Coğrafi verileri noktalar, çizgi dizileri ve çokgenleri destekleyen Well-Known Metin formatıyla ekleyin.

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

Geometri verileri

CAD çizimleri veya kat planları için noktalar ve çokgenler kullanarak düz düzlem geometri verilerini ekleyin.

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

Mekânsal veri sorgusu

Koordinatları okunabilir bir formatta almak için Properties STAsText() gibi mekansal yöntemleri kullanınLat/Long.

Metin olarak alın

Mekânsal koordinatları, enlem ve boylam özellikleriyle birlikte İyi Bilinen Metin (WKT) biçiminde alın.

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

GeoJSON olarak alın

Microsoft SQL GeoJSON dönüşümünü destekler. SQL Server 2017+ sürümünde, GeoJSON'u doğrudan oluşturmak için string fonksiyonlarını kullanabilir veya aşağıda gösterildiği gibi Python'da dönüştürebilirsiniz:

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

Yakındaki noktaları bulun

Bir referans noktasının belirli bir mesafesi içindeki tüm konumları mekânsal mesafe fonksiyonlarıyla bulun.

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

Çokgen içinde noktaları bulun

Coğrafi bir çokgen bölgesiyle kesişen veya içinde kesişen tüm noktaları bulun.

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

Mesafeleri hesaplayın

Microsoft SQL yöntemi, STDistance() coğrafya türleri için mesafeleri metre cinsinden döndürür.

İki nokta arasındaki mesafe

İki coğrafi nokta arasındaki kilometre cinsinden mesafeyi hesaplamak için yardımcı fonksiyon oluşturun.

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

Alan hesaplayın

Bir coğrafi bölgenin alanını kilometrekare cinsinden hesaplayın.

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

Uzamsal işlemler

Microsoft SQL, birleşim, kesişim ve tampon dahil olmak üzere uzamsal nesneler üzerinde set işlemlerini destekler.

Şekillerin birleşimi

İki coğrafi çokgonu tek bir şekle birleştirin ve toplam alanı hesaplayın.

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

Kesişme

İki coğrafi bölgenin kesiştiği örtüşen alanı bulun.

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

Arabellek (alanı genişlet)

Bir coğrafi noktanın etrafında bir tampon bölgesi oluşturun ve tampon yarıçapındaki tüm konumları bulun.

# 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 entegrasyonu

Microsoft SQL'den WKT dizileri alın ve istemci tarafı geometri işleme için Shapely kütüphanesi ile ayrıştırın.

Shapely kütüphanesi ile

pip install shapely komutunu çalıştırarak yükleyin.

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

Shapely ile mekânsal veri oluşturun

Shapely kütüphanesini kullanarak geometrik nesneler oluşturun ve bunları Microsoft SQL'e eklemek için WKT formatına dönüştürebilirsiniz.

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

GeoJSON'a Dönüştür

Web tabanlı uygulamalar ve haritalama hizmetleri için mekânsal verileri WKT formatından GeoJSON'a dönüştürün.

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 entegrasyonu

Microsoft SQL'den mekansal verileri gelişmiş jeouzsal analiz için GeoPandas GeoDataFrame'e yükleyin.

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

Uzamsal dizinler

Büyük uzamsal veri setlerinde sorguları hızlandırmak için Microsoft SQL'de mekânsal indeksler oluşturun.

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

Performans ipuçları

Bu teknikleri, mekânsal sorguların ölçekte verimli kalması için uygulayın.

Mekansal indeks ipuçları kullanın

Büyük uzamsal veri setlerinde sorguları hızlandırmak için mekânsal indeksleri kullanın.

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

Önce filtreleyin, sonra hesaplayın

Mekânsal sorguları optimize etmek için önce hızlı sınırlayıcı kutu filtresi uygulayın, ardından hassas mesafe hesaplamaları yapın.

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

Görüntüleme hassasiyetini azaltın

Koordinat hassasiyetini azaltarak ekran için mekansal geometrileri basitleştirin.

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

Koordinat referans sistemleri

SRID koordinat sistemini belirler ve Microsoft SQL'in mesafe ve alanları hesaplama biçimini etkiler.

Yaygın SRID değerleri

SRID (Mekansal Referans Tanımlayıcısı), verileriniz için koordinat sistemini tanımlar. Yanlış SRID kullanmak yanlış mesafe ve alan hesaplamalarına yol açar.

SRID	Ad	Kullanım örneği
4326	WGS 84	GPS koordinatları, web haritalama
4269	NAD 83	Kuzey Amerika anketleri
0	SRID yok	Düz geometri, yerel koordinatlar

SRID'ler arasında dönüşüm

Mekânsal verileri alın ve koordinat sistemini doğrulamak için SRID'sini kontrol edin.

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

En iyi uygulamalar

Bu yönergeleri mekansal verilerle doğru ve verimli çalışmak için uygulayın.

Geometriyi doğrulama

Mekansal verileri geçerlilik açısından kontrol edin ve işlemden önce geometrik problemleri tespit edin.

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

Geometriyi geçerli kıl

Bu yöntemi geçersiz geometrik şekilleri otomatik olarak düzeltmek için kullanın MakeValid() .

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

Doğru tipi seçin

Arasındaki geography seçim ve geometry Microsoft SQL'in mesafeleri ve alanları nasıl hesapladığını belirler:

• Coğrafya: Gerçek dünya konumları (GPS noktaları), dünya yüzeyi hesaplamaları, metre cinsinden mesafeler.
• geometri: Düz yüzeyler (kat planları, CAD), Kartezyen koordinat sistemleri veya SRID'nin önemi olmadığı zamanlar.

İlgili içerik

• Veri türü eşlemeleri
• Microsoft SQL uzaysal dokümantasyonu