Object Dimensions data mapping

This page contains information about PAHMA's data migration of Object Dimensions.
See parent page on PAHMA data migration ETL work.

Dimensions extract

There are about 38,936 dimension values in the structured subschema in TMS. These are extracted with their metadata and inserted into the CollectionObject records in CSpace.

There are about 49,697 dimension strings in freetext dimension fields in TMS. These need to move as well; the process of extraction and cleanup is described at:

http://wiki.collectionspace.org/pages/viewpage.action?pageId=91488272

The intent is to create two data streams (files) in identical formats that can be combined and loaded in a single step.

field mapping

TMS table name	TMS field name	CSpace field name	notes
Dimensions	dimensionID	dimensionID	carried over for debugging, not imported
DimItemElemXrefs	ID	Objectid
DimItemElemXrefs	Description	measuredPart
DimensionTypes	DimensionType	dimension
		valueQualifier	No values for this in TMS (e.g. "ca.", "approx.")
Dimensions	Dimension	value
DimensionUnits	UnitName	measurementUnit
DimItemElemXrefs	Description	dimensionNote	but also includes TextEntry from TextEntries when present
DimItemElemXrefs	DisplayDimensions	dimensionSummary	contains "original" string value of dimension

sql query

view:

SELECT
  d.dimensionID dimensionID,
  x.ID  Objectid,
  (CASE WHEN LEFT(x.Description,2) != 'WI' THEN x.Description ELSE '' END) measuredPart,
  LOWER(t.DimensionType) dimension,
  ' '   valueQualifier,
  (CASE WHEN d.PrimaryUnitID = 9 THEN d.dimension ELSE ROUND(d.Dimension * u.ConversionFactor,3) END)  value,
  LOWER(u.UnitName) measurementUnit,
  (CASE WHEN LEFT(x.Description,2)  = 'WI' THEN x.Description+CONVERT(VARCHAR,e.TextEntry) ELSE CONVERT(VARCHAR,e.TextEntry) END) dimensionNote,
  x.DisplayDimensions dimensionSummary
FROM DimItemElemXrefs x
LEFT OUTER JOIN Objects o on x.ID = o.ObjectID
LEFT OUTER JOIN TextEntries e ON e.ID = o.ObjectID
  AND e.tableID = 108
  AND o.IsVirtual = 0
  AND o.IsTemplate = 0
  AND e.TextTypeID = 91
JOIN Dimensions d on x.DimItemElemXrefID = d.DimItemElemXrefID
JOIN DimensionUnits u on u.UnitID = d.PrimaryUnitID
JOIN DimensionMethods m on m.MethodID = x. MethodID
JOIN DimensionTypes t on t.DimensionTypeID = d.DimensionTypeID
WHERE
 d.Dimension > 0
ORDER BY d.dimensionID,d.Rank
;

notes

Note that the TextEntry blob is being concatenated with the existing Description and placed in dimensionNote. (There are a few cases where this note contains
a dimension at variance with the other dimension data
The value for measuredPart is being set on the basis of the Description field, with the exception of cases where this field begins with "wi". This heuristic allows values beginning with "with" or "width" to stay (appropriately) as description (there are about 300 cases) while the rest of the values become (also mostly correctly) part descriptions. Yes, it is a bit of a hack.
The units and dimension values are converted to lowercase, as seems to be the CSpace convention.
I can't for the life of me get the dimensionNote to not be (null).

A few example rows

dimensionID	Objectid	measuredPart	dimension	value	measurementUnit	dimensionNote	dimensionSummary
3	11403		height	4.0000000000000	inches	(null)	4 x 5 in
4	11403		width	5.0000000000000	inches	(null)	4 x 5 in
5	11404		height	4.0000000000000	inches	Measured numbers: 204, 206	4 x 5 in
256	81692		height	78.0000000000000	inches	Measured numbers: 204, 206	78 in
257	81693		height	37.0000000000000	inches	(null)	37 in
258	81694		height	333.0000000000000	centimeters	(null)	333 cm
5842	449454		recording speed	110.0000000000000	recording speed	(null)	110 speed, 204 secs
5843	449454		duration	204.0000000000000	seconds	(null)	110 speed, 204 secs
5844	449455		recording speed	110.0000000000000	recording speed	(null)	110 speed, 199 secs
21734	38533		height	3.6000000000000	centimeters	(null)	3.6 cm (1 7/16 in)
21737	486208	c	height	18.2000000000000	centimeters	(null)	18.2 x 1.2 cm (7 3/16 x 1/2 in)
21740	486208	c	diameter	1.2000000000000	centimeters	(null)	18.2 x 1.2 cm (7 3/16 x 1/2 in)
21741	486208	b	height	16.8000000000000	centimeters	(null)	16.8 x 1.2 cm (6 5/8 x 1/2 in)
21744	486208	b	diameter	1.2000000000000	centimeters	(null)	16.8 x 1.2 cm (6 5/8 x 1/2 in)
21745	486208	a	height	10.1000000000000	centimeters	(null)	10.1 x 1.2 cm (4 x 1/2 in)
21748	486208	a	diameter	1.2000000000000	centimeters	(null)	10.1 x 1.2 cm (4 x 1/2 in)
21749	38534		height	3.3000000000000	centimeters	(null)	3.3 cm (1 5/16 in)