Project

General

Profile

Statistics
| Revision:

# Date Author Comment
11519 10/31/2013 01:51 AM Aaron Marcuse-Kubitza

bugfix: inputs/input.Makefile: %/install: don't run map_table, because this instead done by the runscript. although it does not hurt to do it twice, invoking load_data by itself should not run map_table at all, so that the original column names can be inspected in the table and map.csv reordered to match.

11518 10/31/2013 01:30 AM Aaron Marcuse-Kubitza

inputs/VegBank/^taxon_observation.**.sample/create.sql: synced with taxon_observation.**

11517 10/31/2013 01:22 AM Aaron Marcuse-Kubitza

(for r11396) fix: bin/map: put template: comment out the "Put template:" label so that the output is valid XML, and displays properly in a browser rather than showing a syntax error

11516 10/31/2013 12:50 AM Aaron Marcuse-Kubitza

/README.TXT: for each task, documented which machine it's run on. for tasks run on vegbiendev, added pointer to "Connecting to vegbiendev" steps.

11515 10/31/2013 12:19 AM Aaron Marcuse-Kubitza

/README.TXT: added instructions for connecting to vegbiendev

11514 10/30/2013 11:03 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: mapped taxon_determination__is_current, taxon_determination__is_original

11513 10/30/2013 09:49 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: mapped taxon_determination__is_current, taxon_determination__is_original

11512 10/30/2013 09:46 PM Aaron Marcuse-Kubitza

bugfix: mappings/VegCore-VegBIEN.csv: main taxondetermination: use [!isoriginal=true] instead of [!isoriginal] so that adding a manual isoriginal field does not prevent this selector from matching

11511 10/30/2013 09:07 PM Aaron Marcuse-Kubitza

inputs/VegBank/taxonobservation_/map.csv: originalinterpretation, currentinterpretation: removed table name prefix so these would automap

11510 10/30/2013 09:06 PM Aaron Marcuse-Kubitza

mappings/VegCore.htm: regenerated from wiki. added taxon_determination__is_current, taxon_determination__is_original.

11509 10/30/2013 09:02 PM Aaron Marcuse-Kubitza

mappings/VegCore.htm: regenerated from wiki. added taxon_determination__is_current, taxon_determination__is_original.

11508 10/30/2013 08:07 PM Aaron Marcuse-Kubitza

planning/timeline/timeline.2013.xls: geoscrubbing automated pipeline: split into subtasks "build pipeline", "test pipeline", and "integrate pipeline into import process"

11507 10/30/2013 08:04 PM Aaron Marcuse-Kubitza

planning/timeline/timeline.2013.xls: geoscrubbing re-run: moved recent checkmarks to "geoscrubbing automated pipeline" since the work on these actually relates to automating the geoscrubbing, not the one-time reload (which was already completed)

11506 10/30/2013 08:02 PM Aaron Marcuse-Kubitza

planning/timeline/timeline.2013.xls: geoscrubbing: made "geoscrubbing re-run" a subtask of the main geoscrubbing task, instead of geoscrubbing re-run being the supertask. updated for Paul's progresss.

11505 10/30/2013 07:23 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxondetermination_set_iscurrent(): include new iscurrent__verbatim, so that taxondeterminations the datasource marks as current are always considered first. this currently applies to VegBank and CVS.

11504 10/30/2013 07:17 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxondetermination.isoriginal: made it nullable like iscurrent__verbatim, because this is populated from the datasource. taxondetermination_set_iscurrent() now supports isoriginal=NULL, so this is not a problem.

11503 10/30/2013 07:08 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxondetermination.is_datasource_current: renamed to iscurrent__verbatim and made it nullable, so that this can be used to store the verbatim iscurrent status

11502 10/30/2013 07:04 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxondetermination_set_iscurrent(): removed setting of is_datasource_current (which is now the same as iscurrent), so that this can be used to store the verbatim iscurrent status

11501 10/30/2013 06:59 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxondetermination_set_iscurrent(): isoriginal: make sure it is always either true or false, so that if the NOT NULL constraint on this is ever removed you don't end up with the incorrect sort order false, true, NULL (it should be false=NULL, true)

