3liz/qgis-pgmetadata-plugin

QGIS Plugin to manage some metadata from PostgreSQL layer

PgMetadata

Store metadata in a PostgreSQL database and manage it from QGIS.

License

PgMetadata is released under GNU General Public License v2.0 or any later version.

Documentation

Presentation, user guide, installation, Lizmap Web Client, everything is there.

https://docs.3liz.org/qgis-pgmetadata-plugin/

qgis-pgmetadata-plugin's Issues

Do not raise exception if a pgmetadata base is not reachable

One PgMetadata database is not reachable, you will have a python exception everytime you are trying to add a layer from postgis (another DB):

Connection manager L28 is the issue:

    if 'pgmetadata' not in connection.schemas():

Add a proper table for themes

At present, dataset.categories refer to INSPIRE categories (which BTW should be updated...).

We need a way to let the administrator set a list of topics from a dedicated table, to let him choose. To do so:

• Create a new table pgmetadata.theme with fields id serial, code text, label text, description text: add a primary key, a unique constraint on fields code and label. Do not forget to add comments on table and fields
• Add a field dataset.themes as a text[] which will behave as the categories field, but with values coming from the new dataset.theme table
• Edit all the views to take these changes into account
• Change the HTML template files
• Update the QGIS administration project template: add this new table, and form widget for dataset layer
• Change the documentation to explain that this new table content must/can be adapted just after installing the plugin

Foss4G 2021 questions during the PgMetadata presentation

your model seems quite different then the metadata extension in geopackage, did you consider to adopt the geopackage model for metadata?

Do you have the ability to ingest or export to different ISO metadata standards? e.g. required by governments, etc.

Are there applications for publishing the metadata as CSW or OGCAPI endpoints

Is it correct that it is not possible to harvest the metadata directly? fo example from Geonetwork

Is it possible to store the metadata in another database or has to be for each database an own schema?

Can it ingest metadata from a remote CSW or OGC API source?

Is there any overlap with the 'Layer Metadata Search' plugin?

Does pgMetadata integrate with QGIS Layer Properties metadata? If so, how?

Do you plan a GeoNetwork Harvester ? xml exports(MEF)?

How to manage multilingual metadata?

cc @Gustry

Display value instead of the internal code

This must be done for categories and other values in the glossary.

Loading the metadata project in one click

Hi,
it would be great if you could load the project to edit the metadata in on click.
For example, right-click on layer and edit metadata.

Add trigger to calculate some dataset fields based on target table

These fields must be calculated by a trigger BEFORE

• feature_count -> count(*) FROM table
• geometry_type -> GeometryType(geom)
• projection_name -> 'RGF93 Lambert conformal conic' (voir table public.spatial_ref_sys avec un WHERE sur srid INFOS ?)
• projection_authid -> 'EPSG:2154'
• spatial_extent -> 'xmin,ymin,xmax,ymax'
• geom -> geometry containing all the features. Can be a simple polygon make from ST_ConvexHull(geom)

Error detecting layers not based an postgresql table

QgsDataSourceUri can be based on a table like :

(SELECT * FROM foo) AS t

PgMetadata is failing loading the metadatasheet on these layers :

