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 typeIt 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 typesThese 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
- SquareTo 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, belowDECLARE @Sq geometry =geometry::STGeomFromText('LINESTRING (10 10, 10 100, 100 100, 100 10, 10 10)', 0);SELECT @Sq
- CircleA 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 belowDECLARE @circle geometry= geometry::Parse('CIRCULARSTRING(3 2, 2 3, 1 2, 2 1, 3 2)');select @circle
- TriangleA triangle is a polygon with three vertices and three edges. It is a very rudimentary shape in geometry. Check out the below code for itDECLARE @Tri geometry= geometry::STGeomFromText('POLYGON((100 100,200 300,300 100, 100 100))', 0);select @Tri
- Filled SquareNow, 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 drawDECLARE @Sqfilled geometry= geometry::STGeomFromText('POLYGON((10 10, 10 100, 100 100, 100 10, 10 10))', 0);SELECT @Sqfilled
- LayerTo 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 shapeDECLARE @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 @SqUNION ALLSELECT @Tri
- Layer UnionNow 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 objectDECLARE @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 IntersectionAn intersect returns the portion of the object that is in common between two objectsIf 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 STIntersectionDECLARE @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)
- OverlapSTSymDifference finds which part of a group of objects is overlapped. The below query provides the difference between two objectsDECLARE @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)
- CenterFinally, 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 workDECLARE @Sq geometry= geometry::STGeomFromText('POLYGON((100 100, 100 50, 50 50, 50 100, 100 100))', 0);SELECT @Sq.STCentroid().STBuffer(100)UNION ALLSELECT @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.
