[WIP] Import procedure documentation using QGIS expressions

[nliaudat]

Need documentation on procedure to import data.

The sample FME scripts from https://github.com/qwat/qwat-migration are outdated.

As example, I've tested with FME to send data to vw_element_hydrant, but FME do not support writing to postgis view. So I've create an import table : CREATE TABLE qwat_od.hydrant_import AS (SELECT * FROM qwat_od.vw_element_hydrant); and attempted to copy data to view INSERT INTO qwat_od.vw_element_hydrant SELECT * FROM qwat_od.hydrant_import ; but I raised the error

Cannot insert node as element since it already has another subtype. ID: %

from trigger function ft_element_hydrant_insert on line 20

Should I directly import in network_element + hydrant ? I need some advices and a "best practice" procedure.

My goal is to find a way of importing the database structure from AquaFri (Canton Fribourg). It have some similarities with SIRE.

Regards

[ponceta]

I've an idea here for FME : https://knowledge.safe.com/idea/27822/enable-postgis-view-writer.html

Upvote is welcome since upvoted suggestions could be taken into account more easily.

Regarding network elements and hydrants, you should have a look at what is done at the insertion of hydrants :

Importing hydrants though the view should creat subsequent network elements.

In my FME I have something like :

INSERT INTO qwat_od.vw_element_hydrant ("year","remark",identification,fk_model_sup,fk_model_inf,fk_output,pressure_static,fk_precision,fk_precisionalti,fk_distributor,fk_status,fk_provider,fk_object_reference,fk_material,geometry,fk_pressurezone)
VALUES (@Value(year),@Value(remark_),@Value(identification),@Value(a_det),@Value(a_det),@Value(a_det),@Value(pressure_static),@Value(fk_precision),@Value(fk_precisionalti),@Value(fk_distributor),@Value(fk_status),@Value(fk_provider),@Value(fk_object_reference),@Value(fk_material),ST_GeomFromText(@Value(WKT),21781),@Value(fk_pressurezone))

for hydrants in an sql executor

[nliaudat]

For pipes, I've managed to import by adding the mandatory virtual fields in Qgis and then copy/paste.

The simplest solution is always the best [@Occam’s Razor] ....

Qgis expression allows to perform data transformations on the fly from original fields to destination with ease.

I'll continue investigating. Regards

[varrieta]

So I've create an import table : CREATE TABLE qwat_od.hydrant_import AS (SELECT * FROM qwat_od.vw_element_hydrant); and attempted to copy data to view INSERT INTO qwat_od.vw_element_hydrant SELECT * FROM qwat_od.hydrant_import

Here your table qwat_od.hydrant_import is filled with the data in qwat_od.vw_element_hydrant, so your are re-inserting the same datas into qwat_od.vw_element_hydrant, right? This might be why it raises the error :

Cannot insert node as element since it already has another subtype. ID:

Because you can't insert two nodes at the same position...

But it is right to use qwat_od.vw_element_hydrant for data insertion. I did so for our migration..

[nliaudat]

@varrieta I have an empty database, so qwat_od.vw_element_hydrant return null and that's not the problem.

I mean my source data have twice an hydrant on the same position ....

The preceding procedure of simple copy/paste works fine with small dataset.

Many thanks for your help !

Copy/paste is not the right solution. I'll make full scripts or FME for nexts.

[haubourg]

@nliaudat @varrieta you are welcome to share your scripts in the migration repositories. I'll be happy to help you in that.

[3nids]

I would recommend an alternative approach to Arnaud's.

Create a simple copy of the table:

CREATE TABLE import_hydrant as SELECT * FROM qwat_od.vw_element_hydrant LIMIT 1;
TRUNCATE TABLE import_hydrant;

Run FME import on import_hydrant, leave ID column as NULL while inserting.

INSERT INTO qwat_od.vw_element_hydrabt FROM import_hydrant;
TRUNCATE TABLE import_hydrant;

Not sure about last command syntax though.

That's a little hack to FME's limitation.

[nliaudat]

A sample import procedure is available at https://github.com/nliaudat/qwat-import-sample

[haubourg]

@nliaudat Awesome! very nice procedure ! We should add a link to it from QWAT's readthedoc. I think linking is better than embedding, so that you easily update it than into the sphinx doc, and host files with it. @ponceta @varrieta @dsavary @3nids Any thoughts?

[nliaudat]

Better if you clone it to https://github.com/qwat/qwat-migration and take ownership. I've created my own repo cause I was in an hurry and have no right for uploading. That's a work in progress and I'd like you validate, correct, comment before publishing. Regards