Object Collection data mapping

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

Field collection date extract

this data cannot be imported until CSPACE-4775 is done (v2.2)

field mapping

TMS table name	TMS field name	CSpace field name
ObjDates	DateText	fieldCollectionDate_dateDisplayDate
ObjDates	DateBegSearch	fieldCollectionDate_dateEarliestSingleYear
ObjDates	DateEndSearch	fieldCollectionDate_dateLatestYear
ObjDates	DayBegSearch	fieldCollectionDate_dateEarliestSingleDay
ObjDates	DayEndSearch	fieldCollectionDate_dateLatestDay
ObjDates	MonthBegSearch	fieldCollectionDate_dateEarliestSingleMonth
ObjDates	MonthEndSearch	fieldCollectionDate_dateLatestMonth
Objects	Dated	fieldCollectionDate_dateDisplayDate
Objects	DateBegin	fieldCollectionDate_dateEarliestSingleYear
Objects	DateEnd	fieldCollectionDate_dateLatestYear
ConXrefs	DisplayDate	fieldCollectionDate_dateDisplayDate
ConXrefs	DateBegin	fieldCollectionDate_dateEarliestSingleYear
ConXrefs	DateEnd	fieldCollectionDate_dateLatestYear

sql query

view: cs_fieldcollectiondates (refreshed 2012-03-12)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 d.dateText fieldCollectionDate_dateDisplayDate,
 (CASE WHEN d.dateBegSearch != '0' THEN d.dateBegSearch END) fieldCollectionDate_dateEarliestSingleYear,
 (CASE WHEN d.dateEndSearch != '0' THEN d.dateEndSearch END) fieldCollectionDate_dateLatestYear,
 d.dayBegSearch fieldCollectionDate_dateEarliestSingleDay,
 d.dayEndSearch fieldCollectionDate_dateLatestDay,
 d.monthBegSearch fieldCollectionDate_dateEarliestSingleMonth,
 d.monthEndSearch fieldCollectionDate_dateLatestMonth
FROM
 ObjDates d
 JOIN Objects o on d.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(d.eventType) in ('date collected', 'secondary collection date', 'tertiary collection date')
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.dated fieldCollectionDate_dateDisplayDate,
 (CASE WHEN o.dateBegin != '0' THEN o.dateBegin END) fieldCollectionDate_dateEarliestSingleYear,
 (CASE WHEN o.dateEnd != '0' THEN o.dateEnd END) fieldCollectionDate_dateLatestYear,
 null fieldCollectionDate_dateEarliestSingleDay,
 null fieldCollectionDate_dateLatestDay,
 null fieldCollectionDate_dateEarliestSingleMonth,
 null fieldCollectionDate_dateLatestMonth
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.dated IS NOT NULL OR o.dateBegin != '0' OR o.dateEnd != '0'
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 (CASE WHEN LEN(x.displayDate) > 0 THEN x.displayDate END) fieldCollectionDate_dateDisplayDate,
 (CASE WHEN x.dateBegin != '0' THEN x.dateBegin END) fieldCollectionDate_dateEarliestSingleYear,
 (CASE WHEN x.dateEnd != '0' THEN x.dateEnd END) fieldCollectionDate_dateLatestYear,
 NULL fieldCollectionDate_dateEarliestSingleDay,
 NULL fieldCollectionDate_dateLatestDay,
 NULL fieldCollectionDate_dateEarliestSingleMonth,
 NULL fieldCollectionDate_dateLatestMonth
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects o ON x.ID = o.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(r.ROLE) IN ('collector', 'secondary collector', 'tertiary collector')
 AND (x.dateBegin != 0 OR (x.displayDate IS NOT NULL AND len(x.displayDate) > 0))
;

notes

differences in ObjDates.EventType ('date collected', 'secondary collection date', 'tertiary collection date') are not important and do not need to be migrated, per Michael (12/15/11)
DONE: add new field pahmaFieldCollectionDate, to change fieldCollectionDate field into a repeating field

Field collection place extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	MTB_PlaceTermID	fieldCollectionPlace	reference to Place Authority
Objects	Provenance	fieldLocVerbatim
SiteObjXrefs	SiteID	fieldCollectionPlace	FK to Sites table to get MTB_PlaceTermID1; reference to Place Authority
SiteObjXrefs	Subsite1/Subsite2/Subsite3	fieldLocVerbatim
ObjGeography	MTB_PlaceTermID	fieldCollectionPlace	reference to Place Authority
ThesXrefs	TermID	fieldCollectionPlace	reference to Place Authority

