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.

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.

share|improve this question
    
Is the GIS data you are trying to use versioned? –  Jay Cummins Nov 22 '10 at 14:12
    
No it is not versioned. –  Chad Cooper Nov 22 '10 at 14:20

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
    
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 Cooper Nov 22 '10 at 14:25
    
yes, i added my workaround to the answer. –  Jay Cummins Nov 22 '10 at 14:41

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

share|improve this answer

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.