A latitude and longitude pair is useful only when SQL Server knows what space it describes. Spatial data types give points and shapes methods for distance and containment.
Choose Between the geometry and geography Spatial Data Types
geometry uses a planar coordinate system. It fits floor plans, engineering drawings, and local coordinates when a flat model is appropriate. geography uses a round-earth model for latitude and longitude. Its distance calculations use the spatial reference system’s units. Choosing the wrong one of the two spatial data types can produce a plausible number with the wrong meaning.
I ask where the coordinates came from before creating a column. A pair of values without a coordinate reference is incomplete data. A GPS location and a factory grid point can both look like two numbers, yet belong to different spaces.
The spatial reference identifier, or SRID, records the reference system. Operations such as STDistance can return NULL when SRIDs differ. Keep source SRID and column expectations in the data contract.
DECLARE @earth geography = geography::Point(47.6062, -122.3321, 4326);
DECLARE @floor geometry = geometry::Point(10, 20, 0);
SELECT @earth.STAsText() AS EarthPoint,
@floor.STAsText() AS FloorPoint;Create a Point Correctly
A point represents one location. SQL Server geography::Point takes latitude first and longitude second. Well-known text POINT syntax uses longitude first, then latitude. That difference catches even experienced developers when they switch constructors.
I test one known coordinate on a map before loading a whole feed. Values can be within valid ranges and still be reversed. A check that latitude is between minus 90 and 90 helps, but it cannot prove the location is where the business expects.
Store a stable LocationId beside the spatial value. It gives the point a business identity and helps troubleshoot an incorrect coordinate. The geometry or geography value is the shape, not the record key.
SELECT geography::Point(47.6062, -122.3321, 4326).STAsText()
AS SeattlePoint;Describe an Area With a Polygon
A polygon is a closed boundary with ordered points. It can represent a delivery zone, service territory, or floor region. The boundary must be valid for the chosen spatial type. A self-crossing shape can cause errors or unexpected results.
I keep the source coordinates and approved zone version when a polygon affects business decisions. A customer near a border can move in or out when the boundary changes. The system should explain which version was used.
Test a point clearly inside, one clearly outside, and one on the border. Containment semantics at the edge deserve deliberate testing. A map picture alone does not specify how boundary points should be classified.
DECLARE @zone geometry =
geometry::STGeomFromText(
'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 0);
DECLARE @point geometry = geometry::Point(5, 5, 0);
SELECT @zone.STContains(@point) AS IsInside;
Match Spatial Methods to Spatial Data Types
STDistance finds distance between compatible spatial values. STIntersects asks whether shapes touch or overlap. STContains asks whether one shape contains another. Read the method’s specific behavior for your type and test edge cases.
A geography distance with SRID 4326 is expressed in meters. A geometry distance follows the units of its planar coordinate system. Calling both numbers Distance without a unit invites mistakes. Put the unit in the result name or API contract.
I compare one simple known pair before trusting a larger query. If the result is surprising, inspect type, SRID, coordinate order, and validity. Tuning an index before checking those four facts is a long route to the wrong answer.
DECLARE @a geography = geography::Point(47.6062, -122.3321, 4326);
DECLARE @b geography = geography::Point(47.6205, -122.3493, 4326);
SELECT @a.STDistance(@b) AS DistanceMeters;Validate Shapes in Spatial Data Types Before Publishing
Spatial data types can store invalid shapes in some situations. STIsValid checks whether a shape meets validity rules. MakeValid can produce a valid result, but it can change the shape or even its type. Do not run it across business boundaries without review.
I keep invalid source shapes in a reject area with the original representation and reason. The source owner can correct the geometry. A repair function is useful when the correction policy is defined, not as a silent clean-up step.
Check SRID consistency and NULLs along with shape validity. An index over a clean column is more useful than an index over coordinates nobody trusts. A spatial query can omit mismatched points through NULL distance without throwing an error.
SELECT ZoneId
FROM dbo.DeliveryZone
WHERE BoundaryShape IS NULL
OR BoundaryShape.STIsValid() = 0;Add a First Spatial Index
A spatial index helps SQL Server narrow candidates for supported spatial predicates and nearest-neighbor query shapes. Create it on a table with a suitable key and a geometry or geography column. For geography, the automatic grid option is a practical starting point.
The index takes storage and maintenance during writes. I measure its effect on representative searches rather than assuming every spatial query will use it. Small tables can still be faster to scan. The query shape matters too.
Inspect the actual execution plan after adding the index. A spatial index present in sys.indexes is not proof that a report uses it. Keep one test point and one test polygon for repeatable checks.
CREATE SPATIAL INDEX SIX_DeliveryZone_Boundary
ON dbo.DeliveryZone(BoundaryShape)
USING GEOMETRY_AUTO_GRID
WITH (BOUNDING_BOX = (0, 0, 100, 100));Keep Spatial Answers Tied to Business Rules
A store can be geographically close but closed. A zone can contain a point but lack service for a product. Use spatial methods to find candidates, then apply the business filters. Keep those filters visible so a result can be explained.
I ask how the application should handle no match, multiple overlapping zones, or a point exactly on a boundary. Those are not database errors. They are domain cases that need a clear rule and test.
Spatial data becomes useful when coordinate meaning, validity, and query intent agree. Start with one point and one polygon, verify units and SRID, then add an index after the query returns the right answer.
Longitude and latitude are not interchangeable, and geometry and geography express different assumptions. When importing coordinates, label each source field and check one known location on a map before loading the full set. I also check whether the source uses degrees or a projected unit. A valid point with reversed coordinates can still be very far from the intended place.
Which operation does the application need: distance, intersection, containment, or display? Choose the type and spatial reference identifier to match that operation. Keep invalid shapes out of the main workflow and define a repair process for imported polygons. Spatial methods can answer precise geometric questions, but they only operate on the coordinates the application provides. A confident wrong coordinate is still wrong.
Related reading on this blog: Introduction to Spatial Coordinate Systems: Flat Maps for a Round Planet and Validating Spatial Object with IsValidDetailed Function.
A spatial value is not just two numbers, it is a shape in a declared coordinate system.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
