8000 Integration osm by romain-jault · Pull Request #161 · meldig/SQL · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Integration osm #161

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

Open
wants to merge 12 commits into
base: master
Choose a base branch
from
Open

Integration osm #161

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
12 commits
Select commit Hold shift + click to select a range
23d6495
Create G_OSM_MISE_EN_FORME_TABLE_TEMP.sql
romain-jault Jul 30, 2021
6a879c2
Create G_OSM_NORMALISATION.sql
romain-jault Jul 30, 2021
71bc1d0
Create G_OSM_STRUCTURE.sql
romain-jault Jul 30, 2021
fd6828e
Create VM_OSM_MULTIPOLYGON.sql
romain-jault Jul 30, 2021
880dc03
Create VM_OSM_PIVOT_MULTIPOLYGONE.sql
romain-jault Jul 30, 2021
297bb2e
Create VM_OSM_PIVOT_POINT.sql
romain-jault Jul 30, 2021
3165304
Create VM_OSM_POINT.sql
romain-jault Jul 30, 2021
5b0e6a2
Create OSM_METHODOLOGIE.md
romain-jault Jul 30, 2021
a4a4607
Update VM_OSM_PIVOT_POINT.sql
romain-jault Jul 30, 2021
83dcfce
Update VM_OSM_POINT.sql
romain-jault Jul 30, 2021
d3b2a34
Update VM_OSM_PIVOT_POINT.sql
romain-jault Jul 30, 2021
4a2c525
Create G_OSM_DROIT.sql
romain-jault Sep 10, 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
49 changes: 49 additions & 0 deletions integration/OSM/G_OSM_DROIT.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,49 @@
/*
Affectation des droits de lecture et de référence vers le schéma G_GEO pour les métadonnées et les Familles/Libellés.
*/

GRANT REFERENCES ON G_GEO.TA_METADONNEE TO G_OSM;

GRANT REFERENCES ON G_GEO.TA_LIBELLE_LONG TO G_OSM;

GRANT REFERENCES ON G_GEO.TA_LIBELLE_COURT TO G_OSM;

GRANT REFERENCES ON G_GEO.TA_LIBELLE TO G_OSM;

GRANT REFERENCES ON G_GEO.TA_LIBELLE_CORRESPONDANCE TO G_OSM;

GRANT REFERENCES ON G_GEO.TA_LIBELLE_RELATION TO G_OSM;

GRANT REFERENCES ON G_GEO.TA_FAMILLE_LIBELLE TO G_OSM;

GRANT REFERENCES ON G_GEO.TA_FAMILLE TO G_OSM;

GRANT SELECT ON G_GEO.TA_METADONNEE TO G_OSM WITH GRANT OPTION;

GRANT SELECT ON G_GEO.TA_LIBELLE_LONG TO G_OSM WITH GRANT OPTION;

GRANT SELECT ON G_GEO.TA_LIBELLE_COURT TO G_OSM WITH GRANT OPTION;

GRANT SELECT ON G_GEO.TA_LIBELLE TO G_OSM WITH GRANT OPTION;

GRANT SELECT ON G_GEO.TA_LIBELLE_CORRESPONDANCE TO G_OSM;

GRANT SELECT ON G_GEO.TA_LIBELLE_RELATION TO G_OSM;

GRANT SELECT ON G_GEO.TA_FAMILLE TO G_OSM WITH GRANT OPTION;

GRANT SELECT ON G_GEO.TA_FAMILLE_LIBELLE TO G_OSM WITH GRANT OPTION;

GRANT SELECT ON G_GEO.TA_SOURCE TO G_OSM;

GRANT SELECT ON G_GEO.TA_PROVENANCE TO G_OSM;

GRANT SELECT ON G_GEO.TA_DATE_ACQUISITION TO G_OSM;

GRANT SELECT ON G_GEO.TA_METADONNEE_RELATION_ORGANISME TO G_OSM;

GRANT SELECT ON G_GEO.TA_ORGANISME TO G_OSM;

GRANT SELECT ON G_GEO.TA_METADONNEE_RELATION_ECHELLE TO G_OSM;

GRANT SELECT ON G_GEO.TA_ECHELLE TO G_OSM;
223 changes: 223 additions & 0 deletions integration/OSM/G_OSM_MISE_EN_FORME_TABLE_TEMP.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,223 @@
-- 1. MISE EN FORME DE LA TABLE TEMP_POINTS_OSM
-- 1.1. suppresion de la contrainte de clé primaire
SET SERVEROUTPUT ON
DECLARE
v_nom_1 VARCHAR2(200);
BEGIN
SELECT
CONSTRAINT_NAME
INTO v_nom_1
FROM
USER_CONSTRAINTS
WHERE
TABLE_NAME = 'TEMP_POINTS_OSM'
AND CONSTRAINT_TYPE = 'P';
EXECUTE IMMEDIATE 'ALTER TABLE G_OSM.TEMP_POINTS_OSM DROP CONSTRAINT ' || v_nom_1;
END;

