We look at using the Load command to add new records to a layer in this post.  Sometimes it’s a far better option than a spatial join or a merge.

We recently had a summer scholarship student looking at some mapping work for the Department of Conservation (DOC).  Amongst the myriad of things they look after (1/3 of the NZ landmass, all those critters) DOC is also responsible for keeping track of the movements of exotic fish.  So when someone wants to transfer some rainbow trout to a pond in Northland, they need to gain permission from DOC and MPI before doing so.  This also means keeping track of all the paperwork that goes into granting those permissions.  In the spirit of a picture telling a 1,000 words, they were interested in mapping all these permissions as an alternative way to mange the data.  What a great idea!

After several weeks of work, the end result was a webmap that DOC can use to access these data and focus on whatever areas they’re interested in:

Each point represents one of the permissions and can be clicked on to get the details for each.

Our student got started by having a look at the data DOC provided.  Not surprisingly, the data were kept in a table-based database with links to PDFs and spreadsheets in some cases.  For a few, there were only paper documents.  Buried deeply in all the data were several ways of locating the permissions.

In the most fortuitous cases, x and y coordinates were listed.  In other cases, just a place name could be found.  In still other cases, the only spatial data was around the land section details.  We’ll deal with them one by one and see how they ended up as points on a map above.  At the end of this post we’ll see how three separate layers were merged into one using a handy tool.

Permissions with Coordinates

This was the best case with these data, but they weren’t quite as straightforward as we had hoped.  As an example, we found coordinates like this: NZMS 260 M36-671-293 (Ed. – not real coordinates).  These spreadsheets looked something like this (I’ve blanked out the Holders and some other vital details):

Those of you familiar with eastings and northings in New Zealand Transverse Mercator or Map Grid would look a bit askance at these – they don’t look much like coordinates.  They are, but they’re from a much older mapping system.  NZMS 260 is an older version of 1:50,000 scale topo maps.  The country was broken down into 329 tiles with a letter and two numbers to identify it.  Around here, M36 was the map that covered Lincoln and parts of Banks Peninsula.

Within each sheet, two triplets of numbers would allow you to locate a point to within a hundred metres or so. For instance, in the old NZMG system, the rough coordinates for the Forbes Building would be Easting 2467100 Northing 5729300 (these are in meters).  Since this point is on map sheet M36, an alternative way to identify this point is as NZMS 260 M36-671-293.  On the map sheet, the leading 24 and 57 won’t change so the 671 and 293 are shorthand for that point.  With a little bit of practice this becomes easy to use (provided you’ve got the maps) but the bigger problem is that ArcMap isn’t going to like that map reference.  (The current mapping system is set up in the same way.) So how to convert the NZMS map references to something more useful (i.e. current), like NZTM?

LINZ to the rescue – they provide a coordinate conversion page where you can input a series of coordinates and get their NZTM equivalents out the other end.  These could be added to the spreadsheet and then easily mapped using the Display X Y Data option.  That was the easy part.

Permissions with Place Names

I was hoping this would also be an easy one…but… (Ed. – a common refrain in GIS.)  There’s a layer from LINZ called the gazetteer which lists a plethora of place names.  I was hoping it would be a quick table join to bring the spreadsheet and the points together but there weren’t many useable matches so most of these had to mapped manually.  Our poor student is blind now…

Permissions with Legal Descriptions

The remaining points’ only spatial information was tied to the legal descriptions of property sections.  All sections across NZ have a title and a legal description which is sort of a unique identifier.  Unfortunately, there’s not much rhyme or reason to how legal descriptions get assigned, so unlike the map grid references above, we’re pretty much starting from scratch.  There are legal description layers on the LINZ Data Service but try as we might, there were enough discrepancies between legal description texts that we got no useful matches.  With just over 30 records having only this limitation, what to do now?  Here’s an image of the spreadsheet to give you an idea of what we were dealing with (with holder names blocked out).  The “Location” column has the legal descriptions:

There is one mapping software application that allows you to search on legal descriptions – QuickMap, which is available on the computers in C4.  With both ArcMap and QuickMap open on the desktop, I was able to read off the legal descriptions from the spreadsheet and then search from them in QuickMap, as shown below:

Then, in ArcMap, I could place the point in the correct place.  There was no way to get around having to do this record by record, but happily there weren’t too many.

Pulling the Data Together

So now we’ve got three separate layers of points mapped in three different ways.  The final challenge is to bring them all together into one layer so we can produce a map for DOC.  Normally, one could do a spatial join, like Union, or perhaps use a command like Merge.  The problem was that each of my layers had a different set of attributes (a different schema in database terms).  If using something like Merge I would end up with possibly two or three different versions of similar attributes.  This was a good place to use the Load tool. With this tool, one of your layers is the target and has the attribute names that you want to maintain.  The other layers are “loaded” into that one and differently named attributes can be mapped to the target ones.  This allows you to retain the important ones, overlook those that aren’t needed and keep everything consistent.

Load only works with feature classes in a geodatabase and shapefiles.  To load addition data into an existing layer, start by right-clicking the layer name (in Catalog) and choose Load > Load Data…  A wizard then steps up through the process (what would we do without wizards?)

This first window just summaries what the tool does

Here you set the target layer:

Next, set the layer to be loaded:

(We’ll have to talk about subtypes other time.)

This window allows us to map the attribute values from the source layer to the target layer:

Next, confirm that you want to load all the data or only that that are selected:

A final summary and off we go:

Once completed, the new records are added directly into the target layer with the attributes matching up nicely. The benefit here is that the attribute names don’t have to be the same yet we can still match them up.  This would cause us some problems when using a spatial join or a merge.

So two (hopefully) useful things in this post – mapping points in a spreadsheet that have various different types of spatial data and use of the Load tool to standardise attributes.  All those points on the final map look good but don’t really indicate the work that went into getting them in the right place.  Perhaps this is a good example of not paying attention to that man behind the curtain.

(Ed. – we do aim to be apolitical here at the GIS Blog, but occasionally we just can’t help ourselves.)