Open vince7lf opened 1 year ago
WIP; has the SQL to extract the labels and images; next is to get the geojson path, open the geojson template (no annotation), update the image properties with the annotation info of the geojson, and save the geojson. Multiple geojson possible.
SQL to extract the tiles of the project and its annotation labels. will be saved into the geojson.
select image_id, annotation_id, labelclass_id, time_annotated, date_image_added, time_requested_image, image_path_filename, labelclass_name, labelclass_index, vascan_id, bryoquel_id, coleo_vernacular_fr, coleo_vernacular_en, vascan_region, vascan_province
, vascan_port, vascan_statut_repartition, tsn, coleo_category from (
SELECT t.id as annotation_id,
t.timecreated as time_annotated,
img.*, lc.*
FROM "test-jpg-gps-1"."annotation" AS t
JOIN (SELECT annotation, label
FROM "test-jpg-gps-1"."annotation_label"
) AS al
ON t.id = al.annotation
JOIN (
SELECT id AS image_id, filename as image_path_filename, date_added AS date_image_added, last_requested AS time_requested_image
FROM "test-jpg-gps-1"."image"
) AS img
ON t.image = img.image_id
JOIN (SELECT id AS labelclass_id, name AS labelclass_name, idx AS labelclass_index, vascan_id, bryoquel_id, coleo_vernacular_fr, coleo_vernacular_en, vascan_region, vascan_province
, vascan_port, vascan_statut_repartition, tsn, coleo_category
FROM "test-jpg-gps-1"."labelclass"
) AS lc
ON al.label = lc.labelclass_id
) as s
"image_id" "annotation_id" "labelclass_id" "time_annotated" "date_image_added" "time_requested_image" "image_path_filename" "labelclass_name" "labelclass_index" "vascan_id" "bryoquel_id" "coleo_vernacular_fr" "coleo_vernacular_en" "vascan_region" "vascan_province" "vascan_port" "vascan_statut_repartition" "tsn" "coleo_category" "9412dfed-39ef-475c-ad0a-9b1b5e44c42e" "da77429e-0975-42d6-9a4d-f6b244988182" "3c951bc8-44a8-11ed-ab52-75d2e298a4cb" "2022-10-14 15:59:19.436443-04" "2022-10-14 15:31:01.080501-04" "2022-10-14 15:57:17.280729-04" "1imageJPEG/2019-Boucherville-13225474-13410695_JPEG_tile/2019-Boucherville-13225474-13410695_JPEG_tile_4_7.jpg" "Carex annectens" 4 4737 "carex à gaine tronquée" "yellow-fruited sedge" "Central" "Québec" "Plante herbacée" "Indigène" "plantes" "a055e2fa-365c-4a3d-9c1e-cba67aa25d16" "b2a87d05-7caa-42e8-a26a-50bbffd4f155" "3c9517cc-44a8-11ed-ab52-75d2e298a4cb" "2022-10-14 15:59:19.44469-04" "2022-10-05 09:14:30.543295-04" "2022-10-14 15:57:17.280729-04" "test-geojpg/2019-Boucherville-13225474-13410695_tile/2019-Boucherville-13225474-13410695_tile_3_4.jpg" "Carex alopecoidea" 3 4734 "carex alopécoïde" "foxtail sedge" "Central" "Québec" "Plante herbacée" "Indigène" "plantes"
geojson created ; tests in progress
compléter sauf : URL image avec le host:port du host server selon l'environnement ; WIP avec variable environnement AIDE_HOST_ENV_TAG
Sauvegarder les annotations avec le ms.geojson et pour chaque image, dans la section "properties".