[Mapserver-users] Oracle Spatial Extents

[Frans Knibbe]

Vladimir,

Does it take 29 seconds for all ~2500 extents? What version of Oracle 
Spatial are you using? The function you are using is not supported for 
geodetic data in Oracle 9 but it is in Oracle 10.  The Oracle metalink 
article 241661.1 explains why the MBR functions were not supported in 
version 9. Are you sure you want to use the MBRs?

I don't know if the MBR functions use the R-tree index (which stores 
MBRs), but I can imagine the algorithm for calculating a MBR is more 
complex for geodetic data than for projected data, which might explain 
the long duration. Maybe you could make a copy of your table and project 
it, then see how long it takes to execute the same query.

Greetings,

Frans**

Vladimir Guzmán wrote:

> Hello.
> I need to get the extents of a geometry in Oracle Spatial.
> I'm using the following query:
>
> MIN(SDO_GEOM.SDO_MIN_MBR_ORDINATE(GEOMETRY_TABLE.ORA_GEOMETRY, 
> m.diminfo,1)) as XMIN,
> MIN(SDO_GEOM.SDO_MIN_MBR_ORDINATE(GEOMETRY_TABLE.ORA_GEOMETRY, 
> m.diminfo,2)) as YMIN,
> MAX(SDO_GEOM.SDO_MAX_MBR_ORDINATE(GEOMETRY_TABLE.ORA_GEOMETRY, 
> m.diminfo,1)) as XMAX,
> MAX(SDO_GEOM.SDO_MAX_MBR_ORDINATE(GEOMETRY_TABLE.ORA_GEOMETRY, 
> m.diminfo,2)) as YMAX
> FROM GEOMETRY_TABLE, user_sdo_geom_metadata m
> WHERE m.table_name = 'GEOMETRY_TABLE' AND m.column_name = 'ORA_GEOMETRY';
>
> But Oracle takes 29 seconds (!) to get the extents.
> Is this a normal behavior?
> The geometry is indexed using:
>
> CREATE INDEX GEOMETRY_TABLE_SPATIAL_IDX
> ON GEOMETRY_TABLE(ORA_GEOMETRY)
> INDEXTYPE IS MDSYS.SPATIAL_INDEX;
>
> The geometry has ~2500 records.
> The geometry's SRID is 1 (Sinusoidal WGS84).
> Am I using the wrong query?
> Is there another way?
>
> Anyone has experienced a related problem?
>
> Thanks in advance for your help.
>