[GIS] When to store geometries using SQL-server geometry datatypes or ArcSDE

developmententerprise-geodatabasesql server

With the release of SQL Server 2008 Microsoft added geometry data types. So geometries can now in an ESRI enviroment be either be stored using SQL-Server datatypes or ArcSDE datatypes.

A colleague of mine asked today when should we choose one product over the other?

We already have business databases in SQL-Server and in ArcSDE, if we need to create a new database, what options do we have? Are there any advantages/disadvantages to storing data in one way or other?

Refrased, Old Question is here:

"With the release of SQL Server 2008 Microsoft added geometry data types. So geometries can now either be stored in SQL-Server or ArcSDE.
A colleague of mine asked today when should we choose one product over the other?
We already have business databases in SQL-Server and in ArcSDE, if we need to store a new polyline, is there any reason to promote one place over the other? "

Best Answer

So geometries can now either be stored in SQL-Server or ArcSDE.

The two are not mutually exclusive. ArcSDE always requires an RDBMS to store data so you're not storing your data "in ArcSDE" or "in SQL Server." If you're using SQL Server, your data will always be stored in tables in a database in a SQL Server instance.

How your data is stored is up to you. ArcSDE works with data stored as Microsoft's geometry (and geography) types. This is the most versatile option as you get access to your geometries with raw SQL but you can also used advanced functionality only available through Esri's Geodatabase. The alternative to using Microsoft's spatial types is to use Esri's SDE binary type which is not readily usable with raw SQL. Check out this help topic for more info: Feature classes in a geodatabase in SQL Server

Related Solutions

[GIS] Find duplicate vertices in SQL Server geometry line (ArcSDE)

Here's one way with Python. Get the linestring binary as string out of the database:

select shape.ToString() from table_in_sde

then take that and put it into a variable in python, use some regex, list and dictionary goodness to find the dups (to be honest, I Googled to find the dictionary dup stuff):

>>> import re
>>> s = 'LINESTRING (1835815.86 12887142.42 0 0, 1835816.72 12887142.68 170 170, 1835817.53 12887142.76 349.99 350, 1835817.52 12887142.76 559.99 560,....)'
>>> l = re.findall(r'(\d+.\d{2})\s',s)

>>> icount = {}
>>> for i in l:
...     icount[i] = icount.get(i,0) + 1

>>> for key, value in icount.iteritems():
...     if value > 1:
...             dups[key] = value
...
>>> dups
{'12887142.42': 2, '12887142.76': 3, '3081.28': 2}
>>>

The third dictionary item is irrelevant (it's Z values and they can be present muliple times). The first two items are duplicate Y values. Key is the coordinate, value is the count of how many times it appears in the string.

[GIS] Error accessing spatial database connection with ArcGIS 10

Here is how I got around this issue

Uninstall ArcSDE
Detach all Databases
Uninstall SQL Server R2
Install SQL Server
Install ArcSDE

The issue is this, SQL Server 2008 R2 is not a support DB, as the SQL Server Native Client built into ArcGIS was released prior to the release of R2. Here is a link

http://resources.arcgis.com/content/arcsde/10.0/sql-server-system-requirements

Update: Have you tried making a Direct Connection to your database. This can be done by creating a new Spatial Database Connection in ArcCatalog. Next type the following deatils

Server: leave blank Service: sde:sqlserver: Database: leave blank Please let me know if it works

Sorry for the bad news, CDB

Best Answer

Related Solutions

[GIS] Find duplicate vertices in SQL Server geometry line (ArcSDE)

[GIS] Error accessing spatial database connection with ArcGIS 10

Related Question