In SQL Server, spatial data is supported through two fundamental data types: GEOGRAPHY and GEOMETRY. Each type serves a specific purpose and is utilized for handling spatial data in different contexts. Here’s a detailed explanation of both types and how to use them:
GEOGRAPHY Data Type
The GEOGRAPHY data type is used for representing data in a round-earth (geodetic) coordinate system, such as GPS coordinates. It is suitable for applications where you need to work with global or large-scale geographic information.
Key Features:
- Can store points, lines, and polygons that are defined by latitude and longitude.
- Uses the WGS 84 spatial reference system, which is the standard for GPS.
- Supports calculations like distance and area on a curved surface.
Common Functions:
- STDistance(): Computes the distance between two geography instances.
- STBuffer(): Returns a surface that is a specified distance from a geography instance.
- STWithin(): Determines if one geography instance is within another.
- STIntersection(): Returns a geography instance that represents the point set intersection of two geography instances.
Example:
You can create a table that contains geographical data and perform operations on it:
-- Create a table with a geography column
CREATE TABLE Locations
(
LocationID INT PRIMARY KEY,
Name NVARCHAR(100),
GeoLocation GEOGRAPHY
);
-- Insert a row with a point defined by latitude and longitude
INSERT INTO Locations (LocationID, Name, GeoLocation)
VALUES (1, 'Statue of Liberty', GEOGRAPHY::Point(40.6892, -74.0445, 4326));
-- Query the distance between two geographical locations
DECLARE @location1 GEOGRAPHY = GEOGRAPHY::Point(40.6892, -74.0445, 4326);
DECLARE @location2 GEOGRAPHY = GEOGRAPHY::Point(37.7749, -122.4194, 4326); -- San Francisco
SELECT @location1.STDistance(@location2) AS DistanceMeters;
GEOMETRY Data Type
The GEOMETRY data type is used for representing data in a flat, planar coordinate system. It is appropriate for local-scale calculations that do not require accounting for the curvature of the Earth.
Key Features:
- Can store points, lines, and polygons defined in a 2D (or 3D) Cartesian coordinate space.
- Suitable for applications like mapping within a small area (e.g., city planning, architectural design).
Common Functions:
- STDistance(): Similar to geography, but works in a flat plane.
- STArea(): Computes the area of a geometry instance.
- STIntersection(): Computes the intersection of two geometry instances.
Example:
You can create a table with geometric data and perform operations on it:
-- Create a table with a geometry column
CREATE TABLE CityShapes
(
ShapeID INT PRIMARY KEY,
Shape GEOMETRY
);
-- Insert a row with a polygon
INSERT INTO CityShapes (ShapeID, Shape)
VALUES (1, GEOMETRY::STGeomFromText('POLYGON((30 10, 40 40, 20 40, 10 20, 30 10))', 0));
-- Query the area of the polygon
SELECT ShapeID, Shape.STArea() AS Area
FROM CityShapes;
Choosing Between GEOGRAPHY and GEOMETRY
- Use GEOGRAPHY when dealing with global locations, lat/long coordinates, and when you require accurate calculations over long distances.
- Use GEOMETRY when working with planar projections for applications that are local in nature and do not require spherical calculations.
Conclusion
SQL Server provides robust support for spatial data types, allowing for effective storage and manipulation of geographic and geometric data. Depending on your use case, you can choose between GEOGRAPHY for world coordinates and GEOMETRY for flat coordinates. Each type has its own set of functions that make spatial queries intuitive and effective.