query on oraclespatial using OGR/OCI

Fri Oct 22 00:03:21 PDT 2004

Hi Fernando,

I understand that you did some work on the mapserver files and now, your queries are working?

As Pierre knows I'm using mapserver with oracle spatial and my next step is "The Queries".... If you need any help do not hesitate to told me. I'm starting with "The Queries" work, but I will try to help.

I think that I'm going to use php to generate the queries. It isn't the best way, but, for now, Is the only way I know.

THanks 

-----Mensaje original-----
De: Fernando S. [mailto:simon at INF.UNIVALI.BR]
Enviado el: jueves, 21 de octubre de 2004 18:31
Para: MAPSERVER-USERS at LISTS.UMN.EDU
Asunto: Re: [UMN_MAPSERVER-USERS] query on oraclespatial using OGR/OCI

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