Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Chained Features in Quadruplicate #17

Closed
KathiSchleidt opened this issue May 9, 2019 · 9 comments
Closed

Chained Features in Quadruplicate #17

KathiSchleidt opened this issue May 9, 2019 · 9 comments

Comments

@KathiSchleidt
Copy link
Collaborator

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
Copy link

nmco commented May 10, 2019

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:
image

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
Copy link
Collaborator Author

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

@KathiSchleidt
Copy link
Collaborator Author

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
Copy link

nmco commented May 16, 2019 •
edited
Loading

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

But some repeat elements still remain:

  • associationMethod:

image

  • purpose:

image

  • use:

image

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
Copy link

nmco commented May 16, 2019

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

ok, checked the other repeats, all good :)

@KathiSchleidt
Copy link
Collaborator Author

Issue with repeats in referentiel_externe.ln_entite_hydrogeol_bdlisa now #20

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@nmco @KathiSchleidt