Using Joins During Conversions
By: Brett Heist and Alex Finch
***Note: Be mindful of the order of join instructions. X to Y produces a
different result than Y to X.
Steps:
1. Add the current Leasehold and new parcels to map
2. Delete the Possible HBP entries from leasehold
a. Selection > Select by Attributes > Enter "LEASE_STAT" = 'POSSIBLE
HBP' > Delete Selected in Table
3. Run a dissolve on the parcel layer using the PAR_NUM field naming the
output ParcelDissolve and add the this to the data frame
a. Right Click Layer > Data in the drop down > Export Data
b. ArcToolbox > Data Management > Generalization > Dissolve
4. In order to know later in the process how many iterations of a few steps we
will need to do, we will run a quick summarize table. Create a new field in
the leasehold table and call it SUM. Right click the PAR_NUM field in the
leasehold and select summarize. Select the SUM table in the popup and
check the box variance. Run the summary table saved as a dBASE and
minimize it until step 19. The highest number is the highest number of
duplicates.
5. To ensure we keep unique values unique we will now generate our own field
giving each entry a unique number. Create a new field (name it UniqueID)
and enter this code into the pre-code block of the field calculator in python:
rec=0
def autoIncrement():
global rec
pStart = 1
In the second
pInterval = 1
if (rec == 0):
the quotes and
6.
rec = pStart
else:
rec += pInterval
return rec
box enter
autoIncrement() without
hit okay.
7. Export the leasehold attribute table out to a dBase table titled
LeaseholdTable
a. Right Click Layer > Data in the drop down > Export Data
b. Ensure the file is being saved as dBASE type
8. Join the dissolved parcel layer to the newly created dbase table using the
PAR_NUM* field.
a. Right Click Layer > Joins and Relates in the dropdown > Join
9. Run the Select by Attributes query pictured below and export the output to a
new shapefile (Not table!) and call it Converted adding the output to the
data frame.
a. Right Click Layer > Export > Export Selected
b. There will be two PAR_NUM fields generated, one from the Parcels and
one from the Leasehold. Use PAR_NUM field with the asterix *. This
will indicate the joined field.
[Link] join from the parcel layer
a. Right Click Layer > Joins and Relates in drop down > Remove Joins
[Link] the Leasehold table to the newly created Converted shapefile to the
using the Unique ID field.
[Link] table and scroll over to the joined Unique ID field for the shapefile and
sort descending, select all records with non-null value for the PAR_NUM field
and delete those.
a. Because joins cannot have a one-to-many relationship, the duplicate
entries in the PAR_NUM field on parcels with multiple leases will not
join. Only the first entry with a duplicate will successfully join. With
this and the next few steps we will do multiple joins, deleting the
successful matches successively until no duplicates remain and we
have multiple tables with all unique values. Be sure to check that the
number of entries you are deleting is matching to the number of
entries in the newly created Converted shapefile.
[Link] join and save edits, then stop editing the table
[Link] the dissolved parcel layer to the leasehold table using the PAR_NUM field,
scroll over and sort the PAR_NUM* field descending and select all records with
a value for the PAR_NUM* field.
[Link] those out to a new shapefile and call it Converted2
[Link] join from the dissolved parcel layer
[Link] the leasehold dBase table to the Converted2 shapefile using the
OBJECTID field
[Link] to the PAR_NUM field and sort descending, select all records with a
value in that field and delete.
[Link] edits, stop editing and remove join.
[Link] these steps for the highest count found in the summarize step (step
4), resulting in shapefiles Converted1 through Convertedx.
[Link] rows will be leases that contain parcel numbers not found in the
parcel layer, and therefore must be manually checked. Export these to a new
table to work with.
[Link] and paste the results from numbered shapefiles (ex. Converted2) into
the original to create a master table.
[Link] the master table create a check ratio for mapped acres/calculated acres.
[Link], create a possible HBP layer.