GeoServer
  1. GeoServer
  2. GEOS-1896

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

    Details

    • Type: Bug Bug
    • Status: Closed Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Oracle, WMS
    • Labels:
      None
    • Environment:
      Oracle 11.1.0.0.0
      Geoserver 1.6.3
    • Number of attachments :
      0

      Description

      I have an Oracle table with approximately 90M rather simple polygons in it.

      I have a feature type created in Geoserver which responds to WMS GetImage requests and can draw these polygons with no problem. I had to enter the bounding box into the UI myself to create the feature type, as the generate would not work for the same reason the GetFeatureInfo does not work.

      When I attempt a GetFeatureInfo call to WMS, the request takes too long and the browser times out. Geoserver is trying to calculate the bounding box for all the feature types I have configured. In calculating this bounding box, it is calling SDO_AGGR_MBR against the table with 90M polygons which will not return for several hours.

      WFS GetCapabilities calls work very quickly and are evidently not subject to Geoserver recalculating a bounding box.

      If this is not possible to accomplish due to whatever reason, I would gladly accept any workarounds you would like to suggest.

        Activity

        Hide
        Bruce E. Thelen added a comment -

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

        Show
        Bruce E. Thelen added a comment - The title and third paragraph should have read GetCapabilities, sorry for the mix up.
        Hide
        Bruce E. Thelen added a comment -

        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.

        Show
        Bruce E. Thelen added a comment - 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.
        Hide
        Bruce E. Thelen added a comment -

        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

        Show
        Bruce E. Thelen added a comment - 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
        Hide
        Andrea Aime added a comment -

        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).

        Show
        Andrea Aime added a comment - 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).
        Hide
        Bruce E. Thelen added a comment -

        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

        Show
        Bruce E. Thelen added a comment - 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
        Hide
        Bruce E. Thelen added a comment -

        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

        Show
        Bruce E. Thelen added a comment - 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
        Hide
        Andrea Aime added a comment -

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

        Show
        Andrea Aime added a comment - Yep, that's indeed how it's working right now

          People

          • Assignee:
            Andrea Aime
            Reporter:
            Bruce E. Thelen
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: