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

Wed Oct 27 08:43:35 EDT 2010

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