8000 Refact : Install_db by corentinlange · Pull Request #332 · PnX-SI/GeoNature-atlas · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Refact : Install_db #332

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

Merged
merged 20 commits into from
Aug 12, 2021
Merged

Refact : Install_db #332

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
20 commits
Select commit Hold shift + click to select a range
2968b7d
fix : install_app.sh
Aug 10, 2021
2feb473
refactor(DB): refact of the install_db and sql files
Aug 11, 2021
a6f3f45
refactor(DB): refact of the install_db and sql files
Aug 11, 2021
a723f20
Merge branch 'dev-cobiodiv-refact-install-db' of https://github.com/c…
corentinlange Aug 11, 2021
a8580c1
Merge branch 'dev-cobiodiv-refact-install-db' of https://github.com/c…
corentinlange Aug 11, 2021
6358cdc
Merge branch 'dev-cobiodiv-refact-install-db' of https://github.com/c…
corentinlange Aug 11, 2021
20dc809
Merge branch 'dev-cobiodiv-refact-install-db' of https://github.com/c…
corentinlange Aug 11, 2021
e2982ae
Merge branch 'dev-cobiodiv-refact-install-db' of https://github.com/c…
corentinlange Aug 11, 2021
94e0463
Merge branch 'dev-cobiodiv' of https://github.com/PnX-SI/GeoNature-at…
corentinlange Aug 11, 2021
a95eedd
fix : atlas.vm_organisms.sql
corentinlange Aug 11, 2021
f10320a
fix : vm_observations_mailes path in install_db_extended.sh
corentinlange Aug 11, 2021
2bf8a8b
fix : vm_observations_mailes path in install_db_extended.sh
corentinlange Aug 11, 2021
3aa34e8
Merge branch 'dev-cobiodiv-refact-install-db' of https://github.com/c…
corentinlange Aug 11, 2021
f632449
fix: sql scripts observations
corentinlange Aug 11, 2021
ea0f8f9
fix : requirements.txxt
Aug 11, 2021
95e1dfc
fix: requirements.txt
corentinlange Aug 11, 2021
2fea6c5
fix: requirements.txt
corentinlange Aug 11, 2021
a07928a
Merge branch 'dev-cobiodiv-refact-install-db' of https://github.com/c…
Aug 11, 2021
5b2328d
fix: install_db.sh, deleted useless file
corentinlange Aug 11, 2021
b961ee8
Merge branch 'dev-cobiodiv-refact-install-db' of https://github.com/c…
corentinlange Aug 11, 2021
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
Binary file added atlas/static/custom/images/favicon.ico
View file
Open in desktop
Binary file not shown.
13 changes: 7 additions & 6 deletions data/atlas.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -23,14 +23,15 @@ CREATE MATERIALIZED VIEW atlas.vm_observations AS
s.dateobs,
s.observateurs,
s.altitude_retenue,
s.the_geom_point::geometry('POINT',3857),
s.the_geom_point as the_geom_point,
s.effectif_total,
tx.cd_ref,
st_asgeojson(ST_Transform(ST_SetSrid(s.the_geom_point, 3857), 4326)) as geojson_point,
diffusion_level
st_asgeojson(s.the_geom_point) as geojson_point,
s.diffusion_level
FROM synthese.syntheseff s
LEFT JOIN atlas.vm_taxref tx ON tx.cd_nom = s.cd_nom
JOIN atlas.t_layer_territoire m ON ST_Intersects(m.the_geom, s.the_geom_point);
LEFT JOIN atlas.vm_taxref tx ON tx.cd_nom = s.cd_nom
LIMIT 10
WITH DATA;

CREATE UNIQUE INDEX ON atlas.vm_observations (id_observation);
CREATE INDEX ON atlas.vm_observations (cd_ref);
Expand Down Expand Up @@ -508,4 +509,4 @@ BEGIN
REFRESH MATERIALIZED VIEW CONCURRENTLY atlas.vm_taxons_plus_observes;

