SQL Server Spatial Data Types Guide in sqlcmd and DbSchema

Keep a latitude and a longitude in two float columns, and every query that needs a distance carries its own trigonometry, while a test for whether a point lies inside a zone has to be written by hand. An index on the latitude can narrow a search to a band of rows, but no index serves the distance itself. SQL Server has two data types that store the shape itself and answer those questions in T-SQL: geometry for flat, planar data and geography for round-earth data such as GPS positions.

What the geometry and geography types store

Both types hold the same family of shapes, taken from the Open Geospatial Consortium's Simple Features for SQL specification. The geometry type conforms to version 1.1.0 of that specification. Microsoft counts 16 object types, and 11 of them can be created in a database: the ten in solid blue boxes below, which both types share, and FullGlobe, which only geography has.

Point, LineString, CircularString, CompoundCurve, Polygon and CurvePolygon are simple types. MultiPoint, MultiLineString, MultiPolygon and GeometryCollection are collections of other instances. The curved types store circular arcs directly, so a curved boundary needs three points per arc instead of many short straight segments. FullGlobe is a polygon that covers the whole Earth: it has an area, but no border and no vertices.

You write a shape as well-known text, the plain-text format the specification defines, such as LINESTRING(0 0, 2 2). Well-known text puts X first, and for geography X is the longitude, so a point in San Francisco is POINT(-122.4194 37.7749). The geography::Point method, used further down, takes the same two numbers the other way round.

Why a spatial type beats two float columns

A spatial column turns the arithmetic into a method call. The distance between two shapes, the area of one, the part two zones share and the test for whether a point lies inside a zone are all methods on the value. Every application that reads the table gets the same answer, and a spatial index can serve the search.

The units come from the data. In geometry, a distance is in whatever unit the coordinates are in. Microsoft's own example measures from (2, 2) to (5, 6):

DECLARE @a geometry = geometry::STGeomFromText('POINT(2 2)', 0);
DECLARE @b geometry = geometry::STGeomFromText('POINT(5 6)', 0);
SELECT @a.STDistance(@b) AS distance_units;

That is five units, whether the coordinates were meters, feet or pixels. In geography the coordinates are degrees, but lengths come back in meters and areas in square meters, the unit that SRID 4326 declares.

Measuring on a round Earth is an approximation, and Microsoft puts a number on it: on common earth models, STDistance on geography differs from the exact geodesic distance by no more than 0.25%. Over 20 km, that is at most 50 meters.

Every instance carries an SRID

A spatial reference identifier (SRID) says which ellipsoid, datum and projection the coordinates were measured against. 4326 is WGS 84, the system GPS positions are given in, and SQL Server uses it for a geography value that names no SRID. The SRIDs geography accepts are listed in the sys.spatial_reference_systems catalog view.

One column can hold values with different SRIDs, but a method that takes two values works only when both carry the same one. When they differ, the method returns NULL rather than raising an error:

DECLARE @a geometry = geometry::STGeomFromText('POINT(2 2)', 0);
DECLARE @c geometry = geometry::STGeomFromText('POINT(5 6)', 4326);
SELECT @a.STDistance(@c) AS distance_units;

In a WHERE clause, that NULL removes every row without a message. When a spatial query returns nothing, compare the SRIDs of the two sides first.

No permission is specific to spatial columns. Reading one takes SELECT on the table, and writing takes INSERT or UPDATE, as for any other column. The fixed database roles db_datareader and db_datawriter grant the reading and the writing on every user table.

Why the direction of a geography ring matters

In geometry, a polygon's ring can run either way. In geography, the direction decides what the polygon is, because a ring drawn on a globe splits it into two areas, and either one could be the inside. SQL Server settles it with the left-hand rule: walk the ring in the order the points are listed, and the area on your left is the interior.

The same four corners around San Francisco, listed in both orders:

DECLARE @counterclockwise geography = geography::STGeomFromText(
    'POLYGON((-122.52 37.70, -122.35 37.70, -122.35 37.83, -122.52 37.83, -122.52 37.70))', 4326);
