Where is the STContains function for the Geography type. I don't know but its not in Sql Server 2008.

As a workaround you can use STIntersection and compare the results with the geography you are comparing with i..e

declare @g geography = 'POLYGON((0 0,0 10,0 20,0 30, 0 40, 50 40,50 30 ,50 20, 50 10, 50 0, 0 0))'

declare @l geography = geography::Parse('LINESTRING(20 0,20 10,20 20)')

select @g.STIntersection(@l).ToString(), @g.STIntersection(@l).STEquals(@l);

This returns 1 if the LINESTRING  is within the POLYGON

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SQL Server 2008 Spatial - STContains for Geography type

Venturing into the world of LINQ I'm determined to give it a go because writing 3 -10 sps per table is just a pain in the bottom.

If you come from a database background one of the things that will concern you is the SQL that LINQ generates. The good news is that you can capture all the SQL that LINQ generates by using the Log property of you data context. The following sets the PubsDataContext to log to the file c:\log.txt

TextWriter tw = new StreamWriter("c:\\log.txt");

What I noticed straight away was updates, by default when an update is performed, irrespective of what columns are changed, all the columns are specified in the WHERE clause. This is used to enforce optimistice locking. This is to  ensure that when you update data it hasn't already been updated by someone else. The downside however is that every column has to be checked.

You can change this behaviour on each column in the LINQ to SQL model that is generated by adding the UpdateCheck attribute i.e.

[Column(Storage="_col2", DbType="Int", UpdateCheck=UpdateCheck.Never)]

However its a bit of a pain to do that on each column in each table. However there is light at the end of the tunnel. If you have a timestamp on the table only this column will be check to verify the record has't changed.

You might be still asking why I want this at all. Imagine your bank account (£100 balance), you've cash a cheque £50 and you use the cash machine to withdraw £100, what if it happens that the bank pays the cheque into your account at the same time you use the cash point, both  read the balance at £100, the chequed is cashed updating it to £150, but then the cash point withdrawl updates the £100 balance with the £100 withdrawl leaving you with a balance of £0. What happened to the cheque?

If the each transaction checked that the record had not been updated then it would have realised that the data had changed and the update would have failed.

The other option is pessimistic locking, which puts a lock on a record for the duration that the record is being read until it is updated. This is not very good for concurrency as locks cause blocking and so the number of users you application will be able to support will not be very high.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/LINQ to SQL - Optimistic locking

Table Valued Parameters have to be one of the coolest features in SQL Server 2008. With them you are able to pass a set of data around as a single variable. What this means is that if you are saving multiple records you can do very easily in one procedure call.

You might say well I can do that already using an XML column or a delimited list, Well the difference with a Table Valued Parameter is that you get a typed rowset which you can use directly in an INSERT, SELECT, UPDATE statement, and anyother statement that can take a rowset. This means you don't have to shred your XML or have a function to split your delimited string.

create table myTable (col1 int, col2 datetime, col3 varchar(max))

go

create type myTableType as table (col1 int, col2 datetime, col3 varchar(max))

go

create procedure up_myTable_insertFromTable

  @myTableData myTableType readonly

as

  insert into myTable (col1, col2, col3 )

  select col1, col2, col3

    from @myTableData

go

declare @myTable myTableType

insert into @myTable values(1, getdate(), 'Table Valued Parameters are great')

exec up_myTable_insertFromTable @myTable

Its very easy to use.

There are a few gotchas to be aware of,

The stored procedure parameter has to be READONLY. This means that you cannot modify it within the stored procedure. You can only modify it in the scope it was created. i.e. If you created a table type variable in your procedure you can modify the data in it, however if you pass that to another procedure that other procedure cannot modify it as it wasn't create in the scope of that procedure.

A table valued parameter ALWAYS has a default of an empty table. This is not inline with all other types btu has been done for performance reasons. What this means is that in the previous code the following will work

exec up_myTable_insertFromTable

