Category Archives: sql server spatial

PgMap Is Now SpatialKit

I blogged a while back on PgMap, a freeware ArcGIS extension for directly connecting to PostGIS. ST-Links, the makers of PgMap, sent out an e-mail this morning announcing that PgMap and QMap (the equivalent technology for interacting with SQL Server 2008 spatial data) have been combined into a single tool called SpatialKit. This move comes as part of the 3.0 release of the tool.

The same announcement also mentioned a Google Group dedicated to SpatialKit. When I took a look at PgMap before, it seemed to be a pretty nice tool so I’m looking forward to digging into this. If you were a previous user of zigGIS, or if you have experimented with ArcSquirrel for SQL Server 2008 access, you may want to check out SpatialKit.

SQL Server SIG at the UC

My blogging has been slower this time around because I’ve been doing a lot of booth duty. This is my thrid conference this year and some of our other staff are getting to go to the sessions. I did, however, go to the SQL Server SIG a little while ago.

Ed Katibah had a few tricks up his sleeve. First off, he dropped the news that, as of 11:00am, SQL Server 2008 is now in RTM. Very soon, the final version will be in our hands. That’s great news to everyone who has been working with the CTPs and RCs.

Ed had few other nuggets. He described the stress-testing that SQL 2008 has undergone. In short, there has been about 375,000 hours of stress testing run against SQL 2008 so we should be able to expect it to be solid. Additionally, Microsoft has already been running on SQL 2008 for “a few months.” To me, this is all great news that makes me feel even more confident about the product (which already had my confidence). When I first installed the November CTP, I felt that much of the platform was already production-ready but more testing and use can’t have hurt.

Ed went on to describe the two spatial types and reationale for doing that. That discussion is well documented and I fall in the “it’s a good thing category” mainly because of previous work with applications that had over-the-horizon requirements.

Ed gave props to Paul Ramsey during his talk when explaining the XY switching debate. It was great to see that kind of respect on display.

Some nuts and bolts: SQL 2008 limits spatial objects to about 250 million vertices. This greatly exceeds Ed’s largest test case, which is a high-res vector data set of the world’s oceans. Polygons are limited to about 65,000 holes. I think those limits leave a lot of wiggle room. He also discussed the significance of coordinate ordering for the geography data type. Basically, it’s counter-clockwise for outer rings and clockwise for inner rings. This is consistent with implementations in Informix, DB2 and Oracle (those are what he mentioned). He did mention that this was an industry consensus but there was no official specification on this matter. Ed also mentioned it may be a topic of future discussion for OGC.

Ed also demonstrated the spatial results window in the SQL management Studio. This is a really nice tool that lets you visualize the results of your spatial SQL inside the management studio. As a developer, that will be a huge help because I won’t have to do a test run to see if I got the query right. Ed just saved me a bunch of time!

Ed went on to announce a companion CodePlex site for SQL Server 2008 spatial tools: www.codeplex.com/SqlSpatialTools. There are already some tools that Isaac has posted but Ed said that other tools will include shapefile and KML converters. The site will also be open to contribs from the community. All of the tools currently there were developed using the Builder API and full source code is available so they serve has great examples of working with the Builder API.

All in all, it was a great talk. Ed’s one of the nicest guys ever and he is having a lot of fun with ArcSDE on SQL Server 2008.

SQL Server 2008 and 2005 Side-By-Side

Installing the the SQL Server 2008 November CTP side-by-side with SQL Server 2005 is very straightforward. On the previous build of my machine, I had them both running just fine. After my system crash, I began setting things up again. Because I had been working with 2008 before the crash, I installed that first and then got around to 2005 later.

It should have been obvious to me before I did that but installing them in that order seems to break a lot of stuff in 2008. Kinda makes sense. I was a little flustered at having to rebuild in the first place and perhaps rushing things a bit but here it is for the record: If you are installing both of them on a new box, put on 2005 first.

Generally the database engine worked well. Most of the issues I had came up in the management studio. After installing 2005, many of the dialogs and property pages in the Katmai management studio would no longer display. Attempting to invoke them usually resulted in a “class not registered” exception.

In order to fix that, I had to remove and then re-install 2008. Before doing that, I detached my databases and then re-attached them after I was done. Everything works fine now.

This probably says more about me than it does SQL Server ;) but I thought I’d share.

SQL Server 2008 WKT X/Y Switching Illustrated

UPDATE: Microsoft has announced that they will address this issue in an upcoming CTP. They plan to implement longitude-latitude ordering in WKB and WKT for both the geography and geometry types.