11500 10/30/2013 06:42 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: use plain taxondetermination.iscurrent instead of is_datasource_current since these are now the same

11499 10/30/2013 06:38 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxondetermination_set_iscurrent(): is_datasource_current: set to the same value as iscurrent, since these now have the same formula

11498 10/30/2013 06:34 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxondetermination_set_iscurrent(): removed no longer used accepted, matched determinationtypes (for these determinations, left-join to TNRS.ScrubbedTaxon)

11497 10/30/2013 06:24 PM Paul Sarando

Updated biengeo README with new script workflow.

11496 10/30/2013 06:24 PM Paul Sarando

Split geovalidate.sh into install and update scripts.

Split geovalidate.sh into install.sh and update_gadm_data.sh scripts.
The install.sh script creates the databse and uses the install sql
scripts to create all required tables.
The update_gadm_data.sh script downloads the GADM data and creates the...

11495 10/30/2013 06:24 PM Paul Sarando

Refactored geonames.sh to update_geonames_data.sh

Renamed geonames.sh to update_geonames_data.sh and moved many of the SQL
statements from the bash script into supporting update and truncate sql
scripts.
These sql and update_geonames_data.sh scripts now assume all required...

11494 10/30/2013 06:24 PM Paul Sarando

Split up geonames-to-gadm.sql into 3 scripts.

Each script only operates on one table within a transaction.
These scripts now assume the tables have already been created (by
install scripts added in a previous commit), and each starts out by
truncating the table it will update with new data.

11493 10/30/2013 06:24 PM Paul Sarando

Added geoscrub.sh script.

This script runs the load-geoscrub-input.sh, geonames.sql, and
geovalidate.sql scripts in order to load and scrub vegbien input data.
Updated README to explain the new script.
Minor updates to load-geoscrub-input.sh.

11492 10/30/2013 06:03 PM Aaron Marcuse-Kubitza

inputs/SALVIAS/projects/postprocess.sql: remove institutions that we have direct data for: documented that most of the 13139 removed plots are from duplicates (where we have direct data). this leaves only 560 of SALVIAS's original 13699 plots.

11491 10/30/2013 05:53 PM Aaron Marcuse-Kubitza

inputs/SALVIAS/projects/postprocess.sql: remove example data

11490 10/30/2013 05:48 PM Aaron Marcuse-Kubitza

inputs/SALVIAS/projects/postprocess.sql: remove private data that should not be publicly visible (this was probably already removed by the plotMetadata.AccessCode filter in salvias_plots.~.clean_up.sql)

11489 10/30/2013 05:44 PM Aaron Marcuse-Kubitza

inputs/SALVIAS/projects/postprocess.sql: remove institutions that we have direct data for (Madidi, VegBank)

11488 10/30/2013 04:23 PM Aaron Marcuse-Kubitza

bugfix: inputs/VegBank/plot_/postprocess.sql: coordinateUncertaintyInMeters__from_fuzzing: need to convert km to m in the fuzzing radii. updated derived cols runtimes.

11487 10/30/2013 04:05 PM Aaron Marcuse-Kubitza

inputs/VegBank/plot_/postprocess.sql: remove duplicated CVS plots (2323 of 7079 CVS plots are removed by this)

11486 10/30/2013 03:54 PM Aaron Marcuse-Kubitza

planning/timeline/timeline.2013.xls: updated for progress

11485 10/30/2013 03:22 PM Aaron Marcuse-Kubitza

added exports/2013-7-10.Naia.range_limiting_factors.csv.run

11484 10/30/2013 03:04 PM Aaron Marcuse-Kubitza

bugfix: exports/2013-10-18.Brian_Enquist.Canadensys.csv.run: do not override the table to analytical_stem, because the extract-specific view should be used instead. this was actually benign, because extract.run export_() always sets $table to the extract-specific view.

11483 10/30/2013 02:57 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added 2013-7-10.Naia.range_limiting_factors

11482 10/30/2013 02:45 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: sync_analytical_stem_to_view(): row_num: renamed to taxon_occurrence__pkey because previous taxon determinations have been removed, so each row is in fact a taxon_occurrence (~= VegCore.vegpath.org?ERD.taxon_occurrence)

