Writing GeoJSON from SharpMap, Part 1

In between proposals, white papers and the like, Iā€™ve been able to do a little coding to keep myself sane. Recently, I have been playing with SharpMap, GeoJSON and OpenLayers. But not necessarily in that order. Originally, I was looking over the GeoJSON spec to get more of a feel for it and decided that it would be fun (I know) to write an exporter for SharpMap. There is already a converter to write SharpMap geometries as WKT so I went ahead and built another one to convert to GeoJSON. In order to test it, I decided to use OpenLayers. Continue reading “Writing GeoJSON from SharpMap, Part 1”

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.

SQL Server 2008 SharpMap Data Provider Code

As promised, I have uploaded the data provider code I mentioned earlier to the SharpMap site. It’s written in C#. I took the existing Oracle provider, stripped out the Oracle SQL and put in T-SQL. There are a couple of things I’m still trying to chase down but it’s basically done at this point. I know they’re busy trying to finish up SharpMap 2.0 so I don’t know how long it’ll take to become available. Just in case, here’s the code: Continue reading “SQL Server 2008 SharpMap Data Provider Code”

Exporting ArcObjects Feature Class to PGSQL

The same project that has me working with WPF has me busy on a few fronts. I’ve been spending the last few days writing a tool to export data out of an ArcObjects feature class and dump it to a pgsql .sql file. ArcGIS acts as a serviceable generic data accessor to many vector data types and ArcCatalog provides a decent UI for the user community I’m targeting (also, they already have it). As a result, I’ve been developing an ArcCatalog command that exports a selected feature class.

As the title of this post suggests, I didn’t have a lot of time in which to accomplish this task.
Continue reading “Exporting ArcObjects Feature Class to PGSQL”

Flashback…

Just back from vacation šŸ™ and as a way of easing back into things, I have been tinkering with my WCF project that’s been sitting while the paying work has gotten done. It’s the one that wraps PostGIS coordinate conversion as a web service written using WCF. My copy of “Professional WCF Programming” arrived while I was gone so I had some incentive. The purpose of this project is more to get me comfortable with WCF than anything to do with coordinate conversion.

Anyway, I have a simple WinForms client to test my service calls but I’ve noticed I need to clean and rebuild the project a lot. Suddenly, I had a flashback:

How do I get rid of all theseĀ pseudocodes?

I’m using build and clean again! If only there had been an AML editor as nice as this one…

Oh well, back to work.

Consuming GeoRSS in ArcMap With InMemoryWorkspaceFactory

This will be my last post for a couple of weeks. I’m heading out to Florida tomorrow to spend time with my family and the Mouse. But before I head out, I thought I’d share a little something I’ve been working on.

I’ve been playing the last few days with the InMemoryWorkspaceFactory class in ArcObjects. I am looking at using it for a project I will be working on when I get back so I thought I’d do a little prototyping beforehand.

The fact that it works in memory is very attractive, especially for using volatile data. GeoRSS seemed like a natural source to use for prototyping.
Continue reading “Consuming GeoRSS in ArcMap With InMemoryWorkspaceFactory”