Spatial data types in SQL Server

Spatial data type

SQL server furnishes us with the geometry and geography SQL data types for conserving spatial data, which allows us to render graphical data. To be more specific, it is beneficial for creating, analyzing, comparing and retrieving spatial data.

This article will start with the definition of spatial data, with relevant examples. In addition, I’ll discuss the definition of spatial data types and detail elaboration of Spatial Data Objects and Draw spellbinding Objects. I will also explain, what the differences are between geometry and geography SQL Data Types.

What is spatial data?

Spatial data, also known as geospatial data, is a particular type of information about either the physical object or physical location of data that can be constituted by numerical values in geographic collaborate systems.

Generally, the use of spatial data is for representing the location, size, and shape of an object on planet Earth such as a mountain, lake, flat ground, township or so forth. On the top of that, spatial data may include attributes which can be given more information of the entity that is being represented.

Geographic Information Systems (GIS) or other specialized software applications should be appropriate to access, manipulate, visualize and dip analyze geospatial data.

Spatial SQL data types

There are two major supported data-type is SQL server namely geometry data type and geography data type.

Geometry spatial data type
It is substantially a two-dimensional rendering of an object and also useful in case of represented as points on a planar, or flat-earth data. A good example of it is (10, 2) where the first number ‘10’ identifies that point’s position on the horizontal (x) axis and the number ‘2’ represents the point’s position on the vertical axis (y).
A common use case of the Geometry type is for a three-dimensional object, such as a building
Geography spatial data types
These are represented as latitudinal and longitudinal degrees, as on a round-earth coordinate system.
The common use case of the Geography type is to store an application’s GPS data.
In SQL Server, both SQL data types have been implemented in the .NET common language runtime (CLR)

Spatial data objects

This combines both special data types (geometry and geography). It supports a total of sixteen SQL data types in which eleven can be utilized in the database. To be more specific, these objects have inherited a particular property from their parent’s data types and this unique property distributes them as the object. Take the examples of a Polygon or point or CircularString.

Among them, ten of the depicted data objects will available to Geometry and Geography data types. The ten objects are respectively Point, MultiPoint, MultiLineString, CircularString, LineString, MultiLineString, CompoundCurve, Polygon, MultiPolygon, CurvePolygon, and GeometryCollection. However, the FullGlobe is utilized exclusively for the Geography SQL data types.

Spatial data types divided into two groups:

Single geometries: It can be stored in the database only in one way
A geometry collection: As the name suggests, is a collection of types of data objects

The object types associated with a spatial data type form a relationship with each other. In the following diagram, consider it as an example of how the object types of the Geometry SQL data types are related to each other. To be more specific, the graphic depicts the geometry hierarchy in which the geometry and geography data types are included. Dark grey is representing types of geometry and geography.

Using a specific example, if you define a point explicitly it uses the STPointFromText() method, STPointFromText ( ‘point_tagged_text’ , SRID )

The following example is for creating a geometry instance.

DECLARE @g geometry;

SET @g = geometry::STPointFromText('POINT (100 100)', 0);

SELECT @g.ToString();

SQL Server return type: geography

If you define the same instance using the STGeomFromText() method,

DECLARE @g geometry;

SET @g = geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0);

SELECT @g.ToString();

SQL Server return type: geometry

So, both the geometry and geography SQL data types recognize the instance as a point.

Now let start drawing some objects …

Creating an absorbing object

Square
To start, we will attempt to draw a simple object, in this case a square. A square is a regular quadrilateral with 4 lines, in which it has four equal angles with four sides. We should be able to easily draw a square. Let’s take a look at the example, below

1
2
3

DECLARE @Sq geometry =
geometry::STGeomFromText('LINESTRING (10 10, 10 100, 100 100, 100 10, 10 10)', 0);
SELECT @Sq
Circle
A circle is a line enclosed, end to end, in which distance from any given point to another is constant. As we all know, the distance between any of the points is commonly called as the radius. For such and object we have to use a CircularString, which is the collection of circular arc segments. The curved segment is a circular arc segment which is defined by three points in a two-dimensional plane, where the first and third point can not be same.

What happens when all three points of a circular arc are collinear is that the arc is the same as a line segment.

Drawing a circle in geographical coordinates is a crucial task. Let’s review how to do it with the code below

1
2
3

DECLARE @circle geometry
= geometry::Parse('CIRCULARSTRING(3 2, 2 3, 1 2, 2 1, 3 2)');
select @circle
Triangle
A triangle is a polygon with three vertices and three edges. It is a very rudimentary shape in geometry. Check out the below code for it

1
2
3

DECLARE @Tri geometry
= geometry::STGeomFromText('POLYGON((100 100,200 300,300 100, 100 100))', 0);
select @Tri
Filled Square
Now, let’s start with some interesting objects!

If you want to draw a solid square, in lieu of just edge, then a polygon come to mind. In the above example, we have just sketched a line in the object, but now a polygon holds everything within a line you draw

