PetaPoco and Spatial Data Types

PetaPoco is a micro-ORM for .NET and Mono. If you haven’t see it yet I suggest you to check it out. Alternatives to PetaPoco are Massive and Dapper. (If you know more projects let me know).

After this brief introduction let me begin by explaining the problem. I need it to build a simple web app with a fairly simple data model that was using Spatial Data. The app would use SQL Server 2008.

Let’s consider a table with a bunch of columns and one of them is a geometry column. I needed to handle that. Entity Framework doesn’t have support for spatial types (it’s coming though so give your feedback) so it was out of the question. Since I was dealing with a simple data model I decided to use a Micro-ORM like PetaPoco.

I fired-up Visual Studio, created a new Web Application and Installed PetaPoco via Nuget.

PetaPoco package has also a few T4 templates that helps you creating classes. There is also a package named PataPoco.Core which contains only the one file that is needed with no T4 templates.

When I tried to create my classes using the T4 template it worked like a charm! For my geometry column an equivalent Microsoft.SqlServer.Types.SqlGeography was created!

Next, I tried to do a query like this:

var location = SqlGeography.Point(lat, lng, 4326);
var ids = db.Query<int>(@"SELECT Id FROM Points WHERE @0.STIntersects(Location) = 1", location.STBuffer(100));

This is were I got the following error:

UdtTypeName property must be set for UDT parameters.

The solution was simple.

  1. Go to PetaPoco.cs
  2. Find the AddParam method
  3. Just before the final else (somewhere around line 406) add the following code:
    else if (item.GetType() == typeof(bool) && _dbType != DBType.PostgreSQL)
    {
        p.Value = ((bool)item) ? 1 : 0;
    }/*Already there*/
    else if (item.GetType() == typeof(Microsoft.SqlServer.Types.SqlGeography))
    {
        p.Value = item;
        ((System.Data.SqlClient.SqlParameter)p).UdtTypeName = "Geography";
    }
    else if (item.GetType() == typeof(Microsoft.SqlServer.Types.SqlGeometry))
    {
        p.Value = item;
        ((System.Data.SqlClient.SqlParameter)p).UdtTypeName = "Geometry";
    }
    else/*Already there*/
    {
        p.Value = item;
    }

That’s it! Now you can create queries with spatial parameters.

Enjoy!

PS: I hope in the future there will be some kind of extensibility so this fix could be at a separate file.

Add a Comment (gravatar-enabled)