This is just a quick post to capture the outcome of about an hour’s worth of trial and error for me.
Here’s a SQL Server table that contained data that my application needed:
Of particular interest is the [Geocode] field, which holds a Geography instance (POINT) representing the location of a Facility. The [Geocode_Lat] and [Geocode_Lon] fields were from legacy requirements, and would be redundant when all is said and done.
Trying to include a Geography field in my ActiveRecord entity proved to be a challenge. NHibernate has a Spatial extension, but setting it up and trying to use it proved to be somewhat of a nightmare (read: I couldn’t get it to work). In reality, I wasn’t looking to use ActiveRecord to do spatial querying, but rather, was just looking to have the data passed between the database and my application.
SQL Server 2008 saves Geography and Geometry columns as binary data. This isn’t WKB, but rather, an internal serialization of the data.
To have the data pass through ActiveRecord, we need to configure the entity class to treat this data as a byte[] array. The application would then need to deserialize the bytes into a SqlGeography instance. Likewise, when updating the entity, the application would need to serialize the SqlGeography instance to an array of bytes.
My Entity class defines the [Geocode] property as:
[Property(SqlType = "geography", ColumnType = "BinaryBlob")]
public byte[] Geocode { get; set; }
Then, I created a couple Extension Methods to handle the serialization/deserialization:
public static class ExtensionMethods
{
public static SqlGeography AsGeography(this byte[] bytes)
{
var geo = new SqlGeography();
using (var stream = new System.IO.MemoryStream(bytes))
{
using (var rdr = new System.IO.BinaryReader(stream))
{
geo.Read(rdr);
}
}
return geo;
}
public static byte[] AsByteArray(this SqlGeography geography)
{
if (geography == null)
return null;
using (var stream = new System.IO.MemoryStream())
{
using (var writer = new System.IO.BinaryWriter(stream))
{
geography.Write(writer);
return stream.ToArray();
}
}
}
}
And, finally, code to use property. Here is a function that updates the geocode for a particular entity (notice the AsByteArray() extension method):
public static void SetGeocode(int FacilityID, double? Geocode_Lat, double? Geocode_Lon)
{
Facility f = Facility.TryFind(FacilityID);
if (f != null)
{
f.Geocode_Lat = Geocode_Lat;
f.Geocode_Lon = Geocode_Lon;
f.Geocode = SqlGeography.Point(Geocode_Lat.GetValueOrDefault(),
Geocode_Lon.GetValueOrDefault(), 4326).AsByteArray();
f.SaveAndFlush();
}
}
Disclaimer: The method described here satisfied my immediate needs, so I didn’t spend additional time exploring other options. The code is provided AS IS, with no expressed warranty or guarantee. There probably are far better ways to include spatial data in ActiveRecord entities, and as such, I’d be interested in hearing about your own experiences.
52191936-a3ce-4a03-a754-226f88d2dda5|1|1.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04