Script SQL Oracle Spatial – Chargement données test

Ce script très simple de chargement de données test, permettra de démarrer rapidement avec Oracle Spatial. Je l'utilise régulièrement pour développer, tester certaines requêtes ou procédures et pour ce blog.  Les données test sont des points d'éoliennes (ZZ_EOLIENNES_P) liés par une Foreign key à une surface ville (ZZ_VILLE_S)
-- #########################################
-- Table ZZ_EOLIENNES_P

CREATE TABLE ZZ_EOLIENNES_P
(
 EOLIENNES_ID NUMBER(10),
 VILLE_ID NUMBER(10),
 DESC_ VARCHAR2(200),
 GEOM MDSYS.SDO_GEOMETRY
);
ALTER TABLE ZZ_EOLIENNES_P  ADD (CONSTRAINT ZZ_EOLIENNES_P_PK PRIMARY KEY (EOLIENNES_ID));

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)VALUES ('ZZ_EOLIENNES_P', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-83,-54, 0.000005),MDSYS.SDO_DIM_ELEMENT('Y',43, 63,0.000005)),4326);

DROP INDEX ZZ_EOLIENNES_P_IS;
CREATE INDEX ZZ_EOLIENNES_P_IS ON ZZ_EOLIENNES_P("GEOM") INDEXTYPE IS MDSYS.SPATIAL_INDEX;

-- Options possibles
-- PARAMETERS (' geodetic=false layer_gtype=COLLECTION SDO_COMMIT_INTERVAL=100 tablespace=MY_TABLE_SPACE INITIAL=200K NEXT=64K MINEXTENTS=1 MAXEXTENTS=200 PCTINCREASE=0');

