Darwin Core to Specify Mapping

From ETaxonomy

Jump to: navigation, search

The first entry under each term is for a "generic" installation of Specify. The second is for the HUH version.

Following TDWG's Darwin Core Terms quick-reference guide

Record-level Terms

dcterms:type

"PhysicalObject"

select case when f.PrepMethod "Drawing" then "StillImage" when "Photograph" then "StillImage" else "PhysicalObject" end from fragment f where f.FragmentID=?

institutionCode

select ifnull(i.Uri, i.Code) as institutionCode from collectionobject co join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where co.CollectionObjectID=?

select ifnull(i.Uri, i.Code) as institutionCode from fragment f join collection c on f.CollectionMemberID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where f.FragmentID=?

collectionCode

select c.Code as collectionCode from collectionobject co join collection c on co.CollectionID=c.CollectionID where co.CollectionObjectID=?

select f.Text1 as collectionCode from fragment f where f.FragmentID=?

collectionID

Needs lookup in biocol, or other GUID scheme for collections.
HUH:
- select 'http://biocol.org/urn:lsid:biocol.org:col:15406' from fragment where text1 = 'A';
- select 'http://biocol.org/urn:lsid:biocol.org:col:15631' from fragment where text1 = 'GH';
- select 'http://biocol.org/urn:lsid:biocol.org:col:15408' from fragment where text1 = 'AMES';
- select 'http://biocol.org/urn:lsid:biocol.org:col:15407' from fragment where text1 = 'ECON';
- select 'http://biocol.org/urn:lsid:biocol.org:col:13199' from fragment where text1 = 'FH';
- select 'http://biocol.org/urn:lsid:biocol.org:col:15868' from fragment where text1 = 'NEBC';

basisOfRecord

"PreservedSpecimen"

"PreservedSpecimen"

Occurrence

occurrenceID

select ifnull(co.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":collectionobject":", co.CollectionObjectID)) as occurrenceID from collectionobject co join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where co.CollectionObjectID=?

select ifnull(co.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":fragment:", f.FragmentID)) as occurrenceID from fragment f join collectionobject co on f.CollectionObjectID=co.CollectionObjectID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where f.FragmentID=?

catalogNumber

select co.CatalogNumber as catalogNumber from collectionobject co where co.CollectionObjectID=?

select ifnull(f.CatalogNumber, ifnull(p.CatalogNumber, pp.CatalogNumber)) as catalogNumber from fragment f join preparation p on f.PreparationID=p.PreparationID left join preparation pp on p.ParentID=pp.PreparationID where f.FragmentID=?

occurrenceRemarks

select co.Remarks as occurrenceRemarks from collectionobject co where co.CollectionObjectID=?

select co.Remarks as occurrenceRemarks from collectionobject co where co.CollectionObjectID=?

recordNumber

select co.FieldNumber as recordNumber from collectionobject co where co.CollectionObjectID=?

select co.FieldNumber as recordNumber from collectionobject co where co.CollectionObjectID=?

recordedBy

select concat(ifnull(concat(a.FirstName, " "), ""), a.LastName) as recordedBy from agent a join collector cl on a.AgentID=cl.AgentID join collectingevent ce on cl.CollectingEventID=ce.CollectingEventID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select ifnull(av.Name, concat(ifnull(concat(a.FirstName, " "), ""), a.LastName)) as recordedBy from agent a left join agentvariant av on a.AgentID=av.AgentID and av.VarType=4 join collector cl on a.AgentID=cl.AgentID join collectingevent ce on cl.CollectingEventID=ce.CollectingEventID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on f.CollectionObjectID=co.CollectionObjectID where f.FragmentID=?
- update temp_dwc_search left join fragment on temp_dwc_search.temp_identifier = fragment.identifier left join collectionobject on fragment.collectionobjectid = collectionobject.collectionobjectid left join collectingevent on collectionobject.collectingeventid = collectingevent.collectingeventid left join collector on collectingevent.collectingeventid = collector.collectingeventid left join agentvariant on collector.agentid = agentvariant.agentid set temp_dwc_search.collector = trim(concat(agentvariant.name,' ',ifnull(collector.etal,))) where agentvariant.vartype = 4;

individualID

select p.SampleNumber as individualID from preparation p join collectionobject co on p.CollectionObjectID=co.CollectionObjectID where co.CollectionObjectID=?

(no equivalent)

individualCount

select p.CountAmt as individualCount from preparation p join collectionobject co on p.CollectionObjectID=co.CollectionObjectID where co.CollectionObjectID=?

(no equivalent)

sex

(varies according to discipline)

select f.Sex as sex from fragment f where f.FragmentID=?
- -- fit terms to googlecode DarwinCore recommended list http://code.google.com/p/darwincore/wiki/Occurrence
- update temp_dwc_search set sex = 'undetermined' where sex = 'not determined';

reproductiveCondition

(varies according to discipline)

select f.Phenology as reproductiveCondition from fragment f where f.FragmentID=?

preparations

select distinct pt.Name as preparation from preptype pt join preparation p on pt.PrepTypeID=p.PrepTypeID join collectionobject co on p.CollectionObjectID=co.CollectionObjectID where co.CollectionObjectID=?

select f.PrepMethod as preparation from fragment f where f.FragmentID=?

otherCatalogNumbers

select co.AltCatalogNumber as otherCatalogNumber from collectionobject co where co.CollectionObjectID=?

(no equivalent)

associatedReferences

select concat(
         concat(
          case when au1.AuthorID is not null and au2.AuthorID is not null and au3.AuthorID is not null
            then concat(ifnull(concat(a1.FirstName, " "), ""), a1.LastName, ", ", ifnull(concat(a2.FirstName, " "), ""), a2.LastName, " et. al.  ")
          when au1.AuthorID is not null and au2.AuthorID is not null
            then concat(ifnull(concat(a1.FirstName, " "), ""), a1.lastName, ", ", ifnull(concat(a2.FirstName, " "), ""), a2.Lastname, ".  ")
          when au1.AuthorID is not null
            then concat(ifnull(concat(a1.FirstName, " "), ""), a1.LastName, ".  ")
          else "" end, ".  "),
          ifnull(concat(rw.WorkDate, ".  "), ""),
          concat(rw.Title, ".  "),
          ifnull(concat(j.JournalName, ".  "),
                 ""),
          concat(
            ifnull(concat(rw.Volume, ":"), ""),
            ifnull(rw.Pages,"")),
          case when rw.Volume is null and rw.Pages is null then "" else "." end) as associatedReference
