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…
-
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?
-
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.
As long as above SQL using virtual table for SDO_FILTER… Any issue with that?
What i found with created SQLView Layer that it could not load LayerPreview or BBox query… But, with getFeature call, i was able to see returning results as no SDO_FILTER operation… Is it any thing specific to Oracle only or any similar experience?
Appreciate your responses with possible reasons which help me to concentrate on find fixes/workaround sooner.
Thanks
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.
Thanks Andrea for quick response…
On Thu, Jul 15, 2010 at 8:46 PM, Andrea Aime <aaime@anonymised.com> 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.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…
- 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.
- 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.
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/
___________________________________________________________________________
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.
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.
Hi Andrea & Miles,
Thanks for the emails… Today i was checking on application schema codes on how complex feature works… I’m thinking about check SDO_FILTER part during weekend…
Any thought on this JIRA issue http://jira.codehaus.org/browse/GEOT-2062
It looks based on earlier code base and not related to OracleNG… but, interestingly seen SDO_RELATE was used earlier. nut not SDO_GEOM.RELATE.
Now also issue only for BBox query as it is only check with SDO_FILTER… Can we make use loose bbox parameter if it is available? (Note still i have not checked on code in Oracle connector part in GeoTools)
Thanks
regards
Senthil
On Fri, Jul 16, 2010 at 7:56 PM, Andrea Aime <aaime@anonymised.com> wrote:
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…
- 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.
- 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
Geoserver-devel@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel
Senthil ha scritto:
Hi Andrea & Miles,
Thanks for the emails.. Today i was checking on application schema codes on how complex feature works... I'm thinking about check SDO_FILTER part during weekend..
Any thought on this JIRA issue http://jira.codehaus.org/browse/GEOT-2062
Ah, got no feedback from the user. Time to close it, the old oracle store is gone the way of the Dodo.
It looks based on earlier code base and not related to OracleNG.. but, interestingly seen SDO_RELATE was used earlier. nut not SDO_GEOM.RELATE.
Afaik SDO_RELATE also needs a spatial index.
Cheers
Andrea
--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.
Hi Andrea
I had gone through the Oracle connector codes and fine to do changes to that. In meantime did some manual testing for earlier sql statement with SDO_GEOM.RELATE. It worked without any issues.
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.GROUP_TABLE b WHERE a.PART_ID=b.PART_ID Group by b.GRP_ID ) VTABLE WHERE SDO_GEOM.RELATE(GRP_LOCATION, ‘anyinteract’, 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)), 0.005 ) = ‘TRUE’ ) WHERE ROWNUM <= 1000000) gt_limited_;
For above command used following format of SDO_GEOM.RELATE:
SDO_GEOM.RELATE(
geom1 IN MDSYS.SDO_GEOMETRY,
mask IN VARCHAR2,
geom2 IN MDSYS.SDO_GEOMETRY,
tolerance IN NUMBER
) RETURN VARCHAR2;
To implement this change as patch to Oracle connector, i wish to get the advice on that. Can we consider another configuration for SDO_GEOM.RELATE such currently used loose box field for SDO_FILTER or any other suggestions?
Regards
Senthil
On Fri, Jul 16, 2010 at 9:10 PM, Andrea Aime <aaime@anonymised.com> wrote:
Senthil ha scritto:
Hi Andrea & Miles,
Thanks for the emails… Today i was checking on application schema codes on how complex feature works… I’m thinking about check SDO_FILTER part during weekend…
Any thought on this JIRA issue http://jira.codehaus.org/browse/GEOT-2062
Ah, got no feedback from the user. Time to close it, the old oracle store is gone the way of the Dodo.
It looks based on earlier code base and not related to OracleNG… but, interestingly seen SDO_RELATE was used earlier. nut not SDO_GEOM.RELATE.
Afaik SDO_RELATE also needs a spatial index.
Cheers
Andrea
–
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.
Senthil wrote:
Hi Andrea
I had gone through the Oracle connector codes and fine to do changes to that. In meantime did some manual testing for earlier sql statement with SDO_GEOM.RELATE. It worked without any issues.
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.GROUP_TABLE b WHERE a.PART_ID=b.PART_ID Group by b.GRP_ID ) *VTABLE WHERE SDO_GEOM.RELATE*(GRP_LOCATION, 'anyinteract', 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)), 0.005 ) = 'TRUE' ) WHERE ROWNUM <= 1000000) gt_limited_;
For above command used following format of SDO_GEOM.RELATE:
SDO_GEOM.RELATE( geom1 IN MDSYS.SDO_GEOMETRY, mask IN VARCHAR2, geom2 IN MDSYS.SDO_GEOMETRY, tolerance IN NUMBER ) RETURN VARCHAR2;
To implement this change as patch to Oracle connector, i wish to get the advice on that. Can we consider another configuration for SDO_GEOM.RELATE such currently used loose box field for SDO_FILTER or any other suggestions?
It seems to me having just a global flag for it would be a major limitation. As far as I know SDO_GEOM.RELATE never uses spatial indexes so it should be a last resort solution to be used only when the
geometries are manufactured).
Even when using views I guess only a small fraction of the geometries
used are really manufactured, the others might be coming from joined
tables, but that should not prevent the usage of SDO_RELATE.
So I think it should be a per column flag. The easiest way to set it
up is probably to try and run a test with spatial query using SDO_RELATE
(using probably the bbox of the first geometry in the result set) and
fall back on the SDO_GEOM.RELATE only if that query fails with a SQL
exception
Plus, that function has to be used with attention as Oracle Locator does
not support it:
http://download.oracle.com/docs/cd/E14072_01/appdev.112/e11830/sdo_locator.htm
Cheers
Andrea
Hi Andrea,
Yes. I agree with your concerns and i too found that Oracle Locator did not support SDO_GEOM.RELATE. The Oracle developer guide mentioned about performance consideration on using SDO_GEOM.RELATE. But, it is the only option available for manufactured geometric fields used in layers.
Your suggestion on using flag for field in view would be better option too. Initially i will make changes and do some testing on this area. Any schedule timeline for GeoServer next release? (with SQLView feature)
Thanks
regards
Senthil
On Tue, Jul 20, 2010 at 12:28 AM, Andrea Aime <aaime@…1501…> wrote:
Senthil wrote:
Hi Andrea
I had gone through the Oracle connector codes and fine to do changes to that. In meantime did some manual testing for earlier sql statement with SDO_GEOM.RELATE. It worked without any issues.
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.GROUP_TABLE b WHERE a.PART_ID=b.PART_ID Group by b.GRP_ID ) VTABLE WHERE SDO_GEOM.RELATE(GRP_LOCATION, ‘anyinteract’, 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)), 0.005 ) = ‘TRUE’ ) WHERE ROWNUM <= 1000000) gt_limited_;
For above command used following format of SDO_GEOM.RELATE:
SDO_GEOM.RELATE( geom1 IN MDSYS.SDO_GEOMETRY, mask IN VARCHAR2, geom2 IN MDSYS.SDO_GEOMETRY, tolerance IN NUMBER ) RETURN VARCHAR2;
To implement this change as patch to Oracle connector, i wish to get the advice on that. Can we consider another configuration for SDO_GEOM.RELATE such currently used loose box field for SDO_FILTER or any other suggestions?
It seems to me having just a global flag for it would be a major limitation. As far as I know SDO_GEOM.RELATE never uses spatial indexes so it should be a last resort solution to be used only when the
geometries are manufactured).
Even when using views I guess only a small fraction of the geometries
used are really manufactured, the others might be coming from joined
tables, but that should not prevent the usage of SDO_RELATE.
So I think it should be a per column flag. The easiest way to set it
up is probably to try and run a test with spatial query using SDO_RELATE
(using probably the bbox of the first geometry in the result set) and
fall back on the SDO_GEOM.RELATE only if that query fails with a SQL
exception
Plus, that function has to be used with attention as Oracle Locator does
not support it:
http://download.oracle.com/docs/cd/E14072_01/appdev.112/e11830/sdo_locator.htm
Cheers
Andrea
Senthil wrote:
Hi Andrea,
Yes. I agree with your concerns and i too found that Oracle Locator did not support SDO_GEOM.RELATE. The Oracle developer guide mentioned about performance consideration on using SDO_GEOM.RELATE. But, it is the only option available for manufactured geometric fields used in layers.
Your suggestion on using flag for field in view would be better option too. Initially i will make changes and do some testing on this area. Any schedule timeline for GeoServer next release? (with SQLView feature)
We haven't even scheduled a beta so far (all developers seem to be too
busy)
Cheers
Andrea
Andrea Aime wrote:
Senthil wrote:
Hi Andrea
I had gone through the Oracle connector codes and fine to do
changes
to that. In meantime did some manual testing for earlier sql statement
with SDO_GEOM.RELATE. It worked without any issues.
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.GROUP_TABLE b WHERE
a.PART_ID=b.PART_ID Group by b.GRP_ID ) *VTABLE WHERE
SDO_GEOM.RELATE*(GRP_LOCATION, 'anyinteract',
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)), 0.005 ) = 'TRUE' ) WHERE ROWNUM <=
1000000) gt_limited_;
For above command used following format of SDO_GEOM.RELATE:
SDO_GEOM.RELATE(
geom1 IN MDSYS.SDO_GEOMETRY,
mask IN VARCHAR2,
geom2 IN MDSYS.SDO_GEOMETRY,
tolerance IN NUMBER
) RETURN VARCHAR2;
To implement this change as patch to Oracle connector, i wish to get
the advice on that. Can we consider another configuration for
SDO_GEOM.RELATE such currently used loose box field for SDO_FILTER or
any other suggestions?
It seems to me having just a global flag for it would be a major
limitation. As far as I know SDO_GEOM.RELATE never uses spatial
indexes so it should be a last resort solution to be used only when
the geometries are manufactured).
Even when using views I guess only a small fraction of the geometries
used are really manufactured, the others might be coming from joined
tables, but that should not prevent the usage of SDO_RELATE.
So I think it should be a per column flag. The easiest way to set it
up is probably to try and run a test with spatial query using
SDO_RELATE (using probably the bbox of the first geometry in the
result set) and fall back on the SDO_GEOM.RELATE only if that query
fails with a SQL exception
Plus, that function has to be used with attention as Oracle Locator does
not support it:
http://download.oracle.com/docs/cd/E14072_01/appdev.112/e11830/sdo_loc
ator.htm
Cheers
Andrea
I agree 100%. I didn't actually realise that it wasn't a part of locator (I don't use locator but that makes sense). Besides, it is always going to be faster to use a function that relies on indexes and/or spatial metadata over one that compares actual data, but using it *if it works and is available* as a last resort sounds good.
I'm pretty sure Oracle would need to create an index for manufactured geometries anyway but that process takes time too.
The absolute best reference for this stuff is this book - I highly recommend it if you don't already have it: http://books.google.com.au/books?id=-ZDAcf-h3VgC&pg=PA318&lpg=PA318&dq=sdo_geom.relate&source=bl&ots=HP2jtPFENL&sig=0BJRfoUBFBCVM_vTkWn14EKB-Ns&hl=en&ei=qv1ETPywBIPEvQP3tuzHDA&sa=X&oi=book_result&ct=result&resnum=4&ved=0CCcQ6AEwAw#v=onepage&q=sdo_geom.relate&f=false
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/
___________________________________________________________________________