Hana SPS07 and spatial data

[former_member182500]

Hi,

Interested in the intriguing possibilities of spatial data manipulation now that you have your SPS07 instance?

Wish to learn more or contribute along with other explorers in this brave new world of business data meets geospatial?

There appears to be very little in way of example content.  The official reference guide, which albeit useful, does not provide use cases within the context of SQLScript where the functionality could really be exploited over large geospatial datasets.

http://help.sap.com/hana/SAP_HANA_Spatial_Reference_en.pdf

There are a few other blogs out there which present wonderful slides or flashy videos yet add little to illuminate what's going on under the hood, how the engine was put together component by component with example code which we as developers love and learn from best.

I thought I'd throw out this discussion into the wild to hopefully attract like-minded souls who may be starting out on this geo adventure, where we can share thoughts, solutions, workarounds and information to everyone else.

So without further ado.....

[former_member182500]

Adding to an earlier indication by Duke Xu, and as highlighted in Hana SPS07, the spatial engine and taking a byte out of the Big Apple, there are some functions not supported by SRID 4326, including functions ST_CONTAINS, ST_WITHIN, ST_COVERS and ST_COVEREDBY.

Example SQL

select A.name as "POI A", B.name as "POI B",

       A.shape.ST_COVERS(B.shape) as "Covers" from

       SpatialLocations A ,

       SpatialLocations B where A.id = 1 and B.id = 2;

Error

Could not execute 'select A.name as "POI A", B.name as "POI B", A.shape.ST_COVERS(B.shape) as "Covers" from ...' in 54 ms 697 µs .

SAP DBTech JDBC: [7]: feature not supported: Unsupported function: st_covers() on the round earth Spatial Reference System: 4326 at function st_covers()

Q. Are there alternatives for determining containment of one geographic shape within another (casting to another SRID, side effects?).

Q. Can future documentation explicitly state if functions are applicable to round earth systems?

[former_member206741]

Hi Jon-Paul,

Thanks for your post. The error you are receiving is not correct I must admit...

In the current revision we don't support all spatial predicates on “round earth” spatial reference system. The unsupported ones require to distinguish between the interior and the boundary of a geometry which is not possible at present in case of a spherical model.

However, ST_Covers doesn't require this distinction and works as you would expect. It will be fixed with the next revision.

Also, we will update the documentation to explicitly state this.

Cheers

Gerrit

[former_member182500]

Hi Gerrit,

Is it possible for you to publish a list of "round earth" supported methods for current AWS rev 70?  I think this would be very useful.

Thanks,

Jon-Paul.

[former_member206741]

Hi Jon-Paul,

the fix for ST_Covers and ST_CoveredBy has been made and is now in production. You can expect it to be fixed in the next revision.

Also I am writing a short list of (un)supported predicates and a short explanation for the reasons.

Cheers

Gerrit

[former_member182500]

Hi Gerrit,

Now that AWS Hana Developer 72 is available would you know if fixes for ST_Covers and ST_CoveredBy made it?  Any other fixes of note in rev 72?

Many thanks,

Jon-Paul.

[former_member206741]

Hi Jon-Paul,

the fixes are scheduled for delivery and should ship with rev 74. Yes, there are some more fixes. I will assemble a list.

Also, we are currently working on a re-worked documentation and how to guides.

Cheers

Gerrit

[former_member102219]

Hi gentlemen, I hope you don't mind me reviving the old thread (it was quite informative, thank you!)

The problem I'm trying to solve here is: how to move X and Y meters (by long and lat, respectively) from a given point? So far I found only the methods related to distance measuring between two geometries, but what about the reverse task?

So far I've been able to come with the following:

1. Buffer the given point with X meters.

2. Intersect it with a horisontal line beginning in the given point and ending somewhere far enough (+-180).

3. Get the endpoint of the resulting intersection line.

4. Repeat steps 1-3 for that endpoint using Y meters buffer and vertical line.

This kinda works, but it obviously does hell of an unneeded extra work.

An alternative might be:

0. Use a local projection that has (0,0) meters point bound to, say, (151, -31) geodegrees.

1. Transform the given point from degrees to meters of that local projection.

2. Calculate the new (+X, +Y) point in meters.

