GIS » Gisaf

Investigating.

Found that there are 2 points with the same id 2728060639: one in V_FENC_MESH and one in V_TOPO_ELPT.
It's very unlikely that it can happen (although theoretically possible, that's the role of hash functions).

avgis=# select id, orig_id, date, ST_AsText(geom), srvyr_id, equip_id from avsm_raw_survey."RAW_V_TOPO_ELPT" where id=2728060639;
     id     | orig_id |    date    |                st_astext                 | srvyr_id | equip_id 
------------+---------+------------+------------------------------------------+----------+----------
 2728060639 | 250144  | 2017-11-20 | POINT Z (370524.2535 1327309.99 53.4144) |        1 |        2
(1 row)

avgis=# select id, orig_id, date, ST_AsText(geom), srvyr_id, equip_id from avsm_raw_survey."RAW_V_FENC_MESH" where id=2728060639;
     id     | orig_id |    date    |                 st_astext                  | srvyr_id | equip_id 
------------+---------+------------+--------------------------------------------+----------+----------
 2728060639 | 50133   | 2017-11-20 | POINT Z (370556.4542 1327308.5898 53.6987) |        1 |        2
(1 row)

I checked in the basket of survey points and found 2 files for that date 2017-11-20 for the same project, equipment and surveyor.

So, the issue isn't about the reconciliation but actually about the workflow: it wasn't designed that the same surveyor and equipment could have several inputs. So, different points end up being assigned the same id (by the hash function), the latest one being imported is actually stored in the raw_survey table:

avgis=# select id, date, project_id, orig_id, srvyr_id, category, equip_id from gisaf_survey.raw_survey where id = 2728060639;
     id     |    date    | project_id | orig_id | srvyr_id | category | equip_id 
------------+------------+------------+---------+----------+----------+----------
 2728060639 | 2017-11-20 |          1 | 250144  |        1 | T15      |        2

Given that many such duplicate files are in the basket, the issue needs actually a major overview about workflow/process of survey data import.

Closing this ticket: reconciliation works as expected, and we need to open another ticket about the raw survey data import, which should include the mitigation of the data imported so far (possible headache to come...).