Plug-in Oracle pour Mapserver dans UbuntuGIS

Mapgears a réalisé un plug-in Oracle pour Mapserver et GDAL/OGR qui simplifie énormément la configuration d'une configuration Mapserver pour Oracle.

Installer le client Oracle

Oracle met à la disposition des organisations un portail regroupant une panoplie d’outils et de tutoriels logiciels Open Source d’Oracle. Grâce à ce portail, il est possible d’installer une version gratuite de la base de données Oracle et le client Oracle XE plus facilement sur un poste Linux.  Par contre, actuellement les packages de ce type d'installation datent de 2006 et il ne s'agit pas de la dernière version du client.  Ça fonctionne, mais l'option manque un peu de rigueur pour monter une infrastructure solide pour une organisation en mode "stable".

Pour permettre la connectivité entre Mapserver et Oracle, je recommande l'installation de la version du client Oracle officiellement supportée:  Oracle InstantClient.  Vous trouverez sur le site Web d'Oracle toutes les versions OS supportées.  Dans cette procédure, j'utilise les sources (dans fichier zip) et non les packages Redhat (rpm).  Pour télécharger les sources officielles, il faudra vous enregistrer sur le site Web d'Oracle et accepter les termes de la licence.  Par la suite, faire l'installation via la méthode suivante :

  • Télécharger les fichiers zip requis pour l'installation de la version Linux x86 InstantClient v 11.2. Les fichiers zip requis seront les suivants :
  1. Instant Client Package - Basic Lite ( instantclient-basic-linux-11.2.0.4.0.zip )
  2. Instant Client Package - SDK ( instantclient-sdk-linux-11.2.0.4.0.zip )
  3. Instant Client Package - SQL*Plus ( instantclient-sqlplus-linux-11.2.0.4.0.zip )
  • Pour installer le client, aucune installation n'est requise.  Il faut simplement dézipper les fichiers sources dans le répertoire de votre choix (/opt/ dans mon cas):
sudo unzip instantclient-sqlplus-linux-11.2.0.4.0.zip -d /opt/
sudo unzip instantclient-basic-linux-11.2.0.4.0.zip -d /opt/
sudo unzip instantclient-sdk-linux-11.2.0.4.0.zip -d /opt/
ls -l /opt/instantclient_11_2/
-rwxrwxr-x 1 root root     22004 Aug 25 11:18 adrci
-rw-rw-r-- 1 root root       437 Aug 25 11:18 BASIC_README
-rwxrwxr-x 1 root root     38461 Aug 25 11:18 genezi
-r-xr-xr-x 1 root root       368 Aug 25 11:18 glogin.sql
-rwxrwxr-x 1 root root  44316855 Aug 25 11:18 libclntsh.so.11.1
-r-xr-xr-x 1 root root   7098468 Aug 25 11:18 libnnz11.so
-rwxrwxr-x 1 root root   1881900 Aug 25 11:18 libocci.so.11.1
-rwxrwxr-x 1 root root 118729922 Aug 25 11:18 libociei.so
-r-xr-xr-x 1 root root    152585 Aug 25 11:18 libocijdbc11.so
-r-xr-xr-x 1 root root   1499831 Aug 25 11:18 libsqlplusic.so
-r-xr-xr-x 1 root root   1216654 Aug 25 11:18 libsqlplus.so
-r--r--r-- 1 root root   2091135 Aug 25 11:18 ojdbc5.jar
-r--r--r-- 1 root root   2739616 Aug 25 11:18 ojdbc6.jar
drwxrwxr-x 4 root root      4096 Aug 25 11:18 sdk
-r-xr-xr-x 1 root root      6913 Aug 25 11:18 sqlplus
-rw-rw-r-- 1 root root       441 Aug 25 11:18 SQLPLUS_README
-rwxrwxr-x 1 root root    160203 Aug 25 11:18 uidrvci
-rw-rw-r-- 1 root root     66779 Aug 25 11:18 xstreams.jar
  • Pour configurer le client par la méthode couramment utilisée par la majorité des organisations, on va créer l'arborescence de répertoires network et y ajouter un fichier tnsnames fourni par le DBA. ( cette étape est optionnelle)