from referencework rw left join journal j on rw.JournalID=j.JournalID
  join collectionobjectcitation cit on cit.ReferenceWorkID=rw.ReferenceWorkID
  join collectionobject co on cit.CollectionObjectID=co.CollectionObjectID
  left join author au1 on rw.ReferenceWorkID=au1.ReferenceWorkID
  left join agent a1 on au1.AgentID=a1.AgentID
  left join author au2 on rw.ReferenceWorkID=au2.ReferenceWorkID
  left join agent a2 on au1.AgentID=a2.AgentID
  left join author au3 on rw.ReferenceWorkID=au3.ReferenceWorkID
  left join agent a3 on au1.AgentID=a3.AgentID
where au1.OrderNumber=1 and au2.OrderNumber=2 and au3.OrderNumber=3 and co.CollectionObjectID=?

select concat(
         concat(
          case when au1.AuthorID is not null and au2.AuthorID is not null and au3.AuthorID is not null
            then concat(ifnull(concat(a1.FirstName, " "), ""), a1.LastName, ", ", ifnull(concat(a2.FirstName, " "), ""), a2.LastName, " et. al.  ")
          when au1.AuthorID is not null and au2.AuthorID is not null
            then concat(ifnull(concat(a1.FirstName, " "), ""), a1.lastName, ", ", ifnull(concat(a2.FirstName, " "), ""), a2.Lastname, ".  ")
          when au1.AuthorID is not null
            then concat(ifnull(concat(a1.FirstName, " "), ""), a1.LastName, ".  ")
          else "" end, ".  "),
          ifnull(concat(cit.Text2, ".  "), ""),
          concat(rw.Title, ".  "),
          ifnull(concat(j.JournalName, ".  "),
                 ""),
          ifnull(concat(cit.Text1, ".  "), "")) as identificationReference
from referencework rw left join referencework rwchild on rw.ReferenceWorkID=rwchild.ContainedRFParentID
  left join journal j on rw.JournalID=j.JournalID
  join collectionobjectcitation cit on cit.ReferenceWorkID=rwchild.ReferenceWorkID
  join fragment f on cit.FragmentID=f.FragmentID
  left join author au1 on rw.ReferenceWorkID=au1.ReferenceWorkID
  left join agent a1 on au1.AgentID=a1.AgentID
  left join author au2 on rw.ReferenceWorkID=au2.ReferenceWorkID
  left join agent a2 on au1.AgentID=a2.AgentID
  left join author au3 on rw.ReferenceWorkID=au3.ReferenceWorkID
  left join agent a3 on au1.AgentID=a3.AgentID
where au1.OrderNumber=1 and au2.OrderNumber=2 and au3.OrderNumber=3 and f.FragmentID=?

associatedTaxa

(no standard equivalent)

select if(instr(lower(co.Text1), "host")=1, co.Text1, concat("host: ", co.Text1)) as associatedTaxon from collectionobject co join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

Event

eventID

select concat("urn:catalog:", ifnull(i.Uri, i.Code), ":collectingevent:", co.CollectingEventID) as eventID from collectionobject co join collectingevent ce on co.CollectingEventID=ce.CollectingEventID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where co.CollectionObjectID=?

select concat("urn:catalog:", ifnull(i.Uri, i.Code), ":collectingevent:", co.CollectingEventID) as eventID from fragment f join collectionobject co on f.CollectionObjectID=co.CollectionObjectID join collectingevent ce on co.CollectingEventID=ce.CollectingEventID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where f.FragmentID=?

samplingProtocol

select ce.Method as samplingProtocol from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID where co.CollectionObjectID=?

(no standard equivalent)

eventDate

select concat(
               date_format(ce.StartDate,
                    case when ce.StartDatePrecision=1 then "%Y-%m-%d"
                         when ce.StartDatePrecision=2 then "%Y-%m"
                         when ce.StartDatePrecision=3 then "%Y"
                     end),
           if( ce.EndDate is null,
                     "",
                     concat( "/",
                             date_format(ce.EndDate,
                                  case when ce.EndDatePrecision=1 then "%Y-%m-%d"
                                       when ce.EndDatePrecision=2 then "%Y-%m"
                                       when ce.EndDatePrecision=3 then "%Y"
                                   end)))) as eventDate from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID where co.CollectionObjectID=?

select concat(
               date_format(ce.StartDate,
                    case when ce.StartDatePrecision=1 then "%Y-%m-%d"
                         when ce.StartDatePrecision=2 then "%Y-%m"
                         when ce.StartDatePrecision=3 then "%Y"
                     end),
           if( ce.EndDate is null,
                     "",
                     concat( "/",
                             date_format(ce.EndDate,
                                  case when ce.EndDatePrecision=1 then "%Y-%m-%d"
                                       when ce.EndDatePrecision=2 then "%Y-%m"
                                       when ce.EndDatePrecision=3 then "%Y"
                                   end)))) as eventDate from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

startDayOfYear

select dayofyear(ce.StartDate) as startDayOfYear from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID where co.CollectionObjectID=?

select dayofyear(ce.StartDate) as startDayOfYear from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

endDayOfYear

select dayofyear(ce.EndDate) as endDayOfYear from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID where co.CollectionObjectID=?

select dayofyear(ce.EndDate) as endDayOfYear from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

year

select year(ce.StartDate) as year from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID where co.CollectionObjectID=?

select year(ce.StartDate) as year from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

month

select month(ce.StartDate) as month from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID where co.CollectionObjectID=?

select month(ce.StartDate) as month from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

day

select day(ce.StartDate) as day from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID where co.CollectionObjectID=?

select day(ce.StartDate) as day from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

verbatimEventDate