END
$$ LANGUAGE plpgsql;
$$ LANGUAGE plpgsql;
64 changes: 64 additions & 0 deletions data/atlas/atlas.bib_taxref_rangs.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,64 @@
-- Rangs de taxref ordonnés

CREATE TABLE atlas.bib_taxref_rangs (
id_rang character(4) NOT NULL,
nom_rang character varying(20) NOT NULL,
tri_rang integer
);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('Dumm', 'Domaine', 1);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SPRG', 'Super-Règne', 2);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('KD ', 'Règne', 3);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SSRG', 'Sous-Règne', 4);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('IFRG', 'Infra-Règne', 5);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('PH ', 'Embranchement', 6);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SBPH', 'Sous-Phylum', 7);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('IFPH', 'Infra-Phylum', 8);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('DV ', 'Division', 9);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SBDV', 'Sous-division', 10);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SPCL', 'Super-Classe', 11);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('CLAD', 'Cladus', 12);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('CL ', 'Classe', 13);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SBCL', 'Sous-Classe', 14);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('IFCL', 'Infra-classe', 15);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('LEG ', 'Legio', 16);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SPOR', 'Super-Ordre', 17);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('COH ', 'Cohorte', 18);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('OR ', 'Ordre', 19);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SBOR', 'Sous-Ordre', 20);
INSERT INTO atlas. 8000 bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('IFOR', 'Infra-Ordre', 21);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SPFM', 'Super-Famille', 22);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('FM ', 'Famille', 23);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SBFM', 'Sous-Famille', 24);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('TR ', 'Tribu', 26);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SSTR', 'Sous-Tribu', 27);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('GN ', 'Genre', 28);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SSGN', 'Sous-Genre', 29);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SC ', 'Section', 30);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SBSC', 'Sous-Section', 31);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SER', 'Série', 32);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SSER', 'Sous-Série', 33);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('AGES', 'Agrégat', 34);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('ES ', 'Espèce', 35);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SMES', 'Semi-espèce', 36);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('MES ', 'Micro-Espèce',37);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SSES', 'Sous-espèce', 38);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('NAT ', 'Natio', 39);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('VAR ', 'Variété', 40);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SVAR ', 'Sous-Variété', 41);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('FO ', 'Forme', 42);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SSFO', 'Sous-Forme', 43);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('FOES', 'Forma species', 44);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('LIN ', 'Linea', 45);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('CLO ', 'Clône', 46);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('RACE', 'Race', 47);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('CAR ', 'Cultivar', 48);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('MO ', 'Morpha', 49);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('AB ', 'Abberatio',50);
--n'existe plus dans taxref V9
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang) VALUES ('CVAR', 'Convariété');
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang) VALUES ('HYB ', 'Hybride');
--non documenté dans la doc taxref
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang, tri_rang) VALUES ('SPTR', 'Supra-Tribu', 25);
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang) VALUES ('SCO ', '?');
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang) VALUES ('PVOR', '?');
INSERT INTO atlas.bib_taxref_rangs (id_rang, nom_rang) VALUES ('SSCO', '?');
31 changes: 31 additions & 0 deletions data/atlas/atlas.find_all_taxons_childs.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,31 @@
--Fonction qui permet de lister tous les taxons enfants d'un taxon