-- 1.2. suppression des index
SET SERVEROUTPUT ON
DECLARE
v_nom_1 VARCHAR2(200);
BEGIN
SELECT
INDEX_NAME
INTO v_nom_1
FROM
ALL_INDEXES
WHERE
TABLE_NAME = 'TEMP_POINTS_OSM';
EXECUTE IMMEDIATE 'DROP INDEX ' || v_nom_1;
END;

-- 1.3. suppresion metadonnée spatiale
DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'TEMP_POINTS_OSM';
COMMIT

-- 1.4. suppression de la colonne OGR_FID
ALTER TABLE TEMP_POINTS_OSM DROP COLUMN OGR_FID;

-- 1.5. creation de la colonne OBJECTID
ALTER TABLE G_OSM.TEMP_POINTS_OSM
ADD OBJECTID INTEGER GENERATED BY DEFAULT AS IDENTITY
START WITH 1
INCREMENT BY 1;
COMMIT;

-- 1.6. mise à jour de la colonne OBJECTID pour qu'ils suivent les objectids de la table TA_OSM.
SET SERVEROUTPUT ON
DECLARE
-- variable pour récuperer le nom de la séquence de la table.
v_sequence VARCHAR2(200);
-- corp de la procédure.
BEGIN
-- recupere le nom de la sequence
BEGIN
SELECT s.NAME INTO v_sequence
FROM
sys.IDNSEQ$ os
INNER JOIN sys.obj$ t ON (t.obj# = os.obj#)
INNER JOIN sys.obj$ s ON (s.obj# = os.seqobj#)
INNER JOIN sys.col$ c ON (c.obj# = t.obj# AND c.col# = os.intcol#)
INNER JOIN all_users u ON (u.user_id = t.owner#)
WHERE t.NAME = 'TA_OSM'
AND u.username = 'G_OSM';
DBMS_OUTPUT.PUT_LINE(v_sequence || '.nextval');
END;
-- execution de la requete
BEGIN
EXECUTE IMMEDIATE
'UPDATE G_OSM.TEMP_POINTS_OSM SET OBJECTID =' || v_sequence || '.nextval';
END;
END;
/
COMMIT;

-- 1.7. Verification SRID
SELECT
t.geom.sdo_srid,
count(*)
FROM
TEMP_POINTS_OSM t
GROUP BY t.geom.sdo_srid;

-- 1.8. UPDATE SRID
UPDATE
TEMP_POINTS_OSM t
SET t.geom.sdo_srid=2154
WHERE t.geom.sdo_srid IS NULL;

-- 1.9. creation métadonnée spatiale

INSERT INTO "USER_SDO_GEOM_METADATA" (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES ('TEMP_POINTS_OSM', 'GEOM', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 684540, 719822.2, 0.005),SDO_DIM_ELEMENT('Y', 7044212, 7078072, 0.005)), 2154);

-- 1.10. creation index spatiale
CREATE INDEX TEMP_POINTS_OSM_SIDX
ON G_OSM.TEMP_POINTS_OSM (GEOM)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('sdo_indx_dims=2, layer_gtype=POINT, tablespace= G_ADT_INDX, work_tablespace=DATA_TEMP');


-- 1.11. creation métadonnée spatiale
CREATE INDEX TEMP_POINTS_OSM_NAME_IDX ON TEMP_POINTS_OSM("NAME") TABLESPACE "G_ADT_INDX";

CREATE INDEX TEMP_POINTS_OSM_ID_IDX ON TEMP_POINTS_OSM("OSM_ID") TABLESPACE "G_ADT_INDX";

CREATE INDEX TEMP_POINTS_OSM_REF_IDX ON TEMP_POINTS_OSM("REF") TABLESPACE "G_ADT_INDX";

-- 1.12. commentaire

COMMENT ON TABLE G_OSM.TEMP_POINTS_OSM IS 'Table temporaire contenant les points OSM de la MEL importes en base pour normalisation';

-- 2. MISE EN FORME DE LA TABLE TEMP_MULTIPOLYGONE_OSM
-- 2.1. suppresion de la contrainte de clé primaire
SET SERVEROUTPUT ON
DECLARE
v_nom_1 VARCHAR2(200);
BEGIN
SELECT
CONSTRAINT_NAME
INTO v_nom_1
FROM
USER_CONSTRAINTS
WHERE
TABLE_NAME = 'TEMP_MULTIPOLYGONE_OSM'
AND CONSTRAINT_TYPE = 'P';
EXECUTE IMMEDIATE 'ALTER TABLE G_OSM.TEMP_MULTIPOLYGONE_OSM DROP CONSTRAINT ' || v_nom_1;
END;

-- 2.2. suppression des index
SET SERVEROUTPUT ON
DECLARE
v_nom_1 VARCHAR2(200);
BEGIN
SELECT
INDEX_NAME
INTO v_nom_1
FROM
ALL_INDEXES
WHERE
TABLE_NAME = 'TEMP_MULTIPOLYGONE_OSM';
EXECUTE IMMEDIATE 'DROP INDEX ' || v_nom_1;
END;

-- 2.3. suppresion metadonnée spatiale
DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'TEMP_MULTIPOLYGONE_OSM';
COMMIT

-- 2.4. suppression de la colonne OGR_FID
ALTER TABLE G_OSM.TEMP_MULTIPOLYGONE_OSM DROP COLUMN OGR_FID;

-- 2.5. creation de la colonne OBJECTID
ALTER TABLE G_OSM.TEMP_MULTIPOLYGONE_OSM
ADD OBJECTID INTEGER GENERATED BY DEFAULT AS IDENTITY
START WITH 1
INCREMENT BY 1;
COMMIT;

-- 2.6. mise à jour de la colonne OBJECTID pour qu'ils suivent les objectids de la table TA_OSM.
SET SERVEROUTPUT ON
DECLARE
-- variable pour récuperer le nom de la séquence de la table.
v_sequence VARCHAR2(200);
-- corp de la procédure.
BEGIN
-- recupere le nom de la sequence
BEGIN
SELECT s.NAME INTO v_sequence
FROM
sys.IDNSEQ$ os
INNER JOIN sys.obj$ t ON (t.obj# = os.obj#)
INNER JOIN sys.obj$ s ON (s.obj# = os.seqobj#)
INNER JOIN sys.col$ c ON (c.obj# = t.obj# AND c.col# = os.intcol#)
INNER JOIN all_users u ON (u.user_id = t.owner#)
WHERE t.NAME = 'TA_OSM'
AND u.username = 'G_OSM';
DBMS_OUTPUT.PUT_LINE(v_sequence || '.nextval');
END;
-- execution de la requete
BEGIN
EXECUTE IMMEDIATE
'UPDATE G_OSM.TEMP_MULTIPOLYGONE_OSM SET OBJECTID =' || v_sequence || '.nextval';
END;
END;
/
COMMIT;

-- 2.7. Verification SRID
SELECT
t.geom.sdo_srid,
count(*)
FROM
TEMP_MULTIPOLYGONE_OSM t
GROUP BY t.geom.sdo_srid;

-- 2.8. UPDATE SRID
UPDATE
TEMP_MULTIPOLYGONE_OSM t
SET t.geom.sdo_srid=2154
WHERE t.geom.sdo_srid IS NULL;

-- 2.9. creation métadonnée spatiale

INSERT INTO "USER_SDO_GEOM_METADATA" (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES ('TEMP_MULTIPOLYGONE_OSM', 'GEOM', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 684540, 719822.2, 0.005),SDO_DIM_ELEMENT('Y', 7044212, 7078072, 0.005)), 2154);

-- 2.10. creation index spatiale
CREATE INDEX TEMP_MULTIPOLYGONE_OSM_SIDX
ON G_OSM.TEMP_MULTIPOLYGONE_OSM (GEOM)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('sdo_indx_dims=2, layer_gtype=MULTIPOLYGON, tablespace= G_ADT_INDX, work_tablespace=DATA_TEMP');


-- 2.11. creation métadonnée spatiale
CREATE INDEX TEMP_POINTS_OSM_NAME_IDX ON TEMP_MULTIPOLYGONE_OSM("NAME") TABLESPACE "G_ADT_INDX";

CREATE INDEX TEMP_POINTS_OSM_ID_IDX ON TEMP_MULTIPOLYGONE_OSM("OSM_ID") TABLESPACE "G_ADT_INDX";

CREATE INDEX TEMP_POINTS_OSM_REF_IDX ON TEMP_MULTIPOLYGONE_OSM("REF") TABLESPACE "G_ADT_INDX";

-- 2.12. commentaire

COMMENT ON TABLE G_OSM.TEMP_MULTIPOLYGONE_OSM IS 'Table contenant les multypolygones OSM de la MEL importé en base pour normalisation';
Loading
0