[Geoserver-devel] OracleNG: Incorrect Bounding Box

GeoServer GeoServer Developer
1

Hi,

I am in the process of creating a feature based on an Oracle Table that is properly indexed and has an entry in USER_SDO_GEOM_METADATA.

Clicking the Generate button to get the extents returned incorrect values.

I checked the MBR with the following code and got the same values as OracleNG…

select sdo_aggr_mbr(geom_location)

from

141.8076171875, -16.8662109375, 148.2353515625, -15.568359375

Obviously OracleNG is using SDO_AGGR_MBR as there’s no way else to get the strange results, which

aren’t so strange when you read the Oracle docs – “…by contrast, the SDO_AGGR_MBR function can operate on

subsets of rows.”

I then checked using the following statement and got the correct values

SELECT SDO_TUNE.EXTENT_OF(‘TABLENAME’, ‘GEOM_LOCATION’)

FROM DUAL;

137.6845703125, -29.197265625, 152.1845703125, -10.810546875

However, SDO_TUNE.EXTENT_OF only works with 2 dimensional geometries.

Now even though we are only working with 2 dims here, if someone is using

an LRS dim or Z, it won’t work.

This provides a reasonable approximation without much of a performance hit …

SELECT SDO_AGGR_MBR(ch)

from (select SDO_AGGR_CONVEXHULL(SDOAGGRTYPE(geom_location, 0.001)) ch

FROM );

… but you would have to look up the tolerance from USER_SDO_GEOM_METADATA.

The best solution is just to get the diminfo from USER_SDO_GEOM_METADATA. The current

way of generating the data is flawed in any case for all databases, not just Oracle. I

know I have put in the diminfo for my entire area of concern even though it will be

two years before the data is fully populated. I would suspect most people would put in

the layer size in the diminfo up front and that the data may not reflect the actual bounds

required.

On the other hand, if we are not going to fix this, then there needs to be a clear warning about

generated extents possibly (probably) being inaccurate if adding an Oracle feature, right next to

the ‘Generate’ button.

Cheers

Stuart

2

Stuart A. Mitchell ha scritto:

Hi,

I am in the process of creating a feature based on an Oracle Table that is properly indexed and has an entry in USER_SDO_GEOM_METADATA.

Clicking the Generate button to get the extents returned incorrect values.

I checked the MBR with the following code and got the same values as OracleNG…

select sdo_aggr_mbr(geom_location)
from <tablename>
141.8076171875, -16.8662109375, 148.2353515625, -15.568359375

Obviously OracleNG is using SDO_AGGR_MBR as there’s no way else to get the strange results, which
aren’t so strange when you read the Oracle docs – “…by contrast, the SDO_AGGR_MBR function can operate on
subsets of rows.”

Sorry but I don't understand, what is strange in those results?
Using SDO_AGGR_MBR is the only general way to get bounds as you
have noticed. If it does not return the proper results it
seems to just be a bug with Oracle DBMS to me...

I then checked using the following statement and got the correct values
SELECT SDO_TUNE.EXTENT_OF('TABLENAME', 'GEOM_LOCATION')
      FROM DUAL;

137.6845703125, -29.197265625, 152.1845703125, -10.810546875

However, SDO_TUNE.EXTENT_OF only works with 2 dimensional geometries.

Now even though we are only working with 2 dims here, if someone is using

an LRS dim or Z, it won’t work.

Not only 2 dimensional, the documentation says not to use it for geographic coordinates (at least the 10G if my memory serves me
right) which is your case. Maybe you did not specify the SRS
when you created the spatial index?
Moreover, it won't work when you are using a filter to get the
bounds of a selection of features.

This provides a reasonable approximation without much of a performance hit ...

SELECT SDO_AGGR_MBR(ch)
from (select SDO_AGGR_CONVEXHULL(SDOAGGRTYPE(geom_location, 0.001)) ch
FROM <tablename>);

Hum.... what is that doing? Computing the bbox of the convex hull should
be slower in theory:
- nlog(n) time to compute the convex hull
- a linear scan of the convex hull vertices to get the mbr of it
vs
- a linear scan of the original geometry to get the mbr...

... but you would have to look up the tolerance from USER_SDO_GEOM_METADATA.

Eh, and you have to assume the user inserted a reasonable tolerance
value... not sure I trust that.
If this is an optimization, it's probably better to enabled it using
a configuration flag of some kind.
Other people have asked that we use the bbox stored in some other
metadata table (something that an admin has to enter properly, again).
Maybe it's what you're talking about below.

The best solution is just to get the diminfo from USER_SDO_GEOM_METADATA. The current
way of generating the data is flawed in any case for all databases, not just Oracle.

Really, how so?

I know I have put in the diminfo for my entire area of concern even though it will be
two years before the data is fully populated. I would suspect most people would put in
the layer size in the diminfo up front and that the data may not reflect the actual bounds
required.

My experience tells me people do not properly populate metadata, period
:wink:
(you should see how many checks we have in the postgis data store to
make it work with real world data where most of the time metadata
is missing or wrong).

On the other hand, if we are not going to fix this, then there needs to be a clear warning about

generated extents possibly (probably) being inaccurate if adding an Oracle feature, right next to

the ‘Generate’ button.

And finally I have to add that this requires a custom optimization in the code for the case where you are not filtering rows, as the call
is FeatureSource.getBounds(Query). Justin?

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

3

Stuart A. Mitchell ha scritto:
...

Obviously OracleNG is using SDO_AGGR_MBR as there's no way else to

get

the strange results, which
aren't so strange when you read the Oracle docs - "...by contrast,

the

SDO_AGGR_MBR function can operate on
subsets of rows."

Sorry but I don't understand, what is strange in those results?
Using SDO_AGGR_MBR is the only general way to get bounds as you
have noticed. If it does not return the proper results it
seems to just be a bug with Oracle DBMS to me...

It's documented as I wrote from the Oracle doco - "...by contrast, the SDO_AGGR_MBR function can operate on subsets of rows.

Hem... I read that sentence as "you can have it work on the rows
matched by your WHERE clause" as opposed to SDO_TUNE.EXTENT_OF
which does not allow for that.
You believe it's only using a subset of the rows returned by
the WHERE clause to compute the bounds?

I then checked using the following statement and got the correct

values

SELECT SDO_TUNE.EXTENT_OF('TABLENAME', 'GEOM_LOCATION')
      FROM DUAL;

137.6845703125, -29.197265625, 152.1845703125, -10.810546875
However, SDO_TUNE.EXTENT_OF only works with 2 dimensional geometries.

Now even though we are only working with 2 dims here, if someone is

using

an LRS dim or Z, it won't work.

Not only 2 dimensional, the documentation says not to use it for geographic coordinates (at least the 10G if my memory serves me
right) which is your case.

It works fine despite the doco.

When I tried last time it was even slower than SDO_AGGR_MBR thought
(in the case of geographic data). How big is the dataset you're
using for your tests right now?

Maybe you did not specify the SRS
when you created the spatial index?

No mistake.

As in, the SRS was properly specified?

This provides a reasonable approximation without much of a

performance

hit ...
SELECT SDO_AGGR_MBR(ch)
from (select SDO_AGGR_CONVEXHULL(SDOAGGRTYPE(geom_location, 0.001))

ch

FROM <tablename>);

Hum.... what is that doing? Computing the bbox of the convex hull

should

be slower in theory:
- nlog(n) time to compute the convex hull
- a linear scan of the convex hull vertices to get the mbr of it
vs
- a linear scan of the original geometry to get the mbr...

... but you would have to look up the tolerance from
USER_SDO_GEOM_METADATA.

I wasn't proposing this as a solution but I wasn't clear about that.

Oh ok, then I'm confused. What were you proposing then?

My experience tells me people do not properly populate metadata, period
:wink:
(you should see how many checks we have in the postgis data store to
make it work with real world data where most of the time metadata
is missing or wrong).

Garbage in - garbage out. Just throw exceptions if people are too lazy
to do their job properly.

The datastore is there to be used for various applications, not only
for GeoServer. The API says the bounds returned must be the current
ones, and must match an eventual filter.
I'm perfectly ok with the metadata optimization, but only as long
as the user _asked_ for it before.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

4

Andrea Aime ha scritto:
...

The datastore is there to be used for various applications, not only
for GeoServer. The API says the bounds returned must be the current
ones, and must match an eventual filter.
I'm perfectly ok with the metadata optimization, but only as long
as the user _asked_ for it before.

Oh, forgot to add this reference:
http://jira.codehaus.org/browse/GEOT-1998

It's about computing bounds with Oracle (and how that is not fully
possible with the current jdbc-ng API) :slight_smile:
Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

5

Layed down a new 10g database and got incorrect results with SDO_AGGR_MBR.

Layed down a new 10gR2 database and got correct results.

Andrea was right. There is a bug with SDO_AGGR_MBR (in 10.1).

Cheers
Stuart
--
View this message in context: http://www.nabble.com/OracleNG%3A-Incorrect-Bounding-Box-tp20957209p21008071.html
Sent from the GeoServer - Dev mailing list archive at Nabble.com.