11481 10/30/2013 02:20 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: analytical_stem_view: don't ORDER BY datasource, because this requires a slow full-table sort after the hash joins. (when selecting a subset of analytical_stem_view, nested loops are used automatically without needing an ORDER BY to force this.) to get the datasource-sorted order (plus a sort-order guarantee), you can still add a manual `ORDER BY datasource`, which will use a fast index scan on one of the datasource indexes.

11480 10/30/2013 01:58 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem: added row_num, which can serve as the taxon_observation ID (DwC occurrenceID)

11479 10/30/2013 01:53 PM Paul Sarando

Updated load-geoscrub script with configurable db.

load-geoscrub-input.sh now uses a variable with the db name defined at
the top of the script.
Updated the default db host to 'localhost' for this script.

11478 10/30/2013 12:11 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem: locationID... index: use eventDate instead of dateCollected since it's now eventDate that identifies the locationevent

11477 10/30/2013 12:11 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem: locationID... index: use eventDate instead of dateCollected since it's now eventDate that identifies the locationevent

11476 10/30/2013 04:41 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: use plot.** to obtain plot-related fields, so that the same code does not need to be maintained in both analytical_stem_view and plot.**

11475 10/30/2013 04:32 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: moved specimen-specific fields to occurrence section

11474 10/30/2013 03:50 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view, plot.**: added separate location__cultivated__bien

11473 10/30/2013 03:11 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added separate eventDate, in addition to dateCollected

11472 10/30/2013 02:59 AM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: dateCollected: use aggregateoccurrence.collectiondate before locationevent.obsstartdate rather than after, because this is more accurate. it was previously the other way around to allow dateCollected to be the pkey for the row's locationevent (for plots data).

11471 10/30/2013 02:38 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view, plot.**: locationevent__pkey: moved to right before the locationevent-related fields

11470 10/29/2013 06:53 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: changed column order, etc. to match plot.**

11469 10/29/2013 06:52 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: changed column order, etc. to match plot.**

11468 10/29/2013 06:46 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: plot.**: added locationevent__pkey so that this view can be joined to other VegBIEN tables, which require the internal pkey

11467 10/29/2013 06:29 PM Aaron Marcuse-Kubitza

derived/biengeo/README.txt: geoscrub new data: geovalidate.sql: added runtime from Paul

11466 10/29/2013 09:05 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: sync_analytical_stem_to_view(): speciesBinomialWithMorphospecies index: documented runtime (1 h)

11465 10/29/2013 08:56 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: plot.**: updated to use the same column formulas as analytical_stem_view

11464 10/29/2013 08:19 AM Aaron Marcuse-Kubitza

planning/timeline/timeline.2013.xls: add globally-unique occurrenceID: removed "globally-unique" because Naia is actually OK with this being numeric (i.e. unique within our DB)

11463 10/29/2013 08:19 AM Aaron Marcuse-Kubitza

planning/timeline/timeline.2013.xls: updated for progress

11462 10/29/2013 07:46 AM Aaron Marcuse-Kubitza

lib/runscripts/import_subset.run: $version: use new $extract_view, which is set to the same value that this was

11461 10/29/2013 07:45 AM Aaron Marcuse-Kubitza

lib/runscripts/extract.run: use the extract-specific view instead of all of analytical_stem

11460 10/29/2013 07:42 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added 2013-10-18.Brian_Enquist.Canadensys view

11459 10/29/2013 06:51 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: sync_analytical_stem_to_view(): added index on speciesBinomialWithMorphospecies for Brian Enquist's Canadensys request

11458 10/29/2013 06:19 AM Aaron Marcuse-Kubitza

planning/timeline/timeline.2013.xls: updated for progress

11457 10/29/2013 04:16 AM Aaron Marcuse-Kubitza

exports/2013-10-18.Brian_Enquist.Canadensys.csv.run: documented runtime (35 min, now that bugs have been fixed)

11456 10/29/2013 03:33 AM Aaron Marcuse-Kubitza

