query on oraclespatial using OGR/OCI

[FERNANDO SIMON]

Hi Pierre,
    I don't use OGR but your problem can be with the spatial index. I saw this
problem with the 10G version.
    In the 10G version the Oracle don't recommend use SDO_LEVEL when you create
the spatial index. In my tests problems appears when I set the SDO_LEVEL, the
result doesn't correspond whant I expected.
    I don't know if the problem occur with others Oracle Spatial versions. But
you can try  with or without SDO_LEVEL.


----------------------------------------------------------------
Fernando Simon - simon at inf.univali.br
Mapserver and Oracle Spatial developer
G10 - Laboratorio de Computacao Aplicada
http://g10.cttmar.univali.br
----------------------------------------------------------------


Quoting Pierre GIRAUD <bluecarto at FREE.FR>:

>  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
> >
> >
>




------------------------------------------------
Este e-mail foi enviado pelo webmail do curso de
       Ciência da Computação - UNIVALI