Non-unique primary keys, creating coincident points based on secondary key

IsaacBoates — Fri, 11 Oct 2013 15:30:25 GMT

I have a point shapefile of survey locations. I also have a table of data corresponding to said survey locations. The problem is that many of these survey points have been surveyed multiple times or with different equipment. Each point has a unique primary key, and in the table, there are multiple entries with that same primary key, but it has another column that serves as a secondary key.

Is there a way to join the point shapefile to the data table by primary key, but make it create duplicate coincident points for each unique secondary key?

Re: Non-unique primary keys, creating coincident points based on secondary key

RichardFairhurst — Fri, 11 Oct 2013 17:54:20 GMT

I have a point shapefile of survey locations. I also have a table of data corresponding to said survey locations. The problem is that many of these survey points have been surveyed multiple times or with different equipment. Each point has a unique primary key, and in the table, there are multiple entries with that same primary key, but it has another column that serves as a secondary key.

Is there a way to join the point shapefile to the data table by primary key, but make it create duplicate coincident points for each unique secondary key?

Depends on the version of ArcGIS that you have. Prior to 10.1 there were only two ways to do this.

Alternative 1.
1. Use the Make Query Table tool, which requires that the points and the table reside in the same geodatabase. This output does not refresh from the sources without rerunning the Make Query Table tool and the tool output is not editable without being exported. The exported features are disconnected from the sources and have to be recreated to reflect updates to the sources.

Alternative 2.
2.a. Add two fields to the points that contain the X/Y coordinates
2.b. Use the Geometry Calculator to calculate the actual X/Y values to the fields in each point
2.c. Using the table in Table View Mode (in Model Builder this requires the use of the Make Table View tool) as the source table create a join of the points so that the points are the join table.
Now you have two options:

2.d.1. With the Join in place use the Make X/Y Event Layer tool to use the X/Y coordinate fields of the points to convert the table to an in-memory point feature class. All of the fields of the table and the points will be visible in this layer. Updates to the source points or table directly will become immediately visible in this layer. Edits done to the in-memory layer itself become immediately visible in the source table, but the source points are not editable through this layer.

2.d.2.a Add the same two fields to the table to hold the X/Y coordinate values of the points
2.d.2.b. Use the field calculator to transfer the X/Y coordinates of the points to the table records.
2.d.2.c. Remove the join.
2.d.2.d. Use the Make X/Y Event Layer tool to make the X/Y coordinates of the table into a in-memory point feature layer. Only the attributes of the table will be visible in this layer. Edits done to the source table are reflected in the in-memory points layer with a simple refressh, but the fields and features of the source points are not editable or visible through this layer.

2.e. Optionally export the in-memory layer to a permanent feature class to make it a permanent point feature class. This is disconnected from the original source tables and needs to be recreated to reflect updates to the sources.

At 10.1 and later you have a 3rd option.

Alternative 3 at 10.1.
3. Like the Make Query Table option, both the points and the table must reside in the same geodatabase. You can just create a join of the points layer without any special coordinate fields to the table using the points as the source and the table as the join. The one-to-many relationship is not visible or editable through the join. But you can export the features to a new point feature class. The export will duplicate records as necessary so that the one-to-many relationship will output a point feature class that has been converted to a one-to-one relationship. This export output is not connected to the source features and has to be recreated to reflect updates to the sources.

topic Re: Non-unique primary keys, creating coincident points based on secondary key in Geoprocessing Questions

Non-unique primary keys, creating coincident points based on secondary key

Re: Non-unique primary keys, creating coincident points based on secondary key