WMS GetCapabilities operation using Oracle Data Store with large number of items in table fails

The title and third paragraph should have read GetCapabilities, sorry for the mix up.

Does anyone have any suggestions for workarounds for this? It is really killing my ability to use anything that reads the GetCapabilities document against my GeoServer.

Thanks.

I have written a patch which I am using locally in order to circumvent the need to run SDO_AGGR_MBR in order to generate a GetCapabilities doc. In the getEnvelope method of the OracleDataStore class, instead of using SDO_AGGR_MBR I am simply reading all_sdo_geom_metadata in order to determine what the DBA/table owner has designated to be the bounds of this feature.

Possible drawback to this approach are:
-my query could possibly get confused if there are two spatial tables with the same name but different owners thus would return the bounds for the wrong feature - there is no real unique key provided, I can really only query based on the table name as that is all the info I have.
-the bounds the DBA/table owner has set could be non-current or simply the entire world bounding box as I find this is the case quite often.

I would happily provide this patch if these known limitations are acceptable, but if not I will just continue to patch my own installation with each release.

Thanks,
Bruce

I think this could be useful as a datastore configuration option, false by default, that when enabled looks into the metadata tables. Can you attach a patch?
I won't fix the current Oracle datastore, but try to apply it to the one that's in the works to replace it (which suffers from the same issue).

Andrea,

Here is the query to construct an optimized rectangle from the data in all_sdo_geom_metadata. The table (or actually view thus the reason SDO_TUNE.EXTENT_OF does not work for me) that I'm using here is called "PARCEL_VW" so you would just put the table or view name in its place. I can also provide a proper patch to the trunk's .java file if you would prefer but I'm assuming you just want the query to put into the code. My DBA informs me this will work for up to a 4D data set.

select (case when x4 is not null then
MDSYS.SDO_GEOMETRY(gtype,srid,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(hh.x1,hh.x2,hh.x3,hh.x4,hh.y1,hh.y2,hh.y3,hh.y4))
when x3 is not null then
MDSYS.SDO_GEOMETRY(gtype,srid,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(hh.x1,hh.x2,hh.x3,hh.y1,hh.y2,hh.y3))
when x2 is not null then
MDSYS.SDO_GEOMETRY(gtype,srid,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(hh.x1,hh.x2,hh.y1,hh.y2))
else
MDSYS.SDO_GEOMETRY(gtype,srid,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(hh.x1,hh.y1)) end) bbox From ( select num_dim||'003' gtype,g.srid,
max(decode(g.dim,1,g.sdo_lb,null)) x1,
max(decode(g.dim,2,g.sdo_lb,null)) x2,
max(decode(g.dim,3,g.sdo_lb,null)) x3,
max(decode(g.dim,4,g.sdo_lb,null)) x4,
max(decode(g.dim,1,g.sdo_ub,null)) y1,
max(decode(g.dim,2,g.sdo_ub,null)) y2,
max(decode(g.dim,3,g.sdo_ub,null)) y3,
max(decode(g.dim,4,g.sdo_ub,null)) y4
from
(SELECT rownum dim, t.sdo_lb, t.sdo_ub, u.srid
FROM all_sdo_geom_metadata u,
TABLE(u.diminfo) t
where u.table_name='PARCEL_VW') g,
(Select count num_dim
from all_sdo_geom_metadata u, TABLE(u.diminfo) t
where u.table_name='PARCEL_VW') d group by num_dim||'003',g.srid) hh

Andrea,

It seems the WMS GetCapabilities requests under GeoServer 1.7.4 (and possibly sooner, but this is the version I am using) are now simply returning the bounds that are set for the feature type upon creation of the feature type and are no longer attempting to calculate the bounds via database query. If this indeed the way the requests are being handled, feel free to close this bug as I no longer see the same behavior that prompted me to open the bug.

Thanks,
Bruce

Yep, that's indeed how it's working right now