Microsoft SQL은 mssql-python 드라이버를 통해 사용할 수 있는 두 가지 공간 데이터 타입을 제공합니다. 좌표가 나타내는 것을 기준으로 유형을 선택하세요:
| Type | 설명 | 사용 사례 |
|---|---|---|
geography |
둥근 지구 좌표계 | GPS 좌표, 지도, 지구 표면에 있는 모든 것. 거리는 미터 단위입니다. SRID 4326(WGS 84)은 GPS의 표준입니다. |
geometry |
평면면 좌표계 | 평면도, CAD 도면, 게임 세계 또는 기타 직교 좌표계. 거리는 좌표계의 단위로 표시됩니다. |
공간 데이터 삽입
Microsoft SQL의 생성자 함수(예: geography::Point())를 사용하거나 WKT(Well-Known Text) 문자열을 전달하세요.
지리 데이터 (점수)
위도, 경도, SRID가 포함된 Point 생성자를 사용하여 지리적 지점을 삽입합니다.
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(Well-Known Text)의 지리
점, 라인스트링 및 다각형을 지원하는 Well-Known Text 형식을 사용하여 지리 데이터를 삽입합니다.
# 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()
기하학 데이터
CAD 도면이나 평면도에 점과 다각형을 사용하여 평면 기하학 데이터를 삽입하세요.
# 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()
공간 데이터 쿼리
와 같은 STAsText()Lat/Long 공간 메서드를 사용해 좌표를 읽기 쉬운 형식으로 가져오세요.
텍스트로 가져오기
공간 좌표를 Well-Known Text 형식으로 위도 및 경도 속성과 함께 가져옵니다.
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으로 가져오기
Microsoft SQL은 GeoJSON 변환을 지원합니다. SQL Server 2017+에서는 문자열 함수를 사용해 GeoJSON을 직접 빌드하거나, 아래에 표시된 대로 Python으로 변환할 수 있습니다:
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}")
근처 지점 찾기
공간 거리 함수를 사용해 기준점에서 지정된 거리 내 모든 위치를 찾는다.
# 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")
다각형 내에서 점들을 찾기
지리적 다각형 영역과 교차하거나 그 안에 위치한 모든 점을 찾으세요.
# 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
""")
거리 계산
Microsoft SQL의 STDistance() 방법은 지리 유형에 대해 미터 단위의 거리를 반환합니다.
두 지점 간 거리
두 지리적 지점 간 거리를 킬로미터 단위로 계산하는 보조 기능을 만드세요.
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")
면적 계산
지리적 지역의 면적을 제곱킬로미터 단위로 계산합니다.
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")
공간 작업
Microsoft SQL은 유니언, 교차, 버퍼 등 공간 객체에 대한 집합 연산을 지원합니다.
도형의 합
두 지리적 다각형을 하나의 형태로 합치고 전체 면적을 계산하세요.
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")
교차점
두 지리적 지역이 만나는 겹치는 영역을 찾으세요.
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})
버퍼(확장 영역)
지리적 지점 주변에 완충 구역을 만들고 그 반경 내 모든 위치를 찾아보세요.
# 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 통합
Microsoft SQL에서 WKT 문자열을 가져와 Shapely 라이브러리로 파싱하여 클라이언트 측 기하학 처리를 할 수 있습니다.
Shapely 라이브러리와 함께
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}")
Shapely로 공간 데이터 생성
Shapely 라이브러리를 사용하여 기하학적 객체를 생성하고 이를 WKT 형식으로 변환하여 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]}...")
GeoJSON으로 변환하기
웹 기반 애플리케이션 및 지도 서비스에 사용할 공간 데이터를 WKT 형식에서 GeoJSON으로 변환할 수 있습니다.
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))
지오데이터프레임 통합
고급 지리공간 분석을 위해 Microsoft SQL에서 GeoPandas GeoDataFrame에 공간 데이터를 불러오세요.
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())
공간 인덱스
Microsoft SQL에서 공간 인덱스를 생성하여 대규모 공간 데이터셋에 대한 쿼리를 가속화하세요.
-- 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)
);
성능 팁
이러한 기법을 적용하여 공간 쿼리를 대규모로 효율적으로 유지하세요.
공간 인덱스 힌트 사용하세요
공간 인덱스를 사용하여 대규모 공간 데이터셋에 대한 쿼리를 가속화하세요.
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
""")
먼저 필터링하고, 그 다음에 계산하세요
먼저 빠른 경계 상자 필터를 적용한 후 정밀한 거리 계산을 수행하여 공간 쿼리를 최적화합니다.
# 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
""")
디스플레이 정밀도 감소
좌표 정밀도를 낮춰 공간 기하학을 단순화하세요.
cursor.execute("""
SELECT
City,
SpatialLocation.Reduce(100).STAsText() AS SimplifiedWKT -- 100 meter tolerance
FROM Person.Address
WHERE SpatialLocation IS NOT NULL AND City = 'Seattle'
""")
좌표 참조 시스템
SRID는 좌표계를 결정하며 Microsoft SQL이 거리와 면적을 계산하는 방식에 영향을 미칩니다.
일반적인 SRID 값
SRID(공간 참조 식별자)는 데이터의 좌표계를 정의합니다. 잘못된 SRID를 사용하면 잘못된 거리와 면적 계산이 발생합니다.
| SRID | 이름 | 사용 사례 |
|---|---|---|
| 4326 | WGS 84 | GPS 좌표, 웹 매핑 |
| 4269 | NAD 83 | 북미 측량 |
| 0 | SRID 없음 | 평면 기하학, 국소 좌표 |
SRID 간 변환
공간 데이터를 가져오고 좌표계를 확인하기 위해 SRID를 확인한다.
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
""")
모범 사례
이 지침을 적용하여 공간 데이터를 올바르고 효율적으로 활용하세요.
기하학 검증
처리 전에 공간 데이터를 검증하고 기하학적 문제를 식별하세요.
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}")
기하학을 유효하게 만드세요
이 방법을 사용 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
""")
적절한 종류를 선택하세요
와 geography 의 geometry 선택은 Microsoft SQL이 거리와 면적을 계산하는 방식을 결정합니다:
- 지리: 실제 위치(GPS 포인트), 지표면 계산, 미터 단위의 거리.
- 기하학: 평면 면면(평면도, CAD), 데카르트 좌표계, 또는 SRID가 중요하지 않을 때.