In a response to my previous post, Brian Flood mentioned the ongoing discussion about SQL Server 2008 and the “switching” of X and Y values in WKT strings depending upon which spatial data type, geometry or geography, is being used. The SQL snippet below boils the issue down to a simple example:

[sourcecode language=”sql”]
DECLARE @g geometry;
SET @g = geometry::STGeomFromText(‘POINT(3 8)’, 4326);
SELECT @g.STX as geomX;

DECLARE @h geography;
SET @h = geography::STGeomFromText(‘POINT(3 8)’, 4326);
SELECT @h.Lat as geogLat;
[/sourcecode]

In both cases, the value returned is 3. As can be seen, the same WKT string is being used in both cases. The main difference is that one example queries the X value of a GEOMETRY object and the other queries the Lat (Latitude, or Y) value of a GEOGRAPHY object. So, depending on the data type, the same ordinal can be X or Y. You can check out the debate on this topic here. It is a great discussion that covers a lot of ground and I highly recommend checking it out.

For me, the issue is consistency. Regardless of the mindsets of professionals vs. non-professionals or the imprecision of the OGC spec, the bottom line is that the same call behaves differently for two related data types. Admittedly GEOGRAPHY and GEOMETRY do different things. GEOGRAPHY is for geodetic (spherical) data and GEOMETRY is for planar data. This fact, however, has no bearing on how WKT is parsed. The current situation is analagous to having a rounding function that by default rounds up for doubles and rounds down for singles.

At this point, I would normally say pick a method and run with it for both data types but I do have a preference here. I think that WKT should be parsed as X,Y consistently in order to provide better interoperability with a wider variety of client software. The simple fact is that most products out there do X,Y. For instance, I am working with SharpMap and it generates WKT in X,Y order and assumes X,Y order when parsing. Other database platforms such as Oracle, PostGIS and MySQL do the same but it’s the client software that matters most in this case. I’m sure that others can chime in with more examples.

I am intrigued by the use of a separate data type to handle geodetic data but the X,Y order of WKT has no bearing here. To my way of thinking, the best approach would be to commit to X,Y order for both GEOMETRY and GEOGRAPHY.

Inserting SQL 2008 Geometry With a SqlCommand

Update (7/1/2011): I wrote this post early in the SQL 2008 CTP. I no longer use the method described here. If you found this post via StackOverflow or some other Q&A site, I highly recommend this post by Vish as a more efficient way of doing things: http://viswaug.wordpress.com/2008/09/29/inserting-spatial-data-in-sql-server-2008/

I’m in the process of using SharpMap to write a utility to load spatial data into my SQL Server 2008 instance. A couple of the SharpMap data providers have a static method to load data from a shapefile (really any other SharpMap data provider, but I digress). The method creates a table and uses an ADO.Net command object (SqlCommand in this case) load individual records into the table. I started using the existing MsSql provider as a template, which load the geometry’s WKB into an image column.

The problem with SQL 2008 comes in when you add a parameter to the command. You must specify the data type of the parameter by passing it a SqlDbType enumeration. Currently, there is no enumeration that corresponds to geometry (or geography for that matter). My first attempt was to leave the parameter defined as the type SqlDbType.VarBinary and set the value as such:

[sourcecode language=”csharp”]
//define parameter
command.Parameters.Add("@shape", SqlDbType.VarBinary);
//
//code in between omitted
//
//set value of parameter
command.Parameters["@shape"].Value = feature.Geometry.AsBinary();
[/sourcecode]

In this case, feature.Geometry is of type SharpMap.Geometries.Geometry and AsBinary returns the WKB representation of the shape. This code failed when attempting to execute the command, throwing an exception indicating that the table column (type geometry) was not a valid binary data type.

So I next tried defining the data type of the parameter as SqlDbType.Udt (user-defined type) and setting the UdtTypeName property to “geometry”. This failed also.

On a whim (I wish I could say it was something more scientific than a desire to leave no stone unturned), I then tried working with text and set it up this way:

[sourcecode language=”csharp”]
//define parameter
command.Parameters.Add("@shape", SqlDbType.NVarChar);
//
//code in between omitted
//
//set value of parameter
command.Parameters["@shape"].Value = feature.Geometry.AsText();
[/sourcecode]

In this case, AsText returns the WKT representation of the shape. This worked! I have not dug into the details of why it worked but it seems the underlying spatial objects in SQL 2008 have some built-in default behaviours that coerce WKT. I am now loading data via a WinForms app with the aid of SharpMap. So, if you’re trying to update geometry from within your code, the above example is what worked for me.

I’ll probably post the code to the data loader a little bit later after I incorporate SRID and geometry type constraints and the like.