Darwin Core to Specify Mapping

From Filtered Push Wiki
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


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)


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=?