ltree: store tags as hierarchically labelled tree in postgres

[bbest]

• PostgreSQL: Documentation: 13: F.21. ltree

This example uses the following data (also available in file contrib/ltree/ltreetest.sql in the source distribution):

CREATE TABLE test (path ltree);
INSERT INTO test VALUES ('Top');
INSERT INTO test VALUES ('Top.Science');
INSERT INTO test VALUES ('Top.Science.Astronomy');
INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology');
INSERT INTO test VALUES ('Top.Hobbies');
INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy');
INSERT INTO test VALUES ('Top.Collections');
INSERT INTO test VALUES ('Top.Collections.Pictures');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');
CREATE INDEX path_gist_idx ON test USING GIST (path);
CREATE INDEX path_idx ON test USING BTREE (path);

Now, we have a table test populated with data describing the hierarchy shown below:

• Hierarchical data in postgres (Example)
• Modeling Hierarchical Data in Postgres – Pinnacle Solutions Group, Inc.
• PostgreSQL: Speeding up recursive queries and hierarchic data - Cybertec

[bbest]

Need to harmonize Management Measures, eg only "Noise" in Tethys whereas this has "Airborne noise" (-> "Noise.Airborne") and "Underwater noise" (-> "Noise.Underwater").

[bbest]

Most recently updated documents | marineenergy.app Google Sheet to use as lookups this dot notation for:

• technology
• stressor
• receptor
• phase

These are also lists in apps: data/:

• ferc_lookup_receptor.csv
• ferc_lookup_stressor.csv
• ferc_lookup_technology.csv
• ferc_tags_phase_lut.csv

BUT, I haven't re-rendered the ferc.Rmd that corresponds to the Documents page. Probably want to strip off any prefix. and place as extra line header per dropdown. Nor have I updated the database using these new tags (so just in CSV land so far).

[bbest]

Implemented ltree throughout db with tags