Aller au contenu

Requêtes POSTGIS

Les départements qui intersecte la Garonne : 

SELECT d.*
   FROM admin.departements as d
     JOIN hydro.v_garonne as g
ON st_intersects(d.geom,g.geom);

Les forêts autour des forêts de conifères : 

WITH req_f_conifere as (SELECT fid,geom  FROM occsol.clc12  WHERE code_12='312') --forêts de conifères  
--pour chaque forêt de conifère, je veux la liste des types de forêts
-- combien de forêts voisines des forêts de conifères ?
SELECT  req_f_conifere.fid,req_f_conifere.geom, count(*) as nbre_forets,
        string_agg(distinct code_12,', ') as liste_type_forets
       -- clc12.code_12,clc12.area_ha
 FROM occsol.clc12
   JOIN req_f_conifere 
     ON st_touches(clc12.geom, req_f_conifere.geom)
     WHERE code_12 in ('313','311','324')--les autres forêts
    GROUP BY req_f_conifere.fid,req_f_conifere.geom
    ORDER BY 1;

les parcelles bâties : 

--les parcelles bâties
CREATE MATERIALIZED VIEW toulouse.vm_parcelles_batis_overlaps_1 AS
SELECT DISTINCT p.fid, p.geom, p.id, p.numero
    FROM toulouse.parcelles as p
      JOIN toulouse.batiments as b
            ON st_overlaps(p.geom,b.geom)--batis qui chevauchent parcelles
UNION
SELECT DISTINCT p.fid, p.geom, p.id, p.numero
    FROM toulouse.parcelles as p
      JOIN toulouse.batiments as b
            ON st_contains(p.geom,b.geom)--parcelles qui contiennent batis

--parcelles qui contiennent un point situé à l'interieur des batis
CREATE VIEW toulouse.v_parcelle_centroide_bati as
SELECT DISTINCT p.fid, p.geon, p.id, p.numero,
    FROM toulouse.parcelles as p,
      JOIN toulouse.batiment as b
            ON st_contain(p.geom,st_pointomsurface(b.geom)))
 --geometrie invalide
 SELECT fid, geom,(st_isvaliddetail(geom)).location
FROM toulouse.batiments
WHERE not st_isvalid(geom);

--les parcelles qui se chevauchent
SELECT  distinct p1.*
  FROM toulouse.parcelles as p1
    JOIN toulouse.parcelles as p2
      ON st_overlaps(p1.geom,p2.geom)
      WHERE p1.fid!=p2.fid
 -- les batis a 500m du zenith (fid=131389)     
with req_zenith as (SELECT fid, geom
  FROM toulouse.batiments
    WHERE fid=131389)

SELECT b.*
  FROM req_zenith
    JOIN toulouse.batiments as b
     ON st_dwithin(req_zenith.geom,b.geom,500)
  WHERE b.fid != req_zenith.fid ;


--zone 200m autour de la Garonne
SELECT row_number() over() as fid,
       st_buffer(geom, 200) as geom_200m
  FROM hydro.v_garonne;

--modification de la vue v_garonne
CREATE OR REPLACE VIEW hydro.v_garonne
 AS
 SELECT fid,
    geom,
    topooh,
    st_length(geom) AS longueur
   FROM hydro.cours_o
  WHERE  fid in(130658,33421);


  --découper les parcelles du quartier 828 
--par le buffer de 200m
SELECT parcelles.fid, 
st_intersection(parcelles.geom, st_buffer(v_garonne.geom,200)) as geom_inter, 
parcelles.id
  FROM toulouse.parcelles
    JOIN hydro.v_garonne
      ON st_dwithin(parcelles.geom,v_garonne.geom,200)
  WHERE parcelles.id ilike '%828%'

Compléments POSTGIS

Deux possibilités pour croiser les cours d'eau avec les départements : 

SELECT departements.*
  FROM hydro.cours_o
     JOIN admin.departements
       ON st_intersects(cours_o.geom, departements.geom)
    WHERE st_length(cours_o.geom)=(select max(st_length(geom)) 
                                     from hydro.cours_o );
--autre solution
CREATE VIEW admin.v_dep_cours_o_fid0 AS
WITH req_cours_o_long as (
    SELECT geom
      FROM hydro.cours_o
     ORDER BY st_length(geom) DESC LIMIT 3)
SELECT row_number() over() fid0,  departements.*
  FROM req_cours_o_long
     JOIN admin.departements
       ON st_intersects(req_cours_o_long.geom, departements.geom)
       ORDER BY 1
