70-461 – spatial aggregates – geometry

Old_mapThe Exam 70-461: Querying Microsoft SQL Server 2012 syllabus has the following under the ‘Work with Data’ section:

Implement aggregate queries

New analytic functions; grouping sets; spatial aggregates; apply ranking functions

The highlighted bit it what I’m looking at here

Insert and select with geometry

So, first create a table with a column with the datatype of geography:

create table GeometricalStuff (GeometricalData geometry)
  

To insert a single point or a line you do this:

insert into GeometricalStuff (GeometricalData)
values ('point (.5 .5)')

insert into GeometricalStuff (GeometricalData)
values ('linestring (1 1, 3 7)')
 

This inserts data which looks like this in the default view:

select GeometricalData from GeometricalStuff
0x00000000010C000000000000E03F000000000000E03F
0x000000000114000000000000F03F000000000000F03F00000000000008400000000000001C40

….but becomes more interesting when you select the ‘Spatial Result’ tab in SSMS
tabs

….because you get this:
Line

Drawing a triangle with sql geometry

I tried doing a triangle

insert into GeometricalStuff (GeometricalData)
values ('triangle (1 2, 3 8, 5 5)')

but ‘triangle’ isn’t a geometry keyword:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":
System.FormatException: 24114: The label triangle (1 2, 3 8, in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION, CIRCULARSTRING, COMPOUNDCURVE, CURVEPOLYGON and FULLGLOBE (geography Data Type only).
System.FormatException:
   at Microsoft.SqlServer.Types.OpenGisTypes.ParseLabel(String input)
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
   at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometry.Parse(SqlString s)
.
The statement has been terminated.

The keywords are:

Simple ones:

  • Point
  • LineString
  • CircularString
  • CompoundCurve
  • Polygon
  • CurvePolygon

Collections:

So to do a triangle, you use the polygon keyword

insert into GeometricalStuff (GeometricalData)
values ('polygon((1 1, 3 7, 5 5, 1 1))')

which gives you:

select * from GeometricalStuff
0x00000000010404000000000000000000F03F000000000000F03F00000000000008400000000000001C4000000000000014400000000000001440000000000000F03F000000000000F03F01000000020000000001000000FFFFFFFF0000000003

which, in a prettier format is:
triangle

Closing the loop – Msg 6522

Something I found when I tried it first was that if you just specify the three points of the triangle like this:

insert into GeometricalStuff (GeometricalData)
values ('polygon((1 1, 3 7, 5 5))')

…it fails with:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":
System.FormatException: 24118: The Polygon input is not valid because the exterior ring does not have enough points. Each ring of a polygon must contain at least four points.
System.FormatException:
   at Microsoft.SqlServer.Types.GeometryValidator.ValidatePolygonRing(Int32 iRing, Int32 cPoints, Double firstX, Double firstY, Double lastX, Double lastY)
   at Microsoft.SqlServer.Types.Validator.Execute(Transition transition)
   at Microsoft.SqlServer.Types.ForwardingGeoDataSink.EndFigure()
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParseLineStringText()
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePolygonText()
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
   at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometry.Parse(SqlString s)
.
The statement has been terminated.

This is because you have to close the polygon. i.e the last point of the polygon has to be a repetition of the first one. So, this fails:

insert into GeometricalStuff (GeometricalData)
values ('polygon((1 1, 3 7, 5 5))')

….but this works

insert into GeometricalStuff (GeometricalData)
values ('polygon((1 1, 3 7, 5 5, 1 1))')

Geometry Aggregate Functions

So far all the above has just been mucking about with spatial datatypes – specifically geometric spatial datatypes. The aggregate function do stuff with the data (although I can’t really see that all of the functions are necessarily about aggregating things, in the normal sense of the word)

An easy-ish one is the ‘envelope’ function:

select Geometry::EnvelopeAggregate(GeometricalData) from GeometricalStuff

…which takes the triangle we prepared earlier

triangle

…and ‘envelopes‘ it in a rectangle shape:

envelope

select

Note you can’t do:

select Geometry::EnvelopeAggregate(*) from GeometricalStuff

….because you’d just get this error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '*'.
Advertisements