GRIMS Tables
From ICISWiki
The following tables and fields and levels will be implemented unless anyone objects. Changes to tables are in italics
The following tables and fields and levels will be implemented unless anyone objects. Changes to tables are in italics
The following tables and fields and levels will be implemented unless anyone objects. Changes to tables are in italics
Contents |
Changes to tables
LOCATION - change in Central
One change - adding a field NNPID for the LOCID of the nearest named place (as agreed in 2008). This will typically be used for a location where LNAME is a text description of the locality, and NNPID points to the village closest to that locality
Usage for IRGCIS collecting locations: LNAME of a location will contain the smallest scale name or description available from IRGCIS; in order: country, province, district, town, village, locality description. Validated interpreted higher level fields respectively go in CTRYID, SNL1ID, SNL2ID, SNL3ID, NNPID. Original unvalidated data on country, province, district, town, village will go into LOCDES exactly as provided in the format "Field=value". Coordinate data go into GEOREF.
| Columns - Long Name (Name) | Description | Type | Length (bytes) |
|---|---|---|---|
| LOCATION_ID (LOCID) | The unique identifier of the location. | Long | 4 |
| LOCATION_TYPE (LTYPE) | Specifies the type Location E.g., Country, Province or Nursery test site. Links to types defined in the UDFLDS table | Integer | 2 |
| NO_LATLONG (NLLP) | Number of LAT-LONG points in the polygon describing the Location. Zero if no LAT-LONG | Integer | 2 |
| PREF_LOC_NAME (LNAME) | Preferred name of the location
This may be a standardised name, such as from a digital gazetteer, or a coded name such as BUR010, or a text description, such as “2km E of Los Baños”. Note that there is no requirement to assign a name to an unnamed location; an unnamed location may instead be identified by its latitude-longitude and/or altitude coordinates (in table GEOREF) and/or the country and subnat divisions in which it is located. | Character | 60 |
| ABBREV_LOC_NAME (LABBR) | Preferred abbreviated name of the location | Character | 8 |
| NEAREST_NAMED_PLACE (NNPID) | Name of the nearest named place. Typically be used for a location where LNAME is a text description of the locality, and NNPID points to the village closest to that locality. For the purposes of data curation, note the conceptual difference: SNL*ID describe the hierarchy of administrative units within which the location occurs: whereas NNPID refers to the nearest named place. NNPID is thus not necessarily within SNL*ID. | Long | 4 |
| SUBNAT _LEVEL3 (SNL3ID) | Name of third level of political subdivision within country (e.g. Vereda). | Long | 4 |
| SUBNAT _LEVEL2 (SNL2ID) | Name of second level of political subdivision within country (e.g. District or County). | Long | 4 |
| SUBNAT _LEVEL1 (SNL1ID) | Name of first level of political subdivision within country (e.g. State, Province Department) | Long | 4 |
| COUNTRY_CODE (CNTRYID) | Country code linked to COUNTRY lookup table | Long | 4 |
| LOCATION_REPLACE (LRPLCE) | Own LOCID for deletion, replacement LOCID for replaced records, zero otherwise | Long | 4 |
GEOREFERENCE (GEOREF) (change in central)
Changes here are required to document the source and accuracy/precision/certainty of georef data.
| Columns - Long Name (Name) | Description | Type | Length (bytes) |
|---|---|---|---|
| LOCATION_ID (LOCID) | Identifies the location. Link to the LOCATION table. | Long | 4 |
| LATLON_POINT_NUMBER (LLPN) | The lat-lon point (1 to NLLP). | Integer | 2 |
| LATITUDE (LAT) | Latitude in decimal degrees (+ for North, - for South, -1E36 for missing). | Real | 8 |
| LONGITUDE (LON) | Longitude in decimal degrees (+ for East, - for West, -1E36 for missing). | Real | 8 |
| ALTITUDE (ALT) | Altitude (m above sea level, -1E36 for missing) | Real | 4 |
| LatLong_Source (LLSource) | The type of source used for lat-long coordinates (e.g. GPS, digital gazetteer, map, collecting form, collecting report, provider's database with unspecified source: lookup UDFLDS) | Integer | 2 |
| LatLong_Format (LL_FMT) | Format in which Lat-Long coordinates were originally provided (lookup UDFLDS) | Integer | 2 |
| LatLong_Datum (LL_Datum) | Datum used for coordinate data (Lookup UDFLDS). (Assume WGS84 if unknown) | Integer | 2 |
| LatLong_Uncertainty (LL_Uncert) | Quantitative estimate of the uncertainty of the measure of coordinates | Real | 8 |
| LatLong_Reference (LLRef) | Reference to specific map, gazetteer, etc. Links to reference in BibRefs table | Integer | 4 |
INSTITUT (change in central)
Three types of change:
- New field LOCID, referencing the institute record with its corresponding record in LOCATION.
- Delete address fields: they will go into a new table ADDRESS
- Merge IRGCIS and IRIS INSTYPE:
- IRGC -> IRIS
- CG -> INTER
- GB -> GBANK
- ID -> INDIV
- NG -> NGO
- NP -> NATION
- PR -> PRCOMP
- RO -> REGION
- UI -> UNIVER
Note: INSTITUT is used to record details of business organizations that have some sort of business relationship with IRRI, e.g. for the purpose of exchange seed. In this sense, even a private individual could be considered as a “business organization”. By generalising the concept of INSTITUT to include any business organization, we can simplify the management of private and business addresses, by linking persons to addresses only through institutes for both business and private addresses. Note that if we need to know the private and business address of a person, we’d need to have two different records for the person, pointing to different INSTITIDs – just in the same way as for a person working for two organizations.
| Field name | Description | Type | Length |
|---|---|---|---|
| INSTITID | INSTITUTION_ID Unique identifier for each institution | Integer | 2 |
| PINSID | INST_PARENT_ID. INSTID for the “parent” institution. Equal to INSTID if on top of hierarchical structure. In most cases, it is expected that PINSID will not be used, and INSTITID points to the legal entity that enters into legal agreements with IRRI. The exception is envisaged to be for INSTYPE=634=Department, in which PINSID represents the legal entity. | Integer | 2 |
| LOCID | Points to corresponding Location record (Non-null - institute must have a record in location; a location doesn't have to be of an institute) | Long | 4 |
| INSNAME | INSTITUTION_NAME. Full name of the institution. | Char | 150 |
| INSACR | INST_ACRONYM. Acronym of the institution (e.g., CIMMYT, IRRI, INIFAP) | Char | 20 |
| INSTYPE | INSTITUTION_TYPE. To specify the type of institution:, International center, National agricultural research service, University, etc. Link to the UDFLDS table. | Integer | 2 |
| WEBURL | WEB_SITE_URL. | Char | 60 |
| SINS | START_INST_DATE. Date the institution was founded. Format: YYYMMDD. | Integer | 4 |
| EINS | END_INST_DATE. Date the institution was closed or re-named. YYYMMDD. | Integer | 4 |
| ICHANGE | INSTITUTION_CHANGE. If the institution is changed to another institution. | Integer | 2 |
New tables
ADDRESS Table (add to central)
New table to hold addresses of institutes / people
Usage: Provide for multiple addresses per institute/person/location
| Field name | Description | Type | Length |
|---|---|---|---|
| AddressID | Address_ID Unique identifier for the address. Primary key | Integer | 2 |
| LOCID | The ID of the location (institute, individual, department etc) to which the address applies. May be multiple AddressIDs sharing the same LOCID, representing different types of address | Long | 4 |
| AddrType | AddressType. Type of address, such as shipping address, port of entry, normal mailing address, Courier address, P.O Box, Street address, Personal address. Lookup in UDFLDS | Integer | 2 |
| Addr1 | 1st line of address. | Char | 125 |
| Addr2 | 2nd line of address | Char | 125 |
| CITY | City. | Char | 30 |
| STATEID | STATE_OR_PROVINCE. State or province. Link to the SNLEVEL table. | Integer | 2 |
| CPOSTAL | POSTAL_CODE. Postal code (e.g., US zip code). | Char | 10 |
| CNTRYID | ICIS_COUNTRY_CODE. Link to the COUNTRIES table. | Integer | 2 |
| APHONE | PHONE_NUMBER. | Char | 25 |
| AFAX | FAX_NUMBER. | Char | 25 |
| AEMAIL | EMAIL_ADDRESS. | Char | 40 |
| SINS | START_INST_DATE. Date the institution was founded. Format: YYYMMDD. | Integer | 4 |
| EINS | END_INST_DATE. Date the institution was closed or re-named. YYYMMDD. | Integer | 4 |
COLLECTING_MISSION (COLLMISS) (add to local)
New table representing a collecting mission, undertaken in a target country by a team of collectors.
Comment: could be represented by a list with attributes. Should we do that instead?
| Field name | Description | Type | Length |
|---|---|---|---|
| MissID | A unique ID for the collecting mission. Primary key. | Number | 2 |
| MissDesc | Description/title of the collecting mission | Number | 2 |
| MissCounID | Country ID in which the collecting mission was conducted | Number | 2 |
| MissStart | Start date of the collecting mission. YYYYMMDD | Number | 4 |
| MissEnd | End date of the collecting mission. YYYYMMDD | Number | 4 |
| MissPDF | Location of PDF file of mission report | Text | 50 |
| MissRef | ID of reference to the mission. Links to RefID in BibRefs | Integer | 2 |
Team_Members (TeamMemb) (add to local)
New table to the people and institutes involved in a collecting mission.
| Field name | Description | Type | Length |
|---|---|---|---|
| TMemberID | A unique ID for team member record. Primary key | Number | 2 |
| MissID | ID for the collecting mission. Links to MissID in CollMiss | Number | 2 |
| PersonID | ID of the collector. Links to PersonID in Persons | Number | 2 |
| InstitID | ID of the collector’s institute. Links to InstitID in INSTITUT. | Number | 2 |
GID_Represents (GIDREP) (add to local)
New table to show what each GID represents. Should contain exactly 1 record for each record in GERMPLSM.
Comment: could be added as fields to GERMPLSM in central. Might not be necessary if data validation rules are followed very well.
| Field name | Description | Type | Length |
|---|---|---|---|
| GID | The GID. Primary key. | Number | 4 |
| Represents | What type of germplasm is represented by the GID (and hence how to interpret GLOCN and GDATE). Lookup values in UDFLDS. 0 for unvalidated status of a GID. | Number | 2 |
| VReason | Text justification of why the GID is considered to represent the value in REPRESENTS | Text | 255 |
| VTimeStamp | Date and time of validation | DateTime | |
| VRef | Reference document used for validation. Links to RefID in BibRefs | Number | 4 |
| VUserID | UserID of the person responsible for validating | Number | 2 |
Valid_Codes (VALIDCODE) (add to local)
New table to identify valid germplasm naming systems.
Use case: germplasm managers typically assign names to be used as IDs for their germplasm samples, often in the form of an alphabetic prefix followed by a sequential number. In some cases, there may be a suffix after the prefix-number combination; and hyphens may or may not be used in the suffix. In such cases the managing institution can usually be deduced from the code. For example, “PI 123456” is a name assigned by USDA to an accession in the National Plant Genetic resources system of the USA.
Usage: if a name begins with a code, this table should be used to validate NTYPE and NLOCN. Codes may be duplicated if they have been used in more than one system.
| Field name | Description | Type | Length |
|---|---|---|---|
| CodeID | A unique ID internally assigned to identify the record. Primary key. | Number | 2 |
| NamePref | A code used at the beginning of a name. | Text | 10 |
| Suffix | Boolean indicating whether a non-numeric suffix is used after Code-Number | Boolean | 2 |
| Hyphen | Boolean indicating whether hyphens are used in the suffix. | Boolean | 2 |
| CLocID | LOCID of the organization assigning names in this format | Number | 4 |
| CType | Type of germplasm identified by this name. Look up UDFLDS for FTYPE REPRESENTS (FTABLE GIDIS) | Number | 2 |
New records
UDFLDS
See uploaded file media:UDFLDS_GRIMS_Changes_2009.xls
