query on oraclespatial using OGR/OCI

[Pierre GIRAUD]

 I'm still having difficulties to use mapserver queries on a oracle layer.
 Before I report a bug, I would like someone to test the following SQL statement
and mapfile, and agree that something is going wrong, or tell me I am missing
something obvious.

 Thx

 Pierre GIRAUD
 camptocamp.com

---------SQL statement--------------
DROP table mytable;

CREATE TABLE MYTABLE (
  GID VARCHAR(20),
  geometry MDSYS.SDO_GEOMETRY);



insert into MYTABLE (GID,GEOMETRY) values (
  '1',
  mdsys.sdo_geometry(
    '2003',
    null,
    null,
    mdsys.sdo_elem_info_array(1,1003,1),
    mdsys.sdo_ordinate_array(1,1, 1,4, 4,4, 4,1, 1,1))
);

insert into MYTABLE (GID,GEOMETRY) values (
  '2',
  mdsys.sdo_geometry(
    '2003',
    null,
    null,
    mdsys.sdo_elem_info_array(1,1003,1),
    mdsys.sdo_ordinate_array(6,1, 6,4, 9,4, 9,1, 6,1))
);

insert into MYTABLE (GID,GEOMETRY) values (
  '3',
  mdsys.sdo_geometry(
    '2003',
    null,
    null,
    mdsys.sdo_elem_info_array(1,1003,1),
    mdsys.sdo_ordinate_array(1,6, 1,9, 9,9, 9,6, 1,6))
);

delete from user_sdo_geom_metadata where table_name like 'MYTABLE';

insert into user_sdo_geom_metadata (table_name,column_name,diminfo,srid)
values ('MYTABLE','GEOMETRY',
mdsys.SDO_DIM_ARRAY(mdsys.SDO_DIM_ELEMENT('X', 0, 10, 0.1),
mdsys.SDO_DIM_ELEMENT('Y', 0, 10, 0.1)),
NULL);

drop index q_mytable;

create index Q_MYTABLE on MYTABLE(GEOMETRY) indextype is MDSYS.spatial_index
parameters ('SDO_LEVEL=6');

---------mapfile---------------
NAME DEMO
STATUS ON
SIZE 500 300
EXTENT 0 0 10 10
IMAGECOLOR 255 255 255


WEB
  IMAGEPATH "/var/www/cartoserver_DGI_flash/www-data/images/"
  IMAGEURL "cartogfx/"
END

LAYER
  NAME squares
  TYPE POLYGON
  STATUS ON
#  CONNECTIONTYPE oraclespatial
#  CONNECTION "pierre/xbe9a51h at 192.168.226.149:1521/orcl"
  CONNECTION "OCI:pierre/xbe9a51h at 192.168.226.149:1521/orcl"
  CONNECTIONTYPE OGR
#  DATA "select * from mytable"
  DATA "mytable"
  TEMPLATE "ttt"
  CLASS
   SIZE 10
   COLOR 255 255 255
   OUTLINECOLOR 200 200 200
  END
END

END



Selon bluecarto at free.fr:

>  Hello,
>
> Following the good advices of Frank Warmerdam, I improve in using oracle
> spatial
> data with spatial indexes. For now, I know that I should not use sql
> statements
> in the mapfile but queries directly to the table :
>
>   DATA "my_table"
> instead of
>   DATA "select * from my_table"
>
> Both return the same images. Shapes are well drawn, and labeling is correct.
>
> But the first (more performant) statement doesn't give good results to a
> query.
> The data returned doesn't correspond to what is expected. And no shape is
> highlighted.
>
> Do someone using Oracle have same problems ?
>
> Pierre GIRAUD
> camptocamp.com
>
>