3. Transform the new point back to degrees.

In the second option I could actually do a lot more in the "local" projection and then transform the end result into degrees. Yet the spatial reference mentions the existence of ST_Transform method, but doesn't give any details at all.

Can anyone comment on these points? Did I miss any other solution?

P.S. Here's a third option which I may implement for the lack of anything better:

1. For the given point (x0, y0), find the distance between it and (x0+1, y0) -> gets the linear value of 1 longitude degree in the given locality

2. Do the same for the latitude degree.

3. Use those values as multipliers in local calculations.

[Former Member]

Hi Roman

ST_Transform is a function to transform a geometry value from one spatial reference system (SRS) into another one. This feature is planned for SPS10, but will not really help in your case.

You are looking for a function like ST_Translate which translates a geometry to a new location by using specific distance parameters. This function is currently not planned but we could implement it.

I already put it on our feature wish list.

If you want to implement the function yourself, you first need to think about your SRS and the required accuracy. The calculation on a planar world is pretty easy, but it's getting more complicated for a sphere or even spheroid. The correct way to implement it would be by using the vicenty's formulae Vincenty's formulae - Wikipedia, the free encyclopedia.

Best regards,

Hinnerk

[former_member102219]

Thanks for the update Hinnerk, in fact I was a little lazy to implement Vincenty's formulae (which would be the right thing) and went on with something like this, that turned out to be OK for the demo purposes:

select new ST_Point(:v_center, 1000004326).st_distance(new ST_Point(concat(concat(concat(concat('POINT ( ', in_long + 0.001), ' ') , in_lat), ' )'), 1000004326), 'meter') into v_dx from dummy;

Effectively, I find the value in meters for 0.001th of a degree at the point of interest and assume  that it's going to be the same in the nearby area (10-20 km, for my scenario).

The use of multiple concats is nasty, though -- would there be a nicer way to create a geometric feature with SRID defined? Something like "new st_point(long, lat, SRID)" didn't work.

Lastly (since I got your ear here) -- is there a way to define a scalar variable of type st_geometry in SQLScript? I tried "my_shape type st_geometry", and it didn't work. Adding SRID to st_geometry didn't work either. What I'm trying to achieve here is to create some geometry that I'll use as a cookie cutter for further steps in the hdbprocedure. I could store that variable as a string, generate a geometry each time and combine it with other spatial functions, but I judge from experience that conversion from text to geometry (and back, too) is a rather expensive operation.

[former_member102219]

Hinnerk, I also find that performance of ST_IntersectsFilter is slow for me. I wonder why. Here's my code

v_cutter := 'Polygon .......';

pieces = select id, field1, field2

              from "MYSCHEMA"."T_PIECES"

              where "SHAPE".st_intersectsfilter(new st_polygon(:v_cutter, 1000004326).st_buffer(100, 'meter')) = 1;

I also tried to swap the shape field and the cutter, i.e.:

where new st_polygon(:v_cutter, 1000004326).st_intersectsfilter("SHAPE") = 1;

Both times I had to cancel the operation after waiting for 10 min. What do I do wrong?

[EDIT] OK, I discovered that if I remove the .st_buffer(100, 'meter') thing, my script finishes in approx 2 minutes, which is kinda too slow, but already sooner than eternity I guess the reason is that st_buffer may add too many vertices to my v_cutter, and st_intersectsfilter got totally clogged.

This brings another feature missing in HANA (I remember having that feature in ESRI Arc/INFO ) -- ability to recalculate a  line/polygon to a roughened shape, with reduced number of vertices. So, for me it would be something like :v_cutter.st_buffer(1000, 'meter').st_roughen(50), where the latter function would reduce the number of vertices in v_cutter by 50%. Makes sense?

[/EDIT]

Is there any index for st_geometry data? E.g. approximating st_geometry by rectangles and pre-fetching intersect-candidates by centroids etc? In fact, this is the next thing I'm gonna try to work around the issue here: create another table with the list of centroids for polygons from T_PIECES and do some rough prefetch of those (I'll have to buffer the v_cutter a bit, using my obvervations on average polygon size&shape in T_PIECES), then fine fetch using st_intersectsfilter.