CREATE OR REPLACE FUNCTION atlas.find_all_taxons_childs(id integer)
RETURNS SETOF integer AS
$BODY$
--Param : cd_nom ou cd_ref d'un taxon quelque soit son rang
--Retourne le cd_nom de tous les taxons enfants sous forme d'un jeu de données utilisable comme une table
--Usage SELECT atlas.find_all_taxons_childs(197047);
--ou SELECT * FROM atlas.vm_taxons WHERE cd_ref IN(SELECT * FROM atlas.find_all_taxons_childs(197047))
DECLARE
inf RECORD;
c integer;
BEGIN
SELECT INTO c count(*) FROM atlas.vm_taxref WHERE cd_taxsup = id;
IF c > 0 THEN
FOR inf IN
WITH RECURSIVE descendants AS (
SELECT tx1.cd_nom FROM atlas.vm_taxref tx1 WHERE tx1.cd_taxsup = id
UNION ALL
SELECT tx2.cd_nom FROM descendants d JOIN atlas.vm_taxref tx2 ON tx2.cd_taxsup = d.cd_nom
)
SELECT cd_nom FROM descendants
LOOP
RETURN NEXT inf.cd_nom;
END LOOP;
END IF;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 100
ROWS 1000;
39 changes: 39 additions & 0 deletions data/atlas/atlas.refresh_materialized_view_data.sql
AE8F
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,39 @@
--Fonction pour rafraichir toutes les vues matérialisées d'un schéma

--USAGE : SELECT RefreshAllMaterializedViews('atlas');
CREATE OR REPLACE FUNCTION RefreshAllMaterializedViews(schema_arg TEXT DEFAULT 'public')
RETURNS INT AS $$
DECLARE
r RECORD;
BEGIN
RAISE NOTICE 'Refreshing materialized view in schema %', schema_arg;
FOR r IN SELECT matviewname FROM pg_matviews WHERE schemaname = schema_arg
LOOP
RAISE NOTICE 'Refreshing %.%', schema_arg, r.matviewname;
--EXECUTE 'REFRESH MATERIALIZED VIEW ' || schema_arg || '.' || r.matviewname; --Si vous utilisez une version inférieure à PostgreSQL 9.4
EXECUTE 'REFRESH MATERIALIZED VIEW CONCURRENTLY ' || schema_arg || '.' || r.matviewname;
END LOOP;

RETURN 1;
END
$$ LANGUAGE plpgsql;

-- Rafraichissement des vues contenant les données de l'atlas
CREATE OR REPLACE FUNCTION atlas.refresh_materialized_view_data()
RETURNS VOID AS $$
BEGIN

REFRESH MATERIALIZED VIEW CONCURRENTLY atlas.vm_observations;
REFRESH MATERIALIZED VIEW CONCURRENTLY atlas.vm_observations_mailles;
REFRESH MATERIALIZED VIEW CONCURRENTLY atlas.vm_mois;

REFRESH MATERIALIZED VIEW CONCURRENTLY atlas.vm_altitudes;

REFRESH MATERIALIZED VIEW CONCURRENTLY atlas.vm_taxons;
REFRESH MATERIALIZED VIEW CONCURRENTLY atlas.vm_cor_taxon_attribut;
REFRESH MATERIALIZED VIEW CONCURRENTLY atlas.vm_search_taxon;
REFRESH MATERIALIZED VIEW CONCURRENTLY atlas.vm_medias;
REFRESH MATERIALIZED VIEW CONCURRENTLY atlas.vm_taxons_plus_observes;

END
$$ LANGUAGE plpgsql;
66 changes: 66 additions & 0 deletions data/atlas/atlas.vm_altitudes.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,66 @@
--Classes d'altitudes, modifiables selon votre contexte

--DROP TABLE atlas.bib_altitudes;
CREATE TABLE atlas.bib_altitudes
(
id_altitude integer NOT NULL,
altitude_min integer NOT NULL,
altitude_max integer NOT NULL,
label_altitude character varying(255),
CONSTRAINT bib_altitudes_pk PRIMARY KEY (id_altitude)
);

INSERT_ALTITUDE
UPDATE atlas.bib_altitudes set label_altitude = '_' || altitude_min || '_' || altitude_max+1;


-- Fonction qui permet de créer la VM contenant le nombre d'observations par classes d'altitude pour chaque taxon

-- DROP FUNCTION atlas.create_vm_altitudes();

