Strange behaviour with ST_INTERSECTS

StefanoIacovella · ‎02-05-2014

Dear all,

I am experiencing a strange behaviour with ST_INTERSECTS.
The environment is as follow:
ArcSDE 10.1 SP 1 on Oracle 11.2.0.3 64-bit
Red Hat server

ST_LIBRARIES is correctly deployed and configured.

When I run the following query I got an error:

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 6 09:51:59 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SELECT
    IBOW.SEAGRASS_RL13.OBJECTID,
    IBOW.SEAGRASS_RL13.RLSTATUS
FROM
    IBOW.SEAGRASS_RL13,
    IBOW.ITALYF2_REV1_AU
WHERE
    SDE.ST_INTERSECTS (IBOW.ITALYF2_REV1_AU.SHAPE, IBOW.SEAGRASS_RL13.SHAPE) = 1;

SELECT
*
ERROR at line 1:
ORA-28579: network error during callback from external procedure agent
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 100
ORA-06512: at "SDE.ST_RELATION_OPERATORS", line 340

Running the query on a small subset of features works:

SELECT
    IBOW.SEAGRASS_RL13.OBJECTID,
    IBOW.SEAGRASS_RL13.RLSTATUS
FROM
    IBOW.SEAGRASS_RL13,
    IBOW.ITALYF2_REV1_AU
WHERE
    SDE.ST_INTERSECTS (IBOW.ITALYF2_REV1_AU.SHAPE, IBOW.SEAGRASS_RL13.SHAPE) = 1
    AND IBOW.SEAGRASS_RL13.OBJECTID IN (27);

  OBJECTID  RLSTATUS
------------------------------------------------------------------------------------------
        27      LC

My guess is that probably the external process run out of memory when pperforming the intersect on many features. Is there a way to check for the error in the external process?

Thanks in advance for any hint.

Kind Regards,

Stefano

StefanoIacovella · ‎02-06-2014

Just want to add some more details about the strange behavious noted.

If I filter, with a nested query, shape according to envelope intersection it works:

SELECT
  SEAGRASS.OBJECTID,
  SEAGRASS.RLSTATUS
FROM
  (SELECT 
     SEAGRASS_RL13.OBJECTID AS OBJECTID, 
     SEAGRASS_RL13.RLSTATUS AS RLSTATUS, 
     SEAGRASS_RL13.SHAPE AS SHAPE 
   FROM 
     ITALYF2_REV1_AU, 
     SEAGRASS_RL13 
   WHERE 
     SDE.ST_ENVINTERSECTS (ITALYF2_REV1_AU.SHAPE, SEAGRASS_RL13.SHAPE) = 1) SEAGRASS,
  ITALYF2_REV1_AU
WHERE
  SDE.ST_INTERSECTS (ITALYF2_REV1_AU.SHAPE, SEAGRASS.SHAPE) = 1;

  OBJECTID   RLSTATUS                                                                        
------------------------------------------------------------------------------------------
        27       LC
        28       LC
         4        LC
         7        LC

4 rows selected.

Cheers,

Stefano

VinceAngelo · ‎02-06-2014