sudo mkdir /opt/instantclient_11_2/network
sudo mkdir /opt/instantclient_11_2/network/admin
sudo vim /opt/instantclient_11_2/network/admin/tnsnames.ora
  • Pour avoir le runtime library path du le Client Oracle pour tous les utilisateurs, il est préférable d’ajouter un fichier de configuration dans le répertoire de chargement de librairie, pour que tous les logiciels puisent y accéder.
sudo vim /etc/ld.so.conf.d/oracle.conf
/opt/instantclient_11_2 ==>ligne à ajouter dans le fichier
sudo ldconfig
  • Validation de connexion direct (sans TNS)
sqlplus [utilisateur]/[mot_passe]@//[nom_server_ou_ip]:1521/[SID]
  • Validation de connexion (avec TNS)
sqlplus [utilisateur]@[SID]

Installer Mapserver et le plugin Oracle

Les instructions de base pour installer le plugin Oracle pour Mapserver sont maintenant documenté sur le Trac UbuntuGIS.
  • 1_ Connecter les packages privés de UbuntuGIS
sudo apt-get install python-software-properties
sudo add-apt-repository ppa:ubuntugis/ppa
sudo apt-get update
  • 2_ Installer Mapserver
sudo apt-get install cgi-mapserver mapserver-bin libmapcache mapcache-cgi mapcache-tools libapache2-mod-mapcache libmsplugin-oracle-src
  • 3_ Activer le plug-in Oracle pour Mapserver.  NOTE 1: on doit créer des liens symboliques avec les librairies requises par le plug-in.  Oracle ne respecte pas la nomenclature et les façons de faire Debian alors on doit s'adapter un peu.  NOTE 2: la variable d'environnement n'est pas requise pour rouler Mapserver une fois le plug-in activé, elle est seulement requise pour l'activation. NOTE 3: Attention, dans mon cas, je me suis retrouvé avec une librairie 11.1 dans un package annoncé 11.2! Je n’ai pas de félicitation à faire ici...
sudo ln -s /opt/instantclient_11_2/sdk/include/ /opt/instantclient_11_2/include

sudo ln -s /opt/instantclient_11_2/libocci.so.11.1 /opt/instantclient_11_2/libocci.so

sudo ln -s /opt/instantclient_11_2/libclntsh.so.11.1 /opt/instantclient_11_2/libclntsh.so

sudo ORACLE_HOME=/opt/instantclient_11_2 mapserver-oracle-build yes
  • 4_ Par la suite utiliser un répertoire relatif dans le paramètre PLUGIN du LAYER ou paramétriser le répertoire dans l'entête du mapfile avec "CONFIG MS_PLUGIN_DIR"
LAYER
  ...
  CONNECTIONTYPE PLUGIN
  PLUGIN "/usr/lib/msplugins/libmsplugin_oracle.so"
  DATA "GEOM FROM ZZ_EOLIENNES_P USING UNIQUE EOLIENNES_ID SRID 4326"
  ...
END
Note: jeu de données test

MS4W et Oracle Spatial

Même si en principe, l'installation et la configuration pour connecter un layer Oracle Spatial d'un mapfile est vraiment simple,  j'ai passé quelques jours dernièrement à chercher une erreur de configuration un peu stupide!  J'ai cru bon en faire un résumé en lien avec la documentation Mapserver sur le sujet.

À mon avis, la façon la plus simple d'utiliser MS4W avec Oracle Spatial sur votre Windows, est d’utiliser le Instant Client basic light.  Pas besoin de polluer votre setup avec la grosse installation du client complet.  La procédure qui suit montre comment créer à partir de zéro  1) une table avec un champ SDO_GEOMETRY sur Oracle, 2) l’enregistrer dans le catalogue Oracle, 3) installer le client Oracle, 4) créer le mapfile et 5) des astuces pour tester votre config.

Chargement d'un jeux de données test

Il sera très utile d'avoir un jeux de données testes sur votre serveur Oracle.  J'utilise régulièrement ce script pour charger des données rapidement et sans effort.

