ArcSDE and SQL relates

158
4
12-08-2009 09:14 AM
SteveKocsis
Occasional Contributor II
Does anyone know if ArcGIS honors MSSQL relationships that are not built in SDE? How about general MSSQL column types. In the grand scheme I think SDE limits the types to few where MSSQL choices are many. I would like to see ArcSDE and MSSQL make use of dbms for non spatial data, i.e. a parcel feature class (SDE) related to CAMA data (non SDE) without having to build a spatial view.
0 Kudos
4 Replies
RussellBrennan
Esri Contributor
Countygis,

Take a look at the following help topics regarding field types.

Data Types in the DBMS
http://help.esri.com/en/arcgisdesktop/9.4/help/index.htm#/Data-types-in-the-DBMS/005S0000000Q000000/

Geodatabase field data types
http://help.esri.com/en/arcgisdesktop/9.4/help/index.htm#/Geodatabase-field-data-types/005S0000000P0...

A comparison of ArcGIS - ArcSDE - and SQL Server data types
http://help.esri.com/en/arcgisdesktop/9.4/help/index.htm#/A-comparison-of-ArcGIS--ArcSDE--and-SQL-Se...

For building DBMS joins you might want to check out Query Layers which are new in 9.4.

What is a query layer
http://help.esri.com/en/arcgisdesktop/9.4/help/index.htm#/What-is-a-query-layer-/006600000083000000/

Preparing to use query layers
http://help.esri.com/en/arcgisdesktop/9.4/help/index.htm#/Preparing-to-use-query-layers/006600000084...

Creating a query layer
http://help.esri.com/en/arcgisdesktop/9.4/help/index.htm#/Creating-a-query-layer/006600000086000000/

Working with a query layer in ArcMap
http://help.esri.com/en/arcgisdesktop/9.4/help/index.htm#/Working-with-a-query-layer-in-ArcMap/00660...

Choosing a unique id field
http://help.esri.com/en/arcgisdesktop/9.4/help/index.htm#/Choosing-a-unique-identifier-field/0066000...

Defining the spatial reference of the query layer
http://help.esri.com/en/arcgisdesktop/9.4/help/index.htm#/Defining-the-spatial-reference-of-the-quer...

Modifying a query layer
http://help.esri.com/en/arcgisdesktop/9.4/help/index.htm#/Modifying-a-query-layer/00660000008M000000...

Connecting to a database
http://help.esri.com/en/arcgisdesktop/9.4/help/index.htm#/Connecting-to-a-database/00660000008500000...
0 Kudos
TedCronin
MVP Honored Contributor
Does anyone know if ArcGIS honors MSSQL relationships that are not built in SDE? How about general MSSQL column types. In the grand scheme I think SDE limits the types to few where MSSQL choices are many. I would like to see ArcSDE and MSSQL make use of dbms for non spatial data, i.e. a parcel feature class (SDE) related to CAMA data (non SDE) without having to build a spatial view.


You may want to look into query layers.  These are layers you create using fields that are spatial in nature, but not sde.  How about relationship classes based on Assessment Numbers, or build a database view in SQL,  and join interactively with an MXD.  Are you trying to edit the CAMA data inside of ArcGIS, and having to work with all of the sde based tables and how the data crosses between the different databases?  Or are you just wanting to use CAMA tables with GIS data?
0 Kudos
SteveKocsis
Occasional Contributor II
Ted and russellb

Thank you both for pointing to Query Layers. I was primarily looking for ways to use related data that is not registered with the GDB....AND hoping that I could figure out a way to symbolize or follow the relates in published services. The CAMA I referred to is edited outside of GIS, but currently linked to parcel polys by a spatial view. I've had trouble with SDE playing nicely with the CAMA table since it's not registered, no aliases, problems with annotation feature classes..... But the query layer may be the ticket. I'll have to try the query layer in Beta; if it works in 9.4, as the help indicates, that is good news.
0 Kudos
SteveKocsis
Occasional Contributor II
All:

Thanks for the input for query layers. I have successfully created a query layer, mxd with relate (parcel feature class to ownership query layer), AGS service, and AGS application. I was very happy to see that the relate was honored in both the AGS Service (and REST), and in the OTB web application. I tested query tasks built on the query layer in the .net app, and was pleased to get results. Also, using search attributes task on the parcel layer with the parcel-ownership relate returned the linked results. KUDOS ESRI for finally giving rdbms capability to AGS!
0 Kudos