select if(ce.StartDateVerbatim is not null, concat(ce.StartDateVerbatim, if(ce.EndDateVerbatim is not null, concat("-", ce.EndDateVerbatim), "")), ce.VerbatimDate) as verbatimEventDate from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID where co.CollectionObjectID=?

select if(ce.StartDateVerbatim is not null, concat(ce.StartDateVerbatim, if(ce.EndDateVerbatim is not null, concat("-", ce.EndDateVerbatim), "")), ce.VerbatimDate) as verbatimEventDate from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

habitat

select ce.Remarks as habitat from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID where co.CollectionObjectID=?

select ce.Remarks as habitat from collectingevent ce join collectionobject co on ce.CollectingEventID=co.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

dcterms:Location

locationID

select ifnull(loc.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":locality:", loc.LocalityID)) as locationID from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where co.CollectionObjectID=?

select ifnull(loc.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":locality:", loc. LocalityID)) as locationID from fragment f join collectionobject co on f.CollectionObjectID=co.CollectionObjectID join collectingevent ce on co.CollectingEventID=ce.CollectingEventID join locality loc on ce.LocalityID=loc.LocalityID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where f.FragmentID=?

higherGeographyID

select ifnull(g.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":geography:", g.GeographyID)) as higherGeographyID from geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where co.CollectionObjectID=?

select ifnull(g.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":geography:", g.GeographyID)) as higherGeographyID from fragment f join collectionobject co on f.CollectionObjectID=co.CollectionObjectID join collectingevent ce on co.CollectingEventID=ce.CollectingEventID join locality loc on ce.LocalityID=loc.LocalityID join geography g on loc.GeographyID=g.GeographyID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where f.FragmentID=?

higherGeography

select g.FullName as higherGeography from geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select g.FullName as higherGeography from fragment f join collectionobject co on f.CollectionObjectID=co.CollectionObjectID join collectingevent ce on co.CollectingEventID=ce.CollectingEventID join locality loc on ce.LocalityID=loc.LocalityID join geography g on loc.GeographyID=g.GeographyID where f.FragmentID=?

continent

select ifnull(continent.GeographyCode, continent.FullName) as continent from geography continent, geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where continent.RankID=100 and g.NodeNumber >= continent.NodeNumber and g.NodeNumber <= continent.HighestChildNodeNumber and co.CollectionObjectID=?

select ifnull(continent.GeographyCode, continent.FullName) as continent from geography continent, geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where continent.RankID=100 and g.NodeNumber >= continent.NodeNumber and g.NodeNumber <= continent.HighestChildNodeNumber and f.FragmentID=?

waterBody

select loc.WaterBody as waterBody from locality loc join collecting join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select ifnull(lake.GeographyCode, lake.FullName) as waterBody from geography lake, geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where lake.RankID=480 and g.NodeNumber >= lake.NodeNumber and g.NodeNumber <= lake.HighestChildNodeNumber and f.FragmentID=?

islandGroup

select loc.IslandGroup as islandGroup from locality loc join collecting join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select isles.FullName as waterBody from geography isles, geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where (isles.RankID in (230,280,320,450) and (instr(isles.FullName, "Islands") > 0 or instr(isles.FullName, "Island Group") > 0))) and g.NodeNumber >= isles.NodeNumber and g.NodeNumber <= isles.HighestChildNodeNumber and f.FragmentID=? note that theoretically more than one result may be returned per item

island

select loc.Island as island from locality loc join collecting join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select island.FullName as waterBody from geography island, geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where ((island.RankID in (230,280,320,450) and not((instr(island.FullName, "Islands") > 0 or instr(island.FullName, "Island Group") > 0)))) and g.NodeNumber >= island.NodeNumber and g.NodeNumber <= island.HighestChildNodeNumber and f.FragmentID=? note that theoretically more than one result may be returned per item

country

select country.FullName as country from geography country, geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where country.RankID=200 and g.NodeNumber >= country.NodeNumber and g.NodeNumber <= country.HighestChildNodeNumber and co.CollectionObjectID=?

select country.FullName as country from geography country, geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where country.RankID=200 and g.NodeNumber >= country.NodeNumber and g.NodeNumber <= country.HighestChildNodeNumber and f.FragmentID=?

countryCode

select country.GeographyCode as countryCode from geography country, geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where country.RankID=200 and g.NodeNumber >= country.NodeNumber and g.NodeNumber <= country.HighestChildNodeNumber and co.CollectionObjectID=?

select country.GeographyCode as countryCode from geography country, geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where country.RankID=200 and g.NodeNumber >= country.NodeNumber and g.NodeNumber <= country.HighestChildNodeNumber and f.FragmentID=?

stateProvince

select ifnull(state.GeographyCode, state.FullName) as state from geography state, geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where state.RankID=300 and g.NodeNumber >= state.NodeNumber and g.NodeNumber <= state.HighestChildNodeNumber and co.CollectionObjectID=?

select ifnull(state.GeographyCode, state.FullName) as state from geography state, geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where state.RankID=300 and g.NodeNumber >= state.NodeNumber and g.NodeNumber <= state.HighestChildNodeNumber and f.FragmentID=?

county

select ifnull(county.GeographyCode, county.FullName) as county from geography county, geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where county.RankID=400 and g.NodeNumber >= county.NodeNumber and g.NodeNumber <= county.HighestChildNodeNumber and co.CollectionObjectID=?

select ifnull(county.GeographyCode, county.FullName) as county from geography county, geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where county.RankID=400 and g.NodeNumber >= county.NodeNumber and g.NodeNumber <= county.HighestChildNodeNumber and f.FragmentID=?

municipality

select ifnull(city.GeographyCode, city.FullName) as municipality from geography city, geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where city.RankID=500 and g.NodeNumber >= city.NodeNumber and g.NodeNumber <= city.HighestChildNodeNumber and co.CollectionObjectID=?

select ifnull(city.GeographyCode, city.FullName) as municipality from geography city, geography g join locality loc on g.GeographyID=loc.GeographyID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where city.RankID=500 and g.NodeNumber >= city.NodeNumber and g.NodeNumber <= city.HighestChildNodeNumber and f.FragmentID=?

locality

select loc.LocalityName as locality from locality loc join collecting join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select loc.LocalityName as locality from locality loc join collecting join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

verbatimElevation

select loc.VerbatimElevation as verbatimElevation from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select loc.VerbatimElevation as verbatimElevation from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

minimumElevationInMeters

select loc.MinElevation as minimumElevationInMeters from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select loc.MinElevation as minimumElevationInMeters from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

maximumElevationInMeters

select loc.MaxElevation as maximumElevationInMeters from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select loc.MaxElevation as maximumElevationInMeters from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

verbatimDepth

(no standard equivalent, but possibly equivalent to elevation fields for some disciplines)

(no equivalent)

minimumDepthInMeters

(no standard equivalent, but possibly equivalent to elevation fields for some disciplines)

(no equivalent)

maximumDepthInMeters

(no standard equivalent, but possibly equivalent to elevation fields for some disciplines)

(no equivalent)

minimumDistanceAboveSurfaceInMeters

(no standard equivalent, but possibly equivalent to elevation fields for some disciplines)

(no equivalent)

maximumDistanceAboveSurfaceInMeters

(no standard equivalent, but possibly equivalent to elevation fields for some disciplines)

(no equivalent)

locationRemarks

select loc.Remarks as locationRemarks from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select loc.Remarks as locationRemarks from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

verbatimLatitude

select concat(loc.Lat1Text, ifnull(concat(", ", loc.Lat2Text), "")) as verbatimLatitude from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select concat(loc.Lat1Text, ifnull(concat(", ", loc.Lat2Text), "")) as verbatimLatitude from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

verbatimLongitude

select concat(loc.Long1Text, ifnull(concat(", ", loc.Long2Text), "")) as verbatimLongitude from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select concat(loc.Long1Text, ifnull(concat(", ", loc.Long2Text), "")) as verbatimLongitude from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

decimalLatitude

select concat(loc.Latitude1, ifnull(concat(", ", loc.Latitude2), "")) as decimalLatitude from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select concat(loc.Latitude1, ifnull(concat(", ", loc.Latitude2), "")) as decimalLatitude from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

decimalLongitude

select concat(loc.Longitude1, ifnull(concat(", ", loc.Longitude2), "")) as decimalLongitude from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select concat(loc.Longitude1, ifnull(concat(", ", loc.Longitude2), "")) as decimalLongitude from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

geodeticDatum

select loc.Datum as geodeticDatum from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select loc.Datum as geodeticDatum from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

coordinatePrecision

select loc.LatLongAccuracy as coordinatePrecision from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select loc. LatLongAccuracy as coordinatePrecision from locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

georeferencedBy

select concat(ifnull(concat(a.FirstName, " "), ""), a.LastName) as georeferencedBy from agent a join geocoorddetail g on a.AgentID=g.AgentID join locality loc on g.LocalityID=loc.LocalityID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select concat(ifnull(concat(a.FirstName, " "), ""), a.LastName) as georeferencedBy from agent a join geocoorddetail g on a.AgentID=g.AgentID join locality loc join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

georeferenceProtocol

select g.Protocol as georeferenceProtocol from geocoorddetail g join locality loc on g.LocalityID=loc.LocalityID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select g.Protocol as georeferenceProtocol from geocoorddetail g join locality loc on g.LocalityID=loc.LocalityID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

georeferenceSources

select g.Source as georeferenceSource from geocoorddetail g join locality loc on g.LocalityID=loc.LocalityID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select g.Source as georeferenceSource from geocoorddetail g join locality loc on g.LocalityID=loc.LocalityID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

georeferenceVerificationStatus

select g. GeoRefVerificationStatus as georeferenceVerificationStatus from geocoorddetail g join locality loc on g.LocalityID=loc.LocalityID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select g. GeoRefVerificationStatus as georeferenceVerificationStatus from geocoorddetail g join locality loc on g.LocalityID=loc.LocalityID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

georeferenceRemarks

select g. GeoRefRemarks as georeferenceRemarks from geocoorddetail g join locality loc on g.LocalityID=loc.LocalityID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID where co.CollectionObjectID=?

select g. GeoRefRemarks as georeferenceRemarks from geocoorddetail g join locality loc on g.LocalityID=loc.LocalityID join collectingevent ce on loc.LocalityID=ce.LocalityID join collectionobject co on co.CollectingEventID=ce.CollectingEventID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

GeologicalContext

Note that the Specify schema allows for three different stratigraphic records to be associated with any CollectionObject: one LithoStrat, one ChronosStrat, and one BioStrat.

geologicalContextID

select ifnull(g.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":geologictimeperiod:", g.GeologicTimePeriodID)) as geologicalContextID from geologictimeperiod g join paleocontext p on g.GeologicTimePeriodID=p.ChronosStratID join collectionobject co on p.PaleoContextID=co.PaleoContextID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where co.CollectionObjectID=?

select ifnull(g.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":geologictimeperiod:", g.GeologicTimePeriodID)) as geologicalContextID from geologictimeperiod g join paleocontext p on g.GeologicTimePeriodID=p.BioStratID join collectionobject co on p.PaleoContextID=co.PaleoContextID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where co.CollectionObjectID=?

select ifnull(li.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":lithostrat:", li.LithoStratID)) as geologicalContextID from lithostrat li join paleocontext p on li.LithoStratID=p.LithoStratID join collectionobject co on p.PaleoContextID=co.PaleoContextID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where co.CollectionObjectID=?

select ifnull(co.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ": geologictimeperiod:", g. GeologicTimePeriodID)) as geologicalContextID from fragment f join collectionobject co on f.CollectionObjectID=co.CollectionObjectID join paleocontext p on co.PaleoContextID=p.PaleoContextID join geologictimeperiod g on p.ChronosStratID=g.GeologicTimePeriodID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where f.FragmentID=?

select ifnull(co.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ": geologictimeperiod:", g. GeologicTimePeriodID)) as geologicalContextID from fragment f join collectionobject co on f.CollectionObjectID=co.CollectionObjectID join paleocontext p on co.PaleoContextID=p.PaleoContextID join geologictimeperiod g on p.BioStratID=g.GeologicTimePeriodID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where f.FragmentID=?

