Identify initial set of trees to populate into treetracker.tree

[ZavenArra]

Identify initial freetown captures and/or Identify initial forestmatic captures

Create tree records for these and link the capture record.

[ZavenArra]

Initial capture has the #species# format in the notes field. Check dates on this.

[ZavenArra]

select note from trees where planting_organization_id IN (select entity_id from getEntityRelationshipChildren(178));

[ZavenArra]

select note from trees join planter on trees.planter_id = planter.id where planter.organization_id IN (select entity_id from getEntityRelationshipChildren(178));

[sebastiangaertner]

@dadiorchen Yes this is the way to identify the first capture of a tree from Freetown only. (178 and its sub orgs). This is still true and should be the base of the tree entries under Freetown.

[sebastiangaertner]

SELECT *
FROM public.trees t
join planter p on t.planter_id = p.id
WHERE p.organization_id in (select entity_id from getEntityRelationshipChildren(178))
and length(t.note) > 4;

[camwebb]

I'm looking into the potential to use the Freetown paper tag information in the notes field to generate a test set of captures and known recaptures. I queried the DB as above adding a tag regexp of t.note ~ '^ *[A-Za-z]+[0-9]+ *[a-z]+ *[0-9]+ *$' and got 234,746 captures. Yay! Then I wrote a script to find multiple instances of the same tag, and generate a simple webpage so we can view them.

Some results:

Distribution of number of instances of the same tag (mostly 1, but up to 28):

The maximum date range between captures having the same tag was 341 days, but most duplicate occurrences of the tag were on the same date - i.e., the notes field was (auto?) copied into a subsequent capture of a different plant.

You can see the capture matching pages here:

• 100 cases of four capture instances of the same tag: view
• 100 cases of two capture instances of the same tag with a minimum of 50 days between captures: view

The conclusion: it appears we cannot use the tag information in the notes field. Except for a few rare cases, the plants are clearly not the same ones 🙁 This does not mean that there are no true recaptures, but just that repeated tag codes in the notes field appears to be too often erroneous to use.

(One possible modification to the filter in the script is to limit the candidates for true recaptured trees to those within some number of metres apart, using the GPS locations. No time now though.)

[dadiorchen]

@camwebb the SQL above is to generate tree, that means, the first capture, if I understand this correctly, all those captures matching this condition are different trees, so we shouldn't use the same note to bind trees, right @sebastiangaertner ?

[dadiorchen]

@Kpoke I think we should use treetracker.capture as the source of this operation, right? Migrate treetracker.capture first, then do this?