[Geoserver-users] calculating MBR

GeoServer GeoServer User
1

Hello everybody,

is there a method to record somewhere in geoserver the result of the following query executend on a Oracle DB ?

SELECT SDO_AGGR_MBR(SHAPE) as boundingbox from DBMCOLTUREAGRICOLE

Unfortunately, it is not possible to create a materialized view to store this data in Oracle DB.

Thanks

Mauro

–
Mauro Bagazzi
SardegnaIT srl
Viale Trieste 186, Cagliari
Telefono : +39-0706064365
E-mail: <mbagazzi@anonymised.com>
Skype: mauro.bagazzi

2

On Wed, Feb 20, 2013 at 5:10 PM, mbagazzi@anonymised.com <maurobagazzilavoro@anonymised.com> wrote:

Hello everybody,

is there a method to record somewhere in geoserver the result of the following query executend on a Oracle DB ?

SELECT SDO_AGGR_MBR(SHAPE) as boundingbox from DBMCOLTUREAGRICOLE

What do you mean record? Cache?

The request is normally run only when you configure the layer and ask for teh native bbox to be computed,
and can take a lot of time indeed.
If you have a view indeed there is no faster way, but you can specify the bounds manually
if you know them

Cheers
Andrea

–

==
Our support, Your Success! Visit http://opensdi.geo-solutions.it for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

3

2013/2/20 Andrea Aime <andrea.aime@anonymised.com>:

On Wed, Feb 20, 2013 at 5:10 PM, mbagazzi@anonymised.com
<maurobagazzilavoro@anonymised.com> wrote:

Hello everybody,

is there a method to record somewhere in geoserver the result of the
following query executend on a Oracle DB ?

SELECT SDO_AGGR_MBR(SHAPE) as boundingbox from DBMCOLTUREAGRICOLE

What do you mean record? Cache?

The request is normally run only when you configure the layer and ask for
teh native bbox to be computed,
and can take a lot of time indeed.
If you have a view indeed there is no faster way, but you can specify the
bounds manually
if you know them

I thougth that values were extracted from all_sdo_geom_metadata , but
there should be a good reason not to do that.
Would it be a problem having an extent not equal to real extent of all
features in the featuretype?

As usual thanks a lot for you patience and expertise

Stefano

4

I supposed that this request was executed just during the configuration of the layer.
Anyway, analyzing my DB today I found this query among the others and I wonder because I am sure none had modified the configuration recalculating the mbr.
Sometimes, it happens also for others layers that, I am sure, none can modify/recalculate.
How is it possibile for you?

Bye

Mauro

2013/2/20 Andrea Aime <andrea.aime@anonymised.com>

On Wed, Feb 20, 2013 at 5:10 PM, mbagazzi@anonymised.com <maurobagazzilavoro@anonymised.com> wrote:

Hello everybody,

is there a method to record somewhere in geoserver the result of the following query executend on a Oracle DB ?

SELECT SDO_AGGR_MBR(SHAPE) as boundingbox from DBMCOLTUREAGRICOLE

What do you mean record? Cache?

The request is normally run only when you configure the layer and ask for teh native bbox to be computed,
and can take a lot of time indeed.
If you have a view indeed there is no faster way, but you can specify the bounds manually
if you know them

Cheers
Andrea

–

==
Our support, Your Success! Visit http://opensdi.geo-solutions.it for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

–
Mauro Bagazzi
SardegnaIT srl
Viale Trieste 186, Cagliari
Telefono : +39-0706064365
E-mail: <mbagazzi@anonymised.com>
Skype: mauro.bagazzi

5

http://sig.cmparks.net/cmp-ms-90x122.pngStephen V. Mather
GIS Manager
(216) 635-3243 (Work)
clevelandmetroparks.com

6

Yet,

it is a heavy job to do this calculation because it does a full table scan instead to use the spatial index in Oracle.

2013/2/20 Andrea Aime <andrea.aime@anonymised.com>

On Wed, Feb 20, 2013 at 5:10 PM, mbagazzi@anonymised.com <maurobagazzilavoro@anonymised.com> wrote:

Hello everybody,

is there a method to record somewhere in geoserver the result of the following query executend on a Oracle DB ?

SELECT SDO_AGGR_MBR(SHAPE) as boundingbox from DBMCOLTUREAGRICOLE

What do you mean record? Cache?

The request is normally run only when you configure the layer and ask for teh native bbox to be computed,
and can take a lot of time indeed.
If you have a view indeed there is no faster way, but you can specify the bounds manually
if you know them

Cheers
Andrea

–

==
Our support, Your Success! Visit http://opensdi.geo-solutions.it for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

–
Mauro Bagazzi
SardegnaIT srl
Viale Trieste 186, Cagliari
Telefono : +39-0706064365
E-mail: <mbagazzi@anonymised.com>
Skype: mauro.bagazzi

7

Geoserver should run the query using the Oracle function. Maybe in a future release. That would take under a second when adding a layer.

… As said before, run it on Oracle and get the values (4) manually …

Date: Wed, 20 Feb 2013 17:48:57 +0100
From: maurobagazzilavoro@anonymised.com
To: andrea.aime@anonymised.com…
CC: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] calculating MBR

Yet,

it is a heavy job to do this calculation because it does a full table scan instead to use the spatial index in Oracle.

2013/2/20 Andrea Aime <andrea.aime@anonymised.com>

On Wed, Feb 20, 2013 at 5:10 PM, mbagazzi@anonymised.com <maurobagazzilavoro@anonymised.com> wrote:

Hello everybody,

is there a method to record somewhere in geoserver the result of the following query executend on a Oracle DB ?

SELECT SDO_AGGR_MBR(SHAPE) as boundingbox from DBMCOLTUREAGRICOLE

What do you mean record? Cache?

The request is normally run only when you configure the layer and ask for teh native bbox to be computed,
and can take a lot of time indeed.
If you have a view indeed there is no faster way, but you can specify the bounds manually
if you know them

Cheers
Andrea

–

==
Our support, Your Success! Visit http://opensdi.geo-solutions.it for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

–
Mauro Bagazzi
SardegnaIT srl
Viale Trieste 186, Cagliari
Telefono : +39-0706064365
E-mail: <mbagazzi@anonymised.com>
Skype: mauro.bagazzi

------------------------------------------------------------------------------ Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users

8

Stephen and Mauro,

you might want to have a look into this discussion, which Jonathan Moules
started (Start of Jan).

http://osgeo-org.1560.n6.nabble.com/SDO-TUNE-EXTENT-OF-Oracle-td5025640.html#a5026061

Cheers

Christian

-----
____________________________

Dr Christian Maul
Project Manager

Information Services Branch
Department of Sustainability and Environment
Level13, Marland House, 570 Bourke Street
Melbourne 3000

PO Box 500, East Melbourne Vic 3002

Telephone: +61-3-8636 2325
Telefax: +61-3-8636 2813
--
View this message in context: http://osgeo-org.1560.n6.nabble.com/calculating-MBR-tp5035568p5035680.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

9

On Thu, Feb 21, 2013 at 1:59 AM, cmaul <Christian.Maul@anonymised.com> wrote:

Stephen and Mauro,

you might want to have a look into this discussion, which Jonathan Moules
started (Start of Jan).

http://osgeo-org.1560.n6.nabble.com/SDO-TUNE-EXTENT-OF-Oracle-td5025640.html#a5026061

Trying to give a cumulative answer on the various mails piling up on this thread:

  • The metadata tables are not inspected because admins don’t normally fill them with useful
    information. If someone want to provide a patch that allows to look into them when a certain
    configuration flag is enabled, that would be welcomed
  • The current code tries to use SDO_TUNE.EXTENT_OF before falling back on
    envelope aggregation with SDO_AGG_MBR, that works only for tables (not for views afaik),
    and there was a recent fix on the usage of SDO_TUNE.EXTENT_OF when trying to access
    a table that is not in the user own schema

If you have suggestions on how to improve things let’s discuss them and then open
a improvement request on jira.codehaus.org, waiting for someone to sponsor its development
or some developer that wants to spend some of his spare time on the Oracle connector

Cheers
Andrea

–

==
Our support, Your Success! Visit http://opensdi.geo-solutions.it for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it