select ifnull(co.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ": geologictimeperiod:", li.LithoStratID)) as geologicalContextID from fragment f join collectionobject co on f.CollectionObjectID=co.CollectionObjectID join paleocontext p on co.PaleoContextID=p.PaleoContextID join lithostrat li on p.ChronosStratID=li.LithoStratID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where f.FragmentID=?

group

select li.FullName as grp from (select * from lithostrat where RankID=200) li join paleocontext p on li.LithoStratID=p.LithoStratID join collectionobject co on p.PaleoContextID=co.PaleoContextID where co.CollectionObjectID=?

select li.FullName as grp from (select * from lithostrat where RankID=200) li join paleocontext p on li.LithoStratID=p.LithoStratID join collectionobject co on p.PaleoContextID=co.PaleoContextID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

formation

select li.FullName as formation from (select * from lithostrat where RankID=300) li join paleocontext p on li.LithoStratID=p.LithoStratID join collectionobject co on p.PaleoContextID=co.PaleoContextID where co.CollectionObjectID=?

select li.FullName as formation from (select * from lithostrat where RankID=300) li join paleocontext p on li.LithoStratID=p.LithoStratID join collectionobject co on p.PaleoContextID=co.PaleoContextID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

member

select li.FullName as member from (select * from lithostrat where RankID=400) li join paleocontext p on li.LithoStratID=p.LithoStratID join collectionobject co on p.PaleoContextID=co.PaleoContextID where co.CollectionObjectID=?

select li.FullName as member from (select * from lithostrat where RankID=400) li join paleocontext p on li.LithoStratID=p.LithoStratID join collectionobject co on p.PaleoContextID=co.PaleoContextID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

bed

select li.FullName as bed from (select * from lithostrat where RankID=500) li join paleocontext p on li.LithoStratID=p.LithoStratID join collectionobject co on p.PaleoContextID=co.PaleoContextID where co.CollectionObjectID=?

select li.FullName as bed from (select * from lithostrat where RankID=500) li join paleocontext p on li.LithoStratID=p.LithoStratID join collectionobject co on p.PaleoContextID=co.PaleoContextID join fragment f on co.CollectionObjectID=f.CollectionObjectID where f.FragmentID=?

Identification

Note that only the business rules enforce the uniqueness of a "current" determination. For data loaded outside the client, there may be more than one, in which case there may also be more than one determiner, date, reference, etc.; they will have to be matched by identificationID. Note also that for the HUH version, there is no notion of a type being current or not, so the test on IsCurrent should fail.

identificationID

select concat("urn:catalog:", ifnull(i.Uri, i.Code), ":determination:", d.DeterminationID) as identificationID from determination d join collectionobject co on d.CollectionObjectID=co.CollectionObjectID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where d.IsCurrent and co.CollectionObjectID=?

select concat("urn:catalog:", ifnull(i.Uri, i.Code), ":determination:", d.DeterminationID) as identificationID from determination d join fragment f on d.FragmentID=f.FragmentID join collectionobject co on f.CollectionObjectID=co.CollectionObjectID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where d.IsCurrent and f.FragmentID=?

identifiedBy

select concat(ifnull(concat(a.FirstName, " "), ""), a.LastName) as identifiedBy from agent a join determination d on a.AgentID=d.DeterminerID join collectionobject co on co.CollectionObjectID=d.CollectionObjectID where d.IsCurrent and co.CollectionObjectID=?

select ifnull(concat(ifnull(concat(a.FirstName, " "), ""), a.LastName), d.Text1) as identifiedBy from determination d join fragment f on d.FragmentID=f.FragmentID left join agent a on a.AgentID=d.DeterminerID where d.IsCurrent and f.FragmentID=?

dateIdentified

select date_format(d.DeterminedDate,
                    case when d.DeterminedDatePrecision=1 then "%Y-%m-%d"
                         when d.DeterminedDatePrecision=2 then "%Y-%m"
                         when d.DeterminedDatePrecision=3 then "%Y"
                     end) as dateIdentified
 from determination d join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where d.IsCurrent and d.DeterminedDatePrecision !=0 and  co.CollectionObjectID=?

select date_format(d.DeterminedDate,
                    case when d.DeterminedDatePrecision=1 then "%Y-%m-%d"
                         when d.DeterminedDatePrecision=2 then "%Y-%m"
                         when d.DeterminedDatePrecision=3 then "%Y"
                     end) as dateIdentified
 from determination d join fragment f on d.FragmentID=f.FragmentID where d.IsCurrent and d.DeterminedDatePrecision !=0 and f.FragmentID=?

identificationReferences

select concat(
          case when au1.AgentID is not null and au2.AgentID is not null and au3.AgentID is not null
            then concat(ifnull(concat(au1.FirstName, " "), ""), au1.LastName, ", ", ifnull(concat(au2.FirstName, " "), ""), au2.LastName, " et. al.  ")
          when au1.AgentID is not null and au2.AgentID is not null
            then concat(ifnull(concat(au1.FirstName, " "), ""), au1.lastName, ", ", ifnull(concat(au2.FirstName, " "), ""), au2.Lastname, ".  ")
          when au1.AgentID is not null
            then concat(ifnull(concat(au1.FirstName, " "), ""), au1.LastName, ".  ")
          else "" end,
          ifnull(concat(rw.WorkDate, ".  "), ""),
          concat(rw.Title, ".  "),
          ifnull(concat(j.JournalName, ".  "),
                 ""),
          concat(
            ifnull(concat(rw.Volume, ":"), ""),
            ifnull(rw.Pages,"")),
          case when rw.Volume is null and rw.Pages is null then "" else "." end) as identificationReference