Please use the CODE formatting option (# in the editor UI) for code blocks.
Bold without indentation is even less readable than the default font. You can
edit previous posts within a reasonable time period, so there's no need to
follow-up to your own post.

- V

StefanoIacovella · ‎02-06-2014

Please use the CODE formatting option (# in the editor UI) for code blocks.
Bold without indentation is even less readable than the default font. You can
edit previous posts within a reasonable time period, so there's no need to
follow-up to your own post.

- V

Sorry for the bad formatting.
I am doing more tests on this issue.
It seems related to the shapes contained in the layers.
In the previous query the SEAGRASS_RL13 table contains 72 records with polygons shape.
The ITALYF2_REV1_AU table contains 1 record with a polygon shape. It is obtained as a buffer from points.
The target og the query is to extract the features in SEAGRASS_RL13 intersecting the shape onbtained from the buffer.
Using a different shape, drawed by hand with ArcEditor, the query works.
The original shape is valid, tested with ArcToolbox checkgeometry tool.
If I use the same layers in ArcMap with Query by location there is no error and features are selected as expected.

May I check something more?

Thanks

Stefano

EllenDean · ‎02-06-2014

We will get this same error if the feature classes have different SRID values. (We are running ArcSDE 10.1 sp1, Oracle 11.2.0.1).

For example on our system:

The feature classes: stn_et_geom and bnd_024k_county - both are spatial reference of NAD_1983_HARN_UTM_Zone_17N, but have different SRID values:

select sde.st_srid(shape) from stn_et_geom where rownum < 2;

SDE.ST_SRID(SHAPE)
------------------
                11

select sde.st_srid(shape) from bnd_024k_county where rownum < 2;

SDE.ST_SRID(SHAPE)
------------------
                24

Doing an st_intersects with these two will result in an error:

select s.objectid,s.stn_id,c.objectid,c.county
from
   stn_et_geom s, bnd_024k_county c
where
   s.stn_id=22841
and
   sde.st_intersects(c.shape,s.shape)=1
/

select s.objectid,s.stn_id,c.objectid,c.county
*
ERROR at line 1:
ORA-28579: network error during callback from external procedure agent
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 100
ORA-06512: at "SDE.ST_RELATION_OPERATORS", line 340

However, doing a transform on one of them to the other SRID value the query will now work on our system:

select s.objectid,s.stn_id,c.objectid,c.county
from
   stn_et_geom s, bnd_024k_county c
where
   s.stn_id=22841
and
   sde.st_intersects(sde.st_transform(c.shape,11),s.shape)=1
/

OBJECTID     STN_ID   OBJECTID COUNTY
---------- ---------- ---------- --------------------
70332024      22841        105 Indian River

ForrestJones · ‎02-07-2014

Hi Stefano, Ellen,

These network callback errors should not occur and depend on the data used in the query and srid's as you've noticed. We'd like to take a look at these cases so we can fix them as soon as possible.

If you have narrowed down the geometries that fail, you can try getting them as text (sde.st_astext) and then run the query against dual. If it reproduces the problem I can look into it. For example:


set long 9999
select sde.st_asText(shape) from myFCPolys where objectid = 1;

SDE.ST_ASTEXT(SHAPE)
--------------------------------------------------------------------------------
POLYGON  (( 0.00000000 0.00000000, 20.00000000 0.00000000, 20.00000000 20.00000000, 0.00000000 20.00000000, 0.00000000 0.00000000))

select sde.st_asText(shape) from myFCPoints where objectid = 1;

SDE.ST_ASTEXT(SHAPE)
--------------------------------------------------------------------------------
POINT (10.00000000 10.00000000)


select sde.st_intersects(sde.st_geometry('polygon ((0 0, 20 0, 20 20, 0 20, 0 0))',4326), sde.st_geometry('point (10 10)', 4326) ) from dual;

SDE.ST_INTERSECTS(SDE.ST_GEOMETRY('POLYGON((00,200,2020,020,00))',4326),SDE.ST_G
--------------------------------------------------------------------------------
                                                                               1

Otherwise would you be able to contact technical support, please?

Also, what release are you on? You might try the latest st_shapelib.dll (or libst_shapelib.so on unix) along with upgrading the geodatabase packages for your release. We've made some fixes recently that may help.

Thanks.

EllenDean · ‎02-07-2014

Hi FJ,

Thanks for getting back.

We have awhile back worked with Esri Support on this issue (Esri Incident #953779), and they tracked it down to an issue with an Oracle memory leak that was happening with some ST_Geometry queries. We get this error not only with the ST_INTERSECTS query with different SRID's, but in other queries that are in all aspects valid (SRIDs the same, all participating geometries = valid, etc.).

Supposedly this leak was to be fixed in Oracle 11.2.0.2 (we are on 11.2.0.1 on our production Oracle), and we have not seen this error in our test and development environments which are at 11.2.0.2. We've been trying to upgrade to 11.2.0.2 on our production systems, but have run into issues (problems upgrading the grid) and so have not yet been able to. But now I'm wondering if it might still be an issue in later Oracle versions, given Stefano's getting the same error at 11.2.0.3. But maybe Stefano's issue is different.

We've been able to work around this problem whenever we run into it - in the cases of our external web applications, we insert a "dummy" SQL query before executing the query which has the memory glitch. This has so far worked for us - albeit a little clugey.

Hope this helps - we are very interested in hearing any update on this issue.

Thanks,

Ellen

ForrestJones · ‎02-07-2014

Hi Ellen,

Thanks for the update. The incident you mention does look like it was resolved through an oracle fix (not an esri sde fix) on 11.2.0.2, and if your testing shows it is fixed there that is great. Hopefully you'll be able to get production upgraded at some point.

The network callback error is very general in nature and can occur for many different reasons (specific data, os type, specific queries, etc.). Because of this, it is hard to tell if Stefano is running into the same issue or not (the oracle bug may or may not be resolved in 11.2.0.3. I would hope it would still be fixed there, but something to check with oracle). The only way to know for sure is to narrow down specific test cases, test on different environments, and proceed from there depending on the results.

StefanoIacovella · ‎02-10-2014

Hi Ellen,

Thanks for the update. The incident you mention does look like it was resolved through an oracle fix (not an esri sde fix) on 11.2.0.2, and if your testing shows it is fixed there that is great. Hopefully you'll be able to get production upgraded at some point.

The network callback error is very general in nature and can occur for many different reasons (specific data, os type, specific queries, etc.). Because of this, it is hard to tell if Stefano is running into the same issue or not (the oracle bug may or may not be resolved in 11.2.0.3. I would hope it would still be fixed there, but something to check with oracle). The only way to know for sure is to narrow down specific test cases, test on different environments, and proceed from there depending on the results.

Hi all,

thanks to both for your comments.
Indeed I checked my data and the value returned from ST_SRID is not the same.
In the orginal query, exposing the error I have:

SELECT DISTINCT(SDE.ST_SRID(SHAPE)) FROM IBOW.SEAGRASS_RL13; 

(SDE.ST_SRID(SHAPE))
--------------------
                4326
1 row selected.

SELECT DISTINCT(SDE.ST_SRID(SHAPE)) FROM IBOW.ITALYF2_REV1_AU;

(SDE.ST_SRID(SHAPE))
--------------------
              300096
1 row selected.

the latter is a strange SRID, the feature is loaded with a python script from a shapefile. Both layers in ArcMap are showed as WGS84 geographical.

If I run the query with the ST_TRANSFORM it works as Ellen suggested.

SELECT 
    IBOW.SEAGRASS_RL13.OBJECTID, 
    IBOW.SEAGRASS_RL13.RLSTATUS
FROM 
    IBOW.SEAGRASS_RL13, 
    IBOW.ITALYF2_REV1_AU
WHERE 
    SDE.ST_INTERSECTS (SDE.ST_TRANSFORM (IBOW.ITALYF2_REV1_AU.SHAPE, 4326), IBOW.SEAGRASS_RL13.SHAPE) = 1;

OBJECTID RLSTATUS

27 LC
28 LC
4 LC
7 LC

In our case the Oracle versionis 12.2.0.3 so it seems it should be already fixed.
May we check something more? Or should we open an issue with our local distributor, the italian one?

Kind regards,

Stefano

ForrestJones · ‎02-11-2014

Hi Stefano,

Are you using 11.2.0.3 or 12.2.0.3 (is this released yet)? From what I can tell it is fixed in a patch set on top of 11.2.0.2 and in 12.1.0.1. I suggest checking the info in this KB article and then following up with Oracle if you get an hs_core file as described:

KB38823 - Bug: Spatial Type for Oracle: st_intersects can fail with error ORA-028579

Otherwise yes, please open an incident with your local distributor.

Thanks.