PAHMA Storage Location data mapping

Overview

PAHMA's objects are stored in a number of buildings on campus and soon in a new facility in Richmond. Besides the migration of PAHMA's collection data from TMS to CollectionSpace, the musuem will be undertaking a substantial move and reorganization of objects in 2012. Tracking objects for the move will be facilitated through the use of bar code scanners which will recognize 1D and 2D bar codes placed on locations and objects.

The location of objects is described via a "ragged hierarchy", up to 5 levels deep, which generally refer to the set of containers in which an object is located. Normally the containers are fixed in space (e.g. buildings) but note that there is a special case, a "crate", which is understood to be an object that can move, and when it does, all the containers and objects therein are constrained to move with it.

In addition to physical locations, the Storage Location Hierarchy also contains some "non-physical locations" which (redundantly with other fields?) indicate the status of objects. These top-level locations include values such as "On Loan", "Returned to owner", etc.

Use Cases

PAHMA needs to be able to specify and retrieve the location of items in a hierarchical manner. That is, the location hierarchy for this institution needs to allow users to "drill down" into the 5-level hierarchy for various purposes.

Also, the human-readable value for locationString (CSpace:displayName) will be the value encoded in 2D barcodes and therefore needs to be regarded as an inviolate key.

• "There was a small fire in Kroeber Hall, room 20A, and the sprinklers went off.
  What was in there so we can triage and prioritize the treatment for the objects in that room?"
• "What's in yellow safe in Kroeber 20A?"
• "Crate #12 in Hearst Gym, room 30 is getting moved to Marchant 276. Let's make
  sure that all the objects in that container get their locations changed in the database when that happens!"
• Zap the crate 2D barcode with the barcode reader, then zap all the 1D barcodes of items in the crate. Done!

There are currently about 32K locations specified in the database, including some inactive ("legacy") locations. The table below gives some examples of the names of the nodes in the database and how these node values are rendered as a "display name":

Examples from the PAHMA hierarchy

Three records from the TMS

Display Names

Display Names will be used as the primary key for looking up locations; these values will be used to generate 2D bar codes.

At the moment, in TMS, Display Names cannot be deterministically parsed to reproduce the 5-level path. (That is, not every Display Name has the four commas that would be expected to distinguish the five levels. Nevertheless, reading from right to left, each comma does delimit a legitimate and identifiable "parent location".

• Marchant, 276, Pallet Crate 1, 58
• Kroeber, 20A, Yellow safe Drawer 2
• Marchant, 276, 5, 4

Design and Implementation Considerations

It's been pointed out that the 5-level hierarchy is really a simplification of a much deeper hierarchy implicit in the data. A richer version of the hierarchy would distinguish "Drawers" from "Aisles" and "Crates" in the way that "Sites" and "Rooms" are distinguished in the current schema (Michael's working on a 19 level hierarchy that captures this). Further evaluation will inform us whether a more thorough repartitioning of the existing data representation would be helpful, but at the moment no requirement for this modification has been specified.

CSpace supports the representation of hierarchical locations via BT/NT relationships, and at least at first glance this might be the way to implement the Location Authority's hierarchy, though thought and effort will be required to make this work in the UI.

In theory, it would be appropriate to generate Display Names via an algorithmic (and reversible) concatenation of the locations path in the hierarchy. In this case, the Display Name key would be locked and not directly editable -- editing would be done on the underlying hierarchy. This would also make it possible to support the "crate movement functionality". Caution would be needed in permitting editing, as a change to an upper level of the hierarchy would render all the barcodes for locations beneath that level unrecognizable. This would seem to dictate that the values for nodes in the hierarchy have an attribute (perhaps "immutable") for locations that could not or would not change (e.g. "buildings", or "aisles").

Also, it is necessary to create Storage Location Authority records for the Crate objects in TMS, which become Storage Locations in CSpace. This is done via a separate query based on the TMS Crates table which creates additional records in the same format as those pulled from the Locations Table.

For crates, their immediate parent is the displayName of the Location record they were keyed to. For immovable locations, their parent is Site+Room of their location.

Storage Location extract

field mapping

sql query

view:  cs_storagelocations1  (refreshed 2012-04-26)

SELECT
  'sl'+ltrim(str(l.LocationID)) shortIdentifier,
  (case when l.active = 1 then 'accepted' else 'rejected' end) locationStatus,
  l.LocationString displayName,
  l.Site+', '+l.Room Parent,
  l.Site Site,
  l.Room Room,
  l.UnitType UnitType,
  l.UnitNumber UnitNumber,
  l.UnitPosition UnitPosition,
  (case when l.Description = '' then '' else 'Description: '+l.Description end) locNote
FROM
  locations l
;

notes

• the five levels of the hierarchy will need to be mapped into some sort of CSpace hierarchy. The BT/NT relationships may be sufficent, but this remains to be demonstrated. At any rate the schema (and UI?) need to be modified to hold and render these values.
• there is probably some cleaning that can be identified and performed as part of this migration. At least the following should be done, either as part of this SQL or as part of other ETL:
  • "Dedupe" the locations. Since Crates are now locations in their own right, they should be checked against the locations drawn from the Locations table. There seem to be some cases where Crates have been represented as locations in the Locations table.
  • There are cases where the displayName for a location includes the name of the crate. Some of these cases are handled (see note below),  but more investigation is warranted.
  • More TBD!

field mapping

sql query

view:  cs_storagelocations2  (refreshed 2012-04-26)

SELECT
  'cr'+ltrim(str(c.crateID)) shortIdentifier,
  (case when c.active = 1 then 'accepted' else 'rejected' end) locationStatus,
  (case when CharIndex(replace(c.CrateNumber,'-',', '),l.LocationString) > 0 then l.LocationString else l.LocationString+', '+c.CrateNumber end) displayName,
  l.LocationString Parent,
  l.Site Site,
  l.Room Room,
  l.UnitType UnitType,
  l.UnitNumber UnitNumber,
  l.UnitPosition UnitPosition,
  'CrateNumber: '+' '+c.CrateNumber locNote
FROM
  crates c
  JOIN Locations l ON l.LocationID = c.LocationID
;

notes

• In some cases the crateNumber is already included in the locationString field, in which case there is no need to append it again to the display name. A little bit of tricky SQL checks for this case.
• Nevertheless, the crateNumber is being kept as locatNote, just in case.