I’ve previously posted several articles about spatial data support in SQL Server, and as I continue my exploration of SQL Server “Denali” CTP1, I’ve encountered a few interesting new enhancements to the geometry and geography data types. I won’t go into full details here, because Ed Katibah and Milan Stojic have already saved me the trouble by writing a whitepaper that provides comprehensive round-up of the changes in this release; however, I do want to make a few observations relating to the new enhanced support for curved lines and shapes.
SQL Server “Denali” introduces a few new spatial shapes, including CIRCULARSTRING, COMPOUNDCURVE, and CURVEPOLYGON. A CIRCULARSTRING line is a sequence of an odd number of at least three points, which are connected to form a curved arc. For example, consider the following Transact-SQL:
DECLARE @g geography = 'CIRCULARSTRING(-4.115 55.778, -3.399 56.990, -2.237 54.009)'
This creates a geography instance that represents a curved line like this:
Compare this to the line produced by using the LINESTRING shape, as shown here:
DECLARE @g geography = 'LINESTRING(-4.115 55.778, -3.399 56.990, -2.237 54.009)'
Of course, both of these lines are “open”. You can create a closed CIRCULARSTRING line by defining at least five points and making the final point in the line the same as the first, as shown here:
DECLARE @g geography = 'LINESTRING(-4.115 55.778, -3.399 56.990, -2.237 54.009, -3.168 53.863, -4.115 55.778)'
A COMPOUNDCURVE is a curved shape that is composed of one or more CIRCULARSTRING arcs and linear sections that are contiguously joined by having the final point in each segment the same as the first point in the next segment. For example, the following Transact-SQL creates a COMPOUNDCURVE shape from two CIRCULARSTRING arcs and a linear section. Note that you do not specify a keyword for the linear sections.
DECLARE @g geography = 'COMPOUNDCURVE(
CIRCULARSTRING(-4.000 55.000, -4.500 54.500, -4.000 54.000),
(-4.000 54.000, 1.000 54.000),
CIRCULARSTRING(1.000 54.000, 1.500 54.500, 1.000 55.000))'
A CURVEPOLYGON is a surface area that is formed by a closed curved line, which can be defined by a CIRCULARSTRING or a COMPOUNDCURVE. When working with the geometry data type, the points in the curved area can be defined in any order, but when using he geography type, you must observe the “left foot rule”, which dictates that you must describe the shape as if you were pacing it out on the ground and the “inside” of the shape is always on your left. For example, here’s a Transact-SQL statement that defines a CURVEPOLYGON based on a CIRCULARSTRING:
SELECT geography::Parse('CURVEPOLYGON(
CIRCULARSTRING(-4.889 55.844,
-3.924 55.738,
-2.731 56.058,
-4.201 56.134,
-4.889 55.844)
)')
This defines an area in the UK within the so-called “central belt” of Scotland, as shown here. Note that the points describe the shaded area, which is what would be on your left if you paced out the area from point to point in the sequence in which they are specified.
Now, what happens if I reverse the order of the points as shown here?
SELECT geography::Parse('CURVEPOLYGON(
CIRCULARSTRING(-4.889 55.844,
-4.201 56.134,
-2.731 56.058,
-3.924 55.738,
-4.889 55.844)
)')
The “left foot rule” clearly tells SQL Server to include everything on my left side as I pace out the shape, and since the Earth is a sphere, this shape actually describes the entire surface of the planet except for the “hole” defined by the points in the CIRCULARSTRING.
In previous releases of SQL Server, this would have caused an error because shapes larger than a hemisphere were not supported. However, in SQL Server “Denali”, you can create a shape that covers as much of the surface of the Earth as you like, so this code is perfectly valid. Clearly, the message here is that you need to be very careful when defining surface areas (be they CURVEPOLYGON or regular POLYGON shapes) to apply the “left foot rule” to include the surface area you actually intend to, and not the rest of the world!
Speaking of which, one other new feature worth mentioning is the inclusion of a FULLGLOBE shape, which returns a surface area that covers, you guessed it, the full globe. For example, the following code returns the area of the planet’s surface in square kilometres:
DECLARE @theEarth geography = geography::STGeomFromText('FULLGLOBE', 4326)
SELECT @theEarth.STArea()/1000000
The Spatial Reference Identifier (SRID) 4326 specifies that the WGS84 standard model of the earth’s shape should be used, which means that the result of the call to the STArea method is returned in square metres, which we then divide by 1000000 to get the answer in square kilometres.
And just in case you’re interested, the Earth’s surface is just a little over 510,065,621 km2!
No comments:
Post a Comment