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.

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.

share|improve this question

5 Answers 5

up vote 7 down vote accepted

Michael, A few more details on ArcSDE. 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 get 'locked' in an ESRI-only solution and 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

share|improve this answer
1  
+1 for being locked in. I agree completely –  Hairy Sep 21 '11 at 6:50

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:

enter image description here

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

enter image description here
(Be sure that your query includes a geometry field and a unique id field)

enter image description here

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

share|improve this answer

@Hairy, 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.

@Michael, 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.

share|improve this answer

Michael,

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, imo, it's quite immature, still.

share|improve this answer
1  
+1 for pointing out direct connect. Just because you are using ArcSDE does not mean you must utilize it through a service on the default port 5151. Bypass that service (the extra layer) and directly connect to the DB. We just got ours working with MS SQL Server 2008 R2. –  Bratch Sep 20 '11 at 14:30

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!

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.