DECLARE @clockwise geography = geography::STGeomFromText(
    'POLYGON((-122.52 37.70, -122.52 37.83, -122.35 37.83, -122.35 37.70, -122.52 37.70))', 4326);
SELECT CAST(@counterclockwise.STArea() / 1e6 AS decimal(12,1)) AS counterclockwise_km2,
       CAST(@clockwise.STArea() / 1e6 AS decimal(12,1)) AS clockwise_km2;

The clockwise polygon is the whole Earth with a square hole in it:

On a map, counterclockwise keeps the interior on your left, so that is the order to list a small area in. When data arrives the other way round, ReorientObject() swaps a value's interior and exterior, and @clockwise.ReorientObject().STArea() gives back the 216.1 km².

A database at compatibility level 100 or below refuses the large polygon outright. At those levels a geography value must fit inside one hemisphere, and building the clockwise square fails:

Microsoft.SqlServer.Types.GLArgumentException: 24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation. To create a larger than hemisphere geography instance, upgrade the version of SQL Server and change the database compatibility level to at least 110.

At the same levels, STIntersection, STUnion, STDifference, STSymDifference and STBuffer return NULL when their result would not fit in a hemisphere. From compatibility level 110 up, neither restriction applies.

Creating and querying a spatial table in sqlcmd

The examples here ran on SQL Server 2022. If you haven't connected with sqlcmd before, creating a SQL Server database walks through a first session. Open one, and leave -P off: sqlcmd then prompts for the password, which Microsoft recommends over putting it on the command line.

sqlcmd -S localhost -U sa

Type GO on a line of its own to send each batch. Create a database and a table whose third column is a geography, then insert three points:

CREATE DATABASE SpatialDB;
GO
USE SpatialDB;
GO
CREATE TABLE Locations (
    ID       int IDENTITY(1,1) PRIMARY KEY,
    Name     nvarchar(50) NOT NULL,
    Location geography NOT NULL
);
GO
INSERT INTO Locations (Name, Location) VALUES
    ('Depot',    geography::STPointFromText('POINT(-122.4194 37.7749)', 4326)),
    ('Oakland',  geography::Point(37.8044, -122.2712, 4326)),
    ('San Jose', geography::Point(37.3382, -121.8863, 4326));
GO

ID numbers the rows, Name labels them, and Location holds the point. The depot is built from well-known text with STPointFromText, longitude first. The other two rows use geography::Point, which takes the latitude, then the longitude, then the SRID. Swap the first two and SQL Server stops you, since no latitude lies beyond 90 degrees:

System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.

That check only fires when the longitude is outside the range of a latitude. For a place whose longitude lies between -90 and 90, the swapped pair is another valid position, and nothing warns you.

Selecting the column directly returns the stored binary value rather than text. In full, the depot's value is 0xE6100000010CD0D556EC2FE3424050FC1873D79A5EC0, and its first four bytes, E6100000, are the SRID 4326 written with the least significant byte first. ToString() returns well-known text instead:

SELECT ID, Name, Location.ToString() AS Location FROM Locations;
GO

The rows built with geography::Point come back longitude first as well. Location.Lat and Location.Long return the two numbers separately.

Nearness, region and overlap queries

Spatial questions fall into three kinds, and each has its methods:

A nearness query ranks the locations by their distance from the depot. STDistance returns meters, so dividing by 1,000 gives kilometers:

DECLARE @depot geography = (SELECT Location FROM Locations WHERE Name = 'Depot');
SELECT Name, CAST(Location.STDistance(@depot) / 1000 AS decimal(6,1)) AS distance_km
FROM Locations
ORDER BY Location.STDistance(@depot);
GO

A region query asks which locations lie inside an area. STBuffer(20000) turns the depot's point into a zone holding every point within 20 km of it, and STIntersects tests each location against that zone:

DECLARE @zone geography = (SELECT Location FROM Locations WHERE Name = 'Depot').STBuffer(20000);
SELECT Name FROM Locations WHERE Location.STIntersects(@zone) = 1;
GO

