In today’s digital age, spatial data plays a crucial role in various applications and services. Whether it’s mapping, location-based services, or geospatial analysis, the ability to store and manipulate spatial data is essential. In this article, we will explore the concepts and capabilities of SQL Server’s spatial data types.

Introduction to Spatial Data Types

SQL Server 2008 introduced two-dimensional vector data types, namely geography and geometry. These data types allow us to represent and work with spatial information in our databases. Let’s take a closer look at the class hierarchies for both the geometry and geography data types.

Geometry Class Hierarchy

The geometry class hierarchy consists of various concrete classes, including Point, LineString, and Polygon. Each of these classes has a collection equivalent that can store multiple instances. Additionally, there is a GeomCollection class that can contain a mixture of points, linestrings, and polygons.

Data Formats

Spatial data can be represented in three different formats: Well-Known Binary (WKB), Well-Known Text (WKT), and Geography Markup Language (GML). These formats are standardized by the Open Geospatial Consortium (OGC) and serve different purposes. WKB is a compact binary format, WKT is a human-readable text format, and GML is an XML-based format.

The Point Class

The Point class represents a single, exact location in space. It contains X and Y coordinates and can optionally include Z (elevation) and M (measure) values. Let’s explore some basic representations and operations using the Point class.

Inserting and Retrieving Points

To insert a Point instance, we can use the WKT format and specify the X and Y coordinates. We can then retrieve the Point instance in various formats, such as WKT, binary representation, and text representation.

-- Insert a simple Point with an X and Y coordinate
INSERT INTO GeometryDemo (ID, Value) VALUES (1, 'POINT(50 65)');

-- Retrieve the Point instance
SELECT ID, Value.ToString() AS WKT, Value.STX AS [X Coordinate], Value.STY AS [Y Coordinate], Value.STAsBinary() as [Binary Representation], Value.STAsText() as [Text Representation] FROM GeometryDemo WHERE ID = 1;

In the above example, we insert a Point with X coordinate 50 and Y coordinate 65. We then retrieve the Point instance and display its properties and representations.

Working with Z and M Coordinates

A Point can also include Z (elevation) and M (measure) coordinates. These values are optional and can be used for specific purposes. We can insert a Point with Z and M coordinates using the WKT format and retrieve the values accordingly.

-- Insert a Point with X, Y, Z, and M coordinates
INSERT INTO GeometryDemo (ID, Value) VALUES (2, 'POINT(55 70 100.2 50)');

-- Retrieve the Point instance
SELECT ID, Value.ToString() AS WKT, Value.STX AS [X Coordinate], Value.STY AS [Y Coordinate], Value.Z AS [Elevation], Value.M AS [Measure] FROM GeometryDemo WHERE ID = 2;

In the above example, we insert a Point with X coordinate 55, Y coordinate 70, Z coordinate 100.2, and M coordinate 50. We then retrieve the Point instance and display its properties.

Static Point Methods

In addition to instance methods, SQL Server’s spatial data types also provide static methods. These methods are tied to the class itself and can be invoked without an instance. One common usage pattern for static methods is the Factory Pattern, which returns a new instance of the class. Let’s explore some examples of using static methods to create Point instances.

-- Create Point instances using static methods
DECLARE @Pnt1 Geometry, @Pnt2 Geometry, @Pnt3 Geometry, @Pnt4 Geometry, @Pnt5 Geometry

-- 1. Create a Point from X and Y coordinates
SET @Pnt1 = geometry::Point(30, 20, 0);

-- 2. Create a Point using the Parse method
SET @Pnt2 = geometry::Parse('POINT(90 23 56000 231)');

-- 3. Create a Point using the STPointFromText method
SET @Pnt3 = geometry::STPointFromText('POINT(20 25)', 0)

-- 4. Create a Point from GML
SET @xmlSnippet = '<Point xmlns="http://www.opengis.net/gml"> <pos>34 23</pos> </Point>';
SET @Pnt4 = geometry::GeomFromGml(@xmlSnippet, 0);

-- 5. Null Point
SET @Pnt5 = geometry::[Null];

-- Select all points
SELECT @Pnt1.ToString() AS [From Arguments], @Pnt2.ToString() AS [From Parse], @Pnt3.ToString() as [From STPointFromText], @Pnt4.ToString() AS [From Xml], @Pnt5 AS [Null Point];

In the above example, we create Point instances using static methods such as Point(), Parse(), STPointFromText(), and GeomFromGml(). We also demonstrate creating a null Point instance using the [Null] property.

Conclusion

In this article, we explored the concepts and capabilities of SQL Server’s spatial data types, focusing on the Point class. We learned how to insert and retrieve Point instances, work with Z and M coordinates, and use static methods to create Point instances. Understanding spatial data types is essential for leveraging the power of SQL Server in spatial applications and services.

Stay tuned for the next part of this series, where we will dive into the LineString and Polygon classes and explore visualization tools for spatial data.