CREATE OR REPLACE FUNCTION atlas.create_vm_altitudes()
RETURNS text AS
$BODY$
DECLARE
monsql text;
mesaltitudes RECORD;

BEGIN
DROP MATERIALIZED VIEW IF EXISTS atlas.vm_altitudes;

monsql = 'CREATE materialized view atlas.vm_altitudes AS WITH ';

FOR mesaltitudes IN SELECT * FROM atlas.bib_altitudes ORDER BY id_altitude LOOP
IF mesaltitudes.id_altitude = 1 THEN
monsql = monsql || 'alt' || mesaltitudes.id_altitude ||' AS (SELECT cd_ref, count(*) as nb FROM atlas.vm_observations WHERE altitude_retenue <' || mesaltitudes.altitude_max || ' GROUP BY cd_ref) ';
ELSE
monsql = monsql || ',alt' || mesaltitudes.id_altitude ||' AS (SELECT cd_ref, count(*) as nb FROM atlas.vm_observations WHERE altitude_retenue BETWEEN ' || mesaltitudes.altitude_min || ' AND ' || mesaltitudes.altitude_max || ' GROUP BY cd_ref)';
END IF;
END LOOP;

monsql = monsql || ' SELECT DISTINCT o.cd_ref';

FOR mesaltitudes IN SELECT * FROM atlas.bib_altitudes LOOP
monsql = monsql || ',COALESCE(a' ||mesaltitudes.id_altitude || '.nb::integer, 0) as '|| mesaltitudes.label_altitude;
END LOOP;

monsql = monsql || ' FROM atlas.vm_observations o';

FOR mesaltitudes IN SELECT * FROM atlas.bib_altitudes LOOP
monsql = monsql || ' LEFT JOIN alt' || mesaltitudes.id_altitude ||' a' || mesaltitudes.id_altitude || ' ON a' || mesaltitudes.id_altitude || '.cd_ref = o.cd_ref';
END LOOP;

monsql = monsql || ' WHERE o.cd_ref is not null ORDER BY o.cd_ref;';

EXECUTE monsql;
create unique index ON atlas.vm_altitudes (cd_ref);

RETURN monsql;

END;

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

select atlas.create_vm_altitudes();
12 changes: 12 additions & 0 deletions data/atlas/atlas.vm_communes.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,12 @@
-- Communes contenues entièrement dans le territoire

CREATE MATERIALIZED VIEW atlas.vm_communes AS
SELECT c.insee,
c.commune_maj,
c.the_geom,
st_asgeojson(st_transform(c.the_geom, 4326)) as commune_geojson
FROM atlas.l_communes c
JOIN atlas.t_layer_territoire t ON ST_CONTAINS(ST_BUFFER(t.the_geom,200), c.the_geom);

CREATE UNIQUE INDEX ON atlas.vm_communes (insee);
CREATE INDEX index_gist_vm_communes_the_geom ON atlas.vm_communes USING gist (the_geom);
8 changes: 8 additions & 0 deletions data/atlas/atlas.vm_cor_taxon_attribut.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,8 @@
-- Attributs de chaque taxon (description, commentaire, milieu et chorologie)
CREATE MATERIALIZED VIEW atlas.vm_cor_taxon_attribut AS 10000
SELECT id_attribut,
valeur_attribut,
cd_ref
FROM taxonomie.cor_taxon_attribut
WHERE id_attribut IN (100, 101, 102, 103);
CREATE UNIQUE INDEX ON atlas.vm_cor_taxon_attribut (cd_ref,id_attribut);
16 changes: 16 additions & 0 deletions data/atlas/atlas.vm_medias.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,16 @@
-- Médias de chaque taxon

