Helpful Queries

Treetracker

Data Pipeline

select bulk_data from bulk_tree_upload where bulk_data -> 'registrations' @> '[{"device_identifier" : "a3243cf6b33d7db3"}]';

select bulk_data->'trees'->>0 from bulk_tree_upload where bulk_data -> 'trees' @> '[{"planter_identifier" : "0922166"}]';

select * from (select jsonb_array_elements(bulk_data->'locations') from bulk_tree_upload where bulk_data -> 'locations' @> '[{"treeUuid" : "cef0e3f1-b891-4e37-886f-6159ee6710df" }]' ) l where l.jsonb_array_elements->>'treeUuid' = 'cef0e3f1-b891-4e37-886f-6159ee6710df';

Capture Locations

select id, uuid, time_created from trees where estimated_geometric_location = ST_GeomFromText('POINT(0.0 0.0)', 4326) order by id desc;

User Setup

CREATE USER username WITH PASSWORD 'password';

GRANT SELECT ON ALL TABLES IN SCHEMA public TO data;

Audit Log

select * from audit where (operation -> 'payload' ->> 'approved') = 'false';