Le portail francophone de la géomatique

CREATE MATERIALIZED VIEW gpu_import.vm_zone_urba AS
SELECT   a.the_geom, a.gml_id, a.partition, a.libelle, a.libelong, a.typezone, a.destdomi, a.nomfic, a.urlfic, a.insee, a.datappro, a.datvalid , a.idurba, a.idzone, a.lib_idzone
FROM gpu_import.wfs_du_zone_urba a,  public.emprise b
WHERE ST_Intersects(a.the_geom, b.the_geom)

DROP SERVER IF EXISTS fdw_ogr_gpu CASCADE;
CREATE SERVER fdw_ogr_gpu FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
    datasource 'WFS:https://wxs-gpu.mongeoportail.ign.fr/externe/39wtxmgtn23okfbbs1al2lz3/wfs?service=WFS&request=GetCapabilities',
    format 'WFS',
    character_encoding 'UTF-8',
    config_options 'CPL_DEBUG=ON GDAL_HTTP_MAX_RETRY=0 GDAL_HTTP_RETRY_DELAY=3 '
    
    );

SET client_min_messages = debug2;



CREATE SCHEMA IF NOT EXISTS gpu_import;

IMPORT FOREIGN SCHEMA ogr_all
FROM SERVER fdw_ogr_gpu
INTO gpu_import
OPTIONS (
    -- mettre le nom des tables en minuscule et sans caractères bizares
    launder_table_names 'true',
    -- mettre le nom des champs en minuscule
    launder_column_names 'true');


SELECT foreign_table_schema, foreign_table_name
FROM information_schema.foreign_tables
WHERE foreign_table_schema = 'gpu_import'
ORDER BY foreign_table_schema, foreign_table_name;


DROP MATERIALIZED VIEW IF EXISTS gpu_import.vm_zone_urba;

CREATE MATERIALIZED VIEW gpu_import.vm_zone_urba AS
SELECT   a.the_geom, a.gml_id, a.partition, a.libelle, a.libelong, a.typezone, a.destdomi, a.nomfic, a.urlfic, a.insee, a.datappro, a.datvalid , a.idurba, a.idzone, a.lib_idzone
FROM gpu_import.wfs_du_zone_urba a,  public.emprise b
WHERE ST_Intersects(a.the_geom, b.the_geom);

DROP SERVER IF EXISTS fdw_ogr_gpu CASCADE;
CREATE SERVER fdw_ogr_gpu FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
    datasource 'WFS:https://wxs-gpu.mongeoportail.ign.fr/externe/39wtxmgtn23okfbbs1al2lz3/wfs?service=WFS&request=GetCapabilities',
    format 'WFS',    
    config_options 'CPL_DEBUG=ON GDAL_HTTP_MAX_RETRY=5 GDAL_HTTP_RETRY_DELAY=5'    );
    
SET client_min_messages = debug2;

CREATE SCHEMA IF NOT EXISTS gpu_import;


-- Récupérer l'ensemble des couches WFS comme des tables dans le schéma ref_dreal
IMPORT FOREIGN SCHEMA ogr_all
FROM SERVER fdw_ogr_gpu
INTO gpu_import
OPTIONS (
    -- mettre le nom des tables en minuscule et sans caractères bizares
    launder_table_names 'true',
    -- mettre le nom des champs en minuscule
    launder_column_names 'true');


SELECT foreign_table_schema, foreign_table_name
FROM information_schema.foreign_tables
WHERE foreign_table_schema = 'gpu_import'
ORDER BY foreign_table_schema, foreign_table_name;

DROP MATERIALIZED VIEW IF EXISTS gpu_import.vm_zone_urba;
CREATE MATERIALIZED VIEW gpu_import.vm_zone_urba AS
SELECT  *
FROM gpu_import.wfs_du_zone_urba a
WHERE LEFT(a.insee,2) ='12';