CREATE MATERIALIZED VIEW atlas.vm_medias AS
SELECT t_medias.id_media,
t_medias.cd_ref,
t_medias.titre,
t_medias.url,
t_medias.chemin,
t_medias.auteur,
t_medias.desc_media,
t_medias.date_media,
t_medias.id_type,
t_medias.licence,
t_medias.source
FROM taxonomie.t_medias;
CREATE UNIQUE INDEX ON atlas.vm_medias (id_media);
46 changes: 46 additions & 0 deletions data/atlas/atlas.vm_mois.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,46 @@
-- Nombre d'observations mensuelles pour chaque taxon observé

CREATE materialized view atlas.vm_mois AS
WITH
_01 AS (SELECT cd_ref, count(*) as nb FROM atlas.vm_observations WHERE date_part('month'::text, dateobs) = '01' GROUP BY cd_ref),
_02 AS (SELECT cd_ref, count(*) as nb FROM atlas.vm_observations WHERE date_part('month'::text, dateobs) = '02' GROUP BY cd_ref),
_03 AS (SELECT cd_ref, count(*) as nb FROM atlas.vm_observations WHERE date_part('month'::text, dateobs) = '03' GROUP BY cd_ref),
_04 AS (SELECT cd_ref, count(*) as nb FROM atlas.vm_observations WHERE date_part('month'::text, dateobs) = '04' GROUP BY cd_ref),
_05 AS (SELECT cd_ref, count(*) as nb FROM atlas.vm_observations WHERE date_part('month'::text, dateobs) = '05' GROUP BY cd_ref),
_06 AS (SELECT cd_ref, count(*) as nb FROM atlas.vm_observations WHERE date_part('month'::text, dateobs) = '06' GROUP BY cd_ref),
_07 AS (SELECT cd_ref, count(*) as nb FROM atlas.vm_observations WHERE date_part('month'::text, dateobs) = '07' GROUP BY cd_ref),
_08 AS (SELECT cd_ref, count(*) as nb FROM atlas.vm_observations WHERE date_part('month'::text, dateobs) = '08' GROUP BY cd_ref),
_09 AS (SELECT cd_ref, count(*) as nb FROM atlas.vm_observations WHERE date_part('month'::text, dateobs) = '09' GROUP BY cd_ref),
_10 AS (SELECT cd_ref, count(*) as nb FROM atlas.vm_observations WHERE date_part('month'::text, dateobs) = '10' GROUP BY cd_ref),
_11 AS (SELECT cd_ref, count(*) as nb FROM atlas.vm_observations WHERE date_part('month'::text, dateobs) = '11' GROUP BY cd_ref),
_12 AS (SELECT cd_ref, count(*) as nb FROM atlas.vm_observations WHERE date_part('month'::text, dateobs) = '12' GROUP BY cd_ref)

SELECT DISTINCT o.cd_ref
,COALESCE(a.nb::integer, 0) as _01
,COALESCE(b.nb::integer, 0) as _02
,COALESCE(c.nb::integer, 0) as _03
,COALESCE(d.nb::integer, 0) as _04
,COALESCE(e.nb::integer, 0) as _05
,COALESCE(f.nb::integer, 0) as _06
,COALESCE(g.nb::integer, 0) as _07
,COALESCE(h.nb::integer, 0) as _08
,COALESCE(i.nb::integer, 0) as _09
,COALESCE(j.nb::integer, 0) as _10
,COALESCE(k.nb::integer, 0) as _11
,COALESCE(l.nb::integer, 0) as _12
FROM atlas.vm_observations o
LEFT JOIN _01 a ON a.cd_ref = o.cd_ref
LEFT JOIN _02 b ON b.cd_ref = o.cd_ref
LEFT JOIN _03 c ON c.cd_ref = o.cd_ref
LEFT JOIN _04 d ON d.cd_ref = o.cd_ref
LEFT JOIN _05 e ON e.cd_ref = o.cd_ref
LEFT JOIN _06 f ON f.cd_ref = o.cd_ref
LEFT JOIN _07 g ON g.cd_ref = o.cd_ref
LEFT JOIN _08 h ON h.cd_ref = o.cd_ref
LEFT JOIN _09 i ON i.cd_ref = o.cd_ref
LEFT JOIN _10 j ON j.cd_ref = o.cd_ref
LEFT JOIN _11 k ON k.cd_ref = o.cd_ref
LEFT JOIN _12 l ON l.cd_ref = o.cd_ref
WHERE o.cd_ref is not null
ORDER BY o.cd_ref;
CREATE UNIQUE INDEX ON atlas.vm_mois (cd_ref);
24 changes: 24 additions & 0 deletions data/atlas/atlas.vm_observations.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,24 @@
--Toutes les observations

