Miles Jordan ha scritto: > Andrea Aime wrote: >> Senthil ha scritto: >>> Hi all, >>> >>> The following issue came across, when i configured SQLView with >>> complex SQL statement with combining 2 tables and SDO_AGGR_UNION >>> function to group related records. It could be some thing >>> specific to Oracle and surely nothing to do with SQLView. From >>> the logs, i found that SQL generated as below during layer >>> pre-view time: >>> >>> SELECT count(*) AS gt_result_ FROM (SELECT * FROM (SELECT * FROM >>> (SELECT b.group_Id as grp_Id, >>> SDO_AGGR_UNION(SDOAGGRTYPE(a.LOCATION, 0.005)) as grp_location >>> FROM GIS.PART_TABLE a, GIS.MAP_TABLE b WHERE a.part_Id=b.part_Id >>> Group by b.group_Id) *VTABLE *WHERE *SDO_FILTER*(grp_location, >>> >>> MDSYS.SDO_GEOMETRY(2003,2193,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), >>> MDSYS.SDO_ORDINATE_ARRAY(- >>> 154768.4241380731,4257259.84132448,3240947.5 >>> 44118471,6614572.798694815)), 'mask=anyinteract >>> querytype=WINDOW') = 'TRUE' ) WHERE ROWNUM <= 1000000) >>> gt_limited_; >>> >>> The virtual table created and SDO_FILTER operation called to find >>> intersecting polygons. Similar issue could possible with complex >>> feature with application schema too.. >>> >>> 1. As seen in above SQL, it may possible to result multipolygon >>> when used SDO_AGGR_UNION operation, Any issue on using SDO_FILTER >>> with polygon and multipolygon together? I searched in Oracle >>> documentation and could get any concerns on that.. Any experience >>> or idea on this? >> Oracle filters do not work against non indexed columns. So they are >> unusable against manufactured geometries. I know of no workaround. >> >> >>> 2. As per Oracle documentation: SDO_FILTER(geometry1, geometry2, >>> param); geometry1 Specifies a geometry column in a table. *The >>> column must be spatially indexed*. Data type is SDO_GEOMETRY. >> Exactly. Your geometry is manufactured in the subquery, so it does >> not have an index. This is a Oracle specific problem, PostGIS does >> not suffer from such limitations afaik. > > Perhaps you could try to use SDO_GEOM.RELATE? It doesn't use table > indexes (can actually compare manufactured geometries).
Ah ha, interesting! That would actually require a patch to the Oracle data store. We'd need to store into the geometry attribute user data section a flag stating whether a certain geometry attribute is indexed or not, and then use that knowledge in the filter encoder to decide which operator to use. Senthil, if you're interested in making a patch, the Oracle dialect already contains some custom handling of geodetic srid, see OracleDialect.postCreateAttribute and then OracleFilterToSQL.visitBinarySpatialOperator in the GeoTools Oracle module (modules/plugin/jdbc/jdbc-oracle) Hmmm.. views make this quite a bit harder, as there is now way to tell if the geometry has been manufactured or not, and I'd definitely would like to avoid adding a oracle specific set of flags to the view definitions (why oh why Oracle is always the troublemaker...). If the view has data inside we could try to run a spatial query against it and see if it works, if it does not, mark the geometry as something that is not supported by a spatial index. Other ideas? Cheers Andrea -- Andrea Aime OpenGeo - http://opengeo.org Expert service straight from the developers. ------------------------------------------------------------------------------ This SF.net email is sponsored by Sprint What will you do first with EVO, the first 4G phone? Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first _______________________________________________ Geoserver-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geoserver-devel
