Tips and Tricks for linking data in ArcGIS

There are two basic types of joins in GIS tables and geo-data: spatial and relational (based on table IDs). Spatial join is the key concept in GIS and that is what sets all GIS technologies apart from other relational databases. Here we start with performing relational joint of list of zipcodes with names to the US zipcode shapefile.

Relational Join

To join table to shapefile based on ID (zipcode) the data type in the column you are planning to join should be the same. Let’s check on data type in Zip code shapefile. Right click on shapefile, select properties and look under Fields tab. The format of the data is Text and size is 5 digits.

Lets check on data type in our Excel table with names. On the right site open ArcCatalog, find CSV table and right click on it to show Fields – it is a number (Long  Integer). We need to change that to match with Shapefile zipcode ID. Also note that table has omitted leading zeroes in some zipcodes.

On top panel in ArcMap click on Editor drop-down and start editing. You will see to files in the project. Note, that you cannot edit CSV file in ArcMap directly. We need to create a new geodabase and working in ArcMap adding more data to it and editing tables/shapefiles.

Let’s create a new geodatabase in ArcCatalog, and then import CSV table in that database.

Open newly imported table in geodatabase and Add new Field. Make the field Text and exactly 5 digits size. Simply copy the numeric ZipCode field to Zip field – it will convert the forma to txt.

Now to fill leading zeroes on some zipcodes, create another Text field with 5 digit length and using Field Calculator Python create calculation as shown below using .zfill() function.

You can now delete all other temporary zipcode fields (right click/ Delete).

Now import shapefile with North America zipcodes into database. Bring the whole database to the map, setup needed symbology and labeling on the zipcode shapefile. Now you can create a Relate form NAzipcodes shapefile and table with names. This is going to be one-to-many relate (one zipcode shape can have many people living in it). This relate has shown below.

The resultant geodabase can be shared on the ArcGIS Online or on ArcGIS Server, however, the relates does not persist on the server. To improve this, lets create a relationship class within geodatabase.  http://resources.arcgis.com/en/help/main/10.1/index.html#/Relationship_class_properties/004t00000004000000/

The resultant lookup table looks good on the single instance on ArcMap Desktop you created it on, but what if you need to share your work on the ArcServer, ArcGIS online, as webmap?

Problem with sharing one-to-many relationships to ArcServer persists, I still not found best solution for geodatabase. Here is work around – create a new point shapefile from centroids of the zipcodes and link those XY coordinates to Staff Names with Zip codes file:

1. Create Centroid point shapefile from polygon shapefile – refer to this blog post

2. Join Zip codes from point shapefile to the Geodatabase table based on zip code.

3. Clean joints if zipcodes have not matched: Export joint table as DBF and open it in Excel. You can manually edit unmatched zips – usually those are the ones that special P.O. Office zipcodes. Delete unused and duplicate columns with zipcodes and IDs.  Here is a trick to look up coordinates for the point in Google Maps online – enter the place name or address in Google search and the URL info contains Lat/Long in decimal degrees (in this case Lat=33.58 and Long=-91.79):

Now, see that you cannot save the table you worked in Excel as DBF anymore – bummer!

Just go ahead,  save as latest Excel file format. Close Excel. Then bring that file to the ArcMap project as XY data. You will see only zipcode central points which have at least one name associated with them. Export data as shapefile and add to the project. Adjust symbology to your liking.

This map can be easily shared on ArcGIS server or ArcGIS Online as map service. You can now view that map in ArcExplorer – free ESRI GIS data viewer, build a map in SharePoint,  or even  access it directly on the REST server. Individual Map layers can be turned on/off and map is zoom able can be added to other web map services.

	Map service is easily viewable through build in ArcGIS JavaScript API which comes with any ArcGIS server  installation. Map is zoom able, but no information behind the layers is viewable or accessible. Nothing to install on user side. The links only accessible by the from the organization’s computers, cannot be shared outside of the organization.
	access ArcGIS Server data directly through ArcMap online – the base map from ESRI is also shows, not possible to turn layers on/off by unregistered users, table under data is shown. Nothing to install on user’s side. The links only accessible by the from the organization’s computers, cannot be shared outside of the organization.
	Quick map creation with the published map services is possible in the organization Share Point site where ESRI Plug-in is enabled. Anybody from organization can utilize a single or multiple map services from the server, layers can be turned on/off and maps are zoom able. There is no easy way to see data tables underneath of map services. Requires ESRI maps for SharePoint installed on the organization Share Point installation.
	most GIS functionality is offered through ArcExplorer – free GIS data viewer/ simple editor. Data table underneath the map is viewable and layers can be separately turned on/off. Requires ArcExplorer to be installed on the user PC. Link to the ArcGIS server on premises only work from organization’s registered machines or through VPN.