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.

This is my first time working with data directly from MS SQL Server 2012, and I'm unsure whether I can use it in a Relate in ArcMap.

I have a table in MS SQL Server, which I brought into ArcMap via the Database Connections option. When I add this table to ArcMap using the standard Add Data button, a New Query Layer dialog appears:

enter image description here

ArcMap requires a unique identifier field, however this table doesn't have one. I chose one of the existing fields, even though it's not unique (the first problem?).

I can now view the table in ArcMap, and I'm able to set up a Relate between this table and a featureclass, based on the matching BoreholeID field.

However, when I select a borehole from the featureclass, or a row in the table, the Relate is displayed but greyed out:

enter image description here

  • Can I use a table from MS SQL Server Management Studio in an ArcMap relate?
  • If so, is the cause of the problem the lack of a unique ID field?
  • If not, what else could be causing the problem?
share|improve this question
    
If the doc says the ID column must be unique, you probably shouldn't go any further until you have a unique column. –  Vince Aug 19 at 0:56
    
@Vince yeah I think you're right. Preparing to use query layers says "The query layer definition must include a unique, not-null column or combination of columns that can be used as the ObjectID" –  Stephen Lead Aug 19 at 2:03
    
I note that after adding the unique ID field directly in SQL Server, I don't see the New Query Layer dialog in ArcMap. Instead, the table is simply added, which is what I was expecting. –  Stephen Lead Aug 19 at 4:19

2 Answers 2

up vote 3 down vote accepted

ArcGIS makes frequent use of "registered rowid columns". These are

  • INTEGER (32-bit) columns with
  • non-zero positive values (1 - 2^31-1),
  • which are defined to be NOT NULL,
  • contain unique values, and
  • are reproducible on subsequent queries

ArcGIS uses these columns to maintain the relationship between rows in tables and the graphics in the map. Whenever you register a table with an enterprise geodatabase, ArcGIS will create an SDE-set rowid column if one is not previously available.

Query Layers are not required to be registered with geodatabase, but they are required to have a rowid column with the same characteristics. Many folks try to cheat the rules by using a row number computed column in place of a rowid, but this will only create what the documentation refers to as "inconsistent behavior" when attempting to map spatial objects to table rows (and vice versa, as in an identity operation).

You'll need to add a rowid column to this table to use it in a query layer.

share|improve this answer
    
Thanks, I'll give this a try. I'm populating the table using a multi-processing python script, so I'll need to think about how to create a unique ID for each row. –  Stephen Lead Aug 19 at 3:26
    
That's a different question, but ArcSDE does this by using an "iN" table to hold the last range value, and allocating sequences of values from the range sequentially in the application (autonumber was a performance bottleneck in bulk insert). In Oracle, the remaining values are stuffed in a PIPE, but in SQL-Server there will be gaps. I've found that parallel insert usually results in spatial fragmentation and overall poor query performance. –  Vince Aug 19 at 4:01
    
I found that this worked to create a unique ID in SQL Server 2012: (ID INT IDENTITY(1,1) NOT NULL) –  Stephen Lead Aug 19 at 4:06
    
Yes, but you may find insert performance suboptimal. –  Vince Aug 19 at 4:07
    
The relate works correctly after adding this - thanks! –  Stephen Lead Aug 19 at 4:20

First, let's clarify a couple of things. When you say that you want to add a table from MS SQL Server Management Studio 2012, what you really want to do is add a table from MS SQL Server. The management studio is simply the user interface for interacting with the database itself.

Second, I am going on the assumption that this SQL Server database does not have ArcSDE loaded into it. If so, loading a table should be as simple as adding a standard table, and would not be a Query Layer.

ArcGIS is pretty picky when it comes to dealing with databases outside of the SDE environment. You have to make sure you've go the updated drivers for the MS SQL Server. You probably do as you are able to add the table. If you are able to get the data as a table inside ArcGIS, then you should be able to use it as part of a join or relate, regardless of what database it is stored in.

As you have surmised, a Unique ID or lack thereof, is the hangup. As you know, with any standalone table that is created through the ArcGIS environment, an Object ID is created. This is a unique ID that allows ArcGIS to identify any individual row. When you are bringing in a layer from a database outside the ArcGIS environment, you need to specify the field that will be used in place of the Object ID. Failure to specify a field like this means that you will be able to add a table, but won't be able to perform any options that require indexing of the rows. This includes any kind of Joins or Relates and other spatial operations.

