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" > <[email protected]> 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ä: [email protected] > >> [mailto:[email protected]] Puolesta > >> Julian Wiersbitzki > >> Lähetetty: 27. lokakuuta 2010 12:52 > >> Vastaanottaja: [email protected] > >> 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:[email protected] 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 > >> [email protected] > >> http://lists.osgeo.org/mailman/listinfo/mapserver-users > >> > > _______________________________________________ > > mapserver-users mailing list > > [email protected] > > http://lists.osgeo.org/mailman/listinfo/mapserver-users > > _______________________________________________ mapserver-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/mapserver-users
