Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

For an upcoming project, one of the requirements is to store and use basic geogrpahic data as part of an existing SQL-Server/.Net application.

It seems obvious that we could use the SQL Spatial types, however, there doesn't seem to be any way to (say) read them from the result of a SQL query, or to save an updated POI/polygon.

Do developers really have to handle the conversion themselves at the Stored procedure level, or is there a way of using the types natively?

share|improve this question
    
this may be better asked on StackOverflow as you are asking about programming. –  Ian Aug 6 '10 at 13:43
    
@Ian The scope for this site was originally defined as "Q&A site for programmers, DBAs, Cartographers, Geographers and anyone interested in GIS professionally" (emphasis mine) –  Rowland Shaw Aug 16 '10 at 8:08
add comment

8 Answers

up vote 11 down vote accepted

Is this a desktop application or, say a Silverlight application? If it is web-based you have to jump through some hoops. You could create a view that exposes WKT and then parse the WKT client side into WPF / Silverlight geometries.

If it is a desktop application you've got it quite a bit easier. There is a good example at the Code Project of an SQL Geometry viewer that will help for both desktop or web.

You need to reference Microsoft.SqlServer.Types.dll, found at SQL Server Install/100/SDK/Assemblies to use SQLGeometry or SQLGeography directly.

Technologies like RIA don't understand these types but there is a work-around. Essentially you create a view that casts the geometry as a varbinary(max), use it in your OR mapper, RIA, LINQ, etc. and then convert it back on the client. There is a good tutorial here

Very late edit: Silverlight won't accept them either because the previously mentioned DLL's aren't compiled against Silverlight. Getting Silverlight compatible versions is high on many peoples wish list!

share|improve this answer
    
+1 for link to tutorial. –  Kirk Kuykendall Jul 27 '10 at 16:59
add comment

You can use Silverlight ESRI API to visualize spatial data stored in SQL Server 2008:

http://resources.esri.com/arcgisserver/apis/silverlight/index.cfm?fa=codeGalleryDetails&scriptID=16494

share|improve this answer
add comment

take a look at sharpmap: http://sharpmap.codeplex.com/

share|improve this answer
add comment

SharpMap is the best solution. I've looked at all the others, and it's by far the most robust. Make sure you're using the 2.0 beta version, though. I've made a blog post about it (download link included). Feel free to check it out.

http://www.jasonkiesel.com/index.php/119/my-go-to-collection-of-net-gis-libraries/

share|improve this answer
add comment

The Feature Data Objects (FDO) library allows you to access various geospatial data formats (including SQL Server) through a single unified set of interfaces.

The library is in C++, but the library also comes with a .net wrapper.

http://fdo.osgeo.org

share|improve this answer
add comment

You can use sql server data types, you can get them from nuget or here : http://www.microsoft.com/en-us/download/details.aspx?id=16177 (near the end of the page)

After that, you can use fluent nhibernate, again from nuget or this link : (Could not insert link here because looks like I don't have 10 reps...)

After that, you will need a custom UserType to map the sql type to the clr type in nhibernate. Lucky for you, this guy made one : http://blogs.ugidotnet.org/mik/archive/2010/03/26/map-sql-server-2008-geography-type-with-fluent-nhibernate.aspx

Hope this helps !

share|improve this answer
add comment

Did you have a look at nHibernate? It uses the spatial .NET types (as described by dmbrubac above) but could also talk to other DB systems than SQL server.

I don't know if you've asked for the UI level too, but here are my 2 cents: I've worked with SharpMap, GeoAPI and rest a few years ago. They are great but never reached a stadium where you can say that they are mature enough to be used in a business application. I experimented with ESRI's Silverlight and WPF API a year ago and created two prototypes which used the API. It's great, it's not free but it's really solid, state-of-the-art and it does all the things you want it to do and more.

share|improve this answer
    
The UI bit is the easy bit, as we've our our visualisation engine. –  Rowland Shaw Aug 6 '10 at 11:48
add comment

If you're writing a web client, one option is to use the ArcGIS Spatial Data Service, which is currently packaged with Esri's MapIt product (see http://resources.esri.com/MapIt). The service exposes SQL Server tables via a RESTful web service. The web service uses the ArcGIS JSON format, so tables with SQL Server spatial data can be consumed as FeatureLayers in the ArcGIS Web APIs. If you want a desktop client, you can use the WPF API.

An alternative for desktop development is to use the SQL Server CLR Types that are included with the SQL Server feature packs. You can find the download page for the 2008 R2 feature pack here - http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52. Scroll down or do a find for "CLR Types" to find the download link.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.