Performance problems with GeoSpatial function as a filter

[Former Member]

Hi, colleagues

I am facing performance problems with GeoSpatial functions in SPS9.

I populated a Column Table with over 1 million of records with latitude and longitude fields and made a Calculation View with that.

Now, I want to filter records of this view with a square that has the bounds of a map in the client-side.

I'm filtering the results because it is too much data to display in the client side at once.

The where clause look like this one:

(NEW ST_Polygon( 'Polygon(( -34.58284565085074 -58.42738261914063, -34.5714747472561 -58.42738261914063, -34.5714747472561 -58.4441114552002, -34.58284565085074 -58.4441114552002, -34.58284565085074 -58.42738261914063 ))').ST_Contains(NEW ST_Point( "GEO_X", "GEO_Y" )) = 1) 

The whole query execution lasts 9 seconds, and the filter step takes 3,5 seconds:

The most worrying thing is that the query needs 21,1 GB of memory:

versus 2,1 GB without the where clause:

If we execute the query several times the hana server starts having memory and execution planning errors.

Is there a way to optimize this case? Or another way to do this?

Thanks!

[Former Member · Accepted solution]

Hi

Regarding the spatial predicate in the where clause, you can optimize the query performance by using ST_Within instead of ST_Contains. The following statement is much faster (because ST_Within is already optimized in HANA. ST_Contains optimization comes later) and is logically the same:

(NEW ST_Point( "GEO_X", "GEO_Y" )).ST_Within(NEW ST_Polygon( 'Polygon(( -34.58284565085074 -58.42738261914063, -34.5714747472561 -58.42738261914063, -34.5714747472561 -58.4441114552002, -34.58284565085074 -58.4441114552002, -34.58284565085074 -58.42738261914063 ))') = 1)

Additionally, it looks like you are not using a ST_Point column and create a new point out of a double column. This cost extra time and memory. If you work on a native ST_POINT or ST_GEOMETRY column, you will also gain performance and use less memory.

Cheers

Hinnerk