Has anyone successfully implemented a web service that sits on top of ArcSDE (SQL Server 2008)?

Question

Here's the situation. We have planned point locations that lie within predefined polygonal project areas. Our IT department currently has these polygonal project areas in SQL Server 2008 as geography objects. When a user enters a point location in a interface, a query gets issued to the project areas table to return the project area that the new planned point location lies within. Works like a champ. However, these project area boundaries change of course in our GIS and then IT doesn't get the changes and we get data busts. IT has talked of writing a web service that would sit on top of our ArcSDE database (again, SQL Server 2008) so they could just make calls to the fresh data we always have in SDE. This of course would require us to store those datasets in SQL Server geom/geog datatypes.

My question is this: Has anyone successfully implemented anything like this? If so, could you share your thoughts. If you have tried and failed, I'd like to hear about that as well. Thanks.

Answer 1

I have a web service that uses a couple of SDE featureclasses (Geometry data type, not geography) and does basically what you are wanting to do. The web service is used by a two different websites (1 ESRI silverlight, and 1 non-mapping ASP web application).

In my case, the GIS data used by the web service is not versioned, so it's straightforward.

The way ESRI uses SRIDs is annoying(they may have fixed this in later sde versions). But I had issues with comparing two geometries from different featureclasses that are in the same spatial reference system, because ESRI would use 2 different SRIDs. It's frustrating if you don't know what is happening, and it makes you final Select SQL statements a little longer (unless you hard-code the SRID into your SQL). In order to work around, I had to use geometry::STGeomFromText quite a bit to reconstruct the geometry I was trying to compare.

WHERE 
featclass.SHAPE.STContains
(
    geometry::STGeomFromText(
    @wkt,
    (select distinct shape.STSrid from featclass))
) = 1

There's probably a better way to get the STSrid, but the above works as long as there are features present in the featureclass.

I should also point out the data functionality resides in a separate assembly and is called by the web service, but I've seen web service projects that have it all piled into one project in visual studio.

Answer 2

The simple solution (but at cost!) ArcGIS Server is http://www.esri.com/software/arcgis/arcgisserver/index.html

but if you can connect to your SQL 2008 server without ArcSDE you will find a much cheaper solution

SQL Server 2008 R2 - Developing Rich Reporting Solutions Presentation (from slide 18 slide 21 would be ideal) http://www.slideshare.net/msitpro/dev-track-developing-rich-reporting-solutions-with-microsoft-sql-server-2008-r2