SQL Spatial Data Types

Introduction to Spatial Data

Spatial data types allow you to store and query geographic information, such as points, lines, polygons, and more, directly within your SQL Server database. This enables powerful location-based services, mapping applications, and spatial analysis.

SQL Server supports two main spatial data types:

GEOMETRY: Represents data in a flat (Euclidean) plane. It's suitable for representing data where the distance and area calculations are not affected by the Earth's curvature, such as building floor plans or city maps.
GEOGRAPHY: Represents data on a spherical (or spheroid) model of the Earth. It's ideal for representing real-world geographic data, such as country borders, GPS coordinates, and global location services, where accurate distance and area calculations are crucial.

The GEOMETRY Data Type

The GEOMETRY data type stores data in a Cartesian coordinate system. Common methods include:

Creating points, lines, polygons, multi-points, multi-lines, and multi-polygons.
Performing spatial operations like intersection, union, distance, and buffer.
Validating spatial data.

Common GEOMETRY Objects

POINT: A single coordinate (X, Y).
LINESTRING: A sequence of connected points.
POLYGON: A closed shape defined by a boundary and optional holes.

Example Usage (GEOMETRY)

Creating a simple point:


DECLARE @point GEOMETRY = GEOMETRY::STGeomFromText('POINT(1 1)', 0);
SELECT @point.STAsText();

Creating a polygon:


DECLARE @polygon GEOMETRY = GEOMETRY::STGeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 0);
SELECT @polygon.STArea();

The GEOGRAPHY Data Type

The GEOGRAPHY data type uses a spherical model of the Earth, typically based on the SRID (Spatial Reference Identifier) for WGS 84 (SRID 4326). This ensures that spatial calculations are geographically accurate.

Storing latitude and longitude coordinates.
Calculating distances between two points on Earth.
Determining if a point falls within a geographic area.

Common GEOGRAPHY Objects

POINT: A single latitude and longitude coordinate.
LINESTRING: A path on the Earth's surface.
POLYGON: A region on the Earth's surface.

Example Usage (GEOGRAPHY)

Creating a point with WGS 84 coordinates:


DECLARE @city GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT(-74.0060 40.7128)', 4326);
SELECT @city.STDistance(GEOGRAPHY::STGeomFromText('POINT(-118.2437 34.0522)', 4326)); -- Distance to Los Angeles

Checking if a point is within a country (simplified example):


DECLARE @usa GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON((-124.73 24.53, -66.95 24.53, -66.95 49.37, -124.73 49.37, -124.73 24.53))', 4326);
DECLARE @ny GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT(-74.0060 40.7128)', 4326);
SELECT @ny.STWithin(@usa); -- Returns 1 (true) if within the USA polygon

Spatial Indexes

To efficiently query large amounts of spatial data, it's crucial to create spatial indexes. SQL Server offers two types of spatial indexes:

Spatial Index: Optimized for spatial queries like STIntersects, STDistance, etc.
Full-Text Index: For indexing textual descriptions associated with spatial data.

Creating a spatial index significantly improves the performance of operations that involve spatial relationships.