Even though you haven't passed in a value for the @myTableData parameter.

No statistics are created on a TVP. This is inline with table variables which means if you are doing selects with where clauses you have to be careful, as you may end up with table scans.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SQL Server 2008 TSQL - Table Valued Parameters

One of the biggest challenges in understanding spatial data is visualising shapes. In 2D it isn't two bad, and I have started the SpatialViewer project on codeplex (http://www.codeplex.com/SpatialViewer)

However visualising Geography types requires a globe.Virtual Earth can come to the rescue. I have had ago at visualising shapes on Virtual Earth

Due to the need to have lots of javascript the sample is hosted on the SQLBits site http://www.sqlbits.com/HowTo/Spatial/ViewingShapesOnVirtualEarth.aspx 

You should be able to draw a shape on the virtual earth map and get the points in the text box below. The points are in WKT format without the geometry shape and brackets. You can also put in a set of points and add the shape.

I suggest you go to 3D mode (if you have t installed) to see how shapes are represented in virtual earth. This is not the same as the way they are represented in Sql Server. Sql Server uses shortest distance whereas VE maps
lines on a planar projection not allowing for the curve of the earth.

Draw a line from London to New York and then switch to 3D mode. The line should loop up towards the north pole, but it doesn't. It goes straight across the atlantic.

I find I get a few javascript errors, I am trying to iron these out but you should still be able to use it.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Viewing Geometries on Virtual Earth

You think this an odd title, a square is a square. Well whilst that is the case in a planar 2D world in a 3 spatial world where the square is drawn on a sphere.

This is one of the areas that catches most people out.

If you define a polygon  with corners at (0,0) (0,50) (90,50) (90, 0). So this starts on the equator at Greenwich and then moves north until the 50 longitude, then moves to a point a quarter round the world again on longitude 50 and back to the equator on latitude 90.

On first glance you would think that the line from (0,50) to (90,50) follows ring that defines longitude 50, and this is the error. The line between these two points is actually the shortest distance between, which is part of  great circle. Morten has done a great blogpost on this Straight lines on a sphere .

What is a shame is that both Mortens Shape to Shapefile to SqlServer 2008 to WPF and Virtual earth both draw the polygon as though it followed the ring of longitude. What this leads to is confusion over when shapes intersect

If you are likely to be working with Spatial data covering the earth I suggest you buy a globe to enable you to visualise the world.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SQL Server 2008 Spatial - What shape is a square?

How do you create a circle in a planar system. Ok so there are some funky algorithms for creating a set of points all the way round the circle.

Well there is a much easier way.

Create a point i.e. POINT (10 10) and the use the STBuffer(radius) method to get a circle whose centre is at the point and with the radius specified, i.e.

DECLARE @g geometry;

SET @g = geometry::STGeomFromText('POINT(5 5)', 0);

SELECT @g.STBuffer(1).ToString();

Creates a polygon with 129 points aaaaaagggghhhhhh. You can reduce the number of points by using the Reduce method. Reduce with a value of 0.05 results in a 17 point polygon which is a fairly accurate circle

You can achieve this in one step using the BufferWithTolerance method. To get a 17 point polygon you specify a tolerance of 0.1.

POLYGON ((5 4, 5.38924515247345 4.0785849690437317, 5.70710676908493 4.29289323091507, 5.9214150309562683 4.61075484752655, 6 5, 5.9214150309562683 5.38924515247345, 5.70710676908493 5.70710676908493, 5.38924515247345 5.9214150309562683, 5 6, 4.61075484752655 5.9214150309562683, 4.29289323091507 5.70710676908493, 4.0785849690437317 5.38924515247345, 4 5, 4.0785849690437317 4.61075484752655, 4.29289323091507 4.29289323091507, 4.61075484752655 4.0785849690437317, 5 4))

If you want to see what this looks like try the SpatialViewer on codeplex http://www.codeplex.com/SpatialViewer

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SQL Server 2008 Spatial - How to create a circle?

