Support for SQL Server 2008 Geography type
Description
Environment
Activity
David Pfeffer June 26, 2010 at 7:50 AM
This should be closed, as geography is now supported.
Jason Kirkhart November 11, 2009 at 11:09 AM
In integrating the experimental geography support into some software I'm writing, with the intention of using it to calculate distances between locations. Not 100% if I'm using it wrong, or whether I've found a limitation in the experimental code, however, it looks like my geography objects being passed to the STDistance function are not having their SRIDs passed back to SQL. For some sample code...Location is an ActiveRecord object with a geography property, GeoData. I can pull these objects out of the repository and inspect the correct latitudes, longitudes and SRIDs. If I then try to pass it back into a SimpleQuery, I get an error from the SQL client driver (SQL 2008) saying that I'm not using a supported SRID, even though all of my data uses the same SRID, 4326, and the same query directly in SQL works just fine.
Location loc = Location.Find(1);
string strquery = @"from Location where GeoData is not null AND GeoData.STDistance < 1";
SimpleQuery<Location> q = new SimpleQuery<Location>(strquery, loc.GeoData);
Location[] results = q.Execute();
I also tried to force an SRID to be passed by calling the static geography:oint(Lat,Lng,SRID) constructor, and received a fairly unhelpful error message:
Location loc = Location.Find(1);
string strquery = @"from Location where GeoData is not null AND GeoData.STDistance(geography:oint(?, ?, ?)) < 1";
SimpleQuery<Location> q = new SimpleQuery<Location>(strquery, loc.GeoData);
Location[] results = q.Execute();
NHibernate.Hql.Ast.ANTLR.QuerySyntaxException: Exception of type 'Antlr.Runtime.NoViableAltException' was thrown. near line 1, column 16 [select geography:oint(39.83, -98.58, 4326)].
So, I've been trying to work through this numerous different ways for two days, and ready to seek more help than I could google. Can what I'm trying to do be done with what's available today? Am I stuck writing direct queries with a SQLCommand or similar approach?
also, for what it's worth, I've read about some of the limitations of going through NetTopologySuite, NHibernate.Spatial, GeoAPI.Net, etc., and so had hoped to get where I need to be through the above.
Also, apologies if this is posted to the wrong place, but this seems to get some attention, and I think is relevant to where I'm having the problem.
Regards!
Jason
David Fairlie-Jones September 27, 2009 at 10:30 PM
I can confirm the issue is resolved by modifying the x/y parameters in GeographyWriter and NtsGeographySink to latitude/longitude parameters (=y/x).
David Fairlie-Jones September 27, 2009 at 5:59 PM
I have started using revision 959 on MS SQL 2008. When saving an IPoint, Spatial seems to store the x/y coordinate in the reverse positions. A coordinate with y coordinate (latitude) 90 and x coordinate (longitude) 180 in WGS-84 causes an exception when validating the point because SQL thinks the latitude is 180 and therefore out of range.
Oliver Ogg September 10, 2009 at 6:03 AM
Yep, would love to see this. Actually surprised there aren't more voters! Perhaps for legacy reasons people might be using geometry?
hi,
i wonder if it would be possible to support geography type (in addition to geometry type) for sql server 2008.
as we are currently developing a system that uses the geography type heavily, and nhibernate is my preferred
orm, supporting this type in nhibernate.spatial would be awesome.