1
2
3

DECLARE @Sqfilled geometry
= geometry::STGeomFromText('POLYGON((10 10, 10 100, 100 100, 100 10, 10 10))', 0);
SELECT @Sqfilled

Layer

To dig a bit deeper, I will now pull a single object from some other objects.

It is a very common scenario that we would need to draw two objects separately, but here I would like to combine two objects and add some interesting effects. The below query will evaluate a couple of objects, a square and triangle, however, both objects overlap and give a unique image, although they are independent in shape

DECLARE @Sq geometry

= geometry::STGeomFromText('POLYGON((15 15, 15 250, 250 250, 250 15, 15 15))', 0),

@Tri geometry

= geometry::STGeomFromText('POLYGON((100 100,200 300,300 100, 100 100))', 0);

SELECT @Sq

UNION ALL

SELECT @Tri

Layer Union

Now it’s time to merge objects!

When we execute the below query it will come up with the joining of two object layers with the method of STUnion and give a very distinct look. It looks like a triangle and square have merged into a single large image to generate a distinct object

DECLARE @Sq geometry

= geometry::STGeomFromText('POLYGON((15 15, 15 250, 250 250, 250 15, 15 15))', 0),

@Tri geometry

= geometry::STGeomFromText('POLYGON((100 100,200 300,300 100, 100 100))', 0);

SELECT @Sq.STUnion(@Tri)

Layer Intersection

An intersect returns the portion of the object that is in common between two objects

If the portion of the object exists in one query and not in other, it will be removed from the results. So the part that overlaps from object1 to object 2 is returned by the method STIntersection

DECLARE @Sq geometry

= geometry::STGeomFromText('POLYGON((15 15, 15 250, 250 250, 250 15, 15 15))', 0),

@Tri geometry

= geometry::STGeomFromText('POLYGON((100 100,200 300,300 100, 100 100))', 0);

SELECT @Sq.STIntersection(@Tri)

Overlap

STSymDifference finds which part of a group of objects is overlapped. The below query provides the difference between two objects

DECLARE @Sq geometry

= geometry::STGeomFromText('POLYGON((15 15, 15 250, 250 250, 250 15, 15 15))', 0),

@Tri geometry

= geometry::STGeomFromText('POLYGON((100 100,200 300,300 100, 100 100))', 0);

SELECT @Sq.STSymDifference(@Tri)

Center

Finally, if you want to find the center point of the object there are two ways. First you can calculate yourself and the other exciting way is use the method STCentroid. There is a buffer; named STBuffer, which adds the radial to the target. In short, by providing a buffer in the center, it is visible; but it’s just for purposes of visualization and it is not required for the following query work

DECLARE @Sq geometry

= geometry::STGeomFromText('POLYGON((100 100, 100 50, 50 50, 50 100, 100 100))', 0);

SELECT @Sq.STCentroid().STBuffer(100)

UNION ALL

SELECT @Sq

Differences between the geometry and geography SQL data types

How are the geometry and geography SQL Data Types Different? These two data types mostly work similarly however, they have a few key differences, especially while storing and manipulating data.

Connecting edges

Polygon and Line String data types are defined as only vertices. To be more specific, there is a straight line between a couple of vertical geometry types. However, there should be a little elliptic arc between two vertices in the geography type.

Circular arc segments

With the geometry datatype, the circular arc is defined as XY Cartesian coordinate plane in which z values are ignored. On the flipside, with Geography types, it is defined as curve segments on a reference sphere.

Measurements

In the geometry SQL data types, especially in the flat-earth system or planner, the area and measurement of distance are given in the coordinates the same unit of measurement. On the other hand, in the geography, round-earth system or ellipsoidal datatypes, the coordinates are given in degrees of latitude and longitude. Meters are the most common units of measurement.

Conclusion

To summarize, as per my understanding, I would say that for planar spatial data that is on a flat surface, the GEOMETRY SQL data type is the best option. However for the curved surface of the earth, which is terrestrial spatial data, objects can be more easily drawn using the GEOGRAPHY SQL data type.

Nikhilesh Patel

Nikhilesh Patel is a database professional having 7+ years of experience. Most of his vocation focuses on database design, development, administration, performance tuning and optimization (both SQL Server and Oracle). He has collaborated with SQL Server 2000/2005/2008/2012/2014/2016, Oracle and PostgreSQL databases. He has fabricated and developed databases for insurance, telecom and communication domain. He is a database administrator at HighQ solution. He continuously develops his professional skills to keep accelerating with new technologies.

In his downtime, he enjoys spending time with his family, especially with his wife. On the top of that, he loves to do wondering and exploring different places.

Say hi and catch him on LinkedIn

Latest posts by Nikhilesh Patel (see all)

SSIS Web Service Tasks - May 14, 2019
Spatial SQL data types in SQL Server - July 11, 2018
The HashBytes function in T-SQL - May 16, 2018