[Mapserver-users] Oracle Spatial Extents

[FERNANDO SIMON]

Hi Vladimir,
   I had never this problem, but I'll try to help you. Well, I belive that the 
problem can be with the index.    
   When you created the index before you executed this query:
SELECT SDO_TUNE.ESTIMATE_TILING_LEVEL(’GEOMETRY TABLE’, ’GEOMETRY COLUMN’, 
10000, ’ALL_GID_EXTENT’);
   This query return one value that you can use when you create index. One 
example, if this query return 8 you use this to create index:
CREATE INDEX cola_spatial_idx ON cola_markets(shape) INDEXTYPE IS 
MDSYS.SPATIAL_INDEX PARAMETERS(’SDO_LEVEL = 8’);
   After execute this you created a quadtree index, and this index can help you 
to reduce the time spent to execute the query.
   You can found more information aboute the quadtree index in Oracle Spatial 
User Guide, but to resume this index decomposing the coordinate space in a 
regular hierarchical manner. The range of coordinates, the coordinate space, is 
viewed as a rectangle. 
   But if you use the last version of databse, 10G, the Oracle don't recomend 
to use this quadtree index. In the 10G user guide: "The use of spatial quadtree 
indexes is discouraged. You are strongly encouraged to use R-tree indexing for 
spatial indexes....". But to test the performance you can execute the same 
steps to compair the both result.
   Here I already used the 8 and 9 versions. Now I'm testing the 10G version, I 
already defined both r-tree and quadtree indexes in the 10G version. But I not 
compair the results yet.
   I don't know if I solved your problem. If you need more help I cant help you.
   Thank's

---------------------------------------------------------
Fernando Simon - simon at inf.univali.br
Database Manager, Mpaserver and Oracle Spatial developer
http://g10.cttmar.univali.br
---------------------------------------------------------

Citando Vladimir Guzmán <bigfoot at main-task.com>:

> 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.
> 
> -- 
> Vladimir Ilich Guzmán R.
> ------------------------
> http://maintask.com 
> 
> _______________________________________________
> Mapserver-users mailing list
> Mapserver-users at lists.gis.umn.edu
> http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
> 




------------------------------------------------
Este e-mail foi enviado pelo webmail do curso de
       Ciência da Computação - UNIVALI