on 05-26-2015 7:11 PM
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!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
93 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.