query on oraclespatial using OGR/OCI

[Fernando S.]

Hi Pierre,
    The query functions can be possible in the next version of the
Oracle Spatial support. I implemented these functions here in my
computer and I believe that this functions will be include in the next
version of native support.
    I don't know how the OGR implement the query function but this
problem appear when you use functions that use the primary filter like
SDO_FILTER.
   Here the problem disappear when I recreate the indexes without
SDO_LEVEL. Another point is the tolerance, you can try set a  small
value for Tolerance like 0.00001.

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


bluecarto at free.fr wrote:

>First question : how can you query oracle layers if you don't use OGR ? I mean
>the function are not implemented for oraclespatial connectiontype.
>
>I had already try you suggestion (ommitting the SDO_LEVEL parameter). That could
>be a good idea, but, sadly, it doesn't give any better result.
>
>I'm using Oracle 10g on Debian or Oracle 9.2 on RedHat.
>
>In my sample the result given seem to be the first record in the table.
>
>Regards
>
>Pierre
>
>Selon FERNANDO SIMON <simon at inf.univali.br>:
>
>
>
>>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/images/"
>>>  IMAGEURL "images/"
>>>END
>>>
>>>LAYER
>>>  NAME squares
>>>  TYPE POLYGON
>>>  STATUS ON
>>>#  CONNECTIONTYPE oraclespatial
>>>#  CONNECTION "scott/tiger at orcl"
>>>  CONNECTION "OCI:scott/tiger at 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
>>
>>
>>
>
>
>
>