sql query

view: cs_fieldcollectionplaces (refreshed 2012-03-12)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.MTB_PlaceTermID fieldCollectionPlace,
 o.provenance fieldLocVerbatim
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND (o.MTB_PlaceTermID IS NOT NULL OR o.provenance IS NOT NULL)
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 s.MTB_PlaceTermID1 fieldCollectionPlace,
 COALESCE(x.subsite1, '') + (CASE WHEN x.subsite1 IS NOT NULL AND x.subsite2 IS NOT NULL THEN ', ' ELSE '' END) + COALESCE(x.subsite2, '') + (CASE WHEN x.subsite3 IS NOT NULL THEN ', ' + x.subsite3 ELSE '' END) fieldLocVerbatim
FROM
 SiteObjXrefs x
 JOIN Objects o ON x.objectID = o.objectID
 JOIN Sites s ON x.siteID = s.siteID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND s.MTB_PlaceTermID1 IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 g.MTB_PlaceTermID fieldCollectionPlace,
 NULL fieldLocVerbatim
FROM
 ObjGeography g
 JOIN Objects o ON g.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND g.MTB_PlaceTermID IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.termID fieldCollectionPlace,
 NULL fieldLocVerbatim
FROM
 ThesXrefs x
 JOIN ThesXrefTypes t ON x.thesXrefTypeID = t.thesXrefTypeID
 JOIN Objects o ON x.ID = o.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND x.thesXrefTableID = 346
 AND lower(t.thesXrefType) IN ('archaeological site', 'card header', 'provenience')
;

notes

NOTE: fieldCollectionPlace values should be used to construct refName for PlaceAuthority; we may need a mapping table in order to do this?

Field collector extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ConXrefs	constituentID	fieldCollector	reference to Person or Organization

sql query

view: cs_fieldcollectors (refreshed 2012-03-12)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.constituentID fieldCollector
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects o ON x.ID = o.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND  o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(r.ROLE) IN ('collector', 'secondary collector', 'tertiary collector')
;

notes

Field collection note extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ObjDates	Remarks	fieldCollectionNote	pertains to fieldCollectionDate
SiteObjXrefs	Remarks	fieldCollectionNote	pertains to fieldCollectionPlace
ThesXrefs	Remarks	fieldCollectionNote	pertains to fieldCollectionPlace
ConXrefs	Remarks	fieldCollectionNote	pertains to fieldCollector
TextEntries	textEntry	fieldCollectionNote	where textTypeID is 1, 8, 14, 58, 90, or 102

sql query

view: cs_fieldcollectionnotes (refreshed 2012-05-14)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 'Field collection date: 'fieldCollectionNote_prefix,
 d.remarks fieldCollectionNote
FROM
 ObjDates d
 JOIN Objects o on d.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(d.eventType) IN ('date collected', 'secondary collection date', 'tertiary collection date')
 AND d.remarks IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 'Field collection place: 'fieldCollectionNote_prefix,
 x.remarks fieldCollectionNote
FROM
 SiteObjXrefs x
 JOIN Objects o ON x.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND x.remarks IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 'Field collection place: 'fieldCollectionNote_prefix,
 x.remarks fieldCollectionNote
FROM
 ThesXrefs x
 JOIN ThesXrefTypes t ON x.thesXrefTypeID = t.thesXrefTypeID
 JOIN Objects o ON x.ID = o.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND x.thesXrefTableID = 346
 AND lower(t.thesXrefType) IN ('archaeological site', 'card header', 'provenience')
 AND x.remarks IS NOT NULL AND x.remarks != ''
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 'Field collector: 'fieldCollectionNote_prefix,
 x.remarks fieldCollectionNote
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects o ON x.ID = o.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(r.ROLE) IN ('collector', 'secondary collector', 'tertiary collector')
 AND x.remarks IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 (CASE WHEN t.textTypeID = 8 THEN 'NAGPRA site concordance note: ' WHEN t.textTypeID IN (14, 58, 102) THEN tt.textType + ': ' ELSE '' END) fieldCollectionNote_prefix,
 t.textEntry fieldCollectionNote
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID IN (8, 14, 58, 90, 102)
;

notes

NOTE: 'fieldCollectionNote_prefix' should be prepended to 'fieldCollectionNote' prior to import (this can't be done here because some fields are type TEXT which cannot be concatenated in SQL)
NOTE: multiple notes per object should be concatenated into single (non-repeating) field fieldCollectionNote, separated by line breaks