San Jose, 67.6 km away, falls outside:

An overlap query builds a new shape from two. Give Oakland a 20 km zone as well, and the two zones share part of their area:

DECLARE @depotZone   geography = (SELECT Location FROM Locations WHERE Name = 'Depot').STBuffer(20000);
DECLARE @oaklandZone geography = (SELECT Location FROM Locations WHERE Name = 'Oakland').STBuffer(20000);
SELECT CAST(@depotZone.STIntersection(@oaklandZone).STArea() / 1e6 AS decimal(8,1)) AS shared_km2,
       CAST(@depotZone.STUnion(@oaklandZone).STArea() / 1e6 AS decimal(8,1)) AS combined_km2;
GO

STDifference returns what is left of the first zone once the second is taken away, and STSymDifference returns the parts that belong to one zone only.

Finding the nearest location with a spatial index

Every query above reads the whole table, which costs nothing on three rows. On a large table, a spatial index lets SQL Server skip the locations that are far away. It needs a primary key on the table, which Locations has:

SET QUOTED_IDENTIFIER ON;
GO
CREATE SPATIAL INDEX SIX_Locations_Location ON Locations (Location);
GO

The SET line matters in sqlcmd. The ODBC version of sqlcmd starts each session with QUOTED_IDENTIFIER off, and there the index fails without it:

Msg 1934, Level 16, State 1
CREATE INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with spatial index operations.

Starting the ODBC sqlcmd with -I has the same effect as the SET line. The Go version of sqlcmd always has quoted identifiers on and ignores -I.

The index serves a nearest-neighbor query only when the query has this shape:

• TOP (n), without PERCENT
• STDistance on the indexed column in the WHERE clause, joined to any other condition with AND
• the rows where STDistance is NULL filtered out
• STDistance on the same column as the first ORDER BY expression, sorted ascending

DECLARE @customer geography = geography::Point(37.79, -122.40, 4326);
SELECT TOP (1) Name, CAST(Location.STDistance(@customer) / 1000 AS decimal(6,1)) AS distance_km
FROM Locations
WHERE Location.STDistance(@customer) IS NOT NULL
ORDER BY Location.STDistance(@customer);
GO

An index hint tells you whether a query has the shape. Add WITH (INDEX (SIX_Locations_Location)) after FROM Locations, and the query above still returns the depot. Remove its WHERE line, and the same hint fails:

Msg 8622, Level 16, State 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

The geography column in DbSchema

DbSchema connects to the same instance and shows the table as a diagram. Open Connect to Database, choose SQL Server, enter the Server Host, Port, Database User and Password, click Test Connection, then Connect. DbSchema reverse-engineers SpatialDB and draws Locations on a diagram.

In a schema you inherit, the diagram is the quick way to find the spatial columns. Open the Diagram menu and enable Show Column Types, and DbSchema shows each column's data type next to it.

Open the SQL Editor from the Editors menu, paste the nearest-location query and click Execute Query. The result appears as a table in the result pane. The SQL History pane records every statement run in the session, and clicking one loads it back into the editor, so the next distance test starts from the last one.

Statements run in the SQL Editor act on the connected SQL Server database, which is also where the spatial index lives. The diagram's layout and the editor belong to the DbSchema model rather than to the database, so arranging them changes nothing in SpatialDB.

Spatial columns pay off the moment a distance or a containment test moves out of the application and into the query. Download DbSchema, connect it to your SQL Server, and run the nearest-location query against a table of your own. Connecting, reverse-engineering, the diagram and the SQL Editor are all in the free Community Edition.

Sources

1. Spatial Data Types Overview
2. Spatial Reference Identifiers (SRIDs)
3. STDistance (geography Data Type)
4. Polygon
5. Point (geography Data Type)
6. ToString (geography Data Type)
7. Create, Modify, and Drop Spatial Indexes
8. Query Spatial Data for Nearest Neighbor
9. Database-level roles
10. sqlcmd utility
11. DbSchema SQL Editor