Select aggregated Bounding Box of multiple Features using ArcSDE SQL

[G_L_]

I would like to calculate the aggregated bounding box of multiple features using OGC Simple Feature SQL or SQL API of ArcSDE. The following statement will only query the bounding box of each feature:

select ST_Boundary (shape) from polygon_table where country like '%usa%'

What I needed is one bounding box aggregated over all features, resulting to the condition in the where clause.

[VinceAngelo]

A google on "ST_Geometry aggregation" found the Oracle function sde.st_aggr_union.
I'm not sure about the aggregation options on other RDBMS, but min and max could
probably be used to good effect when working with envelopes

If you're looking for an envelope, then you'd be best off placing an sde.st_envelope
around the intermediate products, and then again after union (ST_Boundary only
retrieves the Clementini boundary of polygons, not the envelope).

Note that the union of large multipolygon extents which cross the antemeridian (like
the US and Russia) will generate envelopes which span the globe.

- V

[G_L_]

Just realized that we use SDO_GEOMETRY and not ST_GEOMETRY. So SDE.ST_AGGR_UNION is not working. Any other suggestions?

The Query

SELECT SDE.ST_asText(SDE.ST_AGGR_UNION(SHAPE)) UNION_SHAPE FROM POLYGON_TABLE

will result in

ORA-06553: PLS-306: wrong number or types of arguments in call to 'ST_AGGR_UNION'

[G_L_]

Well - it seems one have to install st_shapelib.dll for this. So I copied st_shapelib.dll to the oracle host and changed extproc.ora to:

SET EXTPROC_DLLS=ONLY:C:\\oracle\\extproc_libs\\st_shapelib.dll

Then I created library in oracle via SQL:

CREATE OR REPLACE LIBRARY st_shapelib AS 'c:\oracle\extproc_libs\st_shapelib.dll';

After this, I changed the select statement so it will create an ST_GEOMETRY from an SDO_GEOMETRY using sdo_geometry.get_wkb:

SELECT SDE.ST_AGGR_UNION(sde.st_geomfromwkb(sdo_geometry.get_wkb(shape),2056)) FROM POLYGON_TABLE

But now I get the following error:

SQL Error: ORA-28579: network error during callback from external procedure agent
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 620
ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 2245
ORA-06512: at "SDE.STGEOM_AGGR_UNION", line 1
28579. 00000 -  "network error during callback from external procedure agent"
*Cause:    An internal network error occurred while trying to execute a
           callback to the Oracle server from the user's 3GL routine.
*Action:   Contact Oracle customer support.

Any Hints, what is wrong now?

[VinceAngelo · Accepted solution]

Time to do a little background:
What version of Oracle are you using? [A.B.C.D.E notation]
What version of ArcSDE are you using? [X.Y spZ]
Did you follow the complete listener configuration procedure?

Esri ST_GEOMETRY functions only work on Esri SDE.ST_GEOMETRY custom types.
Oracle's topology rules are less restrictive, and passing an invalid shape through
well-known binary will cause your cascade to fail.  It's also very expensive to union
all geometries if you only want the extent of their unions.

Finally, if you're using SDO_GEOMETRY, why not ditch the OGC SQL specification
requirement and just use the SDO_GEOMETRY function, SDO_AGGR_MBR?

- V

[G_L_]

When using SDO_AGGR_MBR or any other Oracle Spatial function- don't I need a License for Oracle Locator or Oracle Spatial? Since our custumer has already Licenses for ArcSDE we would like to stick to ArcSDE.

We are using:

- ArcSDE 10.2
- Oracle Database 11.2.0.1.0 - 64bit Production
- Windows 2008 R2 Enterprise
- SDO_Geometry Geometry

Update

I followed the instructions for 10.2. Since we use Oracle 11g, I updated only extproc.ora, and did not configure any listeners:
- http://resources.arcgis.com/en/help/main/10.2/index.html#//002n00000091000000
- http://resources.arcgis.com/en/help/main/10.2/index.html#//002n000000t9000000

[VinceAngelo]

Yes, it seems that SDO_AGGR_MBR use requires a Locator license (at least, it did
in 2007).

How often are you going to execute this query?  At it stands, you've got a hideously
expensive solution (computationally), and it doesn't seem partcularly reliable.

Are you planning to replace your geometry column with SDE.ST_GEOMETRY?  That
would reduce the compute cost, but only you know how expensive a database
re-implementation will cost.

- V

[G_L_]

Thx a lot. We can't use ST_GEOMETRY, since we access the data also via Java using Hibernate. For this we use Hibernate-Spatial, which provides support for SDO_GEOMETRY, but not ST_GEOMETRY. We couldn't find any Hibernate Provider for ST_GEOMETRY at all. We actually had to spend some time for evaluation how to integrate Java/ArcSDE/Oracle, since there is not much documentation about best practises. One approach would have been using ST_GEOMETRY along with Java SDE API, but ESRI says this API won't be supported after 10.2.

Using Oracle "SDO_AGGR_MBR":

After googling I found out the following: Oracle Locator is part of Oracle 11g Standard Edition. Oracle Spatial is part of the Enterprise Edition. The function SDO_AGGR_MBR is included in Oracle Locator! http://docs.oracle.com/cd/E18283_01/appdev.112/e11830/sdo_locator.htm. So we can rely on SDO_AGGR_MBR.

The operation is a background job, so performance is not relevant. But we will now use SDO_AGGR_MBR. At the moment I will also try to install the Oracle 11.2.0.2 Patch Set, to see if SDE functions work as well http://support.esri.com/en/knowledgebase/techarticles/detail/38823.

Update
I initially wrote that SDO_AGGR_MBR is not included in Oracle Locator, but I mixed it up with SDO_AGGR_UNION.

Update 2
Puhh.. the patch is 4.6 GB in 7 files!! I leave it, and don't install it...

[MarcoBoeringa]

Thx a lot. We can't use ST_GEOMETRY, since we access the data also via Java using Hibernate. For this we use Hibernate-Spatial, which provides support for SDO_GEOMETRY, but not ST_GEOMETRY. We couldn't find any Hibernate Provider for ST_GEOMETRY at all. We actually had to spend some time for evaluation how to integrate Java/ArcSDE/Oracle, since there is not much documentation about best practises. One approach would have been using ST_GEOMETRY along with Java SDE API, but ESRI says this API won't be supported after 10.2.

Maybe not entirely relevant to this thread with its requirement for Apache, but while the ArcSDE Java API may be a dead end after 10.2, the ESRI Geometry API for Java certainly isn't. It is a current Open Source GitHub project and may offer functionality you need or could use:

ESRI Geometry API for Java

Also have a look here possibly for Open Source code samples:

http://esri.github.io/

[G_L_]

ESRI Geometry API for Java

As far as I see, this API is only for spatial operations (in memory) and handling ESRI Geometry Types. This is actually not the problem in Java, you can perfectly do it with Geotools. The difficult part I see is, to persist geometry types using common Java Object Relational Mapper like Hibernate or EclipseLink.

With Geotools I could calculate the bounding box in memory by using the provided geometry operations http://docs.geotools.org/latest/userguide/library/jts/operation.html. In certain cases that's fine, but normally you want those cost intensive operations on the data handled by the DB I guess.