Lat and long rounds incorrectly on saving new site

LizziOh commented 11 months ago

Latitude is truncating at 6 dec places and rounds up for no reason, then saves as rounded value Longitude is truncating at 5 dec places (and should be 6) and rounds up incorrectly, then saves as the rounded value

for example I entered longitude 166.1830844 for new site NC16 and it saved as 166.18309

this is quite high priority, i have added many new sites.

also the issue with rounding and survey lat and lontigudes appearing on ingest when not needed still persists.

bpasquer commented 11 months ago

also the issue with rounding and survey lat and lontigudes appearing on ingest when not needed still persists. sorry @LizziOh, I am not sure what issue you are referring to.

LizziOh commented 11 months ago

There should be a previous issue that better explains this.

for example the last one should have blanks in the survey lat and long columns as it is the same as the site's coordinates: <html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">

survey_id | country | area | location | mpa | site_code | site_name | latitude | longitude | depth | survey_date | latest_surveydate_for_site | has_pq_scores_in_db | has_rugosity_scores_in_db | has_pqs_catalogued_in_db | divers | visibility | hour | direction | survey_latitude | survey_longitude -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- 923402660 | Morocco | Morocco | Mediterranean (southwest) | MEL18 | Alhucemas-NE | 35.21381 | -3.88854 | 5.1 | 10/08/2020 | TRUE | FALSE | FALSE | FALSE | Alberto Moreno Mejias, Angel Orozco Rodriguez, José Antonio Sanabria Fernández, Natali Lazzari | 8 | 10:45:00 | SW | 35.21373 | -3.88852 923402463 | Morocco | Morocco | Mediterranean (southwest) | MEL18 | Alhucemas-NE | 35.21381 | -3.88854 | 7 | 16/08/2019 | FALSE | FALSE | FALSE | FALSE | Angel Orozco Rodriguez, José Antonio Sanabria Fernández | 7 | 11:30:00 | NE | 35.21347 | -3.89043 923402436 | Morocco | Morocco | Mediterranean (southwest) | MEL18 | Alhucemas-NE | 35.21381 | -3.88854 | 4.9 | 10/08/2020 | TRUE | FALSE | FALSE | FALSE | Alberto Moreno Mejias, Angel Orozco Rodriguez, José Antonio Sanabria Fernández, Natali Lazzari | 8 | 10:45:00 | SW | 35.21373 | -3.88852 923402671 | Morocco | Morocco | Mediterranean (southwest) | MEL18 | Alhucemas-NE | 35.21381 | -3.88854 | 9.2 | 13/01/2019 | FALSE | FALSE | FALSE | FALSE | José Antonio Sanabria Fernández, Natali Lazzari | 4 | 10:30:00 | W | 35.21381 | -3.88854

bpasquer commented 11 months ago

ok, if it is that issue we are talking about https://github.com/aodn/nrmn-application/issues/722 then yes it has not been addressed yet

bpasquer commented 11 months ago

Looking at past issues related to Lat/Lon rounding: was addressed in https://github.com/aodn/nrmn-application/issues/305 , with relevant backlog https://github.com/aodn/backlog/issues/3032. It was agreed that Lat/Lon should be rounded to 5 decimals. The implementation involved changing Lat and Lon data type in the API and in the database from double to float. However, even though query for the site table creation states that lat and lon are float, all lat and lon are stored as double precision in the DB. What has happened?

utas-raymondng commented 11 months ago

I think I know the reason that decimal place added, this is because the existing code use a lower precision number (float) to hold the lat/lng, when it save to the db, which is of (double), some number added randomly.

I put a fix and ready for PR

bpasquer commented 11 months ago

To provide the correct number of decimals, latitude and longitude should be stored as float in the database, not as double precision

bpasquer commented 7 months ago

it is assumed that the issue stems from the adoption of the double precision data type in DB (Aug 2021) which actually happened before the deployment of the software to prod (1st created ingest job dates from September 2021) https://github.com/aodn/nrmn-application/commit/77c79d49084e3a08f038350fdafcf37fc5dc1644:

Sites:

254 new sites have been created select * from nrmn.site_ref_aud where revtype= 0
83 sites have had their coordinates modified (select * from nrmn.site_ref_aud where revtype= 1 and (latitude_mod =true or longitude_mod =true)) (haven't checked for duplicates between new and modified sites)

Historial site (=existing prior to release of software to prod) coordinates can be checked from site master. There is no registry of site coordinates to provided source of original lat/lon other than the production DB.

Surveys:

2653 surveys have been created
244 surveys have had their coordinates modified (haven't checked for duplicates)

Survey coordinates can be cross-checked with the the values in the original datasheets stored in S3.

bpasquer commented 5 months ago

After discussion during catch-up meeting (2024-02-22, 2024-04-04), the assumption is that only sites newly created or modified since the release to production might be affected by the rounding issue :

Extracting info from new and modified sites in the DB - 328 rows: Results from the following query are attached below:

with stp as(SELECT
    site_ref.site_id,
    case
        when revtype =0 then 'ADD'
        when revtype =1 then 'MOD'
        when revtype =2 then 'DEL'
    end as revtype,
    site_ref.site_code,
    site_ref.site_name,
    site_ref.latitude as site_ref_latitude,
    latitude_mod,
    site_ref.longitude as site_ref_longitude,
    longitude_mod
FROM site_ref
join nrmn.site_ref_aud sra on site_ref.site_id = sra.site_id
    where ((length(CAST(site_ref.latitude AS VARCHAR)) - position('.' in CAST(site_ref.latitude AS VARCHAR)) >8) or
          (length(CAST(site_ref.longitude AS VARCHAR)) - position('.' in CAST(site_ref.longitude AS VARCHAR)) >8))
    and (latitude_mod=true or longitude_mod=true))
select distinct site_id,revtype,site_code,site_name,site_ref_latitude,latitude_mod,site_ref_longitude,longitude_mod from stp
order by site_id

Site_revision.csv

bpasquer commented 5 months ago

it is important to note, however, that site coordinate precision have sometimes be slightly altered and results in values with more than 5(or 8) decimal coordinates for sites tha have note been modified. This can probably due to the conversion from float to double precision: for example ( see the full list attached)	site code	db	db	site master	site master
	lat	lon	lat	lon
PAC40	-14.533079999999998	-168.1567	-14.53308	-168.1567

SiteCoordinatesLong_Full_list.csv

LizziOh commented 5 months ago

Thanks @bpasquer for this information. Is it at all possible to extract the data that these sites were created / modified from the audit tables? It would help a lot for the checking/correction process I think...

bpasquer commented 5 months ago

@LizziOh @atcooper1 Sorry for the delay in extracting the history of the sites that were created/ modified. Here is a file listing: the changes that were applied to sites coordinates: -timestamp -revision type: ADD :created, MOD:edited

site ID, code and name
new site lat and long (site_ref_latitude,site_ref_longitude)
latitude_mod, longitude mod: indicates a change in coordinate
previous_lat, previous_lon: the coordinate value before the change

Note that I assumed the original coordinates were taken from the sites_master we used during the DB migration when dealing with modified coordinates.

Coordinates_history.csv

aodn / nrmn-application

Lat and long rounds incorrectly on saving new site #1320