Installer le InstantClient Oracle

0) Le package MS4W est un build 32bit.  Il est très important de télécharger le InstantClient-basiclight 32bits sur le site d’Oracle même si votre serveur Windows est de type 64bit.   Pour installer le client Oracle, simplement décompresser le fichier et placer dans le répertoire de son choix.  Pour cet exemple utilisons:

 c:instantclient_11_2

1) Il est IMPÉRATIF de placer ce répertoire dans la Variable d’Environnement  PATH du système.  C’est la SEULE variable d’environnement que vous devez configurer.   Les variables ORACLE_HOME, ORACLE_BASE, TNS_ADMIN, ORACLE_SID ou LD_LIBRARY_PATH ne sont pas requises pour MS4W.

2)  On doit déplacer le fichier libmap.dll dans le répertoire cgi-bin de Apache et le fichier ogr_OCI.dll dans le répertoire de gdalplusgins.  NOTE: Il serait préférable d’arrêter le service MS4W Apache sur votre serveur s’il est déjà en route avant de faire la copie des fichiers:

c:ms4wApachecgi-binignored-libmaporacle11glibmap.dll --> c:ms4wApachecgi-bin
c:ms4wgdalpluginsignoredoracle-11gogr_OCI.dll --> c:ms4wgdalplugins
Ne reste plus qu’à redémarrer Apache via un Prompt DOS ou dans le gestionnaire de services Windows: c:ms4wapache-restart
IMPORTANT:

Je suggère ici de vous assurer que le PATH contient bien votre répertoire du client Oracle.  On peut regarder dans un Prompt DOS avec la commande set mais vous devez aussi vérifier qu’elle sera aussi disponible dans Apache.  Vous pouvez valider avec la commande phpinfo.php.  On doit trouver à la rubrique “Environment” la valeur de la variable PATH qui doit inclure le répertoire du client Oracle.  Il se peut très bien que la valeur de PATH du système Windows ne soit pas la même dans Apache et dans DOS.

ogrinfo

Avant de faire des essais avec Mapserver, il est fortement recommandé de tester la connexion Oracle avec ogrinfo parce que moins de composantes sont requises.  J'utilise une connexion sans TNSNAMES.ORA parce que c'est plus propre et plus simple.  Ouvrir un Prompt Dos et faire les commandes suivantes: 

C:ms4w>setenv
C:ms4w>set PATH=C:instantclient_11_2;%PATH%
C:ms4w>ogrinfo OCI:user/pw@my_serveur_name_or_ip:port/my_sid_db_name
-----
INFO: Open of `OCI:user/pw@my_serveur_name_or_ip/my_sid_db_name'
using driver `OCI' successful.
1: ZZ_EOLIENNES_P
2: ZZ_VILLE_S 
IMPORTANT:

En cas de problème, utiliser cette astuce pour avoir un log complet des problèmes potentiels.

C:ms4w>set CPL_DEBUG=on
C:ms4w>ogrinfo OCI:user/pw@my_server_name_or_ip:port/my_sid_db_name

mapfile

J'ai ici mon layer très simple pour me connecter à Oracle.
   LAYER
     NAME "zz_ville_s"
     TYPE POLYGON
     CONNECTIONTYPE oraclespatial
     CONNECTION "user/pw@my_server_name_or_ip:port/my_sid_db_name"
     DATA "GEOM FROM (SELECT geom FROM ZZ_VILLE_S)"
     PROCESSING "CLOSE_CONNECTION=DEFER"
     CLASS
       NAME "Ville"
       STYLE
         COLOR 25 200 20
         OUTLINECOLOR 80 80 80
       END
     END
   END

shp2img

Par la suite on peut produire un mapfile très très simple pour tester un layer Oracle.   Encore une fois on peut essayer avec l’utilitaire shp2img qui a l’avantage de ne pas passer par la composante Apache.  Ça peut être fort utile pour débuger un service qui ne veut pas démarrer.