I recently blogged about the performance of passing spatial data to the database.(SQL Server 2008 Spatial - Performance of database calls? )

In it I found that the the performance radically dropped off as the number of points increases.

Whilst this was suprising I had used my usual approach of eliminaing other factors and running the tests many times and so assume the results correct. Following feeback from Steven Hemingray I did some more investigation.

Steven had said that the time was likely due to the validation process that has to be done when the geometry instance is created. What I realised was that the code I used to generate the geometries used totally random points. This meant the shapes were extremely complex when determining if the shape is valid.

I change the logic to produce less complex shapes and the difference was dramatic.I have a wavy line with no intersections and another shape that is a bit like drunk spider with intersections.

The performance is much more linear and also suprisingly shows that the for uncomplex shapes the WKB format is better than passing the base udt. However as the number of intersections goes up the udt starts to perform better

This maybe explained by the fact that the base udt is very complex. It stores the Z and M values. With the WKB format these are not included.

It is also worth noting that for small shapes < 100 points it seems WKB is always quicker and if you need Z and M values WKT is quicker than the base udt.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SQL Server 2008 Spatial - Performance of database calls follow up

Danny Gould of SQLInternalsViewer fame (which you can download here) has started blogging on http://sqlblogcasts.com. You can catch up on his posts here http://sqlblogcasts.com/blogs/danny/archive/2008/01/03/hello.aspx.

He's started off with an interesting post about automatically shredding xml into a table. FOR XML and back again

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Exciting new blogger

There are 4 ways of holding spatial data, Well Known Text (WKT), Well Known Binary (WKB), Geography Markup Language and the base spatial type (SqlGeometry or SqlGeometry).

This leads me on to the question how should you pass the data to SQL Server.

I've heard bad things about passing udts and so expected the WKB format to be the best, based on the my previous post (SQL Server 2008 Spatial - Which is faster WKT or WKB? )

So I ran my test, I generated a spatial instance, and then created 3 procedures one for WKB, one for WKT and one for the base udt type. (I've ignored the GML becuase it will be similar if not worse than the WKT .

As expected the WKB wins out, over the WKB and the base udt instance, Initially.

I was shocked to find that after your geometry hits ~50 points, assing the udt instance is quicker than passing the WKT or WKB. In fact the performance of passing the base instance is a fairly straight line of performance with only a slight drop off, probably due to the size of the data rather than anything else. However for the WKT and WKB the performance falls through the floor, almost exponetially.

The green line is passing the spatial variable as is using the SQLDbTypes.Udt, the Blue line is the WKB and the Red is the WKT. There are no numbers on this for a reason. suffice to say that the udt method vaires from about 3ms to about 6 ms. The last reading is for a geometry with 900 points. For the WKB type it took 1s (not second and not millisecond) to parse

Steven Hemingray has provided an explanation on the forums here Shocking performance for parsing geometries in SQL  saying it is related to the validating of the data.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SQL Server 2008 Spatial - Performance of database calls?

One of the methods that the SQL Server 2008 Spatial types support is STDistance. This returns the distance between two geometries.

declare @p1 geometry = 'POINT (0 10)';

declare @p2 geometry = 'POINT (0 20)';

select @p1.STDistance (@p2);

If you are doing this for points on the earths surface then you need to use the geography type.

declare @p1 geography = 'POINT (-80 100)';

declare @p2 geography = 'POINT (-80 110)';

select @p1.STDistance (@p2)

For the geography type be aware that the values are the latitude and longitude. Also be aware that the order of these is may change to be in line with the other spatial applications in the world already (See this forum post for the discussion into this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2431933&SiteID=1)

In the examples above I use the built in parse functionality of CLR types rather than using the static methods of the Geometry and Geography types. This makes the code slightly simpler but does impose the default Spatial Reference Id of 0 for Geometeries and 4326 for Geography types.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SQL Server 2008 Spatial - How to find the distance between two points?