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

notes

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