bugfix: bin/with_all: @inputs default value: use `local`, so that the default value is only set for the current function and doesn't leak back out into the caller. this fixes a bug in subset imports where import_all's Source/import call to with_all would add the .* datasources, but these would then stay in for the import_scrub call, causing extra .* datasources to incorrectly be imported.

11455 10/29/2013 02:22 AM Aaron Marcuse-Kubitza

planning/timeline/timeline.2013.xls: usability testing: added additional subtask to validate the scientists' extracts (i.e. check that the extract fulfills their request)

11454 10/29/2013 02:17 AM Aaron Marcuse-Kubitza

planning/timeline/timeline.2013.xls: provide scientists with their requested data: added separate subtask for Brian Enquist's Canadensys extract

11453 10/29/2013 02:12 AM Aaron Marcuse-Kubitza

planning/timeline/timeline.2013.xls: updated for progress and revised schedule

11452 10/29/2013 01:53 AM Aaron Marcuse-Kubitza

bugfix: schemas/pg_hba.Mac.conf: made same change for Mac as was made for Linux in r11451

11451 10/29/2013 01:22 AM Aaron Marcuse-Kubitza

bugfix: schemas/pg_hba.conf: don't allow ident authentication for Unix socket connections, because this apparently prevents having normal, password-based connections ("md5"). note that just switching the order of the ident and md5 entries is not useful, because whichever authentication type comes second will be ignored completely. this problem was previously worked around by just not using Unix socket connections at all, and always specifying "localhost" as the host to force a hostname-based connection. this does not affect the postgres superuser, because they have their own ident line in pg_hba.conf.

11450 10/25/2013 06:15 PM Paul Sarando

Added db user and host to load-geoscrub-input.sh

The psql commands in load-geoscrub-input.sh will now connect with a
specific user on a specific host.
Updated the 'COPY' sql statement to a '\COPY' statement, so that the
psql user does not have to be a PostgreSQL superuser.

11449 10/25/2013 04:51 PM Aaron Marcuse-Kubitza

derived/biengeo/README.txt: geoscrub new data: steps that use .sql scripts: added the psql commands to run these

11448 10/25/2013 04:22 PM Paul Sarando

Updated install instructions in the README.

11447 10/25/2013 03:00 PM Aaron Marcuse-Kubitza

derived/biengeo/README.txt: geoscrub new data: noted that this now deletes any previous geoscrubbing results

11446 10/25/2013 02:58 PM Aaron Marcuse-Kubitza

derived/biengeo/README.txt: added steps to set the working dir for each set of steps

11445 10/25/2013 02:54 PM Aaron Marcuse-Kubitza

derived/biengeo/README.txt: added section on obtaining source code, including path to Paul's in-progress files on vegbiendev (not sure whether the in-progress files are needed to run the core scripts in steps 1-6)

11444 10/25/2013 02:44 PM Aaron Marcuse-Kubitza

derived/biengeo/README.txt: moved commands to run to the top of the README. flagged commands-sections with *** and an identifying label.

11443 10/25/2013 02:04 PM Paul Sarando

Initial checkin of geoscrub install SQL files.

Added install.*.sql files that will do initial table creation for all
required tables.
Added a truncate.vegbien_geoscrub.sql script that will clear tables related to
data downloaded in load-geoscrub-input.sh....

11442 10/25/2013 02:04 PM Paul Sarando

Update load-geoscrub-input.sh to download from URL.

Removed logic to dump input data directly from the vegbien database and
to download the input from a URL provided by AMK instead.
Also updated this script to download the file into an input data
directory, rather than just into the current working directory.

11441 10/25/2013 11:56 AM Aaron Marcuse-Kubitza

planning/timeline/timeline.2013.xls: reload core & analytical database scheduled for this week: postponed to give us additional time to do datasource validations

11440 10/25/2013 09:58 AM Aaron Marcuse-Kubitza

inputs/input.Makefile: added %/import_temp alias for %/import, to mirror the presence of import_temp for import

11439 10/25/2013 09:24 AM Aaron Marcuse-Kubitza

fix: inputs/VegBank/taxonobservation_/map.csv: remapped authorplantname to OMIT because these are not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). see wiki.vegpath.org/Spot-checking#2013-10-10 > Mike Lee's conference call feedback.

