Revision 14490
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/HVAA/Specimen/postprocess.sql | ||
|---|---|---|
| 1 | 1 |
SELECT util.search_path_append('util');
|
| 2 | 2 |
|
| 3 | 3 |
|
| 4 |
-- fix indefinite dates, which aren't supported by Postgres |
|
| 5 |
SELECT mk_derived_col((:table_str, 'monthCollected'), |
|
| 6 |
$$util.date_part_fix("monthCollected_verbatim"::integer)$$);
|
|
| 7 |
SELECT mk_derived_col((:table_str, 'dayCollected'), |
|
| 8 |
$$util.date_part_fix("dayCollected_verbatim"::integer)$$);
|
|
| 9 |
|
|
| 4 | 10 |
SELECT mk_derived_col((:table_str, 'occurrenceRemarks'), |
| 5 | 11 |
$$_merge("occurrenceRemarks_from_occurrenceRemarks", "occurrenceRemarks_from_dynamicProperties")$$)
|
| 6 | 12 |
; -- runtime: 1 s ("Time: 590.875 ms") @starscream
|
| trunk/inputs/HVAA/Specimen/map.csv | ||
|---|---|---|
| 22 | 22 |
typeStatus,typeStatus,, |
| 23 | 23 |
recordedBy,recordedBy,, |
| 24 | 24 |
recordNumber,collectorNumber,, |
| 25 |
eventDate,dateCollected,,
|
|
| 25 |
eventDate,OMIT#eventDate,,omitted because it's easier to fix indefinite dates in the separate year/month/day fields
|
|
| 26 | 26 |
year,yearCollected,, |
| 27 |
month,monthCollected,, |
|
| 28 |
day,dayCollected,, |
|
| 27 |
month,monthCollected_verbatim,,
|
|
| 28 |
day,dayCollected_verbatim,,
|
|
| 29 | 29 |
startDayOfYear,startDayOfYear,, |
| 30 | 30 |
endDayOfYear,endDayOfYear,, |
| 31 | 31 |
verbatimEventDate,verbatimEventDate,, |
| trunk/inputs/HVAA/Specimen/test.xml.ref | ||
|---|---|---|
| 54 | 54 |
<taxonoccurrence> |
| 55 | 55 |
<aggregateoccurrence> |
| 56 | 56 |
<collectiondate> |
| 57 |
<_alt> |
|
| 58 |
<1>$dateCollected</1> |
|
| 59 |
<2> |
|
| 60 |
<_date> |
|
| 61 |
<day> |
|
| 62 |
<_nullIf> |
|
| 63 |
<null>0</null> |
|
| 64 |
<type>float</type> |
|
| 65 |
<value>$dayCollected</value> |
|
| 66 |
</_nullIf> |
|
| 67 |
</day> |
|
| 68 |
<month> |
|
| 69 |
<_nullIf> |
|
| 70 |
<null>0</null> |
|
| 71 |
<type>float</type> |
|
| 72 |
<value>$monthCollected</value> |
|
| 73 |
</_nullIf> |
|
| 74 |
</month> |
|
| 75 |
<year> |
|
| 76 |
<_nullIf> |
|
| 77 |
<null>0</null> |
|
| 78 |
<type>float</type> |
|
| 79 |
<value>$yearCollected</value> |
|
| 80 |
</_nullIf> |
|
| 81 |
</year> |
|
| 82 |
</_date> |
|
| 83 |
</2> |
|
| 84 |
</_alt> |
|
| 57 |
<_date> |
|
| 58 |
<day> |
|
| 59 |
<_nullIf> |
|
| 60 |
<null>0</null> |
|
| 61 |
<type>float</type> |
|
| 62 |
<value>$dayCollected</value> |
|
| 63 |
</_nullIf> |
|
| 64 |
</day> |
|
| 65 |
<month> |
|
| 66 |
<_nullIf> |
|
| 67 |
<null>0</null> |
|
| 68 |
<type>float</type> |
|
| 69 |
<value>$monthCollected</value> |
|
| 70 |
</_nullIf> |
|
| 71 |
</month> |
|
| 72 |
<year> |
|
| 73 |
<_nullIf> |
|
| 74 |
<null>0</null> |
|
| 75 |
<type>float</type> |
|
| 76 |
<value>$yearCollected</value> |
|
| 77 |
</_nullIf> |
|
| 78 |
</year> |
|
| 79 |
</_date> |
|
| 85 | 80 |
</collectiondate> |
| 86 | 81 |
<count>$individualCount</count> |
| 87 | 82 |
<notes>$occurrenceRemarks</notes> |
| trunk/inputs/HVAA/Specimen/unmapped_terms.csv | ||
|---|---|---|
| 5 | 5 |
identificationReferences |
| 6 | 6 |
identificationRemarks |
| 7 | 7 |
typeStatus |
| 8 |
monthCollected_verbatim |
|
| 9 |
dayCollected_verbatim |
|
| 8 | 10 |
startDayOfYear |
| 9 | 11 |
endDayOfYear |
| 10 | 12 |
verbatimEventDate |
Also available in: Unified diff
fix: inputs/HVAA/Specimen/postprocess.sql, map.csv: monthCollected/dayCollected: fix indefinite dates (which aren't supported by Postgres), as decided by Bob (https://docs.google.com/spreadsheets/d/1PI8n0CRttN7ttsXs5qfh5OFFzSoAfJj0gSbylgX6vj4/edit#gid=0)