Chained Features in Quadruplicate

KathiSchleidt commented 5 years ago

All chained elements are now provided 4 times :( Exception: Example: http://brgm-dev.geo-solutions.it/geoserver/wfs3/collections/eposb__Borehole/items/06268X0017?f=application%2Fgeo%2Bjson

Should have

1x eposb:alias/eposb:Alias (4 duplicates are provided)
2x eposb:georesourceFeature/eposb:BoreholeGeoresource (8 times)
3x eposb:boreholeUse/eposb:BoreholeUse (12 times)
4x eposb:BoreholeMonitoringFacility (0 times!!! This seems to be an error in the mapping file, I will sort on Monday!)

SQL for checking the number of elements of the sub-view eposb:georesourceFeature/eposb:BoreholeGeoresource select * from app_diffussion_wfs3.ks_georesource_mv where indice = '06268X0017';

eposb:alias/eposb:Alias select * from app_diffussion_wfs3.ks_alias_mv where indice = '06268X0017';

eposb:boreholeUse/eposb:BoreholeUse select * from app_diffussion_wfs3.ks_borehole_use_mv where indice = '06268X0017';

eposb:BoreholeMonitoringFacility select * from app_diffussion_wfs3.ks_pe_sh_mv where indice = '06268X0017';

nmco commented 5 years ago

This happens because the ID 06268X0017 matches four boreholes:

SELECT indice, bss_id, libelle, date_debut, date_fin, code_station
FROM "app_diffussion_wfs3"."ks_borehole_mv"
WHERE "indice" = '06268X0017';

Partial output:

App-Schema is expecting a normalized table with an unique identifier, when the second, third and fourth borehole with the same ID appears App-Schema thinks they are the same and just merges the nested entities.

Moving forward, @KathiSchleidt are this four boreholes expected? is there any issue whit the ID we are using?

For reference, this is the root query send by App-Schema:

SELECT "app_diffussion_wfs3"."ks_borehole_mv"."indice",
       "app_diffussion_wfs3"."ks_borehole_mv"."bss_id",
       "app_diffussion_wfs3"."ks_borehole_mv"."libelle",
       "app_diffussion_wfs3"."ks_borehole_mv"."profondeur_investigation",
       "app_diffussion_wfs3"."ks_borehole_mv"."z_ouvrage",
       "app_diffussion_wfs3"."ks_borehole_mv"."code_etat",
       "app_diffussion_wfs3"."ks_borehole_mv"."prec_xy",
       "app_diffussion_wfs3"."ks_borehole_mv"."locmescode",
       "app_diffussion_wfs3"."ks_borehole_mv"."locmeslibelle",
       "app_diffussion_wfs3"."ks_borehole_mv"."elmescode",
       "app_diffussion_wfs3"."ks_borehole_mv"."elmeslibelle",
       "app_diffussion_wfs3"."ks_borehole_mv"."depth_date",
       "app_diffussion_wfs3"."ks_borehole_mv"."depth_val",
       "app_diffussion_wfs3"."ks_borehole_mv"."date_maj_dossier",
       "app_diffussion_wfs3"."ks_borehole_mv"."code_statut_referentiel_point_eau",
       "app_diffussion_wfs3"."ks_borehole_mv"."date_creation",
       "app_diffussion_wfs3"."ks_borehole_mv"."code_insee",
       "app_diffussion_wfs3"."ks_borehole_mv"."nom_commune",
       "app_diffussion_wfs3"."ks_borehole_mv"."num_commune",
       "app_diffussion_wfs3"."ks_borehole_mv"."num_departement",
       "app_diffussion_wfs3"."ks_borehole_mv"."latitude",
       "app_diffussion_wfs3"."ks_borehole_mv"."longitude",
       "app_diffussion_wfs3"."ks_borehole_mv"."date_debut",
       "app_diffussion_wfs3"."ks_borehole_mv"."date_fin",
       "app_diffussion_wfs3"."ks_borehole_mv"."code_station"
FROM "app_diffussion_wfs3"."ks_borehole_mv"
INNER JOIN
  (SELECT DISTINCT "ks_borehole_mv"."indice"
   FROM "app_diffussion_wfs3"."ks_borehole_mv"
   WHERE "indice" = '06268X0017') "temp_alias_used_for_filter" ON ("ks_borehole_mv"."indice" = "temp_alias_used_for_filter"."indice")
ORDER BY "app_diffussion_wfs3"."ks_borehole_mv"."indice" ASC LIMIT 1000000;

KathiSchleidt commented 5 years ago

Cool! OK, will check ks_borehole_mv and see what went wrong there - thanks!

KathiSchleidt commented 5 years ago

OK, got it, the (not longer required) join with the facility was causing the duplicates in ks_borehole_mv. I can't drop the view as owner is nmco, I've updated the SQL on the GitHub page, here also for faster update

drop MATERIALIZED VIEW app_diffussion_wfs3.ks_borehole_mv CREATE MATERIALIZED VIEW app_diffussion_wfs3.ks_borehole_mv AS SELECT dos.indice, dos.bss_id, ouv.libelle, len.profondeur_investigation, ouv.z_ouvrage, stat.code_etat, ouv.prec_xy, locmes.code AS locmescode, locmes.libelle AS locmeslibelle, elmes.code AS elmescode, elmes.libelle AS elmeslibelle, dep.date_observation AS depth_date, dep.profondeur_accessible AS depth_val, dos.date_maj_dossier, pteau.code_statut_referentiel_point_eau, dos.date_creation, com.code_insee, com.nom_commune, com.num_commune, com.num_departement, ouv.latitude, ouv.longitude, st_pointfromtext(((('POINT('::text || ouv.latitude) || ' '::text) || ouv.longitude) || ')'::text, 4326) AS loc FROM app_diffussion_wfs3.bss_dossier_mv dos LEFT JOIN app_diffussion_wfs3.bss_ouvrage_mv ouv ON dos.indice::text = ouv.indice::text LEFT JOIN ( SELECT bss_profondeur_investigation.indice, bss_profondeur_investigation.date_observation, bss_profondeur_investigation.profondeur_investigation FROM app_diffussion_wfs3.bss_profondeur_investigation WHERE ((bss_profondeur_investigation.indice::text, bss_profondeur_investigation.date_observation) IN ( SELECT bss_profondeur_investigation_1.indice, max(bss_profondeur_investigation_1.date_observation) AS max FROM app_diffussion_wfs3.bss_profondeur_investigation bss_profondeur_investigation_1 GROUP BY bss_profondeur_investigation_1.indice))) len ON dos.indice::text = len.indice::text LEFT JOIN ( SELECT DISTINCT ON (bss_ouvrage_etat_physique.indice) bss_ouvrage_etat_physique.indice, bss_ouvrage_etat_physique.code_etat, bss_ouvrage_etat_physique.date_debut, bss_ouvrage_etat_physique.date_fin FROM app_diffussion_wfs3.bss_ouvrage_etat_physique WHERE bss_ouvrage_etat_physique.date_fin IS NULL ORDER BY bss_ouvrage_etat_physique.indice, bss_ouvrage_etat_physique.date_debut DESC) stat ON dos.indice::text = stat.indice::text LEFT JOIN app_diffussion_wfs3.referentiel_interne_lex_mode_obtention_xy_mv locmes ON ouv.code_mode_obtention_xy = locmes.code LEFT JOIN app_diffussion_wfs3.referentiel_interne_lex_mode_obtention_z_mv elmes ON ouv.code_mode_obtention_z = elmes.code LEFT JOIN app_diffussion_wfs3.bss_profondeur_accessible dep ON dos.indice::text = dep.indice::text LEFT JOIN app_diffussion_wfs3.bsseau_point_eau pteau ON dos.indice::text = pteau.indice::text LEFT JOIN app_diffussion_wfs3.referentiel_interne_lex_communes com ON dos.num_commune::text = com.num_commune::text AND dos.num_departement::text = com.num_departement::text;

nmco commented 5 years ago

Hi @KathiSchleidt, I run the query above and indeed it removed the repeated borehole :smiley:: http://brgm-dev.geo-solutions.it/geoserver/wfs3/collections/eposb__Borehole/items/06268X0017?f=application%2Fgeo%2Bjson

But some repeat elements still remain:

associationMethod:

purpose:

use:

I investigate the first one, `associationMethod, and indeed repeated result are returned:

SELECT *
FROM "app_diffussion_wfs3"."ks_georesource_mv"
INNER JOIN "app_diffussion_wfs3"."ks_borehole_mv" 
      ON ("ks_borehole_mv"."indice" = "ks_georesource_mv"."indice")
WHERE "ks_borehole_mv"."indice" = '06268X0017';

I'm also copy pasting here all the SQL queries generated by App-Schema and send tot he database, let me know if you need help witht he investigation:

SELECT "app_diffussion_wfs3"."ks_borehole_mv"."indice",
       "app_diffussion_wfs3"."ks_borehole_mv"."bss_id",
       "app_diffussion_wfs3"."ks_borehole_mv"."libelle",
       "app_diffussion_wfs3"."ks_borehole_mv"."profondeur_investigation",
       "app_diffussion_wfs3"."ks_borehole_mv"."z_ouvrage",
       "app_diffussion_wfs3"."ks_borehole_mv"."code_etat",
       "app_diffussion_wfs3"."ks_borehole_mv"."prec_xy",
       "app_diffussion_wfs3"."ks_borehole_mv"."locmescode",
       "app_diffussion_wfs3"."ks_borehole_mv"."locmeslibelle",
       "app_diffussion_wfs3"."ks_borehole_mv"."elmescode",
       "app_diffussion_wfs3"."ks_borehole_mv"."elmeslibelle",
       "app_diffussion_wfs3"."ks_borehole_mv"."depth_date",
       "app_diffussion_wfs3"."ks_borehole_mv"."depth_val",
       "app_diffussion_wfs3"."ks_borehole_mv"."date_maj_dossier",
       "app_diffussion_wfs3"."ks_borehole_mv"."code_statut_referentiel_point_eau",
       "app_diffussion_wfs3"."ks_borehole_mv"."date_creation",
       "app_diffussion_wfs3"."ks_borehole_mv"."code_insee",
       "app_diffussion_wfs3"."ks_borehole_mv"."nom_commune",
       "app_diffussion_wfs3"."ks_borehole_mv"."num_commune",
       "app_diffussion_wfs3"."ks_borehole_mv"."num_departement",
       "app_diffussion_wfs3"."ks_borehole_mv"."latitude",
       "app_diffussion_wfs3"."ks_borehole_mv"."longitude",
       encode(ST_AsEWKB("app_diffussion_wfs3"."ks_borehole_mv"."loc"),'base64') AS "loc"
FROM "app_diffussion_wfs3"."ks_borehole_mv"
INNER JOIN
  (SELECT DISTINCT "ks_borehole_mv"."indice"
   FROM "app_diffussion_wfs3"."ks_borehole_mv"
   WHERE "indice" = '06268X0017') "temp_alias_used_for_filter" ON ("ks_borehole_mv"."indice" = "temp_alias_used_for_filter"."indice")
ORDER BY "app_diffussion_wfs3"."ks_borehole_mv"."indice" ASC LIMIT 1000000 ;

SELECT "app_diffussion_wfs3"."ks_parameter_mv"."indice",
       "app_diffussion_wfs3"."ks_parameter_mv"."namestr",
       "app_diffussion_wfs3"."ks_parameter_mv"."valuestr",
       "app_diffussion_wfs3"."ks_borehole_mv"."indice" FOREIGN_ID_0_0
FROM "app_diffussion_wfs3"."ks_parameter_mv"
INNER JOIN "app_diffussion_wfs3"."ks_borehole_mv" ON ("ks_borehole_mv"."indice" = "ks_parameter_mv"."indice")
INNER JOIN
  (SELECT DISTINCT "ks_borehole_mv"."indice"
   FROM "app_diffussion_wfs3"."ks_borehole_mv"
   WHERE "indice" = '06268X0017') "temp_alias_used_for_filter" ON ("ks_borehole_mv"."indice" = "temp_alias_used_for_filter"."indice")
ORDER BY "app_diffussion_wfs3"."ks_borehole_mv"."indice" ASC ;

SELECT "app_diffussion_wfs3"."ks_borehole_use_mv"."date_debut",
       "app_diffussion_wfs3"."ks_borehole_use_mv"."date_fin",
       "app_diffussion_wfs3"."ks_borehole_use_mv"."bss_id",
       "app_diffussion_wfs3"."ks_borehole_use_mv"."indice",
       "app_diffussion_wfs3"."ks_borehole_use_mv"."code_sandre",
       "app_diffussion_wfs3"."ks_borehole_mv"."indice" FOREIGN_ID_0_0
FROM "app_diffussion_wfs3"."ks_borehole_use_mv"
INNER JOIN "app_diffussion_wfs3"."ks_borehole_mv" ON ("ks_borehole_mv"."indice" = "ks_borehole_use_mv"."indice")
INNER JOIN
  (SELECT DISTINCT "ks_borehole_mv"."indice"
   FROM "app_diffussion_wfs3"."ks_borehole_mv"
   WHERE "indice" = '06268X0017') "temp_alias_used_for_filter" ON ("ks_borehole_mv"."indice" = "temp_alias_used_for_filter"."indice")
ORDER BY "app_diffussion_wfs3"."ks_borehole_mv"."indice" ASC 2019-05-16 22:58:20,
                                                                             225 TRACE [org.geotools.data.complex.filter] - ENTRY FeatureImpl:BoreholeUse<BoreholeUseType id=ks_borehole_use_mv.fid-fdbc820_16a9b8f1c52_-774e>=[] FEATURE_LINK 06268X0017 NULL NULL ;

SELECT "app_diffussion_wfs3"."ks_borehole_use_mv"."date_debut",
       "app_diffussion_wfs3"."ks_borehole_use_mv"."date_fin",
       "app_diffussion_wfs3"."ks_borehole_use_mv"."bss_id",
       "app_diffussion_wfs3"."ks_borehole_use_mv"."indice",
       "app_diffussion_wfs3"."ks_borehole_use_mv"."code_sandre",
       "app_diffussion_wfs3"."ks_borehole_mv"."indice" FOREIGN_ID_0_0
FROM "app_diffussion_wfs3"."ks_borehole_use_mv"
INNER JOIN "app_diffussion_wfs3"."ks_borehole_mv" ON ("ks_borehole_mv"."indice" = "ks_borehole_use_mv"."indice")
INNER JOIN
  (SELECT DISTINCT "ks_borehole_mv"."indice"
   FROM "app_diffussion_wfs3"."ks_borehole_mv"
   WHERE "indice" = '06268X0017') "temp_alias_used_for_filter" ON ("ks_borehole_mv"."indice" = "temp_alias_used_for_filter"."indice")
ORDER BY "app_diffussion_wfs3"."ks_borehole_mv"."indice" ASC 2019-05-16 22:58:20,
                                                                             435 TRACE [org.geotools.data.complex.filter] - ENTRY FeatureImpl:Purpose<PurposeType id=ks_borehole_use_mv.fid-fdbc820_16a9b8f1c52_-774b>=[] FEATURE_LINK 06268X0017 NULL NULL ;

SELECT "app_diffussion_wfs3"."ks_pe_sh_mv"."indice",
       "app_diffussion_wfs3"."ks_pe_sh_mv"."date_debut",
       "app_diffussion_wfs3"."ks_pe_sh_mv"."date_fin",
       "app_diffussion_wfs3"."ks_pe_sh_mv"."code_station",
       "app_diffussion_wfs3"."ks_pe_sh_mv"."date_fin_unknown",
       "app_diffussion_wfs3"."ks_borehole_mv"."indice" FOREIGN_ID_0_0
FROM "app_diffussion_wfs3"."ks_pe_sh_mv"
INNER JOIN "app_diffussion_wfs3"."ks_borehole_mv" ON ("ks_borehole_mv"."indice" = "ks_pe_sh_mv"."indice")
INNER JOIN
  (SELECT DISTINCT "ks_borehole_mv"."indice"
   FROM "app_diffussion_wfs3"."ks_borehole_mv"
   WHERE "indice" = '06268X0017') "temp_alias_used_for_filter" ON ("ks_borehole_mv"."indice" = "temp_alias_used_for_filter"."indice")
ORDER BY "app_diffussion_wfs3"."ks_borehole_mv"."indice" ASC ;

SELECT "app_diffussion_wfs3"."ks_alias_mv"."indice",
       "app_diffussion_wfs3"."ks_alias_mv"."ident_pour_organisme",
       "app_diffussion_wfs3"."ks_alias_mv"."nom_organisme",
       "app_diffussion_wfs3"."ks_alias_mv"."datmaj",
       "app_diffussion_wfs3"."ks_alias_mv"."bss_id",
       "app_diffussion_wfs3"."ks_borehole_mv"."indice" FOREIGN_ID_0_0
FROM "app_diffussion_wfs3"."ks_alias_mv"
INNER JOIN "app_diffussion_wfs3"."ks_borehole_mv" ON ("ks_borehole_mv"."indice" = "ks_alias_mv"."indice")
INNER JOIN
  (SELECT DISTINCT "ks_borehole_mv"."indice"
   FROM "app_diffussion_wfs3"."ks_borehole_mv"
   WHERE "indice" = '06268X0017') "temp_alias_used_for_filter" ON ("ks_borehole_mv"."indice" = "temp_alias_used_for_filter"."indice")
ORDER BY "app_diffussion_wfs3"."ks_borehole_mv"."indice" ASC ;

SELECT "app_diffussion_wfs3"."ks_georesource_mv"."indice",
       "app_diffussion_wfs3"."ks_georesource_mv"."id",
       "app_diffussion_wfs3"."ks_georesource_mv"."commentaire",
       "app_diffussion_wfs3"."ks_georesource_mv"."date_debut",
       "app_diffussion_wfs3"."ks_georesource_mv"."date_fin",
       "app_diffussion_wfs3"."ks_georesource_mv"."prof_mur",
       "app_diffussion_wfs3"."ks_georesource_mv"."prof_toit",
       "app_diffussion_wfs3"."ks_georesource_mv"."entite_captee",
       "app_diffussion_wfs3"."ks_georesource_mv"."code_entite",
       "app_diffussion_wfs3"."ks_georesource_mv"."nom_entite",
       "app_diffussion_wfs3"."ks_georesource_mv"."met_code",
       "app_diffussion_wfs3"."ks_georesource_mv"."qua_code",
       "app_diffussion_wfs3"."ks_borehole_mv"."indice" FOREIGN_ID_0_0
FROM "app_diffussion_wfs3"."ks_georesource_mv"
INNER JOIN "app_diffussion_wfs3"."ks_borehole_mv" ON ("ks_borehole_mv"."indice" = "ks_georesource_mv"."indice")
INNER JOIN
  (SELECT DISTINCT "ks_borehole_mv"."indice"
   FROM "app_diffussion_wfs3"."ks_borehole_mv"
   WHERE "indice" = '06268X0017') "temp_alias_used_for_filter" ON ("ks_borehole_mv"."indice" = "temp_alias_used_for_filter"."indice")
ORDER BY "app_diffussion_wfs3"."ks_borehole_mv"."indice" ASC ;

nmco commented 5 years ago

Byt he way @KathiSchleidt if you still have access \ permission problems let me know, you should be able now to do all the changes you need.

KathiSchleidt commented 5 years ago

OK, have permissions, am working! But, I fear that the issues are "below my control" I just followed up the georesourceFeature duplicates, issue is in the unterlying view app_diffussion_wfs3.referentiel_externe_ln_entite_hydrogeol_bdlisa, comes from referentiel_externe.ln_entite_hydrogeol_bdlisa which has 4172 duplicates. I'll check the others next

KathiSchleidt commented 5 years ago

purpose - here the 3 repeats are correct, end date is missing in the base table app_diffussion_wfs3.bss_ouvrage_fonction, so not provided, rest correct to the best of my knowledge

KathiSchleidt commented 5 years ago

ok, checked the other repeats, all good :)

KathiSchleidt commented 5 years ago

Issue with repeats in referentiel_externe.ln_entite_hydrogeol_bdlisa now https://github.com/INSIDE-information-systems/WFS3/issues/20

INSIDE-information-systems / OAPIF

Chained Features in Quadruplicate #17