Using ArcGIS Desktop with SQL Server Spatial as backend?

Question

I'm wondering how best to use ArcGIS Desktop and SQL Server 2008 together. At the moment we run manual imports and exports between sql 2008 and arcgis 10 shape files. With sql 2008 having a geography data type I was expecting it to be simple to replace to the map files with a relational database, but for some reason it doesn't seem as simple as I expected.

Has anyone got any ideas how I can use sql 2008 for data storage and do away with shape files? I've read about arcsde, but really don't understand the point of another layer. No idea what it's actually supposed to do.

Answer 1

Since you've tagged the question for ArcGIS 10, check out Query Layers: http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/What_is_a_query_layer/00s50000000n000000/

I've used it to connect to non-ArcSDE SQL Server 2008 databases.

You get to it from the file menu:

And there is a couple of other screens that walk you through defining what ArcMap needs to know to make the query layer.

(Be sure that your query includes a geometry field and a unique id field)

You'll want to test it out, since I know it was buggy at release and SP1.

Answer 2

Using ArcSDE you can store spatial data in basically 2 formats. Either using the SDE native format (which is a BLOB), OR depending on the database you use (SQL Server, Oracle, PostGIS, i think DB2 spatial as well) in the respective database native format. Which for Oracle for example, is the SDO_GEOMETRY type and for SQL Server the Geography or Geometry spatial types.

Which format you actually end up using is up to you and and it wouldn't/shouldn't make any (huge) difference on the client side. Any ESRI client (e.g. Arcmap) would handle the data in the same way. My suggestion would be to go for the SQL Server native format (or Oracle's if you were using it) as otherwise I think you would have to use ESRI-only tools to do even the simplest spatial queries or analysis. Using the native db format on the other hand would allow you to query the data from other clients as well and even from SQL Server Management Studio. And as @Blomster says you can move a lot of the spatial logic into stored procedures.

As first step to import shapefiles into SQL Server would be to download the extremeley handy Shape2SQL utility by Morten Nielsen

Answer 3

In my experience, adding a SQL spatial column type has allowed me to move a lot of my spatial operations (distance, buffer) into stored procedures which is really handy.

ArcGIS toolbox has a "Copy Features" tool to push data into a sde geodatabase, and if you specify a Configuration Keyword of "Geometry" or "Geography" a sql spatial column should be generated.

Answer 4

ArcSDE allows you to store spatial data in the SQL Server instance, by effectively adding a spatial column to your tables; it doesn't change it, or affect existing connectivity/ applications. ArcSDE acts, if you like, as a metadata store for your spatial data, like it's featureclass name, owner, extent, type of geometry you're storing, etc, etc (all in metadata tables built on installing ArcSDE). It allows you to store anything you like, images etc, and still have spatial references (in SQL Server, if I remember correctly, spatial data is stored as tpe image?). ArcSDE manages the storage of this data. It's actually a good tool and all this from a sceptical ESRI user.

I am currently using ArcGIS Server, with ArcSDE and direct connect (using tnsnames) on an Oracle 11g back end and it's grand. I don't understand why you'd use SQL Server for spatial data when I think it is still quite immature.

Answer 5

You can edit vector data stored in SQL 2008 or 2012 in ArcGIS by installing the GISquirrel extension, available from www.gisquirrel.com This also provides tools to import spatial data into SQL Server from any data source that can be viewed as a layer in ArcMap. I have recently imported a table with 90 million polygons - it took 4 days, but worked without a hitch on a 2 year old laptop!