In the image below, I am loading in a table. The first attribute listed is gid. As you can see, it shows that it is Not Nullable. That is a second criteria that is important, but not required at the outset. An error will be raised if NULL values are found, but not if the field allows NULL values. This is the field that I will choose as the Unique Identifier attribute. Add Query Layer

Even though this layer is a spatial layer, the steps are the same for a non-spatial table as well.

Now, back to your problem, and a couple options for solutions. Your table doesn't have a Unique ID field. You need one. You have a couple of options.

  1. Add SQL Server's equivalent of an Autonumber field to your table. This method will create a Unique ID that will not change with respect to a particular row. Once an ID is assigned, it will persist, and never repeat or duplicate.
  2. Create a View in SQL Server, based on your existing table, and add an Autonumber field there. This method will create a Unique ID similar to an ArcGIS Object ID. It should NOT be used as the basis for a join or relate, as the assignation of ID's to rows is solely based on the sort order changes. It is sufficient, however, for fulfilling the unique table ID requirement for a Query Layer. Here is some example code for that instance:

    CREATE VIEW VIEW_NM AS SELECT Row_number() OVER(ORDER BY "EventName" DESC, "BoreholeID") AS tbl_id, "EventName", "Method", "BoreholeID" FROM yourtable

    ------ Edit ----- A concern was raised that using a Row_number() function would not yield an adequate unique ID, because it may return different features. In the case I provided, this is true, in that the Order By field is not unique. Therefore, you must be sure that you include a combination of fields in that function that will return a unique sort order, or values when converted to a number. These will ensure that a particular feature will return the same Object ID, regardless of what query is placed against it in ArcGIS. I am adding "BoreholdID" to the Order By section.

    One issue that may crop up when working with a view, is the field type that ArcGIS will apply to this "tbl_id" field. It may see it as a field type it is not able to handle, therefore you may need to cast it to an Integer field type. This is how I did something similar with a view in PostgreSQL:

    SELECT (int4(Row_number() OVER(ORDER BY "EventName" DESC)) AS tbl_id

    The syntax for SQL Server might be slightly different. It might be int instead of int4.

  3. Another way to create this unique ID would be to use the Make Query Table tool. This tool allows you to select a table, view or create a custom SQL query using database tables, and display them in ArcGIS either as a Layer or a Stand-Alone Table. One of the parameters in the tool is how to create the Key field. It again is done by selecting one or more fields found in the query layer, that will be used to dynamically create the ObjectID. This is much the same as the Row_number() function, as they are both creating a unique identifier based on data in the query result. One is simply doing it on the database side, the other is handling it within ArcGIS. One potential upside to this method is the easy ability to incorporate it as part of a python script. An example is shown at the bottom of the help document.

As a rule of thumb, you want to have a unique identifier attribute for every table, whether spatial or attribute, that you create. Most of the databases work more efficiently on tables where this attribute exists, and is designated as a primary key, or similar. It allows for indexes to be created, and in many cases, editing and selection functionality is restricted when a unique ID is not present.

Anyway, give this a shot and see if it works. As I mentioned, ArcGIS can be very particular when adding query layers. Some other issues you may into are with field types that translate differently from how they are defined in the database, to how ArcGIS interprets them.

This help document can help you ensure that you cast data types in your table to ones that will be recognized by ArcGIS. SQL Server data types supported in ArcGIS

share|improve this answer
    
Excellent answer, thanks for the detailed steps and explanations. –  Stephen Lead Aug 19 at 2:27
1  
No, it not safe to use a row number as a rowid. If you change the query constraints, the value of the row number will not be consistent. –  Vince Aug 19 at 2:28
    
@Vince, you are correct. The values created with the Row_number function should be treated in the same way as an ArcGIS ObjectID. They should not be ever be used to base a join or relate on, as they cannot be guaranteed to be consistent with regard to a particular id and row. If you want to create a Unique ID that can be used as the basis for a join or relate, it should be done at the table level itself. –  Get Spatial Aug 19 at 2:32
1  
That's not what I'm saying. You will get "inconsistent results" (random behavior) if you use a row number any place that a rowid is expected, because the value will not serve as a tie between the table and the corresponding geometry. –  Vince Aug 19 at 2:37
    
I'm not sure that I agree with the statement that it is the tie between a table and corresponding geometry. That doesn't seem to be shown in the link you provided in your answer. The main issues there were related to potential duplication of Object ID's, which won't happen with this function. If, as you say, you change the query constraints, you should refresh the query layer, which establishes the link again. You would need to do this anytime you change a query, whether using row_number or a rowid. –  Get Spatial Aug 19 at 3:02

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.