Error processing HANA geospatial columns in ArcGIS Server

[Former Member]

I have a table with a geospatial column that was loaded from an ESRI Shape file. It contains the Zip Codes in the US.  To this, I've joined Medicare data which is aggregated by Zip Code.  This is stored in a collection of SQL views in HANA. 

So far, so good.

Everything works when the views are added as query layers in ArcGIS Desktop. The map displays as expected and there are no errors. The resulting map can be published to ArcGIS server.

Again, so far, so good.

When I attempt to access the same data via the ArcGIS server, rarely I'll get part of the data, but most of the time I get nothing. The following error are recorded in the ArcGIS server log with each request:

"There is an error during the draw AVG_CASE_COST_2013 (1.) Underlying DBMS error[[SAP AG][LIBODBCHDB DLL][HDBODBC] General error;2048 column store error: search table error: [2617] executor: plan operation execution failed with an exception::S1000]."

After checking SCN, some users with similar errors say the root problem may be an out of memory condition but that I should check the log files on the server. 

The Index Server trace file has entries that look like this:

pop.prepare()

[4914]{317064}[43/-1] 2015-07-01 19:52:14.719124 e SERVER_TRACE     TRexApiSearch.cpp(10124) : IndexID: SYSTEM:0x00007facfed01700:3:0:#60000000001172a (432345564227639083): rc=2617

[4914]{317064}[43/-1] 2015-07-01 19:52:14.719142 e SERVER_TRACE     TRexApiSearch.cpp(10125) : cannot execute plan

[4914]{317064}[43/-1] 2015-07-01 19:52:14.719201 e SERVER_TRACE     TRexApiSearch.cpp(10126) : query: MEDICARE_MAPPER:TEST_ZIP_SHAPEen/SHAPE (IN):'SRID=4326;POLYGON((-179.999996 -89,179.999996 -89,179.999996 89,-179.999996 89,-179.999996 -89))'(GE)

[4914]{317064}[43/-1] 2015-07-01 19:52:14.719208 e SERVER_TRACE     TRexApiSearch.cpp(10127) : rc: 0

[4510]{317064}[43/-1] 2015-07-01 19:52:23.576695 i TraceContext     TraceContext.cpp(00827) : UserName=SYSTEM

[4510]{317064}[43/-1] 2015-07-01 19:52:23.576685 e join_eval        JEPlanOperation.cpp(00138) : Caught exception in pop 129

[4510]{317064}[43/-1] 2015-07-01 19:52:23.576710 e join_eval        JEPlanOperation.cpp(00139) : exception  1: no.0  (geometry/error/GeometryError.h:93)

The HANA server is at SRS92. The ArcGIS software is at 10.3.3.

Does anybody know what this means? 

Is it really an out-of-memory condition or is there some deeper problem?

[Former Member · Accepted solution]

After several experiments and SQL traces, I have a solution and an explanation.

ArcGIS Server (10.3.1) is specifically looking for SRID 1000004326. Geospatial columns that need to work with ESRI ArcGIS products should use this SRID.

The ArcGIS issues the query shown above and uses the ST_Intersects() function to locate records that fall within the currently-visible portion of the map.  ArcGIS Server's initial query uses a rectangle that covers a large portion of the earth.

When the SRID is spherical (as 4326 is) and the area involves large portions of the earth, HANA is unable to compute intersections and generates the error described above. Switching to the planar version (SRID 1000004326) makes the problem goes away.

HANA-ESRI integration is new. It's reasonable to expect this problem to go away or get better as both products evolve.

[Former Member]

Hi Michael

This is not an out of memory error (if this occurs you will see it in the trace and even get your own oom-dump-file).

The issue is a WKT polygon which we are rejecting on a round earth model like WGS84 (error messages are improved with SPS10).

This query works fine because you are on a planar world and it’s just a big rectangle:

SELECT new ST_Polygon('POLYGON((-179.999996 -89,179.999996 -89,179.999996 85.051129,-179.999996 85.051129,-179.999996 -89))', 0) from dummy;

This query is rejected because you are on WGS84 and your polygon contains nearly antidopal points:

SELECT new ST_Polygon('POLYGON((-179.999996 -89,179.999996 -89,179.999996 85.051129,-179.999996 85.051129,-179.999996 -89))', 4326) from dummy;

Curve contains nearly antipodal points (179.999996,-89.000000) and (179.999996,85.051129)

https://en.wikipedia.org/wiki/Antipodal_point

[Former Member]

Hinnerk,

I suspect you are right, but to confirm this I enabled SQL tracing and re-ran the experiment.  ArcGIS generates this SQL:

select 
  SHAPE, 
  AVG_CASE_COST, 
  1 as ESRI_OID, 
  ZIPCODE, 
  YEAR_DATA 
from (select * from MEDICARE_MAPPER.V_STATS_BY_ZIPCODE where YEAR_DATA = '2013') a 
where  SHAPE.ST_IntersectsRect(ST_GeomFromEWKT(?), ST_GeomFromEWKT(?)) = 1

The SRID of the SHAPE column is 4326, which is a spheroid, not planar.

Do you think the remedy is to reload the table using the planar SRID 1000004326?