NHibernate Spatial brings the spatial capabilities of several relational databases to the NHibernate API. In this recipe, I'll show you how to use NHibernate Spatial with Microsoft SQL Server 2008 to query for a geographic region containing a point.
In SQL Server 2008 Express, create a new, blank database named Spatial.
Download the State shapes from the US Census website by following these steps:
SpatialData
.st99_d00_shp.zip
.SpatialData
folder. The files are named st99_d00.shp
, st99_d00.dbf
, and st99_d00.shx
.Import the data from the Shapefile into the Spatial
database using the following steps:
SpatialTools
.SpatialTools
folder.Shape2SQL.exe
from the SpatialTools
folder.SpatialData
folder.Test your imported data using the following steps:
Spatial
database in Microsoft SQL Server Management Studio 2008.SELECT * FROM StatePart WHERE Name LIKE 'Texas'
Download the NHibernate Spatial assemblies using the following steps:
Lib
in your solution directory.Lib
folder.Eg.Spatial
.GeoAPI.dll
in the Lib
folder.StatePart
with the following code:public class StatePart { public virtual int Id { get; protected set; } public virtual string Name { get; protected set; } public virtual float Area { get; protected set; } public virtual float Perimeter { get; protected set; } public virtual IGeometry Geometry { get; protected set; } }
StatePart
with the following XML:<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Eg.Spatial" namespace="Eg.Spatial"> <typedef name="Geometry" class="NHibernate.Spatial.Type.GeometryType, NHibernate.Spatial"> <param name="srid">4269</param> <param name="subtype">GEOMETRY</param> </typedef> <class name="StatePart" table="StatePart" mutable="false" schema-action="none"> <id name="Id" column="ID"> <generator class="assigned" /> </id> <property name="Name" column="NAME"/> <property name="Area" column="AREA"/> <property name="Perimeter" column="PERIMETER"/> <property name="Geometry" type="Geometry" column="geom" /> </class> </hibernate-mapping>
Eg.Spatial.Runner
.Eg.Spatial
model, GeoAPI.dll
, log4net.dll
, NetTopologySuite.dll
, NHibernate.dll
, NHibernate.ByteCode.Castle.dll
, NHibernate.Spatial.dll
, and NHibernate.Spatial.MsSql2008.dll
.App.config
file with standard log4net
and hibernate-configuration
sections as done in Chapter 2.<connectionStrings> <add name="db" connectionString="Server=.SQLExpress; Database=Spatial; Trusted_Connection=SSPI"/> </connectionStrings>
MsSql2008GeometryDialect
:<property name="dialect"> NHibernate.Spatial.Dialect.MsSql2008GeometryDialect, NHibernate.Spatial.MsSql2008 </property>
Main
method of Program.cs
:static void Main(string[] args) { XmlConfigurator.Configure(); var log = LogManager.GetLogger(typeof (Program)); NHibernateProfiler.Initialize(); var cfg = new Configuration().Configure(); cfg.AddAuxiliaryDatabaseObject( new SpatialAuxiliaryDatabaseObject(cfg)); var sessionFactory = cfg.BuildSessionFactory(); //Houston, TX var houstonTX = new Point(-95.383056, 29.762778); using (var session = sessionFactory.OpenSession()) { using (var tx = session.BeginTransaction()) { var query = session.CreateCriteria( typeof (StatePart)) .Add(SpatialExpression.Contains( "Geometry", houstonTX)); var part = query.UniqueResult<StatePart>(); if (part == null) { log.InfoFormat("Houston, we have a problem."); } else { log.InfoFormat("Houston is in {0}", part.Name); } tx.Commit(); } } }
In this recipe, we have simply created a Point
with the latitude and longitude of Houston, Texas. Then we created an NHibernate criteria query to find the geometries containing that point. The
geom
field in each row of our StateParts
table has a single polygon representing some distinct landmass. For example, Texas has two rows. The first polygon defines the border of mainland Texas while the other represents Padre Island, the large barrier island that runs along the South Texas shore. When our query returns the StatePart
entity that contains our point, we output the Name
field.
To allow for the additional spatial-related SQL keywords and syntax, we use the MsSql2008GeometryDialect
.
The Geometry
property on our StatePart
entity is an IGeometry
. This is mapped using the user type GeometryType
. We also provide the
spatial reference identifier, or SRID, for our datum and a subtype as parameters for this user type. Datums and SRIDs are explained later in this recipe.
This recipe barely scratches the surface of what is possible with NHibernate Spatial. With just the basic spatial data, it's possible to query for any number and combination of criteria from the availability of valuable natural resources to the standard "Find the nearest retail location" feature on a website.
To phrase this question differently, should you use a globe or map? Geography corresponds to the round-earth model, much like a globe. It works well for making measurements over great distances, accounting for the curvature of the earth.
Geometry, on the other hand, corresponds with the planar system or flat-earth model, like a map. As with a map, some distortion is tolerated, and this system is best-suited for smaller regions. However, standards for full-featured geometry data types are well established, while standards for geography data types are generally lacking. NHibernate Spatial has full support for geometry, as well as limited support for geography.
A datum is a model of the shape of the earth, combined with defined points on the surface used to measure accurate latitude and longitude. It's a sort of calibration where an exact location is defined in the datum as being at a precise latitude and longitude, and then everything else is measured from that point. For example, the North American Datum of 1927 (NAD 27) defines a marker on Meades Ranch in Kansas as 39° 13' 26.71218" N, 98° 32' 31.74604" W. Using NAD 27, every other point in North America was measured from this one point.
Each datum has a corresponding spatial reference identifier or SRID. The census Shapefile we used was built with the North American Datum of 1983, or NAD 83, an update to NAD 27. A query of SQL Server's sys.spatial_reference_systems
table reveals that the corresponding SRID for NAD 83 is 4269.
Incidentally, most GPS devices use the World Geodetic System of 1984 (WGS 84), which corresponds with SQL Server's default SRID of 4326. NAD 83 and WGS 84 are essentially interchangeable for all but the most accurate applications. Given a set of coordinates, the location measured with NAD 83 will be at most, about one meter away from the location measured with WGS 84. That is, the two systems differ by about one meter at most.
Spatial data can be broken down into three essential data types. First, a point consists of a simple X and Y coordinate. It has no length or area. A Linestring is simply two or more points in sequence, and the shortest possible line from each point to the next, as shown in the following diagrams. It has length, but no area. There are two special cases of linestring. A simple Linestring is one that doesn't cross itself. A ring is a Linestring whose first point is the same as its last.
In its most basic form, a polygon is a simple ring. It has length (or rather perimeter), as well as area. As shown in the second diagram, the line string forming the perimeter of the polygon must be simple; it can't cross over itself to form a bow-tie. A polygon may have inner negative areas defined with inner rings. The Linestrings forming these rings may touch, but they can never cross each other or the outside ring. This can best be explained with the following diagrams: