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

Wed Oct 27 07:56:47 PDT 2010

Thanx a lot folks! That solves my problems!

Julian

> -----Original Message-----
> From: mapserver-users-bounces at lists.osgeo.org 
> [mailto:mapserver-users-bounces at lists.osgeo.org] On Behalf Of 
> Smith, Michael ERDC-CRREL-NH
> Sent: Wednesday, October 27, 2010 4:20 PM
> To: Rahkonen Jukka; mapserver-users at lists.osgeo.org
> Subject: Re: [mapserver-users] Best Practices for the Spatial 
> Index withOracleDB 11gR2
> 
> 
> 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
> 
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
> 