Notitie
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen u aan te melden of de directory te wijzigen.
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen de mappen te wijzigen.
Microsoft SQL biedt twee ruimtelijke datatypen die je kunt gebruiken via de mssql-python-driver. Kies het type op basis van wat je coördinaten vertegenwoordigen:
| Type | Beschrijving | Gebruiksituatie |
|---|---|---|
geography |
Ronde-aarde coördinatensysteem | GPS-coördinaten, kaarten, alles op het aardoppervlak van de aarde. Afstanden zijn in meters. SRID 4326 (WGS 84) is standaard voor GPS. |
geometry |
vlakcoördinatensysteem | Plattegronden, CAD-tekeningen, spelwerelden of elk cartesisch coördinatensysteem. Afstanden zijn in de eenheden van je coördinatensysteem. |
Plaats ruimtelijke gegevens in
Gebruik de constructorfuncties van Microsoft SQL, zoals geography::Point(), of geef tekenreeksen in Well-Known Text (WKT) door.
Geografiegegevens (punten)
Voeg geografische punten in met de Puntconstructor met breedtegraad, lengtegraad en 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 uit WKT (Well-Known Text)
Voeg geografische gegevens in met Well-Known Text-formaat, dat punten, lijnstrings en polygonen ondersteunt.
# 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()
Geometrische gegevens
Voeg vlakvlakgeometriegegevens in met behulp van punten en polygonen voor CAD-tekeningen of plattegronden.
# 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()
Zoek ruimtelijke gegevens op
Gebruik ruimtelijke methoden zoals STAsText() en de Lat/Long eigenschappen om coördinaten in een leesbaar formaat op te halen.
Ophalen als tekst
Haal ruimtelijke coördinaten op als Well-Known tekstformaat, samen met breedte- en lengtegraadeigenschappen.
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}")
Ophalen als GeoJSON
Microsoft SQL ondersteunt GeoJSON-conversie. In SQL Server 2017+ kun je met stringfuncties direct GeoJSON bouwen, of converteren in Python zoals hieronder getoond:
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}")
Zoek nabijgelegen punten
Vind alle locaties binnen een gespecificeerde afstand van een referentiepunt met behulp van ruimtelijke afstandsfuncties.
# 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")
Punten vinden binnen een veelhoek
Zoek alle punten die elkaar kruisen met of binnen een geografisch polygongebied vallen.
# 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
""")
Bereken afstanden
De methode van STDistance() Microsoft SQL geeft afstanden in meters terug voor geografietypen.
Afstand tussen twee punten
Maak een hulpfunctie om de afstand in kilometers tussen twee geografische punten te berekenen.
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")
Bereken de oppervlakte
Bereken de oppervlakte van een geografisch gebied in vierkante kilometers.
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")
Ruimtelijke bewerkingen
Microsoft SQL ondersteunt set-operaties op ruimtelijke objecten, waaronder union, intersection en buffer.
Unie van vormen
Combineer twee geografische polygonen tot één vorm en bereken de totale oppervlakte.
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")
Kruispunt
Vind het overlappende gebied waar twee geografische gebieden elkaar kruisen.
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 (gebied uitbreiden)
Maak een bufferzone aan rond een geografisch punt en vind alle locaties binnen de bufferstraal.
# 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-integratie
Haal WKT-strings op van Microsoft SQL en parse ze met de Shapely-bibliotheek voor client-side geometrieverwerking.
Met de Shapely-bibliotheek
Installeer door pip install shapely uit te voeren.
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}")
Genereer ruimtelijke data met Shapely
Maak geometrische objecten met de Shapely-bibliotheek en zet deze om naar WKT-formaat voor invoeging 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]}...")
Converteer naar GeoJSON
Converteer ruimtelijke data van WKT-formaat naar GeoJSON voor webgebaseerde applicaties en kaartdiensten.
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-integratie
Laad ruimtelijke data van Microsoft SQL in een GeoPandas GeoDataFrame voor geavanceerde georuimtelijke analyse.
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())
Ruimtelijke indexen
Maak ruimtelijke indexen in Microsoft SQL om zoekopdrachten op grote ruimtelijke datasets te versnellen.
-- 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)
);
Tips voor prestaties
Pas deze technieken toe om ruimtelijke queries efficiënt op schaal te houden.
Gebruik ruimtelijke indexhints
Gebruik ruimtelijke indexen om zoekopdrachten op grote ruimtelijke datasets te versnellen.
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
""")
Eerst filteren, dan berekenen
Optimaliseer ruimtelijke queries door eerst een snelle begrensingsboxfilter toe te passen en vervolgens nauwkeurige afstandsberekeningen uit te voeren.
# 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
""")
Verminder de precisie voor weergave
Vereenvoudig ruimtelijke geometrieën voor weergave door de coördinatenprecisie te verlagen.
cursor.execute("""
SELECT
City,
SpatialLocation.Reduce(100).STAsText() AS SimplifiedWKT -- 100 meter tolerance
FROM Person.Address
WHERE SpatialLocation IS NOT NULL AND City = 'Seattle'
""")
Referentiesystemen coördineren
De SRID bepaalt het coördinatensysteem en beïnvloedt hoe Microsoft SQL afstanden en oppervlakten berekent.
Veelvoorkomende SRID-waarden
De SRID (Spatial Reference Identifier) definieert het coördinatensysteem voor uw gegevens. Het gebruik van de verkeerde SRID levert onjuiste afstands- en oppervlakteberekeningen op.
| SRID | Naam | Gebruiksituatie |
|---|---|---|
| 4326 | WGS 84 | GPS-coördinaten, webkaarten |
| 4269 | NAD 83 | Noord-Amerikaanse onderzoeken |
| 0 | Geen SRID | Vlakke geometrie, lokale coördinaten |
Converteer tussen SRID's
Haal ruimtelijke gegevens op en controleer de SRID om het coördinatensysteem te verifiëren.
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
""")
Beste praktijken
Pas deze richtlijnen toe om correct en efficiënt met ruimtelijke data te werken.
Geometrie valideren
Controleer de ruimtelijke gegevens op geldigheid en identificeer eventuele geometrische problemen voordat je ze verwerkt.
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 valideren
Gebruik de MakeValid() methode om automatisch ongeldige geometrische vormen te corrigeren.
# 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
""")
Kies het juiste type
De keuze tussen geography en geometry bepaalt hoe Microsoft SQL afstanden en oppervlakten berekent:
- Geografie: Echte locaties (GPS-punten), berekeningen van het aardoppervlak, afstanden in meters.
- geometrie: Vlakke oppervlakken (plattegronden, CAD), Cartesische coördinatensystemen, of wanneer SRID niet uitmaakt.