programming4us
programming4us
DATABASE

Transact-SQL in SQL Server 2008 : Spatial Data Types (part 1) - Representing Spatial Data, Working with Geometry Data

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
8/1/2012 6:12:24 PM
SQL Server’s support of SQLCLR allows for very rich user-defined types to be utilized. For example, a developer could create a single object that contains multiple properties and can also perform calculations internally (methods), yet still store it in a single column in a single row in a database table. This allows multiple complex types of data to be stored and queried in the database, instead of just strings and numbers.

SQL Server 2008 makes use of SQLCLR to support two new .NET CLR data types for storing spatial data: GEOMETRY and GEOGRAPHY. These types support methods and properties that allow for the creation, comparison, analysis, and retrieval of spatial data. Spatial data types provide a comprehensive, high-performance, and extensible data storage solution for spatial data, enabling organizations of any scale to integrate geospatial features into their applications and services.

The GEOMETRY data type is a .NET CLR data type that supports the planar model/data, which assumes a flat projection and is therefore sometimes called flat earth. Geometry data represents information in a uniform two-dimensional plane as points, lines, and polygons on a flat surface, such as maps and interior floor plans where the curvature of the earth does not need to be taken into account. For example, perhaps your user-defined coordinate space is being used to represent a warehouse facility. Within that coordinate space, you can use the GEOMETRY data type to define areas that represent storage bays within the warehouse. You can then store data in your database that tracks which inventory is located in which area. You could then query the data to determine which forklift driver is closest to a certain type of item, for example.

The GEOGRAPHY data type provides a storage structure for geodetic data, sometimes referred to as round-earth data because it assumes a roughly spherical model of the world. It provides a storage structure for spatial data that is defined by latitude and longitude coordinates using an industry standard ellipsoid such as WGS84, the projection method used by Global Positioning System (GPS) applications. The SQL Server GEOGRAPHY data type uses latitude and longitude angles to identify points on the earth. Latitude measures how far north (or south) of the equator a point is, while longitude measures how far east (or west) of a prime meridian a point is. Note that this coordinate system can be used to identify points on any spherical object, be it a baseball, the earth, or even the moon.

The GEOMETRY and GEOGRAPHY data types support seven instance types that you can create and work with in a database:

  • POINT—A POINT is an exact location and is defined in terms of an X and Y pair of coordinates, as well as optionally by Z (elevation) and M (measure) coordinates. It does not have a length or any area associated with it. These instance types are used as the fundamental building blocks of more complex spatial types.

  • MULTIPOINT—A MULTIPOINT is a collection of zero or more points.

  • LINESTRING—A LINESTRING is the path between a sequence of points (that is, a series of connected line segments). It is considered simple if it does not cross over itself and is considered a ring if the starting point is the same as the ending point. A LINESTRING is always considered to be a one-dimensional object; it has length but does not have area (even if it is a ring).

  • MULTILINESTRING—A MULTILINESTRING is a collection of zero or more GEOMETRY or GEOGRAPHY LINESTRING instances.

  • POLYGON—A POLYGON is a closed two-dimensional shape defined by a ring. It has both length and area and has at least three distinct points. A POLYGON may also have holes in its interior (a hole is defined by another POLYGON). Area within a hole is considered to be exterior to the POLYGON itself.

  • MULTIPOLYGON—A MULTIPOLYGON instance is a collection of zero or more POLYGON instances.

  • GEOMETRYCOLLECTION—A GEOMETRYCOLLECTION is a collection of zero or more GEOMETRY or GEOGRAPHY instances. A GEOMETRYCOLLECTION can be empty. This is similar to a list or an array in most programming languages. The most generic type of collection is the GEOMCOLLECTION, whose members can be of any type.

Representing Spatial Data

The Open Geospatial Consortium, Inc. (OGC) is a nonprofit, international, voluntary consensus standards organization that is leading the development of standards for geospatial and location-based services. The OGC defines different ways to represent geospatial information as bytes of data that can then be interpreted by the GEOMETRY or GEOGRAPHY types as being POINTS, LINESTRINGS, and so on. SQL Server 2008 supports three such formats:

  • Well-Known Text (WKT)

  • Well-Known Binary (WKB)

  • Geography Markup Language (GML)

We stick to WKT examples because they are both concise and somewhat readable. The syntax of WKT is not too difficult to understand, so let’s look at some examples:

  • POINT(10 100)— Here, 10 and 100 represent X and Y values of the point.

  • POINT(10 100 10 1)— This example shows Z and M values in addition to X and Y.

  • LINESTRING(0 0, 10 100)— The first two values represent the starting point, and the last two values represent the end point of the line.

  • POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))— Each pair of numbers represents a point on the edge of the polygon. Note that the end point is the same as the starting point.

Working with Geometry Data

As mentioned previously, the geometry data type is implemented as a common language runtime (CLR) data type in SQL Server and is used to represent data in a Euclidean (flat) coordinate system. The GEOMETRY type is predefined and available in each database. Any variable, parameter, or table column can be declared with the GEOMETRY data type, and you can operate on geometry data in the same manner as you would use other CLR types using the built-in methods to create, validate, and query geometry data.