-- Ajout de quelques données
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (0, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.59743928909302,48.79196964612326,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (1, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.59743928909302,48.79196964612326 ,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (2, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.59606599807739,48.79290616097424 ,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (3, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.59743928909302,48.79196964612326 ,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (4, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.59836196899414,48.79070090566935,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (5, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.59883940219879,48.789729007576526,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (6, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.59963870048523,48.78895854407517,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (7, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.60334014892578,48.78498940933159,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (8, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.60491728782654,48.784289564951145,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (9, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.6097023487091,48.78265655677682,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (10, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.61019051074982,48.781429998379544,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (11, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.6094663143158,48.78035187474781,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (12, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.60951459407806,48.77851370892812,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (13, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.61433720588684,48.77799406138623,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (14, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.6143479347229,48.77916414452893,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (15, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.61436402797699,48.78015038677945,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (15, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.69213199615479,48.76361501275503,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (16, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.69311904907227,48.76106899360166,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (17, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.69861221313477,48.759909097641696,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (18, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.69869804382324,48.7569809484446,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (19, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.69979238510132,48.75425068455974,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (20, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.70485639572144,48.75188810849875,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (21, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.7077317237854,48.74953956949669,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (22, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.71073579788208,48.74755878862284,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (23, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.71384716033936,48.74552133255116,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (24, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.7171516418457,48.740667892655736,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (25, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.72170066833496,48.74253574424521,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (26, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.72356748580933,48.745761869860054,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (27, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.73457527160645,48.75017623248125,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (28, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.73459672927856,48.75318966115,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (29, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.74504661560059,48.75215691017849,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (30, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.73092746734619,48.755467297157985,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (31, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.72560596466064,48.75379798397896,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (32, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.72288084030151,48.758805757115624,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (33, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.72876024246216,48.773232142600904,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (34, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.72545576095581,48.771464426020316,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (35, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.7218508720398,48.76937136911909,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (36, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.71783828735352,48.76710850675318,NULL), NULL, NULL));
INSERT INTO ZZ_EOLIENNES_P (EOLIENNES_ID, GEOM) VALUES (37, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-67.71483421325684,48.76515670605726,NULL), NULL, NULL));

SELECT count(*) from ZZ_EOLIENNES_P;
------
-- 38 rec

-- #########################################
--Table VILLE_S
DROP TABLE ZZ_VILLE_S;
CREATE TABLE ZZ_VILLE_S
(
 VILLE_ID NUMBER(10),
 NOM VARCHAR2(200),
 GEOM MDSYS.SDO_GEOMETRY
);
ALTER TABLE ZZ_VILLE_S ADD (CONSTRAINT VILLE_S_PK PRIMARY KEY (VILLE_ID));

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)VALUES ('ZZ_VILLE_S', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-83,-54, 0.000005),MDSYS.SDO_DIM_ELEMENT('Y',43, 63,0.000005)),4326);

DROP INDEX VILLE_S_IS;
CREATE INDEX ZZ_VILLE_S_IS ON ZZ_VILLE_S("GEOM") INDEXTYPE IS MDSYS.SPATIAL_INDEX ;
--Options possibles
-- PARAMETERS (' geodetic=false layer_gtype=POLYGONE SDO_COMMIT_INTERVAL=100 tablespace=MY_TABLESPACE INITIAL=200K NEXT=64K MINEXTENTS=1 MAXEXTENTS=200 PCTINCREASE=0')

-- Ajout de quelques données
INSERT INTO ZZ_VILLE_S VALUES(10, 'polygon_sud',  SDO_GEOMETRY( 2003, 4326, NULL,
                                              SDO_ELEM_INFO_ARRAY(1,1003,1),
                                            SDO_ORDINATE_ARRAY(-67.75,48.739,-67.596,48.739, -67.596,48.760,-67.750,48.760,-67.75,48.739)));
INSERT INTO ZZ_VILLE_S VALUES(20, 'polygon_nord',  SDO_GEOMETRY( 2003, 4326, NULL,
                                              SDO_ELEM_INFO_ARRAY(1,1003,1),
                                            SDO_ORDINATE_ARRAY(-67.750,48.760,-67.596,48.760,-67.596,48.793,-67.750,48.793,-67.750,48.760)));

SELECT count(*) from ZZ_VILLE_S;
------
-- 2 rec

-- #########################################
-- Test requêtes spatiales Licence Oracle Locator

SELECT p.EOLIENNES_ID,v.NOM FROM ZZ_EOLIENNES_P p, ZZ_VILLE_S v
WHERE SDO_RELATE(v.GEOM, p.GEOM, 'mask=anyinteract  querytype=window') = 'TRUE'
AND v.VILLE_ID=10;
------
--16 rec

SELECT p.EOLIENNES_ID,v.NOM FROM ZZ_EOLIENNES_P p, ZZ_VILLE_S v
WHERE SDO_RELATE(v.GEOM, p.GEOM, 'mask=anyinteract  querytype=window') = 'TRUE'
AND v.VILLE_ID=20;
------
--22 rec

-- retourner les 7 points les plus proche de lat/long spécifique
SELECT e.EOLIENNES_ID
FROM ZZ_EOLIENNES_P e
WHERE SDO_NN(e.geom,mdsys.sdo_geometry(2001, 4326,
mdsys.sdo_point_type(-67.530932 ,48.843439,NULL), NULL,
   NULL),  'sdo_num_res=7') = 'TRUE';

-- Générer un buffer de 1 kilomètre autour des eoliennes.
SELECT e.EOLIENNES_ID, SDO_GEOM.SDO_BUFFER(e.GEOM, m.diminfo, 1, 'unit=km arc_tolerance=0.05')
 FROM ZZ_EOLIENNES_P e, user_sdo_geom_metadata m
 WHERE m.table_name = 'ZZ_EOLIENNES_P'
 AND m.column_name = 'GEOM';

-- #########################################
-- Appliquer un Foreign Key aux EOLIENNES_P vs la ville

UPDATE ZZ_EOLIENNES_P SET VILLE_ID=10 WHERE eoliennes_id IN (
SELECT p.eoliennes_id FROM ZZ_EOLIENNES_P p, ZZ_VILLE_S v
WHERE SDO_RELATE(v.GEOM, p.GEOM, 'mask=anyinteract  querytype=window') = 'TRUE'
AND v.VILLE_ID=10);
---
--16 lignes mises à jour

UPDATE ZZ_EOLIENNES_P SET VILLE_ID=20 WHERE eoliennes_id IN (
SELECT p.eoliennes_id FROM ZZ_EOLIENNES_P p, ZZ_VILLE_S v
WHERE SDO_RELATE(v.GEOM, p.GEOM, 'mask=anyinteract  querytype=window') = 'TRUE'
AND v.VILLE_ID=20);
---
--22 lignes mises à jour

ALTER TABLE ZZ_EOLIENNES_P ADD
      CONSTRAINT ZZ_EOLIENNES_P_FK
      FOREIGN KEY (VILLE_ID)
      REFERENCES ZZ_VILLE_S(VILLE_ID)
      ON DELETE CASCADE;