NearNamedPlace the MEL way

The current Texpress implementation of MELISR does not have a gazetteer field. Hence, MEL does not presently record generalised locality information and has so far been unable to deliver this information to AVH.

The following script obtains the nearest named place for all geocoded records in MELISR.

In short, the script retrieves geocoded records from MELISR (function getMelisrRecords), then passes the records one by one to the reverse geocode functions, findNearestAu for Australian records and findNearestForeign for foreign ones. The query results returned by these functions are then inserted into the abcd_nearnamedplace table, or, if a record with the same MEL accession number already exists, the existing record is updated (function insertNearest).

The reverse geocoding functions query for places that are within one degree in each direction from the latitude and longitude recorded in the MELISR record, calculate the distance between the MELISR record and all these places (as well as the bearing) and orders the results by this distance. The results are then limited to the first row, so only the place that is closest to the collecting locality is returned. In order to avoid the more obscure place names, only those places categorised as a populated place are included in the query.

The script runs from the command line. There is a compulsory argument (see usage) to set the start date. A second optional argument tells the script the maximum number of records to select from the collections database. This probably does not have a practical value anymore, but was very handy for testing the script. It takes just under a minute to reverse geocode a thousand records. It took about 11 hours to do all geocoded records in MELISR (appr. 680,000).

 Note [2012-05-06]: we are still using this script after the migration of our database to Specify. Only the SQL in the getMelisrRecords function has changed.