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.544118471,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. 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
