SQL Server Geometry data type

Original: SQL Server geometry data type

Excerpt from SQL Server 2008 Help

The flat spatial data type geometry is implemented as a common language-time (CLR) data type in SQL Server. This type represents the data in the Euclidean (planar) coordinate system.

Registering the Geometry type

The geometry type is pre-defined and can be used in each database. You can create table columns of type geometry and manipulate geometry data Just as you would with other CLR types.

Example

The following two examples show how to add and query geometry data. The first example creates a table with identity columns and geometry columns GeomCol1 . The third column geometry renders the column as its Open Geospatial Consortium (OGC) well-known text (WKT) representation and uses the STAsText() method. Next, you will insert two rows: One row contains geometry an instance of the type LineString , and one row contains the Polygon instance.

IF object_id (' dbo. Spatialtable ', ' U ') is not NULL     DROP TABLE dbo. spatialtable; Gocreate TABLE spatialtable     (id int IDENTITY (),    GeomCol1 geometry,     GeomCol2 as Geomcol1.stastext ());  Goinsert into Spatialtable (GeomCol1) VALUES (Geometry::stgeomfromtext (' LINESTRING (+ 0) ', + + + +), INSERT Into Spatialtable (GeomCol1) VALUES (Geometry::stgeomfromtext (' POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0) ', 0)); GO

The second example uses the STIntersection() method geometry to return the point at which the two previously inserted instances intersect.

DECLARE @geom1 geometry;declare @geom2 geometry;declare @result geometry; SELECT @geom1 = GeomCol1 from spatialtable WHERE id = 1; SELECT @geom2 = GeomCol1 from spatialtable WHERE id = 2; SELECT @result = @geom1. Stintersection (@geom2); SELECT @result. Stastext ();

Dimension of

Non-empty geometry instances can be 0-, one-, or two-dimensional. 0-D geometries( For example, point and MultiPoint) have no length or area. One-dimensional objects, such as LineString and multilinestring, have a length. Two-dimensional instances, such as Polygon and Multipolygon, have area and length. An empty instance is reported as-1 dimensions, and geometrycollection will report an area based on its content type.

Empty

An "empty"geometry instance does not contain any points. Empty LineString and multilinestring instances have a length of zero. Empty Polygon and Multipolygon instances have an area of 0.

Simple

To make the geometry of an instance "simple", you must meet all of the following two requirements:

Each graphic of an instance cannot intersect itself, except its end point.

Any two graphics of an instance can intersect at a point, except for points on two boundaries.

boundary, internal, and external

The "internal" of an geometry instance refers to the space occupied by the instance, while "external" refers to unoccupied space.

The "Boundary " is defined by the OGC as follows:

• Point and MultiPoint instances have no boundaries.
• The LineString and multilinestring boundaries are formed by the starting and ending points, and the points that have an even number of occurrences are removed.

Envelope Line

The envelope of an geometry instance, also known as the bounding box, is an axis-aligned rectangle formed by the minimum and maximum coordinates (x, y) of the instance.

closed

A closedgeometry instance is a graph with the same starting and ending points. The Polygon instance is closed. The point instance is not closed.

The ring is a simple, closed LineString instance.

Spatial Reference Identifier (SRID)

A spatial reference identifier (SRID) is an identifier that specifies the coordinate system in which the geometry instance resides. Two instances with different SRID are not comparable.

The Geometry data type provides a number of built-in methods that you can use to create new geometry instances based on existing instances.

The Geometry data type provides a number of built-in methods that you can use to determine the relationship between two geometry instances.

All geometry instances have a number of properties that can be retrieved through SQL Server-provided methods. The following topics define the properties and behavior of the geometry type, and define methods for querying each graph.

You can construct an instance of the geometry spatial data type by entering data in well-known text (well-known text,wkt) or well-known binary (well-known BINARY,WKB) format.

Stgeomfromtext (geometry data type)

Returns an geometry instance from the Open Geospatial Consortium (OGC) well-known text (WKT) representation, increasing any Z (elevation) and M (metric) values passed by the instance.

Grammar

Stgeomfromtext ('geometry_tagged_textSRID )

Parameters

Geometry_tagged_text

The WKT representation of the geometry instance that you want to return. Geometry_tagged_text is an nvarchar (max) expression.

SRID

An int expression that represents the spatial reference ID (SRID) of the geometry instance that you want to return.

return type

SQL Server return type:geometry

CLR return type:SqlGeometry

Note

STGeomFromText()The OGC type of the returned geometry instance is set to the appropriate WKT input.

If the input format is incorrect, this method throws FormatException.

Example

The following example uses the STGeomeFromText() create geometry instance.

DECLARE @g geometry; SET @g = Geometry::stgeomfromtext (' LINESTRING (100 100, 20 180, 180 180) ', 0); SELECT @g.tostring ();

SQL Server Geometry data type