[mapserver-users] Best Practices for the Spatial Index with OracleDB 11gR2

Wed Oct 27 10:19:33 EDT 2010

When you use NONE, yes. No spatial filtering is taking place.

On 10/27/10 10:08 AM, "Rahkonen Jukka" <Jukka.Rahkonen at mmmtike.fi> wrote:

> Hi,
> 
> Does it mean that Mapserver is making then all the Oracle queries without BBOX
> and just selects everything?
> 
> -Jukka-
> 
> Smith, Michael ERDC-CRREL-NH wrote
> 
>> Aihe: Re: [mapserver-users] Best Practices for the Spatial
>> Index with OracleDB 11gR2
>> 
>> You can disable the spatial filtering (which is what requires
>> the spatial
>> index) by using
>> 
>>    DATA "the_geom from (SELECT SDO_GEOM.SDO_CENTROID(geometrie, 2) AS
>> the_geom, id FROM ereignis) USING SRID 31468 NONE"
>> 
>> Mike
>> 
>> 
>> On 10/27/10 8:14 AM, "Rahkonen Jukka"
>> <Jukka.Rahkonen at mmmtike.fi> wrote:
>> 
>>> Hi,
>>> 
>>> I made a little test and I believe it is just impossible.
>> Oracle cannot do
>>> spatial query for the transformed geometries (centroids)
>> returned by the first
>>> SELECT. It cannot find where the centroids are because they
>> are not indexed
>>> and the index of the primary table does not help. You can
>> for sure use
>>> centroids by pushing them into a new table or column and
>> creating spatial
>>> index. There may be some SQL/Oracle trick to do it in some
>> other way, PL/SQL
>>> people are doing amazing things but I am not aware of any.
>>> 
>>> This is the query I used for testing and Oracle messages.
>> I am not at all
>>> sure if this query is OK, but it may be.
>>> 
>>> SQL> select the_geom from (SELECT
>> SDO_GEOM.SDO_CENTROID(tt.geoloc, 2) AS
>>> the_geom, id
>>>      from test_table tt)
>>>      WHERE SDO_ANYINTERACT(the_geom, SDO_GEOMETRY(2003, 2393,
>>>      
>> NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(3289429,7024458,
>>> 3288529,7026558)))
>>>      = 'TRUE';
>>> 
>>> select the_geom from (SELECT
>> SDO_GEOM.SDO_CENTROID(gp.geoloc, 2) AS the_geom,
>>> id from gis_polygon gp
>>> *
>>> VIRHE rivillä 1:  # error on row 1:
>>> ORA-13226: interface not supported without a spatial index
>>> ORA-06512: at "MDSYS.MD", line 1723
>>> ORA-06512: at "MDSYS.MDERR", line 8
>>> ORA-06512: at "MDSYS.SDO_3GL", line 70
>>> ORA-06512: at "MDSYS.SDO_3GL", line 221
>>> 
>>> -Jukka Rahkonen-
>>> 
>>> 
>>>> -----Alkuperäinen viesti-----
>>>> Lähettäjä: mapserver-users-bounces at lists.osgeo.org
>>>> [mailto:mapserver-users-bounces at lists.osgeo.org] Puolesta
>>>> Julian Wiersbitzki
>>>> Lähetetty: 27. lokakuuta 2010 12:52
>>>> Vastaanottaja: mapserver-users at lists.osgeo.org
>>>> Aihe: [mapserver-users] Best Practices for the Spatial Index
>>>> with OracleDB 11gR2
>>>> 
>>>> Hi List!
>>>> 
>>>> I am new here in this list and with mapserver, so please tell
>>>> me if my question is already a faq.
>>>> 
>>>> We have mapserver 5.6.3 in use together with an Oracle DB
>>>> 11gR2 Standard Edtion. All works well so far and configuring
>>>> layer with the mapfile is no problem. But we have two layers
>>>> which use SDO-Function in the SELECT Statement, e.g.:
>>>> 
>>>>    DATA "the_geom from (SELECT
>>>> SDO_GEOM.SDO_CENTROID(geometrie, 2) AS the_geom, id FROM
>>>> ereignis) USING SRID 31468"
>>>> or
>>>>    DATA "the_geom from (SELECT
>>>> SDO_AGGR_UNION(SDOAGGRTYPE(the_geom, 0.005)) AS the_geom,
>>>> bezirksinspektion AS label FROM stadtbezirk GROUP BY
>>>> bezirksinspektion) USING SRID 31468"
>>>> 
>>>> If I want to fetch one of these layers the mapserver tells me
>>>> there is no spatial index. Normally we have spatial indexes
>>>> on all our tables with SDO_GEOMETRY columns, e.g.:
>>>> 
>>>> INSERT INTO user_sdo_geom_metadata VALUES (
>>>>   UPPER('ereignis'),
>>>>   UPPER('geometrie'),
>>>>   MDSYS.SDO_DIM_ARRAY(
>>>>     MDSYS.SDO_DIM_ELEMENT('X',4452550.887,4479484.083,0.001),
>>>>     
>>>> 
>> MDSYS.SDO_DIM_ELEMENT('Y',5324953.397000001,5345694.052999999,0.001)),
>>>>     31468);
>>>> CREATE INDEX ereignis_geom_idx ON ereignis(geometrie)
>>>> INDEXTYPE IS MDSYS.SPATIAL_INDEX;
>>>> 
>>>> But how do I create a spatial index on SDO-functions in the
>>>> database? Can I even do that? The documentations only
>>>> describes spatial index on SDO_GEOMETRY columns, but not on
>>>> spatial functions.
>>>> 
>>>> What are the best practices for this kind of layers?
>>>> 
>>>> TIA, Julian
>>>> 
>>>> ______________________________________________________________
>>>> 
>>>> Hamburger Berater Team GmbH       Handelsregister: HRB 31629
>>>> Stadthausbrücke 3 (Fleethof)      Amtsgericht Hamburg
>>>> 20355 Hamburg                     Geschäftsführer:
>>>> Fon:  040/369779-43               Ilse Habermann
>>>> Fax:  040/369779-99               Hans-Joachim Habermann
>>>> Mail: mailto:jw at hbt.de          Daniel Hoffmann
>>>> ______________________________________________________________
>>>> 
>>>> Internet:                       http://www.hbt.de
>>>> http://www.geofox.de            http://www.geonetwatch.de
>>>> ______________________________________________________________
>>>> 
>>>> DIN EN ISO 9001:2000: DQS-Zertifikat Nr.5206 QM
>>>> Diese Email wurde mit ständig aktualisierten Virenscannern geprüft
>>>> ______________________________________________________________
>>>> 
>>>> Diese Email einschließlich eventuell angehängter Dateien
>>>> enthält vertrauliche Informationen und ist ausschließlich
>>>> für den bezeichneten Adressaten bestimmt. Wenn Sie nicht
>>>> der richtige Adressat oder dessen Vertreter sind, verständigen
>>>> Sie bitte sofort den Absender und löschen Sie dann diese Email
>>>> und eventuell angehängte Dateien. Vielen Dank!
>>>> Beachten Sie: Jede Form der Nutzung, Vervielfältigung oder
>>>> Weitergabe des Inhalts fehlgeleiteter Emails ist unzulässig.
>>>> ______________________________________________________________
>>>> 
>>>> This e-mail is intended only for the above addressee.
>>>> It may contain privileged information. If you are not the
>>>> addressee you must not copy, distribute, disclose or use any
>>>> of the information in it. If you have received it in error
>>>> please delete it and immediately notify the sender.
>>>> ______________________________________________________________
>>>> _______________________________________________
>>>> mapserver-users mailing list
>>>> mapserver-users at lists.osgeo.org
>>>> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>>>> 
>>> _______________________________________________
>>> mapserver-users mailing list
>>> mapserver-users at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>> 
>> 
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users