11438 10/25/2013 09:22 AM Aaron Marcuse-Kubitza

fix: inputs/VegBank/taxonobservation_/map.csv: remapped int_* to OMIT because these are not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). see wiki.vegpath.org/Spot-checking#2013-10-10 > Mike Lee's conference call feedback.

11437 10/24/2013 07:09 PM Aaron Marcuse-Kubitza

exports/2013-10-18.Brian_Enquist.Canadensys.csv.run: inherit from new import_subset.run (which uses extract.run)

11436 10/24/2013 07:08 PM Aaron Marcuse-Kubitza

added lib/runscripts/import_subset.run, extract.run

11435 10/24/2013 05:21 PM Aaron Marcuse-Kubitza

added exports/2013-10-18.Brian_Enquist.Canadensys.csv.run

11434 10/24/2013 05:07 PM Aaron Marcuse-Kubitza

bin/make_analytical_db: removed no longer needed setting of $schema to $public, because this is now done by psql()

11433 10/24/2013 05:06 PM Aaron Marcuse-Kubitza

lib/sh/local.sh: psql(): also accept $public as the $schema param, since this is used by a lot of import scripts

11432 10/24/2013 04:24 PM Aaron Marcuse-Kubitza

lib/sh/util.sh: added require_dot_script()

11431 10/24/2013 04:13 PM Aaron Marcuse-Kubitza

bugfix: lib/sh/util.sh: $top_script: use @BASH_SOURCE instead of $0, because this is also defined for .-scripts

11430 10/24/2013 04:03 PM Aaron Marcuse-Kubitza

bugfix: bin/import_all: restore the working dir when main() is done, in case it started as something other than the root dir

11429 10/24/2013 03:49 PM Aaron Marcuse-Kubitza

bin/after_import: support turning off the end-of-import backup for imports that are not the full database

11428 10/24/2013 03:26 PM Aaron Marcuse-Kubitza

bugfix: lib/runscripts/util.run: `trap on_exit EXIT`: only set this if the script is not a dot script, because if it is a dot script, on_exit() will not be invoked until the calling shell exits, which may be much later than when the script is run. previously, this was handled by canceling the EXIT trap if on_exit() is run manually, but this would not work correctly if a load-time error prevented on_exit() from running and canceling the trap.

11427 10/24/2013 03:21 PM Aaron Marcuse-Kubitza

bugfix: lib/runscripts/util.run: if is_dot_script, fix $ when no args causes this to incorrectly contain the script name. use is_dot_script rather than the presence of $ args to decide whether to use @BASH_ARGV, because @BASH_ARGV is actually wrong when run as a .-script (it contains the script name).

11426 10/24/2013 03:17 PM Aaron Marcuse-Kubitza

bugfix: lib/sh/util.sh: is_dot_script(): need to subtract 1 from ${#BASH_LINENO[@]}, because this is the array length rather than the index of the last element as in Perl

11425 10/24/2013 02:58 PM Aaron Marcuse-Kubitza

lib/sh/util.sh: added is_dot_script()

11424 10/24/2013 01:15 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: taxondetermination_set_iscurrent(): is_datasource_current (used by analytical_stem_view): need to separately check if `determinationtype IS NULL`, because `determinationtype NOT IN (accepted, matched))` will return NULL (false) if determinationtype is NULL, causing no match

11423 10/24/2013 01:11 PM Aaron Marcuse-Kubitza

bugfix: bin/make_analytical_db: when running into a public schema other than "public", also pass this to `/run export_` (which currently uses $schema instead of $public)

11422 10/24/2013 01:10 PM Aaron Marcuse-Kubitza

bugfix: bin/import_all: fix $ when .-included without args (which causes bash to put the wrong values in $ instead of leaving it empty)

11421 10/24/2013 01:09 PM Aaron Marcuse-Kubitza

bin/import_all: `make schemas/$version/install`: reinstall instead to allow re-running the import to the same custom schema (e.g. 2013-10-18.Brian_Enquist.Canadensys)

11420 10/24/2013 01:07 PM Aaron Marcuse-Kubitza

bin/import_all: `make schemas/$version/install`: ignore errors if schema exists, to support running with -e