The slide deck used for my presentations to the
There is also a set of demo code to go along with the slide deck variants.
The following blog posts may also be useful:
The spatial data can be added to a table by specifying the column type of geometry or geography. The exact detail of what is in the column can be varied as a spatial column can represent a point, line string, and polygon and so on. For example, to create a table that represents the venues of developer events that I’ve been to might look like this:
CREATE TABLE Venue
(
Id INT IDENTITY(1,1) NOT NULL,
Name NVARCHAR(256),
Location geography
)
There are a number of different ways to insert data. Points, have the most varied set of options.
First of all there is the standard STGeomFromText:
INSERT INTO Venue
VALUES(
'HBOS',
geography::STGeomFromText(
'POINT(55.9271035250276 -3.29431266523898)',4326));
The function takes two parameters; the first is the Well Known Text (WKT) representation of the geometry, in this case a point, and the second is the SRID. The example above shows the location of the SQL Server UG events held in one of the conference rooms at HBOS’s offices in Sighthill, Edinburgh.
Next is the extended method Parse. I should mention that there are two types of methods with regards to standards. There are a group of methods that comply with the OGC standards (these are all prefixed with ST). Then there are “extended methods”. These are not standards compliant and have added, I’m guessing, in order to improve the capabilities to some extent over the standards.
An example of the Parse method:
INSERT INTO Venue
VALUES(
'Glasgow Caledonian University',
geography::Parse(
'POINT(55.8659449685365 -4.25072511658072)'));
The function takes only one parameter, which is the WKT. There is no SRID, but it is set to 4326 (WGS84). The example above shows the location of the Scottish Developers events held in the Continuing Professional Development Centre in Glasgow Caledonian University.
The next method is to use Well Known Binary (WKB). I won’t, however, be detailing the format of the binary. At present I would simply like to demonstrate that it can be done.
An example of WKB:
INSERT INTO Venue
VALUES(
'Dundee University',
geography::STGeomFromWKB(0x01010000000700ECFAD03A4C4001008000B5DF07C0, 4326));
The function, like its WKT counterpart, takes two parameters. The first is the binary representation of the spatial data, while the second is the SRID. The example above is the location of the North East Scotland .NET User Group who meet at Dundee University.
Next is another extended method, Point. For example:
INSERT INTO Venue
VALUES(
'Microsoft Campus, TVP',
geography::Point(51.4618933852762, -0.926690306514502, 4326));
The function takes three parameters, the latitude, the longitude and the SRID. The above example is the location of the Microsoft Campus at Thames Valley Park in Reading where events like DDD are held.
Finally, the standard function, STPointFromText, is used. For example:
INSERT INTO Venue
VALUES(
'Microsoft Edinburgh Office',
geography::STPointFromText('POINT(55.9523783996701 -3.2051030639559)', 4326));
The function takes WKT as did Parse and STGeomFromText, however, it is constrained to only WKT that represent points. If the WKT represents something else the method will fail. If, say, a linestring was supplied then an error message would be generated such as “Expected POINT at position 1. The input has LINES.” The example above shows the location of Microsoft’s Edinburgh office.
The result of adding all this information will produce a table with the following data:
| Id | Name | Location |
| 1 | HBOS | POINT (55.9271035250276 -3.29431266523898) |
| 2 | Glasgow Caledonian University | POINT (55.8659449685365 -4.25072511658072) |
| 3 | Dundee University | POINT (56.4595025684685 -2.98423195257783) |
| 4 | Microsoft Campus, TVP | POINT (51.4618933852762 -0.926690306514502) |
| 5 | Microsoft Edinburgh Office | POINT (55.9523783996701 -3.2051030639559) |
In SQL Server 2008, each piece of spatial data must be tagged with an SRID (Spatial Reference Identifier). Geometry types can have a SRID of 0 (which means undefined) but geographies must have a defined SRID. By default geography types use an SRID of 4326 which equates to WGS84. Spatial operations can only occur between spatial types with the same SRID. The result of spatial operations between two pieces of data with different SRIDs is null.
The geography needs an SRID applied to it because, in order to perform calculations, it needs to know the details of the ellipsoid in use. That information is not required to perform calculations on a geometry type.
Although SRIDs are not required on geometry objects it may be useful to apply them if data using different projections is to coexist in the same database. It will provide the safety net of returning null if spatial operations are attempted between two geometries in different projections.
It is possible to find out the available SRIDs in the database by querying the system view sys.spatial_reference_systems. The view will detail the SRID (spatial_reference_id) and its attributes.
Currently, all SRIDs in the system are defined by the European Petroleum Survey Group, hence the value of the Authority (authority_name) column is “EPSG”. The WKT (well_known_text) describes the datum, ellipsoid and units of the geographic coordinate system. The Units (unit_of_measure) column describes in English the units of the projected coordinate system. Finally, the Factor (unit_conversion_factor) is the conversion factor between the units in the projected coordinate system to SI units.
For example:
| SRID | Authority | WKT | Units | Factor |
| 4157 | EPSG | GEOGCS[“Mount Dillon”, DATUM[“Mount Dillon”, ELLIPSOID[“Clarke 1858”, 6378293.64520876, 294.260676369261]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]] | Clarke’s Foot | 0.304797265 |
| 4243 | EPSG | GEOGCS[“Kalianpur 1880”, DATUM[“Kalianpur 1880”, ELLIPSOID[“Everest (1830 Definition)”, 6377299.36559538, 300.8017]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]] | Indian Foot | 0.304799518 |
| 4268 | EPSG | GEOGCS[“NAD27 Michigan”, DATUM[“NAD Michigan”, ELLIPSOID[“Clarke 1866 Michigan”, 6378450.0475489, 294.978697164674]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]] | US Survey Foot | 0.30480061 |
| 4277 | EPSG | GEOGCS[“OSGB 1936”, DATUM[“OSGB 1936”, ELLIPSOID[“Airy 1830”, 6377563.396, 299.3249646]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]] | metre | 1 |
| 4293 | EPSG | GEOGCS[“Schwarzeck”, DATUM[“Schwarzeck”, ELLIPSOID[“Bessel Namibia (GLM)”, 6377483.86528042, 299.1528128]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]] | German legal metre | 1.000013597 |
| 4326 | EPSG | GEOGCS[“WGS 84”, DATUM[“World Geodetic System 1984”, ELLIPSOID[“WGS 84”, 6378137, 298.257223563]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]] | metre | 1 |
| 4748 | EPSG | GEOGCS[“Vanua Levu 1915”, DATUM[“Vanua Levu 1915”, ELLIPSOID[“Clarke 1880 (international foot)”, 6378306.3696, 293.46630765563]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]] | foot | 0.3048 |
For more information about WKT, Wikipedia has a good overview and acts as a jumping off point to more information.
CODE EXAMPLES IN THIS POST WORK WITH THE NOVEMBER 2007 CTP (CTP 5) OF SQL SERVER 2008.
Say you would like to create a polygon out of a group of points. One way of doing this is to union the points together then create a convex hull from those points. A convex hull is a polygon that contains all the points of the geometries that it is made from. “The convex hull may be easily visualized by imagining an elastic band stretched open to encompass the given object; when released, it will assume the shape of the required convex hull.” [Wikipedia:Convex Hull]
It is possible to create a convex hull from just two points, however in this case you will end up with a linestring rather than a polygon because a polygon requires a minimum of 3 points.
DECLARE @a geometry
DECLARE @b geometry
SELECT @a = geometry::STGeomFromText('POINT(0 0)',0),
@b = geometry::STGeomFromText('POINT(10 10)', 0);
SELECT @a.STUnion(@b).STConvexHull().ToString();
Results in: LINESTRING (10 10, 0 0)
With an additional point a polygon can be created.
DECLARE @a geometry
DECLARE @b geometry
DECLARE @c geometry
SELECT @a = geometry::STGeomFromText('POINT(0 0)',0),
@b = geometry::STGeomFromText('POINT(10 10)', 0),
@c = geometry::STGeomFromText('POINT(20 0)', 0);
SELECT @a.STUnion(@b).STUnion(@c).STConvexHull().ToString();
Results in: POLYGON ((20 0, 10 10, 0 0, 20 0))
What you’ll notice is that the polygon has 4 points, but we only gave 3 to start with. That is because the first and last point in the polygon are the same.
If you were to look at the geometry that had been created with just the union operations before the convex hull was made then you’ll see it is a MultiPoint: MULTIPOINT ((10 10), (20 0), (0 0))
Unioning different types of geometry together, such as a point, linestring and polygon (see figure on the right) will, if the geometries don’t overlap, result in a GeometryCollection. For instance the code:
DECLARE @a geometry
DECLARE @b geometry
DECLARE @c geometry
SELECT @a = geometry::STGeomFromText(
'POLYGON ((25 5, 15 15, 5 5, 25 5))',0),
@b = geometry::STGeomFromText(
'POINT(5 10)', 0),
@c = geometry::STGeomFromText(
'LINESTRING(20 20, 30 5)', 0);
SELECT @a.STUnion(@b).STUnion(@c).ToString();
Will result in the following: GEOMETRYCOLLECTION (POINT (5 10), LINESTRING (20 20, 30 5), POLYGON ((5 5, 25 5, 15 15, 5 5)))
Moving the point to a position within the polygon, such as POINT(15 10) will result in a geometry collection that does not contain a separate point. As the point is within the boundary of the polygon it does not need to be separately listed in the geometry collection. The actual geometry looks like this: GEOMETRYCOLLECTION (LINESTRING (20 20, 30 5), POLYGON ((5 5, 25 5, 15 15, 5 5)))
Moving the linestring to travel from 5,10 to 30,10 (through the polygon) results in a geometry collection with two linestrings (see figure on the left). One that runs from 5,10 to the boundary of the polygon at 10,10 and the second that runs from the boundary of the polygon at 20,10 to the original end point at 30,10. The resulting MultiGeometry looks like this: GEOMETRYCOLLECTION (LINESTRING (30 10, 20 10), LINESTRING (10 10, 5 10), POLYGON ((5 5, 25 5, 20 10, 15 15, 10 10, 5 5)))
DECLARE @a geometry
DECLARE @b geometry
DECLARE @c geometry
SELECT @a = geometry::STGeomFromText(
'POLYGON ((25 5, 15 15, 5 5, 25 5))',0),
@b = geometry::STGeomFromText(
'POINT(15 10)', 0),
@c = geometry::STGeomFromText(
'LINESTRING(5 10, 30 10)', 0);
SELECT @a.STUnion(@b).STUnion(@c).ToString();
Other posts in this series:
THIS POST REFERS TO THE NOVEMBER 2007 CTP (CTP 5) OF SQL SERVER 2008
Following from my previous posts (Getting started with Spatial Data in SQL Server 2008, Spatial Data in a .NET application) on the new spatial features of SQL Server 2008 I’ve been looking at how to get spatial data into SQL Server from a .NET application. This has not been as easy as expected.
I suspect I just have not found the right piece of documentation because my eventual solution isn’t one I’m entirely happy with.
I was unable to add a SqlGeometry as a parameter to the collection of parameters on the SqlCommand object in my .NET application. The SqlGeometry does not appear in the enumeration for SQL Server data types. My first thought was to put the data through as binary as I vaguely remember reading something about using binary in something I read recently, but I couldn’t quite remember where. However, when I created the geometry object in .NET then used .STAsBinary() on it so the parameter object would accept it the INSERT failed. The exception message was:
A .NET Framework error occurred during execution of user defined routine or aggregate 'geometry': System.FormatException: One of the identified items was in an invalid format. System.FormatException: at Microsoft.SqlServer.Types.GeometryData.Read(BinaryReader r) at Microsoft.SqlServer.Types.SqlGeometry.Read(BinaryReader r) at SqlGeometry::.DeserializeValidate(IntPtr , Int32 ) . The statement has been terminated.
Yes, that was the MESSAGE from the exception. The stack trace above comes from within SQL Server itself. There is a separate stack track for my application. (I’m guessing if you are familiar with CLR stored procedures you may have seen this sort of thing before. I’ve not used the CLR code in SQL Server before, so it is a new experience)
The solution I found was to mark the parameter as an NVarChar, skip the creation of an SqlGeometry object and use a string containing the WKT (Well Known Text) representation. For example:
cmd.Parameters.Add("@Point", SqlDbType.NVarChar) = "POINT (312500 791500)";
I mentioned earlier that I’m not all that happy with this solution. That is because if I already have an SqlGeometry object I don’t want to have to convert it to a human readable format and have SQL Server parse it back again. Human Readable formats tend not to be the most efficient way of representing data and the geometry type already has a more efficient (as I understand it) format. Although in this case I bypassed the creation of an SqlGeometry object in my code, I can foresee cases where I might have created an SqlGeometry object through various operations and would have produced a fairly complex object. In those cases I wouldn’t want the additional overhead formatting it into WKT, passing it over the network and parsing it on the other side.
If I find a better solution I will post it.
If you have ever been to an agile coding session you may have come across the concept of the coding kata. It is an exercise designed to improve coding skill by making you more aware of the different ways of building the same solution. It also tends to lend itself extremely well to TDD.
I was just looking at ways of converting OS National Grid References from their alphanumeric form to a purely numeric form and it occurred to me that it might make an excellent project for a coding kata.
So, what’s the deal with OS National Grid References. Well, they consist of two letters followed by a number of digits. For example NT2474. NT relates to a square 100km along each side. The first two digits represent eastings within that square, and the second two represent northings within the square. The complete reference gives you a square that is one kilometre along each side. Of course, you can modify this to produce larger or smaller squares. NT, NT27, NT245746. As The actual coordinate the grid reference resolves to is the south west corner of the square. Also, there are optional spaces between parts, so NT245746 could be written as NT 245 746.
There is a more detailed guide to the national grid on the Ordnance Survey website.
THIS POST REFERS TO THE NOVEMBER 2007 CTP (CTP 5) OF SQL SERVER 2008
Following from my last post about Getting Started with Spatial Data in SQL Server 2008 in the database, how about actually using it in an application? Since that is where most data will end up at some point, it must be possible to use the spatial data in an application some how.
So, using the Town table created before and populated with our fictional village of Pitcardine I wrote this very simple program to see what actually came through ADO.NET to the end application.
static void Main(string[] args)
{
SqlConnection connection =
new SqlConnection("Server=(local);"+
"database=SpatialSandbox;Trusted_Connection=Yes;");
SqlCommand command =
new SqlCommand("SELECT * FROM Town", connection);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
object value = reader.GetValue(i);
Console.WriteLine("{0} ({1}) = {2}",
reader.GetName(i), value.GetType(), value);
}
}
}
Console.ReadLine();
}
The result was:
TownId (System.Int32) = 1 Name (System.String) = Pitcardine TownGeom (Microsoft.SqlServer.Types.SqlGeometry) = POLYGON ((0 0, 370 0, 370 160 , 200 250, 0 250, 0 0)) TownGeomText (System.String) = POLYGON ((0 0, 370 0, 370 160, 200 250, 0 250, 0 0))
At this point I’m looking around to find the assembly that I can reference in my .NET application. After some looking I found it in C:MSSQLMSSQL10.MSSQLSERVERMSSQLBinn however that directory is not guaranteed as it depends on your SQL Server installation. It would be nice if the assembly showed up in the .NET tab in the Add Reference dialog rather than require the developer to hunt it down. (I’m guessing that this is due to the CTP nature of the product I’m currently using)
Once the column value can be accessed as an SqlGeometry object in the code it becomes possible to query the object. However, the results are pretty much all SQL data types. For example:
SqlGeometry geometry = (SqlGeometry)reader.GetValue(2); SqlDouble sqlArea = geometry.STArea(); double area = sqlArea.Value; // could also cast to double
Note that STArea() is a method, not a property as you might expect in this sort of scenario. I suspect that each time it is called the area is worked out which may be an expensive operation hence keeping the method syntax.
