[mapserver-users] Best Practices for the Spatial Index with
OracleDB 11gR2
Rahkonen Jukka
Jukka.Rahkonen at mmmtike.fi
Wed Oct 27 10:08:42 EDT 2010
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
>
>
More information about the mapserver-users
mailing list