from referencework rw left join journal j on rw.JournalID=j.JournalID
  left join (select a.FirstName, a.LastName, au.AuthorID, au.AgentID, au.ReferenceWorkID from author au join agent a on au.AgentID=a.AgentID where au.OrderNumber=1) au1 on au1.ReferenceWorkID=rw.ReferenceWorkID 
  left join (select a.FirstName, a.LastName, au.AuthorID, au.AgentID, au.ReferenceWorkID from author au join agent a on au.AgentID=a.AgentID where au.OrderNumber=2) au2 on au2.ReferenceWorkID=rw.ReferenceWorkID
  left join (select a.FirstName, a.LastName, au.AuthorID, au.AgentID, au.ReferenceWorkID from author au join agent a on au.AgentID=a.AgentID where au.OrderNumber=3) au3 on au3.ReferenceWorkID=rw.ReferenceWorkID
  join determinationcitation cit on cit.ReferenceWorkID=rw.ReferenceWorkID
  join determination d on cit.DeterminationID = d.DeterminationID
  join collectionobject co on d.CollectionObjectID=co.CollectionObjectID
where co.CollectionObjectID=?

select concat(
          case when au1.AgentID is not null and au2.AgentID is not null and au3.AgentID is not null
            then concat(ifnull(concat(au1.FirstName, " "), ""), au1.LastName, ", ", ifnull(concat(au2.FirstName, " "), ""), au2.LastName, " et. al.  ")
          when au1.AgentID is not null and au2.AgentID is not null
            then concat(ifnull(concat(au1.FirstName, " "), ""), au1.lastName, ", ", ifnull(concat(au2.FirstName, " "), ""), au2.Lastname, ".  ")
          when au1.AgentID is not null
            then concat(ifnull(concat(au1.FirstName, " "), ""), au1.LastName, ".  ")
          else "" end,
          ifnull(concat(cit.Text2, ".  "), ""),
          concat(rw.Title, ".  "),
          ifnull(concat(j.JournalName, ".  "),
                 ""),
          ifnull(concat(cit.Text1, ".  "), "")) as identificationReference
from referencework rw left join referencework rwchild on rw.ReferenceWorkID=rwchild.ContainedRFParentID
  left join journal j on rw.JournalID=j.JournalID
  left join (select a.FirstName, a.LastName, au.AuthorID, au.AgentID, au.ReferenceWorkID from author au join agent a on au.AgentID=a.AgentID where au.OrderNumber=1) au1 on au1.ReferenceWorkID=rwchild.ReferenceWorkID 
  left join (select a.FirstName, a.LastName, au.AuthorID, au.AgentID, au.ReferenceWorkID from author au join agent a on au.AgentID=a.AgentID where au.OrderNumber=2) au2 on au2.ReferenceWorkID=rwchild.ReferenceWorkID
  left join (select a.FirstName, a.LastName, au.AuthorID, au.AgentID, au.ReferenceWorkID from author au join agent a on au.AgentID=a.AgentID where au.OrderNumber=3) au3 on au3.ReferenceWorkID=rwchild.ReferenceWorkID
  join determinationcitation cit on cit.ReferenceWorkID=rwchild.ReferenceWorkID
  join determination d on cit.DeterminationID = d.DeterminationID
  join fragment f on d.FragmentID=f.FragmentID
where f.FragmentID=?

- There are no records currently in HUH-specify which have a parent and child referencework for determination citations. Instead use the following query and function:

update temp_dwc_search t 
       left join determinationcitation dc on t.temp_determinationid = dc.determinationid
       left join referencework crw on dc.referenceworkid = crw.referenceworkid
   set identificationreferences = trim(
       concat( 
         concatAuthors(crw.referenceworkid), 
         ' ',
        ifnull(concat(dc.Text2, '. '), ''),
        ifnull(concat(crw.title,' '),''),
        ifnull(concat(dc.Text1, ".  "), "") 
      )
  )
  where crw.referenceworkid is not null;


drop function if exists specify.concatAuthors;

delimiter |
create function specify.concatAuthors(aReferenceWorkID INT)
returns text
DETERMINISTIC
CONTAINS SQL
BEGIN
    declare auth varchar(64);
    declare sep varchar(2) default '';
    declare terminator varchar(2) default '';
    declare t_result text default '';
    declare told_result text default '' ;
    declare done int default 0;
    declare getauths cursor for
    select name from author left join agentvariant on author.agentid = agentvariant.agentid where vartype = 2 and referenceworkid = aReferenceWorkID order by ordernumber asc;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    open getauths;
    readloop: LOOP
      fetch getauths into auth;
      if done then
        set told_result = t_result;
        set t_result = concat(told_result,terminator);
        LEAVE readloop;
      end if;
      set told_result = t_result;
      set t_result = concat(told_result,sep,auth);
      set sep = ', ';
      set terminator = '.';
    end LOOP;
    return t_result;
END |
delimiter ;

identificationRemarks

select d.Remarks as identificationRemarks from determination d join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where d.IsCurrent and co.CollectionObjectID=?

select d.Remarks as identificationRemarks from determination d join fragment f on d.FragmentID=f.FragmentID where d.IsCurrent and f.FragmentID=?

identificationQualifier

select d.Qualifier as identificationQualifier from determination d join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where d.IsCurrent and co.CollectionObjectID=?

select pli.Title as identificationQualifier from determination d join fragment f on d.FragmentID=f.FragmentID join (select * from picklistitem where PickListID=(select PickListID from picklist where Name="HUH Determination Qualifier")) pli on d.Qualifier=pli.Value where d.IsCurrent and f.FragmentID=?

typeStatus

select d.TypeStatusName as typeStatus from determination d join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where d.IsCurrent and co.CollectionObjectID=?

select pli.Title as typeStatus from determination d join fragment f on d.FragmentID=f.FragmentID join (select * from picklistitem where PickListID=(select PickListID from picklist where Name="HUH Type Status")) pli on d.TypeStatusName=pli.Value where f.FragmentID=?

Taxon

scientificNameID

select ifnull(t.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":taxon:", t.TaxonID)) as scientificNameID from taxon t join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where d.IsCurrent and co.CollectionObjectID=?

select ifnull(t.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":taxon:", t.TaxonID)) as scientificNameID from taxon t join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID join collection c on f.CollectionMemberID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

acceptedNameUsageID

