[Mapserver-users] Oracle Spatial Extents
Fernando S.
simon at inf.univali.br
Tue Jul 27 07:16:48 EDT 2004
This is a multi-part message in MIME format.
--------------000103090608040706080001
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Hi Vladimir,
Here I tested this query's and with this results:
(This functions return the same result, the extend)
1 -
select to_char(sysdate, 'HH:MI:SS') from dual;
select MIN(SDO_GEOM.SDO_MIN_MBR_ORDINATE(h.coordenada, m.diminfo,1)) as
XMIN,
MIN(SDO_GEOM.SDO_MIN_MBR_ORDINATE(h.coordenada, m.diminfo,2)) as YMIN,
MAX(SDO_GEOM.SDO_MAX_MBR_ORDINATE(h.coordenada, m.diminfo,1)) as XMAX,
MAX(SDO_GEOM.SDO_MAX_MBR_ORDINATE(h.coordenada, m.diminfo,2)) as YMAX
FROM HIDROGRAFIA_TOTAL h, user_sdo_geom_metadata m
WHERE m.table_name = 'HIDROGRAFIA_TOTAL' AND m.column_name = 'COORDENADA';
select to_char(sysdate, 'HH:MI:SS') from dual;
select count (*) from hidrografia_total;
2 -
(The same table of 1)
select to_char(sysdate, 'HH:MI:SS') from dual;
select (SDO_AGGR_MBR(h.coordenada)) FROM HIDROGRAFIA_TOTAL h,
user_sdo_geom_metadata m WHERE m.table_name = 'HIDROGRAFIA_TOTAL' AND
m.column_name = 'COORDENADA';
select to_char(sysdate, 'HH:MI:SS') from dual;
3 -
(The same table of 1, The Oracle don't recomend to use this after 9i
version)
select to_char(sysdate, 'HH:MI:SS') from dual;
select SDO_TUNE.EXTENT_OF('hidrografia_total', 'coordenada') from dual;
select to_char(sysdate, 'HH:MI:SS') from dual;
And the results:
1 -
TO_CHAR(
--------
06:41:40
XMIN YMIN XMAX YMAX
---------- ---------- ---------- ----------
-53.839252 -29.348521 -48.393637 -25.95654
TO_CHAR(
--------
06:42:25
COUNT(*)
----------
6499
2 -
TO_CHAR(
--------
06:43:43
(SDO_AGGR_MBR(H.COORDENADA))(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z),
SDO_ELEM_I
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3),
SDO_ORDINATE_ARR
AY(-53.839252, -29.348521, -48.393637, -25.95654))
TO_CHAR(
--------
06:44:10
3 -
TO_CHAR(
--------
06:45:23
SDO_TUNE.EXTENT_OF('HIDROGRAFIA_TOTAL','COORDENADA')(SDO_GTYPE,
SDO_SRID, SDO_PO
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3),
SDO_ORDINATE_ARR
AY(-53.839252, -29.348521, -48.393637, -25.95654))
TO_CHAR(
--------
06:45:30
Here I used this script to create the index:
/insert into mdsys.user_sdo_geom_metadata values
('hidrografia_total','coordenada',mdsys.sdo_dim_array(mdsys.sdo_dim_element('x',-180,180,0.00000005),
mdsys.sdo_dim_element('y',-90,90,0.00000005)),null);
create index hidrografia_total_spatial_idx on
rioitajai.hidrografia_total(coordenada) indextype is mdsys.spatial_index
parameters ('sdo_level = 4');
commit;
/
I tested with a table without SRID defined, I run this with one
Celeron 2.4, 256MB of Ram running Oracle 10G.
You can compair how I created the index and how you defined your index.
I don't know if I solved your problem. If you need more help I can
help you.
Sorry, yesterday I forgot sent the e-mail to list.
-------------------------------------------------------------------------------------
Fernando Simon - simon at inf.univali.br
Database Manager, Mapserver and Oracle Spatial developer
http://g10.cttmar.univali.br
-------------------------------------------------------------------------------------
Vladimir Guzmán wrote:
> Hi, Frans.
>
> It takes 29 secs to get the full extents of the geometry, which has
> ~2500 elements.
> The oracle gets all the ~2500 extents, and then gets the minimum and
> maximum value for X and Y, so I obtain the full extents this way.
> I'm using Oracle Spatial 9i.
> My data is not geodetic (SRID=1=>Sinusoidal WGS84)
> I don't understand very well the difference between geodetic and
> non-geodetic data, anyway, at the beginning I had my data in SRID=8307
> and I wasn't able to use the MBR functions, so I switched to SRID=1.
>
> And trying the Fernando Simon's suggestion
> (http://mapserver.gis.umn.edu/data2/wilma/mapserver-users/0407/msg00767.html),
> I had no luck.
> The level with SDO_TUNE.ESTIMATE_TILING_LEVEL was 6, but the query
> didn't get better. Besides, after indexing this way I wasn't able to
> draw the geometry outside the extents.
>
> My question now is: Is there another way to get the extents of a
> geometry?
> I also tried SDO_AGGR_MBR with the same results (too much time
> executing the query).
>
> The point is that I use a lot this functionality in Postgis
> (extent(the_geom)) with very large datasets and Postgis does it very
> fast.
>
> Any help will be very appreciated.
>
> Thanks a lot in advance,
>
>
--------------000103090608040706080001
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Vladimir,<br>
Here I tested this query's and with this results:<br>
(This functions return the same result, the extend)<br>
<br>
1 - <br>
select to_char(sysdate, 'HH:MI:SS') from dual;<br>
select MIN(SDO_GEOM.SDO_MIN_MBR_ORDINATE(h.coordenada, m.diminfo,1)) as
XMIN,<br>
MIN(SDO_GEOM.SDO_MIN_MBR_ORDINATE(h.coordenada, m.diminfo,2)) as YMIN,<br>
MAX(SDO_GEOM.SDO_MAX_MBR_ORDINATE(h.coordenada, m.diminfo,1)) as XMAX,<br>
MAX(SDO_GEOM.SDO_MAX_MBR_ORDINATE(h.coordenada, m.diminfo,2)) as YMAX<br>
FROM HIDROGRAFIA_TOTAL h, user_sdo_geom_metadata m<br>
WHERE m.table_name = 'HIDROGRAFIA_TOTAL' AND m.column_name =
'COORDENADA';<br>
select to_char(sysdate, 'HH:MI:SS') from dual;<br>
select count (*) from hidrografia_total;<br>
<br>
2 -<br>
(The same table of 1)<br>
select to_char(sysdate, 'HH:MI:SS') from dual;<br>
select (SDO_AGGR_MBR(h.coordenada)) FROM HIDROGRAFIA_TOTAL h,
user_sdo_geom_metadata m WHERE m.table_name = 'HIDROGRAFIA_TOTAL' AND
m.column_name = 'COORDENADA';<br>
select to_char(sysdate, 'HH:MI:SS') from dual;<br>
<br>
3 -<br>
(The same table of 1, The Oracle don't recomend to use this after 9i
version)<br>
select to_char(sysdate, 'HH:MI:SS') from dual;<br>
select SDO_TUNE.EXTENT_OF('hidrografia_total', 'coordenada') from dual;<br>
select to_char(sysdate, 'HH:MI:SS') from dual;<br>
<br>
And the results:<br>
1 - <br>
TO_CHAR(<br>
--------<br>
06:41:40<br>
<br>
XMIN YMIN XMAX YMAX<br>
---------- ---------- ---------- ----------<br>
-53.839252 -29.348521 -48.393637 -25.95654<br>
<br>
TO_CHAR(<br>
--------<br>
06:42:25<br>
<br>
COUNT(*)<br>
----------<br>
6499<br>
<br>
<br>
2 -<br>
TO_CHAR(<br>
--------<br>
06:43:43<br>
<br>
(SDO_AGGR_MBR(H.COORDENADA))(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z),
SDO_ELEM_I<br>
--------------------------------------------------------------------------------<br>
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3),
SDO_ORDINATE_ARR<br>
AY(-53.839252, -29.348521, -48.393637, -25.95654))<br>
<br>
TO_CHAR(<br>
--------<br>
06:44:10<br>
<br>
<br>
3 -<br>
TO_CHAR(<br>
--------<br>
06:45:23<br>
<br>
SDO_TUNE.EXTENT_OF('HIDROGRAFIA_TOTAL','COORDENADA')(SDO_GTYPE,
SDO_SRID, SDO_PO<br>
--------------------------------------------------------------------------------<br>
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3),
SDO_ORDINATE_ARR<br>
AY(-53.839252, -29.348521, -48.393637, -25.95654))<br>
<br>
TO_CHAR(<br>
--------<br>
06:45:30<br>
<br>
<br>
Here I used this script to create the index:<br>
<i>insert into mdsys.user_sdo_geom_metadata values
('hidrografia_total','coordenada',mdsys.sdo_dim_array(mdsys.sdo_dim_element('x',-180,180,0.00000005),
mdsys.sdo_dim_element('y',-90,90,0.00000005)),null);<br>
create index hidrografia_total_spatial_idx on
rioitajai.hidrografia_total(coordenada) indextype is
mdsys.spatial_index parameters ('sdo_level = 4');<br>
commit;<br>
</i><br>
I tested with a table without SRID defined, I run this with one
Celeron 2.4, 256MB of Ram running Oracle 10G.<br>
You can compair how I created the index and how you defined your index.<br>
I don't know if I solved your problem. If you need more help I can
help you.<br>
<br>
<br>
Sorry, yesterday I forgot sent the e-mail to list.<br>
<br>
<br>
-------------------------------------------------------------------------------------<br>
Fernando Simon - <a class="moz-txt-link-abbreviated"
href="mailto:simon at inf.univali.br">simon at inf.univali.br</a><br>
Database Manager, Mapserver and Oracle Spatial developer<br>
<a class="moz-txt-link-freetext" href="http://g10.cttmar.univali.br">http://g10.cttmar.univali.br</a><br>
-------------------------------------------------------------------------------------<br>
<br>
Vladimir Guzmán wrote:
<blockquote cite="mid410561F2.3010304 at main-task.com" type="cite">Hi,
Frans. <br>
<br>
It takes 29 secs to get the full extents of the geometry, which has
~2500 elements. <br>
The oracle gets all the ~2500 extents, and then gets the minimum and
maximum value for X and Y, so I obtain the full extents this way. <br>
I'm using Oracle Spatial 9i. <br>
My data is not geodetic (SRID=1=>Sinusoidal WGS84) <br>
I don't understand very well the difference between geodetic and
non-geodetic data, anyway, at the beginning I had my data in SRID=8307
and I wasn't able to use the MBR functions, so I switched to SRID=1. <br>
<br>
And trying the Fernando Simon's suggestion
(<a class="moz-txt-link-freetext"
href="http://mapserver.gis.umn.edu/data2/wilma/mapserver-users/0407/msg00767.html">http://mapserver.gis.umn.edu/data2/wilma/mapserver-users/0407/msg00767.html</a>),
I had no luck. <br>
The level with SDO_TUNE.ESTIMATE_TILING_LEVEL was 6, but the query
didn't get better. Besides, after indexing this way I wasn't able to
draw the geometry outside the extents. <br>
<br>
My question now is: Is there another way to get the extents of a
geometry? <br>
I also tried SDO_AGGR_MBR with the same results (too much time
executing the query). <br>
<br>
The point is that I use a lot this functionality in Postgis
(extent(the_geom)) with very large datasets and Postgis does it very
fast. <br>
<br>
Any help will be very appreciated. <br>
<br>
Thanks a lot in advance, <br>
<br>
<br>
</blockquote>
</body>
</html>
--------------000103090608040706080001--
More information about the mapserver-users
mailing list