Create a tool to repatriate data from CoGe and other georeferencing utilities

[grantfitzsimmons]

The problem

Specify users in the DigIn project are dumping textual locality information from their databases and sending it out for georeferencing (primarily using the CoGe collaborative system). This information is sent on a specimen-by-specimen basis, and includes data fields from multiple Specify tables. After georeferencing, there is a need to merge the results of the georeferencing operation back into a specific subset of Specify fields. The intent is to do this using a GUID-based update back into the original locality records.

We think we have defined a specific pathway for this that (we believe) should be practical to implement as a tool for Specify 6 and Specify 7 users.

Tables and fields involved in the update

The required fields are all in the Locality table. Desirable fields are all in the Geo Coord Detail table.
Record update would be based on the Locality::GUID field. That field would be present in all records sent out for georeferencing, and in all records coming back for update.
Our understanding is that there can be zero or one Geo Coord Detail records for each Locality record. If that is the case, the appropriate fields in the matching Geo Coord Detail record would also be updated from the georeferencing response data. If the Geo Coord Detail record does not exist, it would be created by the update tool and filled.

Table	Field
Locality	GUID
Locality	Datum
Locality	Latitude1
Locality	Longitude1
Geo Coord Detail	GeoRefDetDate
Geo Coord Detail	GeoRefDetRef
Geo Coord Detail	GeoRefRemarks
Geo Coord Detail	GeoRefVerificationStatus
Geo Coord Detail	MaxUncertaintyEst
Geo Coord Detail	MaxuncertaintyEstUnit
Geo Coord Detail	UncertaintyPolygon
Geo Coord Detail	NoGeoRefBecause
Geo Coord Detail	Protocol
Geo Coord Detail	Source

Optimal and fallback solutions

The optimal solution would be as described above: update fields in Locality and Geo Coord Detail records (creating a Geo Coord Detail record if needed), using the Locality::GUID field as the update matching key. The data to be updated could be provided as a spreadsheet or a CSV/TSV file with the appropriate data columns.
As a fallback, if only Locality table records can be updated, we’d look into concatenating the other desirable fields (that otherwise would go into a Geo Coord Detail record) and stashing them into a co-opted Text# field in Locality.

Requested By: Dean Pentcheff and Nelson Rios