2022-03-29T12:36:50     CRITICAL    Error when querying the database : Erreur SQL : SELECT pgmetadata.get_dataset_item_html_content('france', '(SELECT * FROM france.parcelles WHERE id_parcelles IN ('11203000AL0122','11203000AL0123','11203000AL0124'))', 'fr'); renvoyé 0 [ERROR: syntax error at or near "11203000"
             LINE 1: ...T * FROM france.parcelles WHERE id_parcelles IN ('11203000AL...

Generate a HTML report for a given dataset on the SQL side

• Recursive call for contacts and links
• Take care of i18n with a COALESCE ? #20
• Return empty string if no HTML template defined

Global connection and multiple databases

For now, we are using a global connection variable to let the plugin knows where is the pgmetadata schema.
This is needed for the locator for instance, to know in which database the query can be made.

For the OTF metadata display, we should check in the current database layer, and not using the variable. But people might wonder why the results is different between the locator?

Create views to search tables not referenced in the dataset table & dataset items with no corresponding PostgreSQL table

The idea is to have 2 views allowing to control the orphas:

• get all the lines in pgmetadata.dataset which point to tables that do not exists
• get all the tables in the PostgreSQL database which are not yet referenced in the table pgmetadata.dataset
• add these views in the administration project

Commit error : Unable to commit changes for Dataset layer

Good morning,
I work with the last pgmetadata plugin : 1.1.

I'm not able to save edits. When i add a new feature into Dataset, there is an error message saying :

"WARNING Commit errors: Unable to commit changes for Dataset layer"
ERROR: 1 feature(s) not added.
Data provider error:
               PostGIS error when adding feature: ERROR: Duplicate key value breaks unique constraint 'dataset_table_name_schema_name_key'
               DETAIL: The key "(table_name, schema_name)=(CigeoZH_Po_20220228, andra_naturel)" already exists. 

If anyone could help me? Thanks a lot.

Keywords, use a single string, not an array

Keywords are stored in an array of text.

I htink we talked with @mdouchin to switch to a single text field.

"Metadata:Created at" not autopopulating in QGIS 3.20.2 (but is in QGIS 3.18.0)

Hello,

Firstly, thank you very much for making a very useful and well designed QGIS plugin!

When using the plugin on QGIS 3.20.2 the "Created At" field in Metdata will not autopopulate and therefore not allow me save the edits.

In comparison, loading the exact same administrator project from the same database in QGIS 3.18.0 causes no issues.

Given it is the more recent version that is misbehaving, I thought I would let you know.

Thanks.

The locator should take care of orphan metadata

The locator shouldn't show orphan metadata

• Add a view pgmetadata.v_valid_dataset which gives a list of lines from pgmetadata.dataset with corresponding (existing) tables. This view returns only table_name and schema_name, no need for a autogenerated id with row_number
• In the Python locator.py use it in the existing SQL query: add a INNER JOIN to return only data for existing datasets
• Add this view in the administration template project

Checking the path when saving PDF/HTML in a file

Hi,
On Windows, it seems the path is not valid.
Indeed, it misses a trailing slash on lastFileNameWidgetDir variable.

Hide or make readonly ID fields

Generate PDF/HTML in a file

Next to the settings gear in the dock, we can have a save button, with a drop down menu :

• Save as PDF
• Save as HTML

It would take the content of the HTML panel and save it into a file, with the given format.

Frequency - Add a predefined list

Keyword sensitivity?

For one of my data, i filled some key words. For instance : "limite" (french).
But via the locator, when i tape "limite", the table is not proposed.

Template generation

This SQL is not working about the template generation :

INSERT INTO pgmetadata.dataset (table_name,schema_name,title,abstract)
VALUES ('lines','test','Test title','Test abstract.') RETURNING id;
--> 7

INSERT INTO pgmetadata.link (name,type,url,description,size,fk_id_dataset)
VALUES ('test link','pdf','https://metadata.is.good','',0.5,7)

DELETE FROM pgmetadata.html_template WHERE section IN ('main', 'link');

INSERT INTO pgmetadata.html_template (section,content)
VALUES ('main','<p>[% "title" %]</p><b>[%"abstract"%]</b><p>[% meta_links %]<p>');

INSERT INTO pgmetadata.html_template (section,content)
VALUES ('link','<p>[% "name" %] [% "description" %]</p><p>[% "size" %]</p>');

SELECT pgmetadata.get_dataset_item_html_content('test','lines');

No need to have the underlying layer behind. This SQL must work.

<p>Test title</p><b>Test abstract.</b><p><p>

I should be able to see the link. Maybe I'm missing something obvious ?

Maybe between [% "name" %] and [% name %] ?

support views

Hi,
in order to support (geodata-/layer-)views you could use information_schema.tables[1] instead of pg_tables in

• v_valid_dataset,
• v_orphan_tables and
• v_orphan_dataset_items

(like it is already in pgmetadata.v_table_list).
It works on my side. Side effect: as v_valid_dataset is used by the locator code, views will also be found.
Could make a PR, if you like.

[1] https://www.postgresql.org/docs/13/infoschema-tables.html

Great plugin!
Cheers

Button for adding all layers of a Theme

I’m not sure if I understood the goal of the Themes field correctly, but during my evaluation of the plugin, this use case came up:

I have created themes for topics and spatial divisions like

• base information for Germany
• base information for the State of Brandenburg
• hydrogeology for the State of Brandenburg
• water management for the State of Brandenburg
• protection zones for the State of Brandenburg
• …
• base information for the State of Saxony-Anhalt
• hydrogeology for the State of Saxony-Anhalt
• …
  and assigned layers to them.

So when I need to get started with a map e.g. for the State of Brandenburg, I would like to add the themes “base information for Brandenburg“ and „hydrogeology Brandenburg“ to have the layers I usually need conventiently loaded all at once.

I have added a button to the dock, which offers me a list selection of database (if more than one DB is connected) and of the available themes. When I select a theme, all the layers in that theme get added as a layer group.

Is this a use case of general interest? If so, I can prepare a PR.

When displaying categories, take the value in the glossary

Successor / child / parent relationships

This would be a nice-to-have enhancement:

Add links to other metadata records (via their UUID) for relationships like

• child→parent (for derivative works, e.g. adding more objects to a published dataset, or combining several datasets)
• successor→predecessor (for frequently updated datasets, e.g. annually published State’s groundwater monitoring network)

Qgis native layer metadata already has a “parent UUID” field, so there would be some compatability already.

The estimatedMetadata is wrong

I thought @mdouchin could fix it with #26 but for me it still wrong.
I tried to disable in the QGS project, but still.

Either :

• Use the show feature count, convienent for the warnings about orphan tables
• in the attribute table

We need to investigate in this.

Localization - Add localization capability

• Add new fields with french translation in glossary : label_fr and description_fr
• Add new table to store pgmetadata configuration, and add a locale field
• Add an option with available languages in the Create metadata administration project: this will add a new project variable with pgmetadata locale and set the corresponding value in the new configuration table

form view editing auto-switches to first feature

Hello,

I noticed that when if I have more than one table connected to the "Dataset" table in the QGIS pgmetadata manager project I cannot edit attributes under the attributes tab using form view. As soon as I enter a text box and begin typing the form automatically switches to the first feature in the "Dataset". This is also true for any of the other widgets- again it automatically switches to the first feature. I am able to edit all the data fine in table view however.

I am not sure if this is a bug or if I am just not understanding something. Thanks!

Suggested "update frequency" field

Hello,

As a "GIS Administrator" using your plugin (thanks for making it), it would be useful to have an "update frequency" field. This would help keep track of datasets which require regular updating or identify those that have been produced as a one-off. On many occasions this differs from publication frequency, which is often determined by a 3rd party.

Perhaps it could go under the "Data last update" field?

License attribution/reference

Enhancment proposal: It would be great to have an additional field for license numbers, ODC-BY and German Data Licence attributions etc.

Schema and table are inverted in DCAT export

Since 855efc6, it shows that the table and schema are inverted in the SQL output.
Make the switch as well in 0612227 about the DCAT export.

"data_last_update" is not rendered in the HTML

There is an issue rendering this field in the HTML.

calculate_fields_from_data doesn't like geometries with Z or M

While inserting rows in the dataset table, the function calculate_fields_from_data doesn't like if the geometry has a Z or M dimension.

Geometry has Z dimension but column does not

calculate_fields_from_data line 51 execute

We need a test case with a layer having Z dimension

CC @drillinP @mdouchin

German translation with Transifex

I’d like to help with a German translation on Transifex. However, I cannot add it: after clicking “Join team” I only get offered French, and the hint says I should request a language that’s not offered. But when I click “Request language”, I cannot request German, because it is already marked “Requested language”.
Could you have a look into why German isn’t available in either option? Thanks!

Raise QGIS Min version

In the metadata.txt, it's currently 3.4.

I propose to update at least to QGIS 3.10 and we should also make it compatible with higher version of QGIS (3.16 will be LTR soon).

• Use proper Processing widget for DB and schema when possible
• Use DB API or fetch_data_from_sql_query with a simple switch
• Create/Upgrade database structure must stay as generic as possible, using variables at the top of the file, so we can copy/paste easily to other plugins

Translate the QGIS Project

It's possible to generate a TS file from a QGIS project :

This TS file could be sent to Transifex with a new resource and translated.
Then the QM file should be copied as well in the Processing algorithm.

Metadata for raster datasets

Sorry for flooding the Issues with enhancement ideas ;-)

I’ve realised that it is already possible to enter metadata for rasters. After that, the layer can be found and added with the locator search bar. However, the added layer is a vector layer with its datasource set to the raster dataset, so the warning triangle about a missing datasource is shown.

The other way round, when I add the raster layer, the corresponding metadata is not shown, probably because PgMetadata only looks for information for vector layers.

So several aspects of using raster data with PgMetadata already work. Would it be difficult to add full raster layer support? I can have a look at it.

Non-UTF8 characters prevent plugin set up

Using QGIS 3.16.6-Hannover and postgresql12 on MacOSX 11.5.2 (Big Sur). Plugin loads, but when running "installation of the database structure" and "create metadata administration project", each fails. Managed to install by removing non-UTF8 characters from 90_glossary.sql and pg_metadata_administration.qgs. These characters seemed to be French language characters and once replaced with UTF-8-like characters, both scripts ran fine.

Storing the metadata project in postgresql

Hi,
Is there any reason for not storing the project in postgresql ? (Maybe for the support of old version)
I think it could be done with the install database structure step, right ?
Plus, it would be nice to load the project in one click (right click on layer and edit metadata for example)

Anyway thanks for your amazing work !
The French Ministry of Ecological Transition is very interested about this plugin.

Bests regards,

Packaging issue

• Remove test folder from the package
• Include i18n folder in the package

Level of confidentiality - Add a predefined list

Export all datasets as a table layer

Since the commit 71109de there is a new function pgmetadata.export_datasets_as_flat_table(_locale text) which allows to get a flat version of the datasets. This could be used to add a "Export all" button which will create a new QGIS vector (table) layer by invoking SELECT * FROM pgmetadata.export_datasets_as_flat_table(LOCALE)

The user will be able to save this layers as LibreOffice Calc, or Microsoft Excel or CSV file via the "Save as" QGIS feature.

I think there are 2 places for this flat export:

• in the dock panel, for the average users: "Export all the catalog"
• in the admin project, as a pre-loaded layer

Improve the QGIS admin project to ease the addition of child features

We should use the following option to improve the user experience when editing contacts or links from the parent "dataset" form

cc @Gustry

Auto fill native QGIS metadata from the dataset table

This ticket is a nice-to-have.

I'm not talking about filling the dataset table from QGIS native metadata, but the reverse.
We could read the dataset table and fill QGIS native metadata.

It would be better because :

• QGIS can use this metadata (some configuration in QGIS will read the layer metadata
• For plugin approval, it shows that the plugin is compatible with native QGIS Metadata and we are not recreating another metadata system in QGIS.

Empty values in Links or contacts are not evaluated correctly

"email" and "size"

My email is not empty

ST_ConvexHull might return point or line

On a point layer with

• a single feature, ST_ConvexHull will return a point
• two features, ST_ConvexHull will return a line string
• more features aligned, ST_ConvexHull will return a line string as well

The dataset table is expecting a polygon:

_core.QgsProviderConnectionException: SQL error: INSERT INTO pgmetadata.dataset (table_name,schema_name,title,abstract) VALUES ('test_geom_z','pgmetadata','Test title Z','Test abstract Z.') returned 0 [ERROR:  Geometry type (Point) does not match column type (Polygon)
CONTEXT:  PL/pgSQL function pgmetadata.calculate_fields_from_data() line 56 at EXECUTE

Low probability I know, but still a possible bug.

Attribute field descriptions

Does this metadata system provide a way to store attribute field descriptions? We currently have excel spreadsheets separate from the pgmetadata system in order to keep track of what each field represents, units, etc.

Add SQL based layers for schema and tables

In the administration project, we should add schemas and tables which would be based on SQL queries 👍🏻

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT IN ('pg_toast', 'pg_temp_1', 'pg_toast_temp_1', 'pg_catalog', 'information_schema');

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_toast', 'pg_temp_1', 'pg_toast_temp_1', 'pg_catalog', 'information_schema');

And use these tables in the form for auto-completion.

Generate a HTML report for a given dataset on the Python side

Using #22, if the string is empty, the HTML must be generated on the Python side, using the default HTML templates

User : searching with several key words/themes/categories ?

The tool allows to the administrator to set several key words for a table.
But as far as i tested, it is not possible for the user to make a search by entering several key words. (through the locator? throughThe data source manager?)...Or by entering for instance some key words or/and themes and or a category? Am I wrong?

Make pretty the DCAT export

Since 0612227, it's now possible to have DCAT export as XML file.

It would be nice if the XML is correctly indented with carriage return.

PS, I tried quickly with from xml.dom.minidom import parseString but it failed. Maybe the special dcat format ? Not sure.

We should add the parsing step into the CI test to be sure that we have a valid XML output.