Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

ASP.NET - scratching discs of code & entrepreneurship

Archive · View All
January 2012 8
April 2011 6
March 2011 4
August 2012 2
June 2011 2
May 2012 1
May 2011 1

John Katsiotis's Blog

PetaPoco and Spatial Data Types

May 14 2011 12:00AM by John Katsiotis   

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.


Republished from djsolid - scratching discs of code & entrepreneurship [19 clicks].  Read the original version here [2 clicks].

John Katsiotis
1010 · 0% · 26
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

1  Comments  

  • Hi John!

    Thanks for your post.

    Do you also happen to know how to map a database column of type Geography (http://msdn.microsoft.com/en-us/library/cc280766.aspx) to your peta poco Info object class?

    I tried:

    public class ItemInfo
    {
        public SqlGeography ColumnName;
    }
    

    but this leads to IConvertible implementation missing excpetion

    Thanks in advance!

    commented on Feb 21 2013 2:21AM
    rudgr
    2760 · 0% · 4

Your Comment


Sign Up or Login to post a comment.

"PetaPoco and Spatial Data Types" rated 5 out of 5 by 1 readers
PetaPoco and Spatial Data Types , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]