UTM zone cleanup

[dustymc]

Issue Documentation is http://handbook.arctosdb.org/how_to/How-to-Use-Issues-in-Arctos.html

Is your feature request related to a problem? Please describe. See https://github.com/ArctosDB/arctos/issues/5910#issuecomment-1472450552 - some UTM zones aren't functional, and UTM and datum have a really wonky intersection

Describe what you're trying to accomplish

Make it better

Describe the solution you'd like

??

Describe alternatives you've considered

I'm up for anything...

Additional context

Priority

[Jegelewicz]

@dustymc I think that UTM conversions are working now that the code table has appropriate terms, so we need to help NMMNH clean up stuff that was assigned coordinates from the UTM that was entered in data entry.

My first question is - can we find all localities that were entered with UTM coordinates (as entered coordinates) that Arctos converted to decimal degrees?

[acdoll]

I can't seem to get the converter to work on a record bulkload. What goes into the coodinate_datum column?

[dustymc]

Data? Example is probably easiest.

You have to sync up https://arctos.database.museum/info/ctDocumentation.cfm?table=ctutm_zone and https://arctos.database.museum/info/ctDocumentation.cfm?table=ctdatum

https://arctos.database.museum/info/ctDocumentation.cfm?table=ctutm_zone#13n will ONLY work with https://arctos.database.museum/info/ctDocumentation.cfm?table=ctdatum#world_geodetic_system_1984 for example.

[acdoll]

They are all UTM 12N and 13N. I started with World Geodetic System 1984 in coordinate_datum and it didn't like it. CNHP_2023-25.csv

[dustymc]

OK, Step One is to ignore everything I just wrote because it's wrong...

coordinate_datum=North American Datum 1927 coordinate_utm_zone = NAD27 / UTM zone 13N

It's the slash in the value that matters, not documentation.

From the first row of your CSV:

select convertRawCoords('{
    "debug":"true",
    "orig_lat_long_units":"UTM",
    "utm_zone":"NAD27 / UTM zone 13N",
    "utm_ns":"4520027",
    "utm_ew":"743221",
    "datum":"North American Datum 1927"
}'::json)::text ;

NOTICE:  orig_lat_long_units: UTM
NOTICE:  converting from UTM SRID
                (https://arctos.database.museum/info/ctDocumentation.cfm?table=ctutm_zone) 
                to datum SRID (https://arctos.database.museum/info/ctDocumentation.cfm?table=ctdatum)
NOTICE:  utm_srid: 26713
NOTICE:  datum_srid: 4267
NOTICE:  utm_ew: 743221
NOTICE:  utm_ns: 4520027
NOTICE:  conversion operation: 
            ST_Transform(
                ST_SetSrid(
                    ST_MakePoint(
                        utm_ew,
                        utm_ns
                    ),
                    utm_srid
                ),
                datum_srid
            )
NOTICE:   SELECT ST_X(geom), ST_Y(geom) from (SELECT ST_Transform(ST_SetSrid(ST_MakePoint(743221,4520027),26713),4267) as geom) x
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  At this point we should have coordinates transformed to DD.ddd format, and a datum
NOTICE:  noconvlat is not null and noconvlong is not null - we can proceed
NOTICE:  noconvlat: 40.79714534930391
NOTICE:  noconvlong: -102.11708623233476
NOTICE:  v_datum: North American Datum 1927
NOTICE:  datum_srid: 4267
NOTICE:  wgs84: 4326
NOTICE:  Operation: 
            SELECT 
                ST_Transform(
                    ST_SetSrid(
                        ST_MakePoint(
                            noconvlong,
                            noconvlat
                        ),
                        datum_srid
                    ), 
                    wgs84
                )
NOTICE:  Evaluated: 
                SELECT 
                    ST_X(geom), 
                    ST_Y(geom) 
                from (
                    select
                        ST_Transform(
                            ST_SetSrid(
                                ST_MakePoint(
                                    -102.11708623233476,
                                    40.79714534930391
                                ),
                                4267
                            ), 
                            4326
                        ) as geom
                ) g
                         convertrawcoords                          
-------------------------------------------------------------------
 {"lat":40.79712476827078,"lng":-102.11757439932005,"status":"OK"}
(1 row)

[acdoll]

I'm not sure I did that right, but it still didn't pass: ACD_bulkloader_stage (4).csv

[acdoll]

Wait - I got it! I had to remove the space from 'NAD 27'

[acdoll]

Thanks!

[campmlc]

I've never had to deal with UTMS previously, but now have a dataset with recently collected UTM data. Is this something we can now easily add to and map in Arctos? UTM_Xea_13S UTM_Yno_13S 0349118 3804765

[Jegelewicz]

But only if you don't also have lat/long