2

I am using MS4w v 3.0.3 (with Java mapscript) with Oracle Spatial 11g. Can we add layers to MapServer mapfile from oracle database views? If yes, is there any specific way to create views with spatial column for mapserver mapfile?

I am getting the following error when I try to add a layer from a view : Exception in thread "http-bio-8080-exec-1" java.lang.UnknownError: msDrawMap(): Image handling error. Failed to draw layer named 'amplifier'.;msOracleSpatialLayerWhichShapes(): OracleSpatial error. Error: ORA-13226: interface not supported without a spatial index.

Regards, Shiva

0

You can disable the spatial filtering (which is what requires the spatial index) by using (See the USING NONE at the end of the query)

DATA "the_geom from (SELECT SDO_GEOM.SDO_CENTROID(geometrie, 2) AS the_geom, id FROM ereignis) USING SRID 31468 NONE"

This means that MapServer will query everything without applying a BBox filter. If you do want to use a filter, you will need to create a spatial index on the base table and use it in your DATA statement. See the MapServer documentation on Oracle for more information

  • Hello Julien, Thanx for replying. Using the USING NONE in the DATA statement did get the spatial data from the database view. But since there is no spatial indexing, the layer data loading takes lot of time. The reason I am creating a view is I am translating a polygon geom to point location in another spatial table. – Shiva Nov 21 '13 at 7:10
  • Following is the query I am using to create a view : – Shiva Nov 21 '13 at 7:12
  • select AMP.XFM_ID as XFM_ID,SDO_UTIL.AffineTransforms(SSL.GEOM,'TRUE',AMP.OGC_GEOMETRY.SDO_POINT.x-SDO_GEOM.SDO_CENTROID(SSL.GEOM,m.diminfo).SDO_POINT.x,AMP.OGC_GEOMETRY.SDO_POINT.y-SDO_GEOM.SDO_CENTROID(SSL.GEOM,m.diminfo).SDO_POINT.y,0.0,'FALSE',NULL,0.0,0.0,0.0,'TRUE',SDO_GEOM.SDO_CENTROID(SSL.GEOM,m.diminfo),NULL,AMP.MS_ANGLE,-1,'FALSE',0.0,0.0,0.0,0.0,0.0,0.0,'FALSE',NULL,NULL,-1,'FALSE',NULL,NULL) as OGC_GEOMETRY from SPL_SYMBOL_LIB SSL inner join AMPLIFIER AMP on AMP.EQUIP_ID=SSL.EQUIP_ID inner join user_sdo_geom_metadata m on m.table_name='AMPLIFIER' where AMP.EQUIP_ID='PLECOM'; – Shiva Nov 21 '13 at 7:12
  • Both the spatial tables SPL_SYMBOL_LIB (contains the polygon geom) and AMPLIFIER (the table containing large no. of points to which the polygon is translated) are indexed. I tried even putting the above query in the DATA statement. Following is the mapfile excerpt : – Shiva Nov 21 '13 at 7:30
  • LAYER NAME amplifier TYPE POLYGON CONNECTIONTYPE oraclespatial CONNECTION "*******/*****@253_orcl" DATA "OGC_GEOMETRY FROM ( select XFM_ID, SDO_UTIL.AffineTransforms (SSL.GEOM, 'TRUE', AMP.OGC_GEOMETRY.SDO_POINT.x-SDO_GEOM.SDO_CENTROID(SSL.GEOM,m.diminfo).SDO_POINT.x , AMP.OGC_GEOMETRY.SDO_POINT.y-SDO_GEOM.SDO_CENTROID(SSL.GEOM,m.diminfo).SDO_POINT.y, 0.0, 'FALSE',NULL,0.0,0.0,0.0,'TRUE', SDO_GEOM.SDO_CENTROID(SSL.GEOM,m.diminfo), NULL, AMP.MS_ANGLE, – Shiva Nov 21 '13 at 7:33
0

That simply means that the view is based on a table that does not have any spatial index defined. Without a spatial index, no spatial queries are possible, including the simple FILTER queries used for panning and zooming.

To do that you will first need to setup the proper "metadata" with bound, tolerance, SRID, ... then create the spatial index on the table.

  • Hello Albert, Thanx for replying. The base tables on which the view is based are spatially indexed. – Shiva Nov 21 '13 at 9:47
  • Ah OK. I see what happens. I assumed (wrongly) that the view was a simple view on a base table that included the unchanged geometry from that base table. But your view invokes a transformation function on the base geometry, and that then requires an index to be based on that function. However that may not be too efficient since Oracle will need to invoke the function each time the geometry is fetched. But the bigger issue is the complexity of the function that includes input from multiple tables. I am not sure Oracle allows an index on such a function. – Albert Godfrind Nov 21 '13 at 16:16
  • Can you clarify what you want to achieve here ? My understanding is this: you have a table called AMPLIFIER that describes various equipment with their point location and an orientation angle. You need to show a symbol for each amplifier, taken from table SPL_SYMBOL_LIB, properly oriented. For that you use Oracle's affine transform function to place the proper symbol on the location of each amplifier, properly oriented. Is this correct ? – Albert Godfrind Nov 21 '13 at 16:30
  • If so, then my preferred approach would be to expand the AMPLIFIER table with an additional geometry column that will receive the result of the function call. You can then index that column and use it for your mapping. The flip side is that you will need additional tooling (a trigger on the AMPLIFIER table) to automatically (re)compute that geometry from the function call each time a new AMPLIFIER is created, or if one gets moved to a new location, or if the ANGLE changes. You may also need a trigger on the SPL_SYMBOL_LIB table in case a symbol changes. – Albert Godfrind Nov 21 '13 at 16:34

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.