Andrea Aime ha scritto: > 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
err... "there is no way" 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