select ifnull(at.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":taxon:", at.TaxonID)) as scientificNameID from taxon at join taxon t on at.TaxonID=t.AcceptedID join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where d.IsCurrent and co.CollectionObjectID=?

(no equivalent)

parentNameUsageID

select ifnull(p.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":taxon:", p.TaxonID)) as parentNameUsageID from taxon p join taxon t on p.TaxonID=t.ParentID join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID join collection c on co.CollectionID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where d.IsCurrent and co.CollectionObjectID=?

select ifnull(p.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":taxon:", p.TaxonID)) as parentNameUsageID from taxon p join taxon t on p.TaxonID=t.ParentID join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID join collection c on f.CollectionMemberID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

originalNameUsageID

(no standard equivalent)

select ifnull(b.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":taxon:", b.TaxonID)) as scientificNameID from taxon b join taxon t on b.TaxonID=t.BasionymID join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID join collection c on f.CollectionMemberID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

namePublishedInID

select ifnull(rw.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":referencework:", rw.ReferenceWorkID)) as namePublishedInID from referencework rw join taxoncitation cit on cit.ReferenceWorkID=rw.ReferenceWorkID join taxon t on cit.TaxonID=t.TaxonID join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID join collection c on co.CollectionMemberID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where (d.IsCurrent or d.TypeStatusName is not null) and co.CollectionObjectID=?