row_number() over() fid0 permet de définir un identifiant unique pour afficher tous les objets (même ceux identiques) dans QGIS en passant par le gestionnaire de base de données. Quand vous avez des doublons dans les identifiants, vous pouvez passer par la connexion postgis (dans le gestionnaire de sources de données et décocher "vérifier l'unicité de la clé primaire") La vue avec le nom des cours d'eau qui traversent les départements : ```CREATE VIEW admin.v_dep_cours_o_fid0 AS WITH req_cours_o_long as ( SELECT geom, topooh FROM hydro.cours_o ORDER BY st_length(geom) DESC LIMIT 3) SELECT row_number() over() fid0, departements.*, topooh FROM req_cours_o_long JOIN admin.departements ON st_intersects(req_cours_o_long.geom, departements.geom) ORDER BY 1 
row_number() over(**order by fid**) fid0 permet de trier par la colonne fid avant de mettre les numéros de ligne

Pour n'avoir qu'un seul objet et la liste des cours d'eau, on fait des groupes par département : 
```sql_more=
WITH req_cours_o_long as (
    SELECT geom, topooh
      FROM hydro.cours_o
     ORDER BY st_length(geom) DESC LIMIT 3)
SELECT  departements.fid, code_insee, nom, departements.geom, 
        string_agg(topooh, ', ') as cours_eau
  FROM req_cours_o_long
     JOIN admin.departements
       ON st_intersects(req_cours_o_long.geom, departements.geom)
GROUP BY 1,2,3,4
       ORDER BY 1;
string_agg(topooh, ', ') permet de regrouper les textes de l'objet dupliqué.

Créer une table à partir d'une fonction spatiale :

```sql_more= --création de l'objet region occitanie CREATE TABLE admin.region( code_reg text primary key, nom text, geom geometry(multipolygon,2154) ); INSERT INTO admin.region SELECT '76' as code_reg, 'Occitanie' as nom, st_union(geom ) as geom FROM admin.communes; 

### Sélectionner des objets qui ne correspondent pas à un critère spatial
Par exemple : 
```sql_more=
--y a t il des communes qui n'ont pas 
--de cours d'eau (le nom du cours d'eau doit être renseigné)

1ere étape : ne travailler que sur les cours d'eau d'Occitanie en créant une vue matérialisée qui fige le résultat de la requête dans une table ```sql_more= CREATE MATERIALIZED VIEW hydro.vm_cours_o_occitanie AS SELECT cours_o.fid, topooh, cours_o.geom FROM hydro.cours_o JOIN admin.region
ON st_intersects(region.geom,cours_o.geom); 

2eme étape : selection des communes qui ont des cours d'eau : 
```sql_more=
SELECT communes.fid
            FROM hydro.vm_cours_o_occitanie 
                JOIN admin.communes
ON st_intersects(vm_cours_o_occitanie.geom,communes.geom)
            WHERE topooh is not null
3eme étape : selection des communes qui ne sont pas dans la liste précédente : 
SELECT *
 FROM admin.communes
 WHERE fid not in (
        SELECT communes.fid
            FROM hydro.vm_cours_o_occitanie 
                JOIN admin.communes
ON st_intersects(vm_cours_o_occitanie.geom,communes.geom)
            WHERE topooh is not null );

Requête spatiale sur l'occupation du sol d'une commune

```sql_more= --L'occupation du sol -- pour la commune de Villefranche d'Astarac(32465) SELECT ocs_ge32.fid,code_cs,code_us, st_intersection(ocs_ge32.geom,communes.geom) geom FROM admin.communes JOIN occsol.ocs_ge32 ON st_intersects(ocs_ge32.geom,communes.geom) WHERE insee_com='32465' -- ou nom ilike 'villefranche%astarac%' 

