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.

2 Answers
2

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.

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.

Yeah, I know what you are talking about with the SRIDs and it is annoying. The same spatial reference will be in the SDE_spatial_references table multiple times, but with different SRIDs. Why did they do that?? So yes, you have to query the table to get it's SRID as well - is that what you are talking about?
–
Chad CooperNov 22 '10 at 14:25