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). The catch is that it is actually comparing geometries, not MBRs as SDO_FILTER will do, so it's slower (but more accurate). It is confusing that Oracle supply 3 very similar functions. You may find some more helpful info at http://milesjordan.com/index.php/2009/11/10/sdo_geom-relate-vs-sdo_filter-vs-sdo_relate Regards, Miles ___________________________________________________________________________ Australian Antarctic Division - Commonwealth of Australia IMPORTANT: This transmission is intended for the addressee only. If you are not the intended recipient, you are notified that use or dissemination of this communication is strictly prohibited by Commonwealth law. If you have received this transmission in error, please notify the sender immediately by e-mail or by telephoning +61 3 6232 3209 and DELETE the message. Visit our web site at http://www.antarctica.gov.au/ ___________________________________________________________________________ ------------------------------------------------------------------------------ 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