C:ms4w>setenv
C:ms4w>set PATH=C:instantclient_11_2;%PATH%
C:ms4w>shp2img -m C:/view-oracle2.map -i png -o C:/temp/test-oracle.png -map_debug 5 -e -67.7 48.7 -67.5 48.8 -l ZZ_VILLE_S
----------
msDrawMap(): rendering using outputformat named png (AGG/PNG).
msDrawMap(): WMS/WFS set-up and query, 0.000s
msDrawMap(): Layer 0 (ZZ_EOLIENNES_P), 0.674s
msDrawMap(): Drawing Label Cache, 0.000s
msDrawMap() total time: 0.677s
msSaveImage(C:/temp/test-oracle.png) total time: 0.079s
msFreeMap(): freeing map at 020BC200.

mapserv via Apache

Sous Windows avec MS4W, il n’y a pas vraiment de configuration à faire.  Le fait d’ajouter dans la Variable d’Environnement PATH, le répertoire du client Oracle ferra en sorte que  celle-ci se retrouvera dans la Variable d’Environnement PATH de Apache.

http://127.0.0.1/cgi-bin/mapserv.exe?map=C:/view-oracle2.map&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&LAYERS=ZZ_VILLE_S&STYLES=&SRS=EPSG:4326&BBOX=-67.7,48.7,-67.5,48.8&WIDTH=400&HEIGHT=300&FORMAT=image/png

mapserv via commande DOS

Autre astuce, on peut aussi utiliser la commande mapserv pour tester la URL avec l'option QUERY_STRING.

C:ms4w>setenv
C:ms4w>set PATH=C:instantclient_11_2;%PATH%
C:ms4w>mapserv “QUERY_STRING=map=C:/view-oracle2.map&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&LAYERS=ZZ_VILLE_S&STYLES=&SRS=EPSG:4326&BBOX=-67.7,48.7,-67.5,48.8&WIDTH=400&HEIGHT=300&FORMAT=image/png”
Simon Mercier (smercier at mapgears.com)

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;

Oracle NSL_LANG variable

Vous utilisez Apache comme moteur Web avec Mapserver ou GeoServer et vous voulez avez des problèmes d'accents dans votre application. 1) Identifier votre encodage de BD; SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET' or PARAMETER = 'NLS_LANGUAGE'; ------------------------------------------------- PARAMETER VALUE NLS_LANGUAGE CANADIAN FRENCH NLS_CHARACTERSET WE8ISO8859P1 2) Identifier votre code de langue http://download.oracle.com/docs/cd/A84870_01/doc/nt.816/a73010/appc.htm Dans mon cas il sera: "CANADIAN FRENCH_CANADA.WE8ISO8859P1" 3) On peut spécifier ce code dans une variable d'environnement sur le serveur export NLS_LANG="CANADIAN FRENCH_CANADA.WE8ISO8859P1" ou l'ajouter dans le profile de tous les user sudo vim /etc/profile 4) Spécifier votre encodage de langue Oracle correctement dans Apache On doit placer une variable d'environnement dans le fichier de configuration d'Apache. Attention, cette conf NE DOIT PAS être placée dans un balise <VirtualHost> </VirtualHost> ... sudo vim /etc/apache2/httpd.conf ScriptAlias /cgi-bin/ /usr/lib/cgi-bin/ <Directory /usr/lib/cgi-bin/ >    AllowOverride None    Options ExecCGI -MultiViews +SymLinksIfOwnerMatch    Order allow,deny    Allow from all </Directory> #spécifier le client Oracle et la langue <Location "/cgi-bin/">    SetEnv ORACLE_HOME "/opt/oracle/clients/10.2.0.1"    SetEnv NLS_LANG "CANADIAN FRENCH_CANADA.WE8ISO8859P1" </Location> #Rep temp pour mapserver Alias /ms_tmp/ "/var/www/ms_tmp/" <Directory "/var/www/ms_tmp/">    Options Indexes MultiViews FollowSymLinks    AllowOverride None    Order allow,deny    Allow from all </Directory>

De SDEBINARY à SDO_GEOMETRY dans Oracle Spatial