select ifnull(rw.GUID, concat("urn:catalog:", ifnull(i.Uri, i.Code), ":referencework:", rw.ReferenceWorkID)) as namePublishedInID from referencework rw join taxoncitation cit on cit.ReferenceWorkID=rw.ReferenceWorkID join taxon t on cit.TaxonID=t.TaxonID join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID join collection c on f.CollectionMemberID=c.CollectionID join discipline dsc on c.DisciplineID=dsc.DisciplineID join division dv on dsc.DivisionID=dv.DivisionID join institution i on dv.InstitutionID=i.InstitutionID where (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

scientificName

select concat(t.FullName, ifnull(concat(" ", t.Author), "")) as scientificName from taxon t join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where d.IsCurrent and co.CollectionObjectID=?

select concat(t.FullName, ifnull(concat(" ", t.Author), "")) as scientificName from taxon t join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID where (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

acceptedNameUsage

select concat(at.FullName, ifnull(concat(" ", at.Author), "")) as acceptedNameUsage from taxon at join taxon t on at.TaxonID=t.AcceptedID join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where d.IsCurrent and co.CollectionObjectID=?

(no equivalent)

parentNameUsage

select concat(p.FullName, ifnull(concat(" ", p.Author), "")) as parentNameUsage from taxon p join taxon t on p.TaxonID=t.ParentID join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where d.IsCurrent and co.CollectionObjectID=?

select concat(p.FullName, ifnull(concat(" ", p.Author), "")) as parentNameUsage from taxon p join taxon t on p.TaxonID=t.ParentID join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID where (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

originalNameUsage

(no standard equivalent)

select concat(b.FullName, ifnull(concat(" ", b.Author), "")) as originalNameUsage from taxon b join taxon t on b.TaxonID=t.BasionymID join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID where (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

namePublishedIn

select concat(
         concat(
          case when au1.AuthorID is not null and au2.AuthorID is not null and au3.AuthorID is not null
            then concat(ifnull(concat(a1.FirstName, " "), ""), a1.LastName, ", ", ifnull(concat(a2.FirstName, " "), ""), a2.LastName, " et. al.  ")
          when au1.AuthorID is not null and au2.AuthorID is not null
            then concat(ifnull(concat(a1.FirstName, " "), ""), a1.lastName, ", ", ifnull(concat(a2.FirstName, " "), ""), a2.Lastname, ".  ")
          when au1.AuthorID is not null
            then concat(ifnull(concat(a1.FirstName, " "), ""), a1.LastName, ".  ")
          else "" end, ".  "),
          ifnull(concat(rw.WorkDate, ".  "), ""),
          concat(rw.Title, ".  "),
          ifnull(concat(j.JournalName, ".  "),
                 ""),
          concat(
            ifnull(concat(rw.Volume, ":"), ""),
            ifnull(rw.Pages,"")),
          case when rw.Volume is null and rw.Pages is null then "" else "." end) as namePublishedIn
from referencework rw left join journal j on rw.JournalID=j.JournalID
  join taxoncitation cit on cit.ReferenceWorkID=rw.ReferenceWorkID
  join taxon t on cit.TaxonID=t.TaxonID
  join determination d on t.TaxonID=d.TaxonID
  join collectionobject co on d.CollectionObjectID=co.CollectionObjectID
  left join author au1 on rw.ReferenceWorkID=au1.ReferenceWorkID
  left join agent a1 on au1.AgentID=a1.AgentID
  left join author au2 on rw.ReferenceWorkID=au2.ReferenceWorkID
  left join agent a2 on au1.AgentID=a2.AgentID
  left join author au3 on rw.ReferenceWorkID=au3.ReferenceWorkID
  left join agent a3 on au1.AgentID=a3.AgentID
where d.IsCurrent and au1.OrderNumber=1 and au2.OrderNumber=2 and au3.OrderNumber=3 and co.CollectionObjectID=?

select concat(
         concat(
           case when t.CitInAuthorID is not null
                then ifnull((select Name from agentvariant where AgentID=t.CitInAuthorID and VarType=2),
                            (select concat(ifnull(concat(FirstName, " "), ""), LastName) from agent where AgentID=t.CitInAuthorID))
                when t.StdExAuthorID is not null
                then ifnull((select Name from agentvariant where AgentID=t.StdExAuthorID and VarType=2),
                            (select concat(ifnull(concat(FirstName, " "), ""), LastName) from agent where AgentID=t.StdExAuthorID))
                else ifnull((select Name from agentvariant where AgentID=t.StdAuthorID and VarType=2),
                            (select concat(ifnull(concat(FirstName, " "), ""), LastName) from agent where AgentID=t.StdAuthorID))
                end, ".  "),
          ifnull(concat(cit.Text2, ".  "), ""),
          concat(rw.Text1, ".  "),
          ifnull(concat(j.JournalName, ".  "),
                 ""),
          ifnull(concat(cit.Text1, ".  "), "")) as namePublishedIn
from referencework rw left join journal j on rw.JournalID=j.JournalID
  join taxoncitation cit on cit.ReferenceWorkID=rw.ReferenceWorkID
  join taxon t on cit.TaxonID=t.TaxonID
  join determination d on t.TaxonID=d.TaxonID
  join fragment f on d.FragmentID=f.FragmentID
where (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

kingdom

select kingdom.Name as kingdom from taxon kingdom, taxon t join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where kingdom.RankID=10 and t.NodeNumber>=kingdom.NodeNumber and t.NodeNumber<=kingdom.HighestChildNodeNumber and d.IsCurrent and co.CollectionObjectID=?

(no equivalent)

phylum

select phylum.Name as phylum from taxon phylum, taxon t join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where phylum.RankID=30 and t.NodeNumber>=phylum.NodeNumber and t.NodeNumber<=phylum.HighestChildNodeNumber and d.IsCurrent and co.CollectionObjectID=?

select phylum.Name as phylum from taxon phylum, taxon t join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID where phylum.RankID=30 and t.NodeNumber>=phylum.NodeNumber and t.NodeNumber<=phylum.HighestChildNodeNumber and (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

class

select class.Name as class from taxon class, taxon t join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where class.RankID=60 and t.NodeNumber>=class.NodeNumber and t.NodeNumber<=class.HighestChildNodeNumber and d.IsCurrent and co.CollectionObjectID=?

select class.Name as class from taxon class, taxon t join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID where class.RankID=60 and t.NodeNumber>=class.NodeNumber and t.NodeNumber<=class.HighestChildNodeNumber and (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

order

select ordr.Name as "order" from taxon ordr, taxon t join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where ordr.RankID=100 and t.NodeNumber>=ordr.NodeNumber and t.NodeNumber<=ordr.HighestChildNodeNumber and d.IsCurrent and co.CollectionObjectID=?

select ordr.Name as "order" from taxon ordr, taxon t join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID where ordr.RankID=100 and t.NodeNumber>=ordr.NodeNumber and t.NodeNumber<=ordr.HighestChildNodeNumber and (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

family

select family.Name as family from taxon family, taxon t join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where family.RankID=140 and t.NodeNumber>=family.NodeNumber and t.NodeNumber<=family.HighestChildNodeNumber and d.IsCurrent and co.CollectionObjectID=?

select family.Name as family from taxon family, taxon t join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID where family.RankID=140 and t.NodeNumber>=family.NodeNumber and t.NodeNumber<=family.HighestChildNodeNumber and (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

genus

select genus.Name as subgenus from taxon genus, taxon t join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where genus.RankID=180 and t.NodeNumber>=genus.NodeNumber and t.NodeNumber<=genus.HighestChildNodeNumber and d.IsCurrent and co.CollectionObjectID=?

select genus.Name as subgenus from taxon genus, taxon t join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID where genus.RankID=180 and t.NodeNumber>=genus.NodeNumber and t.NodeNumber<=genus.HighestChildNodeNumber and (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

subgenus

select subgenus.Name as subgenus from taxon subgenus, taxon t join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where subgenus.RankID=190 and t.NodeNumber>=subgenus.NodeNumber and t.NodeNumber<=subgenus.HighestChildNodeNumber and d.IsCurrent and co.CollectionObjectID=?

select subgenus.Name as subgenus from taxon subgenus, taxon t join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID where subgenus.RankID=190 and t.NodeNumber>=subgenus.NodeNumber and t.NodeNumber<=subgenus.HighestChildNodeNumber and (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

specificEpithet

select t.Name as infraspecificEpithet from taxon t join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where t.RankID = 220 and d.IsCurrent and co.CollectionObjectID=?

select t.Name as scientificName from taxon t join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID where t.RankID = 220 and (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

infraspecificEpithet

select t.Name as infraspecificEpithet from taxon t join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where t.RankID > 220 and d.IsCurrent and co.CollectionObjectID=?

select t.Name as scientificName from taxon t join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID where t.RankID > 220 and (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

taxonRank

select tdi.Name as taxonRank from taxontreedefitem tdi join taxon t on tdi.RankID=t.RankID join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where d.IsCurrent and co.CollectionObjectID=?

select tdi.Name as taxonRank from taxontreedefitem tdi join taxon t on tdi.RankID=t.RankID join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID where (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

scientificNameAuthorship

select t.Author as scientificNameAuthorship from taxon t join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where d.IsCurrent and co.CollectionObjectID=?

select t.Author as scientificNameAuthorship from taxon t join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID where (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

vernacularName

select t.CommonName as vernacularName from taxon t join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where d.IsCurrent and co.CollectionObjectID=?

(this data is stored as an attribute of the collection object in HUH's version)

nomenclaturalCode

(varies according to discipline)

"ICBN"

taxonomicStatus

select if(t.IsAccepted, "accepted", "synonym") as taxonomicStatus from taxon t join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where d.IsCurrent and co.CollectionObjectID=?

"accepted"

nomenclaturalStatus

(no standard equivalent)

select pli.Title as nomenclaturalStatus from taxon t join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID join (select * from picklistitem where PickListID=(select PickListID from picklist where Name="HUH Taxon Status")) pli on t.Text1=pli.Value where (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

taxonRemarks

select t.Remarks as taxonRemarks from taxon t join determination d on t.TaxonID=d.TaxonID join collectionobject co on d.CollectionObjectID=co.CollectionObjectID where d.IsCurrent and co.CollectionObjectID=?

select t.Remarks as taxonRemarks from taxon t join determination d on t.TaxonID=d.TaxonID join fragment f on d.FragmentID=f.FragmentID where (d.IsCurrent or d.TypeStatusName is not null) and f.FragmentID=?

Darwin Core to Specify Mapping

From ETaxonomy

Contents

Record-level Terms

Occurrence

Event

dcterms:Location

GeologicalContext

Identification

Taxon

Views

Personal tools

Navigation

Search

SMW

Toolbox

All Hands Meeting