Allow spatial searching on WKT (HG) for specimens

[mkoo]

Use case: searching for specimens that intersect a polygon like Yosemite NP using the footprint or WKT polygon even if specimen does not reference Yosemite in the locality description. Can we leverage Google Maps API or BerkeleyMapper perhaps?

[dustymc]

We don't have Oracle Spatial, so I don't think that back-end polygon intersection queries are possible. (Perhaps there's some tool I haven't found though??)

The only way I see to leverage a service would be to pass it all of our (currently 733,197) localities along with the "search shape," which doesn't seem practical.

Perhaps I could narrow that down and grab the coordinates within {a few degrees or something} of our "yosemite" polygon and check those. (That code, which leverages Google APIs, exists as the "Find specimens with coordinates "outside" the WKT shape (new window)" widget from edit geography.) I'd expect that to be a fairly slow and somewhat wonky operation not particularly practical for public specimen search, at best.

You can SORT OF get at the idea with the cached reverse georeferencing data, but that relies on Google's shape for Yosemite (which we can't see). Results of....

create table temp as 
select 
  higher_geog,
  spec_locality,
  S$GEOGRAPHY
from 
  locality,
  geog_auth_rec 
where 
  locality.geog_auth_rec_id=geog_auth_rec.geog_auth_rec_id and 
  upper(higher_geog) not like '%YOSEMITE%' and 
  upper(spec_locality) not like '%YOSEMITE%' and 
  upper(S$GEOGRAPHY) like '%YOSEMITE NATIONAL PARK%'
;

.... not attached, because GitHub doesn't like CSV?!?

https://docs.google.com/spreadsheets/d/1PpiuJ3XPyjTeZc3U9OUowh4J3pNeMq-nRnJV_2dDCzg/edit?usp=sharing

Moving to wish list - we have a place for these data, we have a fair bit of data in place, finding tools to do this seems extremely useful.

[dustymc]

Merge-->#2660