1

I have a table map_building_data and I create view all_incidents_unions

CREATE OR REPLACE FORCE VIEW ALL_INCIDENTS_UNIONS
(
   ORA_GEOMETRY
)
AS
SELECT sdo_aggr_union (MDSYS.sdoaggrtype (ora_geometry, 0.005))
             ora_geometry
       FROM (  SELECT sdo_aggr_union (MDSYS.sdoaggrtype (ora_geometry, 0.005))
                         ora_geometry
                 FROM (  SELECT sdo_aggr_union (
                                   MDSYS.sdoaggrtype (ora_geometry, 0.005))
                                   ora_geometry
                           FROM (  SELECT sdo_aggr_union (
                                             MDSYS.sdoaggrtype (ora_geometry, 0.005))
                                             ora_geometry
                                     FROM (  SELECT sdo_aggr_union (
                                                       MDSYS.sdoaggrtype (ora_geometry,
                                                                          0.005))
                                                       ora_geometry
                                               FROM (  SELECT sdo_aggr_union (
                                                                 MDSYS.sdoaggrtype (
                                                                    ora_geometry,
                                                                    0.005))
                                                                 ora_geometry
                                                         FROM (  SELECT SDO_AGGR_UNION (
                                                                           SDOAGGRTYPE (
                                                                              COM.GEOM,
                                                                              0.005))
                                                                           ora_geometry
                                                                   FROM MAP_BUILDING_DATA COM
                                                                  WHERE ID =9307433
                                                               GROUP BY MOD (ROWNUM, 128))
                                                     GROUP BY MOD (ROWNUM, 64))
                                           GROUP BY MOD (ROWNUM, 32))
                                 GROUP BY MOD (ROWNUM, 16))
                       GROUP BY MOD (ROWNUM, 8))
             GROUP BY MOD (ROWNUM, 4))
   GROUP BY MOD (ROWNUM, 2));

After create view I am inserting data on USER_SDO_GEOM_METADATA

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME,COLUMN_NAME,DIMINFO,SRID)VALUES ('ALL_INCIDENTS_UNIONS','ORA_GEOMETRY',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',8255000,8754700,0.005),MDSYS.SDO_DIM_ELEMENT('Y',4261000,4614700,0.005)),'2400000');

After this when I create layer on geoserver and want see result, this return error

Rendering process failed java.io.IOExceptionORA-13226: interface not supported without a spatial index ORA-06512: at "MDSYS.MD", line 1723 ORA-06512: at "MDSYS.MDERR", line 8 ORA-06512: at "MDSYS.SDO_3GL", line 1224

please help ..........

  • Could you edit the title to describe your real problem? – user30184 Sep 23 '17 at 15:54
2

As the error message says "interface not supported without a spatial index" - so you need to add a spatial index to your view.

I don't use Oracle if I can help it but I think you will need to make a materialised view for this.

  • view don't having spatial index, but I add INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME,COLUMN_NAME,DIMINFO,SRID)VALUES ('ALL_INCIDENTS_UNIONS','ORA_GEOMETRY',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',8255000,8754700,0.005),MDSYS.SDO_DIM_ELEMENT('Y',4261000,4614700,0.005)),'2400000'); – TIKO Sep 23 '17 at 9:10
  • problem is sdo_aggr_union – TIKO Sep 23 '17 at 9:12
  • 1
    if it needs a spatial index to work then that is indeed the problem until you create a spatial index. Or you could switch to postgis – Ian Turton Sep 23 '17 at 10:36
  • As you can see the message comes from Oracle. It can't handle the spatial SQL query that GeoServer sends (probably with bbox filter). It is understandable that Oracle can't use spatial index because the geometry is created on-the-fly as an aggregate. Materialize the view, create spatial index, and it should work. I am not sure how well PostGIS could handle such aggregates with spatial queries. – user30184 Sep 23 '17 at 12:22
  • 1
    As for this business of spatial index and views, the solution is to define a function-based index, i.e. a spatial index on the result of the aggregation function. However that function is too complicated in your case. A simple aggregation would be possible. – Albert Godfrind Sep 25 '17 at 10:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.