Voici donc une méthode propre, étape par étape, pour transformer une table ESRI (Geodatabase Feature Class) d'un format SDEBINARY vers un format SDO_GEOMETRY dans Oracle à l'aide de FME. Cette méthode va aider à transformer en lot plusieurs Feature Class avec de Workbench FME, des scripts SQL et des lignes de commandes ArcSDE. Le fait que ArcGIS server ne supporte pas les Features Class avec deux colonnes géométriques, complique un peu la chose, mais il est possible d'y arriver en suivant ce roadmap:

1) Créer une copie de la Feature Class avec un suffixe “_SDO” à l’aide de FME.  Dans cette opération, on va transformer la géométrie SDEBINARY en SDO_GEOMETRY dans Oracle.  On ajoute un suffixe parce qu'on travaille dans la Même BD.

2) Une fois le transfert fait dans FME on va valider les données SELECT count(*) FROM REGION_S_SDO;SELECT count(*) FROM REGION_S_SDO WHERE GEOM is null; 3) FME gère bien les métadonnées spatiales dans Oracle, mais on peut tout de même les harmoniser soit-même. DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME ='REGION_S_SDO';INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)VALUES ('REGION_S_SDO', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-83,-54, 0.0000000000000005),MDSYS.SDO_DIM_ELEMENT('Y',43, 63,0.0000000000000005)),4326); 4) Il est important de vérifier que toutes les géométries de notre nouveau Feature Class sont valides DROP TABLE val_results;CREATE TABLE val_results (sdo_rowid rowid, result varchar2(2000));EXECUTE SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('REGION_S_SDO' ,'GEOM', 'VAL_RESULTS'); 5) Le résultat de cette requête indique le nombre de géométries invalides SELECT count(*) from val_results; 6) On peut au besoin appliquer des correctifs si des géométries sont invalides: UPDATE REGION_S_SDO SET geom = (SELECT sdo_util.rectify_geometry(REGION_S_SDO.geom, 0.0000000000000005) from val_results WHERE val_results.sdo_rowid=REGION_S_SDO.rowid);

7) Bien qu’impossible via FME, il pourrait arriver de trouver des géométries provenant d’ailleurs sans SRID.  Pour ne pas avoir de problème il faut corriger le SRID - NOTE on doit utiliser l'alias pour accéder au propriété de SDO_GEOMETRY select count(srid) as cnt_srid,srid from (SELECT t.geom.sdo_srid as srid FROM REGION_S_SDO t) group by srid; UPDATE REGION_S_SDO t set t.GEOM.SDO_SRID = 4326 WHERE GEOM is not null; 8 ) Créer index avec PK et Spatial sur la copie de la table. CREATE UNIQUE INDEX PK_REGION_S_SDO ON REGION_S_SDO(OBJECTID); 9) On va en même temps reconstruire les index de la table SDE pour la requête UPDATE plus loin de le process: SELECT ' ALTER INDEX ' || INDEX_NAME || ' REBUILD;' FROM USER_INDEXES WHERE TABLE_NAME = 'REGION_S' AND INDEX_TYPE='NORMAL'; ------------------------------------------- ALTER INDEX R262_SDE_ROWID_UK REBUILD;

10) On va détruire les tables géométriques ArcSDE avec la commande sdelayer.  Cette commande ne détruira pas la table, mais bien juste les classes géométriques du modèle ArcSDE (tables FXX, SXX, triggers et indexs).

Deletes an entire feature class with sdelayer means that all features and the feature class definition are permanently removed. The business table is not deleted.  For feature classes that have been implemented using the binary schema (Oracle and SQL Server only), deleting a feature class drops the DBMS spatial tables (the F and S tables) and removes the definition of the feature class from the ArcSDE data dictionary tables. select 'sdelayer -o delete -l ' || table_name || ',SHAPE -i port:5151 -u '|| owner || ' -p '|| owner || ' -N' from sde.table_registry where owner = 'GEO'; -------------------------------------------- sdelayer -o delete -l REGION_S,SHAPE -i port:5151 -u GEO -p baby@geo -N . . .

11) Ajouter une colonne SDO_GEOMETRY à la classe géométrique.  On utilise cette SQL pour générer le code: select 'ALTER TABLE ' || table_name || ' ADD GEOM SDO_GEOMETRY;'from sde.table_registry where owner = 'GEO'; ---------------------------- ALTER TABLE REGION_S ADD GEOM SDO_GEOMETRY; . . 12) Faire la requête update qui va transférer les SDO_GEOMETRY de la table temporaire à la classe géométrique UPDATE REGION_S SET GEOM = (select REGION_S_SDO.GEOM from REGION_S_SDO where REGION_S.objectid = REGION_S_SDO.objectid); 13) On doit détruire les métadonnées du catalogue Oracle pour en remettre de bonnes infos propres. DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME ='REGION_S'; INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)VALUES ('REGION_S', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-83,-54, 0.00005),MDSYS.SDO_DIM_ELEMENT('Y',43, 63,0.00005)),4326); 14) Créer l’index spatial. L’index utilisé est un Index R-Tree. select 'CREATE INDEX '|| table_name || '_IDX ON ' || table_name||'("GEOM") INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('' geodetic=false layer_gtype=COLLECTION SDO_COMMIT_INTERVAL=100 tablespace=BDTAG INITIAL=200K NEXT=64K MINEXTENTS=1 MAXEXTENTS=200 PCTINCREASE=0'');' sde.table_registry where owner = 'GEO'; ---------------------------- CREATE INDEX EQUIP_L_GEOIDX ON REGION_S("GEOM") INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS (' geodetic=false layer_gtype=COLLECTION SDO_COMMIT_INTERVAL=100 tablespace=BDTAG INITIAL=200K NEXT=64K MINEXTENTS=1 MAXEXTENTS=200 PCTINCREASE=0'); . . .

NOTE: Il est possible que l’index par défaut R-Tree ne fonctionne pas, même en corrigeant les géométrie avec FME et/ou les packages de fonctions d’Oracle spatial.  Dans un tel cas on aura le message d’erreur qui ressemble à ceci: Erreur SQL : ORA-29855: erreur d'exécution de la routine ODCIINDEXCREATEORA-13205: erreur interne lors de l'analyse des paramètres spatiaux ORA-06512: à "MDSYS.SDO_INDEX_METHOD_10I", ligne 1029855. 00000 -  "error occurred in the execution of ODCIINDEXCREATE routine" *Cause: Failed to successfully execute the ODCIIndexCreate routine .*Action:   Check to see if the routine has been coded correctly.

Pour remédier à ce problème, on doit utiliser un index de type Quadtree.  Oracle ne recommande plus d’utiliser ce type d’index mais il fonctionne toujours très bien.  Plus le paramètre SDO_LEVEL est élevé, plus la pyramide d’indexation sera longue à créer et volumineuse en espace disque.  Ne chercher plus les fonctions obsolètes pour évaluer ce paramètre (tel SDO_TUNE.ESTIMATE_TILING_LEVEL): CREATE INDEX REGION_S_SDO_GEOIDX ON REGION_S("GEOM") INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS (' SDO_LEVEL=7 geodetic=false layer_gtype=COLLECTION SDO_COMMIT_INTERVAL=100 tablespace=BDTAG INITIAL=200K NEXT=64K MINEXTENTS=1 MAXEXTENTS=200 PCTINCREASE=0');

15) Il est conseillé de faire un analyse table sur l’index spatial pour optimiser les performances.  Puisqu’il s’agit d’un index système on doit requêter la table sdo_index_table pour trouver le nom de l’index spatial: select 'analyze table ' || sdo_index_table || ' compute statistics;'from user_sdo_index_metadata where sdo_index_name like 'BDGA%'; -------------------------- 16) On va enregistrer la classe géométrique de type SDO_GEOMETRY dans le registre ArcSDE. sdelayer -o register -l REGION_S,GEOM -e l -C OBJECTID,USER -R 2 -k SDO_GEOMETRY -t SDO_GEOMETRY -i port:5151 -u geo -p baby -s sigdev

Merci au DBA Beauceron pour son aide! Vive la Beauce