To assign a value to a column or variable of type GEOMETRY, you must use one of the static methods to parse the representation of the data into the spatial data type. For example, to parse geometry data provided in a valid WKT syntax, you can use the STGeomFromText method:

Declare @geom GEOMETRY
Declare @geom2 GEOMETRY
SET @geom = geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0)
SET @geom2 = geometry::STGeomFromText
                ('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0)


					  

Note

The last parameter passed to the method is the spatial reference ID (SRID) parameter. The SRID is required. SQL Server 2008 does not perform calculations on pieces of spatial information that belong to separate spatial reference systems (for example, if one system uses centimeters and another uses miles, SQL Server simply does not have the means to automatically convert units). For the GEOMETRY type, the default SRID value is 0. The default SRID for GEOGRAPHY is 4326, which maps to the WGS 84 spatial reference system.


If you are declaring a LINESTRING specifically, you can use the STLineFromText static method that accepts only valid LINESTRINGs as input:

Declare @geom GEOMETRY
SET @geom = geometry::STLineFromText('LINESTRING (100 100, 20 180, 180 180)', 0)


					  

The GEOMETRY type, like other SQLCLR UDTs, supports implicit conversion to and from a string. The string format supported by the GEOMETRY type for implicit conversion is WKT. Due to this feature, all the following SET statements are functionally equivalent (the last two SET statements use an implicit SRID of 0):

DECLARE @geom GEOMETRY
SET @geom = geometry::STLineFromText('LINESTRING (100 100, 20 180, 180 180)', 0)
set @geom = Geometry::Parse('LINESTRING (100 100, 20 180, 180 180)')
set @geom = 'LINESTRING (100 100, 20 180, 180 180)'


					  

After defining a GEOMETRY instance, you can use the CLR UDT dot notation to access other properties and methods of the GEOGRAPHY instance. For example, the following code uses the STLength() method to return the length of the LINESTRING:

DECLARE @geom GEOMETRY
SET @geom = geometry::STLineFromText('LINESTRING (100 100, 20 180, 180 180)', 0)

select @geom.STLength() as "Length"
go

Length
----------------------
273.137084989848


					  

The following example uses the STIntersection() method to return the points where two GEOMETRY instances intersect:

DECLARE @geom1 GEOMETRY;
DECLARE @geom2 GEOMETRY;
DECLARE @result GEOMETRY;

SET @geom1 = geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0)
SET @geom2 = geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0
0))', 0)
SELECT @result = @geom1.STIntersection(@geom2);
SELECT @result.STAsText();
go

----------------------------
LINESTRING (50 150, 100 100)


					  

All the preceding examples use local variables in a batch. You also can declare columns in a table with the GEOMETRY type, and you can use the instance properties and methods against the columns as well:

CREATE TABLE #geom_demo
(
    GeomID      INT IDENTITY NOT NULL,
    GeomCol     GEOMETRY
)

INSERT INTO #geom_demo (GeomCol)
VALUES    ('LINESTRING (100 100, 20 180, 180 180)'),
          ('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))'),
          ('POINT(10 10)')

SELECT
    GeomID,
    GeomCol.ToString() AS WKT,
    GeomCol.STLength() AS LENGTH,
    GeomCol.STArea() as Area
FROM #geom_demo

drop table #geom_demo
go

GeomID      WKT                                          LENGTH            Area
----------- -------------------------------------------- ----------------- ------
1           LINESTRING (100 100, 20 180, 180 180)        273.137084989848  0
2           POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))  600               22500
3           POINT (10 10)                                0                 0
Other  
  •  Transact-SQL in SQL Server 2008 : Sparse Columns
  •  SQL Server 2005 : Beyond OWC: Full-On OLAP Development (part 4) - Analysis Services CLR Support: Server-Side ADO MD.NET
  •  SQL Server 2005 : Beyond OWC: Full-On OLAP Development (part 3) - XMLA at Your Service
  •  SQL Server 2005 : Beyond OWC: Full-On OLAP Development (part 2) - OLAP Development with ADO MD.NET
  •  SQL Server 2005 : Beyond OWC: Full-On OLAP Development (part 1) - Management Studio as an MDX Client
  •  Microsoft SQL Server 2008 R2 : Using FILESTREAM Storage (part 2) - Using FILESTREAM Storage for Data Columns
  •  Microsoft SQL Server 2008 R2 : Using FILESTREAM Storage (part 1) - Enabling FILESTREAM Storage
  •  SQL Server 2005 : Using Excel (part 2) - Using PivotTables and Charts in Applications and Web Pages
  •  SQL Server 2005 : Using Excel (part 1) - Working Within Excel
  •  Microsoft SQL Server 2008 R2 : Hierarchyid Data Type (part 2) - Modifying the Hierarchy
  •  
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us
    programming4us
     
     
    programming4us