--DROP materialized view atlas.vm_observations;
CREATE MATERIALIZED VIEW atlas.vm_observations AS
SELECT s.id_synthese AS id_observation,
s.insee,
s.dateobs,
s.observateurs,
s.altitude_retenue,
s.the_geom_point as the_geom_point,
s.effectif_total,
tx.cd_ref,
st_asgeojson(s.the_geom_point) as geojson_point,
s.diffusion_level
FROM synthese.syntheseff s
LEFT JOIN atlas.vm_taxref tx ON tx.cd_nom = s.cd_nom
WITH DATA;

CREATE UNIQUE INDEX ON atlas.vm_observations (id_observation);
CREATE INDEX ON atlas.vm_observations (cd_ref);
CREATE INDEX ON atlas.vm_observations (insee);
CREATE INDEX ON atlas.vm_observations (altitude_retenue);
CREATE INDEX ON atlas.vm_observations (dateobs);
CREATE INDEX index_gist_vm_observations_the_geom_point ON atlas.vm_observations USING gist (the_geom_point);
16 changes: 16 additions & 0 deletions data/atlas/atlas.vm_observations_mailles.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,16 @@
-- Creation de la VM des observations de chaque taxon par mailles...

CREATE MATERIALIZED VIEW atlas.vm_observations_mailles AS
SELECT obs.cd_ref,
obs.id_observation,
m.id_maille,
m.geojson_maille,
date_part('year', dateobs) as annee
FROM atlas.vm_observations obs
JOIN atlas.t_mailles_territoire m ON st_intersects(st_transform(obs.the_geom_point, 3857), m.the_geom)
WITH DATA;

create unique index on atlas.vm_observations_mailles (id_observation);
create index on atlas.vm_observations_mailles (id_maille);
create index on atlas.vm_observations_mailles (cd_ref);
create index on atlas.vm_observations_mailles (geojson_maille);
23 changes: 23 additions & 0 deletions data/atlas/atlas.vm_observations_mailles_extended.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,23 @@
DROP MATERIALIZED VIEW atlas.t_mailles_territoire;

-- MV for having only meshs of the territory
CREATE MATERIALIZED VIEW atlas.t_mailles_territoire
AS SELECT c.geom AS the_geom,
st_asgeojson(st_transform(c.geom, 4326)) AS geojson_maille,
c.id_area AS id_maille
FROM ref_geo.l_areas c
JOIN ref_geo.bib_areas_types t ON t.id_type = c.id_type
JOIN atlas.t_layer_territoire mt ON ST_intersects(c.geom,st_transform(mt.the_geom, 2154))
WHERE c.enable = true AND c.id_type = 27;

CREATE UNIQUE INDEX t_mailles_territoire_id_maille_idx ON atlas.t_mailles_territoire USING btree (id_maille);

CREATE MATERIALIZED VIEW atlas.vm_observations_mailles
AS SELECT obs.cd_ref,
obs.id_observation,
m.id_maille,
m.geojson_maille,
date_part('year', dateobs) as annee
FROM atlas.vm_observations obs
JOIN atlas.t_mailles_territoire m ON st_intersects(st_transform(obs.the_geom_point, 2154), m.the_geom)
WITH DATA;
Loading
0