SQL Server 2008 Spatial - How many ways to make your point?
The spatial functionality is hugely standards based.
There spatial standards are well established what this means is that there are
many methods to support the standards. One of the areas that you will see this
is the creation of spatial data.
To create an instance of a spatial type you have many many options, most use
the methods on the base types, i.e. geometry::STGeomFromText.
This is the set of ways of creating a point,
declare @p
geometry;
set @p = geometry::Point(10,10,0);
set @p = geometry::STGeomFromText('POINT(10 10)',0);
set @p = geometry::STPointFromText('POINT(10 10)',0);
set @p =
geometry::STGeomFromWKB(0x010100000000000000000024400000000000002440,0);
set @p = geometry::STPointFromWKB(0x010100000000000000000024400000000000002440,0);
set @p = geometry::GeomFromGml('<Point
xmlns="http://www.opengis.net/gml"><pos>10
10</pos></Point>',0);
set @p = geometry::Parse('POINT(10 10)');
set @p = 'POINT(10 10)';
select
@p
Only the point sub type has its own specific method geometry::Point. This I
guess is because the attributes are well defined. You have one X, one Y and a
SRID (Spatial Reference ID), where as the other types you have many Xs and many
Ys.
Let me explain a few of these.
The STGeom... and STPoint... are the same expect the later verifies that the
geometry you pass in is a Point.
The ..Text methods use the Well-Known Text (WKT) format as input and the
...WKB use the Well-Known Binary (WKB) format. You can use the STAsText method
to get the text (WKT) representation of a variable and STAsBinary to find the
binary (WKB) represenation
The GeomFromGml is an extension to the methods specified in the OGC and uses
the Geography Markup Language (A subset for SQL Server) as input. You can use
the AsGml method to get the GML representation . I would personally avoid GML
representations due to the bulkiness of XML and overhead or associated
processing.
Note: In BOL the method is GeomFromGML. This is incorrect only
GeomFromGml works (notice the case of GML) this is consistent with the AsGml
method, but contrary to the WKB methods. Who knows what CTP6 or RTM will
bring.
You will undoubtedly see most examples using the text based methods because
they are more readable. However on on doing some testing it seems the binary
methods are quicker (in some tests twice a quick). This makes some sense as each
point coordinate can be read as a single entity (8 byte float) where as the text
representation has to be read character by character and then creating the value
once a boundary (space, comma or bracket) has been reached.
It is also easier to create binary values in TSQL
geometry::STPolyFromWKB(0x00+0x00000003+0x00000001 +
0x00000005
+ cast(@minx as binary(8)) + cast(@miny as binary(8))
+ cast(@maxx as binary(8)) + cast(@miny as binary(8))
+ cast(@maxx as binary(8)) + cast(@maxy as binary(8))
+ cast(@minx as binary(8)) + cast(@maxy as binary(8))
+ cast(@minx as binary(8)) + cast(@miny as binary(8)) ,0)
This creates a simple 4 sided polygon (aka a square). Key thing to note is
the first two bytes that specify the byte order which for TSQL binary functions
you want 0 and not 1 (most examples use 1.
-