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
>