Mais il faut etre sûr que les résultat de l'intersection ne ramene que des multipolygones : 
```sql_more=
--L'occupation du sol 
-- pour la commune de Villefranche d'Astarac(32465)
CREATE VIEW occsol.v_ocs_villefranche_astarac AS
SELECT ocs_ge32.fid,code_cs,code_us, 
    st_multi(--définis en multipolygones
            ST_CollectionExtract(--que des polygones ou des multipolygones
                  st_intersection(ocs_ge32.geom,communes.geom)--intersection
             ,3)--  3 pour sélectionner que les polygones
      ) as geom_inter    
  FROM admin.communes
    JOIN occsol.ocs_ge32
      ON st_intersects(ocs_ge32.geom,communes.geom)
    WHERE insee_com='32465'; -- ou nom ilike 'villefranche%astarac%'
Pour créer la vue, je dois définir la colonne geom résultant de l'intersection avec :: geometry(multipolygon,2154)

```sql_more= --L'occupation du sol -- pour la commune de Villefranche d'Astarac(32465) -- et les communes autour CREATE OR REPLACE VIEW occsol.v_ocs_villefranche_astarac as WITH req_communes as (SELECT st_union(autres_communes.geom) --pour n'avoir qu'un polygone commune à croiser avec l'occupation du sol FROM admin.communes as commune_villefranche JOIN admin.communes as autres_communes ON st_intersects(commune_villefranche.geom,autres_communes.geom) WHERE commune_villefranche.insee_com='32465') SELECT ocs_ge32.fid,code_cs,code_us, st_multi(--définis en multipolygones ST_CollectionExtract(--que des polygones ou des multipolygones st_intersection(ocs_ge32.geom,communes.geom)--intersection ,3)-- 3 pour sélectionner que les polygones )::geometry(multipolygon,2154) as geom_inter
FROM req_communes as communes JOIN occsol.ocs_ge32 ON st_intersects(ocs_ge32.geom,communes.geom) ; 

## Exercice : mettre en place une base de données sur les panneaux publicitaires

Données : 
**Panneaux publicitaires** : 
Localisation 

Il y a plusieurs **types de panneaux** à gérer : 
* les panneaux sucettes traditionnels : ce sont des planimètres rectangulaires installés sur des supports verticaux ;
* les panneaux sucettes numériques : ils comprennent des écrans lumineux qui permettent d’afficher un contenu dynamique comme des vidéos ;
* les panneaux sucettes interactifs : ils sont dotés d’écrans tactiles qui offrent la possibilité aux clients d’interagir avec les contenus ;
* les panneaux sucettes rotatifs : montés sur un mécanisme spécial, ils tournent lentement sur eux-mêmes pour capter l’attention du client.

Les emplacements sont loués par des **publicitaires** pour une période donnée mais peuvent être utilisés par la **mairie** (convention avec le publicitaire)

Les panneaux sont soit muraux soit scellés au sol (**format**)

SCRIPT SQL issu du MCD/MLD : 
```sql_more=
CREATE EXTENSION postgis;
CREATE SCHEMA publicite;
CREATE SCHEMA admin;

CREATE TABLE publicite.type_panneaux(
   id_type_panneaux INT GENERATED ALWAYS AS IDENTITY,
   nom TEXT,
   PRIMARY KEY(id_type_panneaux)
);

CREATE TABLE publicite.publicitaire(
   id_publicitaire INT GENERATED ALWAYS AS IDENTITY,
   nom TEXT,
   PRIMARY KEY(id_publicitaire)
);

CREATE TABLE admin.commune(
   cog VARCHAR(5) ,
   nom_com TEXT,
   geom geometry(multipolygon,2154),
   PRIMARY KEY(cog)
);

CREATE TABLE publicite.format(
   id_format INT GENERATED ALWAYS AS IDENTITY,
   libelle TEXT,
   PRIMARY KEY(id_format)
);

CREATE TABLE publicite.panneaux(
   id_panneaux INT GENERATED ALWAYS AS IDENTITY,
   geom geometry(point,2154),
   id_format INTEGER NOT NULL,
   id_type_panneaux INTEGER NOT NULL,
   PRIMARY KEY(id_panneaux),
   FOREIGN KEY(id_format) REFERENCES publicite.format(id_format),
   FOREIGN KEY(id_type_panneaux) REFERENCES publicite.type_panneaux(id_type_panneaux)
);

CREATE TABLE publicite.louer(
   id_panneaux INTEGER,
   id_publicitaire INTEGER,
   date_debut_location DATE,
   duree INTEGER,
   PRIMARY KEY(id_panneaux, id_publicitaire),
   FOREIGN KEY(id_panneaux) REFERENCES publicite.panneaux(id_panneaux),
   FOREIGN KEY(id_publicitaire) REFERENCES publicite.publicitaire(id_publicitaire)
);

CREATE TABLE publicite.convention(
   id_publicitaire INTEGER,
   cog VARCHAR(5) ,
   date_convention DATE,
   PRIMARY KEY(id_publicitaire, cog),
   FOREIGN KEY(id_publicitaire) REFERENCES publicite.publicitaire(id_publicitaire),
   FOREIGN KEY(cog) REFERENCES admin.commune(cog)
);

Inserer les données dans les tables de référence :

sql_more= INSERT INTO publicite.format(libelle) VALUES ('muraux' ),('scellés au sol' ); INSERT INTO publicite.type_panneaux(nom) VALUES('panneaux sucettes traditionnels'), ('panneaux sucettes numériques'), ('panneaux sucettes interactifs'), ('panneaux sucettes rotatifs'); 

### Faire la connexion avec QGIS

## Ajout du libellé d'occupation du sol à la vue sur Villefranche d'Astarac 
Après avoir importer la nomenclature dans la base de données (avec QGIS et le gestionnaire de base de données), on modifie la vue avec le libellé issu de la nomenclature
```sql_more=

 WITH req_communes AS (
         SELECT st_union(autres_communes.geom) AS geom
           FROM admin.communes commune_villefranche
             JOIN admin.communes autres_communes ON st_intersects(commune_villefranche.geom, autres_communes.geom)
          WHERE commune_villefranche.insee_com::text = '32465'::text
        )
 SELECT ocs_ge32.fid,
    ocs_ge32.code_cs,n_cs.libelle,
    ocs_ge32.code_us,n_us.libelle,
    st_multi(st_collectionextract(st_intersection(ocs_ge32.geom, communes.geom), 3))::geometry(MultiPolygon,2154) AS geom_inter
   FROM req_communes communes
     JOIN occsol.ocs_ge32 ON st_intersects(ocs_ge32.geom, communes.geom)
     JOIN  occsol.nomenclature_ocsge n_cs
       ON n_cs.code=ocs_ge32.code_cs
     JOIN  occsol.nomenclature_ocsge n_us
       ON n_us.code=ocs_ge32.code_us
     ;
La création de la vue nécessite de renommer les 2 colonnes libelle et du supprimer la vue avant de la recréer (car la structure change) :sql_more= DROP VIEW occsol.v_ocs_villefranche_astarac;

CREATE OR REPLACE VIEW occsol.v_ocs_villefranche_astarac AS WITH req_communes AS ( SELECT st_union(autres_communes.geom) AS geom FROM admin.communes commune_villefranche JOIN admin.communes autres_communes ON st_intersects(commune_villefranche.geom, autres_communes.geom) WHERE commune_villefranche.insee_com::text = '32465'::text ) SELECT ocs_ge32.fid, ocs_ge32.code_cs,n_cs.libelle as libelle_couverture, ocs_ge32.code_us,n_us.libelle as libelle_usage, st_multi(st_collectionextract(st_intersection(ocs_ge32.geom, communes.geom), 3))::geometry(MultiPolygon,2154) AS geom_inter FROM req_communes communes JOIN occsol.ocs_ge32 ON st_intersects(ocs_ge32.geom, communes.geom) JOIN occsol.nomenclature_ocsge n_cs ON n_cs.code=ocs_ge32.code_cs JOIN occsol.nomenclature_ocsge n_us ON n_us.code=ocs_ge32.code_us ; 

## Croisement de l'occupation du sol avec l'emprise de 20m des routes
L'objectif est d'extraire l'emprise des routes de l'occupation du sol
### 1ere étape : créer un buffer de 20m autour du réseau routier
```sql_more=
SELECT 
     st_union(st_buffer(geom_inter, 20))::geometry(multipolygon,2154) as geom20m
 FROM occsol.v_ocs_villefranche_astarac
 WHERE code_us='US4.1.1';
On considère que l'intersection des routes avec les rivières permettent le passage des animaux : enlevez de de la zone de 20m les 5m de largeur des cours d'eau

sql_more= WITH req_zone20m /*zone 20m autour des routes */ AS (SELECT st_union( st_buffer(geom_inter, 20) )::geometry(multipolygon,2154) as geom20m FROM occsol.v_ocs_villefranche_astarac WHERE code_us='US4.1.1' --le reseau routier ), req_zone5m /*zone 5m autour des cours d'eau */ AS (SELECT st_union(st_buffer(cours_o.geom,5))::geometry(multipolygon,2154) geom5m FROM hydro.cours_o JOIN req_zone20m ON (st_intersects(req_zone20m.geom20m, cours_o.geom)) ) --résultat du croisement entre les 2 buffers avec st_difference() SELECT st_difference(geom20m,geom5m) as geom20moins5 FROM req_zone20m JOIN req_zone5m ON st_intersects(geom20m,geom5m) 

### Ajout des autres usages du sol faisant barrière à la continuité écologique

```sql_more=
--à regrouper avec le buffer des routes : US1.3, US2, US235, US3, 
--US4.1.2, US4.1.3,US4.1.4,US4.1.5,US4.2,US4.3,US5,
--US6.1
SELECT st_union(geom_inter) as geom
 FROM occsol.v_ocs_villefranche_astarac
   WHERE code_us in('US1.3', 'US2', 'US235', 'US3','US4.1.2', 
   'US4.1.3','US4.1.4','US4.1.5','US4.2','US4.3','US5','US6.1')
      OR code_cs in ('CS1.1.1.1','CS1.1.2.1','CS1.1.2.2')
Pour créer des zones artificialisées continues, il faut définir un 1er buffer positif autour des zones sélectionnées puis un 2ème buffer négatif pour garder les contours et remplir l'intérieur :sql_more= SELECT st_buffer(st_buffer(st_union(geom_inter),20),-19) as geom FROM occsol.v_ocs_villefranche_astarac WHERE code_us in('US1.3', 'US2', 'US235', 'US3','US4.1.2', 'US4.1.3','US4.1.4','US4.1.5','US4.2','US4.3','US5', 'US6.1') OR code_cs in ('CS1.1.1.1','CS1.1.2.1','CS1.1.2.2') 
On rassemble le buffer des routes avec les autres zones artificialisées : 
```sql_more=
WITH 
req_zone20m AS (
 SELECT st_union(
                st_buffer(geom_inter, 20)
                )::geometry(multipolygon,2154)
          as geom20m
 FROM occsol.v_ocs_villefranche_astarac
 WHERE code_us='US4.1.1' --le reseau routier
 ),
req_autres_zones as (
 SELECT st_buffer(st_buffer(st_union(geom_inter),20),-19) as geom_autre_20m
  FROM occsol.v_ocs_villefranche_astarac
   WHERE code_us in('US1.3', 'US2', 'US235', 'US3','US4.1.2', 
   'US4.1.3','US4.1.4','US4.1.5','US4.2','US4.3','US5',
   'US6.1')
   OR code_cs in ('CS1.1.1.1','CS1.1.2.1','CS1.1.2.2')
),
 req_union_des_2_couches as (SELECT geom20m
   FROM req_zone20m
 union
 SELECT geom_autre_20m
   FROM req_autres_zones)
SELECT st_union(geom20m) as geom20m_total
 FROM req_union_des_2_couches;

La requête complète avec création de la vue :

```sql_more= CREATE MATERIALIZED VIEW occsol.vm_inverse_trame_verte AS WITH req_zone20m AS ( SELECT st_union( st_buffer(geom_inter, 20) ) as geom20m FROM occsol.v_ocs_villefranche_astarac WHERE code_us='US4.1.1' --le reseau routier ), req_autres_zones as ( SELECT st_buffer(st_buffer(st_union(geom_inter),20),-19) as geom_autre_20m FROM occsol.v_ocs_villefranche_astarac WHERE code_us in('US1.3', 'US2', 'US235', 'US3','US4.1.2', 'US4.1.3','US4.1.4','US4.1.5','US4.2','US4.3','US5', 'US6.1') OR code_cs in ('CS1.1.1.1','CS1.1.2.1','CS1.1.2.2') ), req_union_des_2_couches as (SELECT geom20m FROM req_zone20m union SELECT geom_autre_20m FROM req_autres_zones) , req_zones_artificialisees as (SELECT st_union(geom20m) as geom20m_total FROM req_union_des_2_couches)

, req_zone5m as (SELECT st_union(st_buffer(cours_o.geom,5))::geometry(multipolygon,2154) geom5m FROM hydro.cours_o JOIN req_zones_artificialisees ON (st_intersects(req_zones_artificialisees.geom20m_total, cours_o.geom)) ) SELECT 1 as fid, st_difference(geom20m_total,geom5m)::geometry(multipolygon,2154) as geom20moins5 FROM req_zones_artificialisees JOIN req_zone5m ON st_intersects(geom20m_total,geom5m);

### Création des trames vertes : 
```sql_more=
CREATE VIEW occsol.v_trame_verte AS
WITH req_union_communes as (
 SELECT st_union(autres_communes.geom) AS geom
  FROM admin.communes commune_villefranche
    JOIN admin.communes autres_communes 
      ON st_intersects(commune_villefranche.geom, autres_communes.geom)
    WHERE commune_villefranche.insee_com::text = '32465'
)
SELECT 1 as fid,st_difference(geom,geom20moins5)::geometry(multipolygon,2154) as geom
 FROM req_union_communes
  JOIN occsol.vm_inverse_trame_verte ON st_intersects(geom,geom20moins5);
Pour supprimer de la couche trame verte les petits polygones, il faut transformer une multigeometry en autant de simples polygones avec st_dump() : on décompose le multipolygone en polygones ce qui permet de sélectionner les polygones dont la surface est infèrieur à 10000 m² :

sql_more= WITH req as (SELECT (st_dump(geom)).path[1] as fid,(st_dump(geom)).geom FROM occsol.v_trame_verte) SELECT * FROM req WHERE st_area(geom)>10000;