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 · Accepted solution]

Aron,

Specifying the SRID in the point constructor, using the "John O'Groats to Lands End" example.

Test 1 - SRID 4326 - WSG84

SELECT NEW ST_Point('POINT(58.641759 -3.068672)',4326).ST_Distance(NEW ST_Point('POINT(50.05935 -5.708054)',4326)) FROM dummy

Returns 996229m or 996Km.  Still wondering why it's 30Km over what I'd expect.

Test 2 - SRID 1000004326 - WSG84 (Planar)

If I change to the WSG84 (Planar) SRID with the following I get the result in decimal degrees with 8.979091356508434

SELECT NEW ST_Point('POINT(58.641759 -3.068672)',1000004326).ST_Distance(NEW ST_Point('POINT(50.05935 -5.708054)',1000004326)) FROM dummy

Which surprises me as looking at ST_SPATIAL_REFERENCE_SYSTEMS  the linear unit of measure for both 4326 and 1000004326 is meter.

Guess I need to understand the relationship between the below and how ST_SPATIAL_REFERENCE_SYSTEMS is defined (reference, page 38):

"The ST_Distance method computes the shortest distance between two geometries. For planar spatial reference systems, the distance is calculated as the Cartesian distance within the plane, computed in the linear units of measure for the associated spatial reference system. For round-Earth spatial reference systems, the distance is computed taking the curvature of the Earth's surface into account using the ellipsoid parameters in the spatial reference system definition."

Test 3 - SRID 0 - Default

And for completeness, default SRID 0, also in decimal degrees with 8.979090690612793 (small difference comparing with WSG84 Planar)

SELECT NEW ST_Point('POINT(58.641759 -3.068672)',0).ST_Distance(NEW ST_Point('POINT(50.05935 -5.708054)',0)) FROM dummy

This at least confirms that when no SRID is specified the system does indeed use SRID 0 as indicated in the reference, as the computed decimal degrees is the same as given in an earlier post above..

Now to understand why I get an additional 30Km than expected for test 1.

(By the way, it could be a good idea to use a common "from-to" set of coordinates when we are fleshing the geographic side out, to compare results etc - could go with this example or anything else you suggest?)

[Former Member · Accepted solution]

Well done, getting closer.  The UK example points work for me

Until  we get the insert/select working using the stored SRID 4316 then here's a workaround:

CREATE COLUMN TABLE SpatialLocations(

id integer, location ST_POINT);

INSERT INTO SpatialLocations VALUES(1, new ST_POINT('POINT(58.641759 -3.068672)'));

INSERT INTO SpatialLocations VALUES(2, new ST_POINT('POINT(50.05935 -5.708054)'));

select A.location.ST_AsText(), B.location.ST_AsText() , new ST_POINT(A.location,4326).ST_Distance(NEW ST_Point(B.location,4326))  as "ST_Distance"

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

RETURNS: 996,229.6684717659 meters  

Perhaps it's a bug that SRID isn't being stored on INSERT when specified.

BTW: If you look at ST_SPATIAL_REFERENCE_SYSTEMS only 4326 is set to Round_Earth=True

Could that be the trigger to use Meter? Seems odd but stranger things have happened.

[former_member182500 · Accepted solution]

Thanks for the nice example.

I however don't think there is an issue with assignment of SRID at INSERT.  If I do:

INSERT INTO SpatialLocations VALUES(1, new ST_POINT('POINT(58.641759 -3.068672)',4326));

INSERT INTO SpatialLocations VALUES(2, new ST_POINT('POINT(50.05935 -5.708054)',4326));

Followed by following which indicates the database knows about the SRID of inserted value:

SELECT location.ST_AsEWKT() FROM SpatialLocations;

I did struggle to replicate your SELECT calculating distance without specifying the SRID in the select - take the SRID 4326 out and I get SQL syntax issues regarding arguments - could you give it a go?

P.S.@Aron - Many thanks for your contributions today, truly appreciated and I think we are making headway.  It's been an interesting exercise, cheers!

[Former Member · Accepted solution]

No prob.

Odd, my SQL from above works for me without changed:

If I simpify it a bit based on your latest comment:

drop table SpatialLocations;

CREATE COLUMN TABLE SpatialLocations(

id integer, location ST_POINT(4326));

INSERT INTO SpatialLocations VALUES(1, new ST_POINT('POINT(58.641759 -3.068672)',4326));

INSERT INTO SpatialLocations VALUES(2, new ST_POINT('POINT(50.05935 -5.708054)',4326));

SELECT location.ST_AsEWKT() FROM SpatialLocations;

I get:

Note: SRID is still 0  ?????  Thats why I assumed issue on INSERT

My distance SQL still works though if 4326 is specified:

select A.location.ST_AsText(), B.location.ST_AsText() , new ST_POINT(A.location,4326).ST_Distance(NEW ST_Point(B.location,4326))  as "ST_Distance"

from SpatialLocations A , SpatialLocations B where A.id = 1 and B.id = 2

It's odd if we are getting different results with the same SQL. If you are doing something slightly different with the create table statement then please let me know and I will try your version as well.

[former_member182500 · Accepted solution]

Hi Aron,

The only difference is that you are specifying SRID 4326 when defining column location in the CREATE - I did not.  Perhaps remove, retain the 4326 during insert and try the SELECT. 

[Former Member · Accepted solution]

Weird. I've tried it both ways and SRID is  always 0.  I also tried using the Alter table statement to define the location field, similar to the PDF,  but that also didn't help.

My HANA server is 1.00.70.00.386119  & Studio is:

Version: 1.0.7000

Build id:  386119

[former_member182500 · Accepted solution]

Hi Aron,

OK I started getting strange SRID=0 results, but perhaps due to long day and user error.

Starting from scratch, I can confirm the exact following SQL:

Example 1

drop table SpatialLocations;

CREATE COLUMN TABLE SpatialLocations(id integer, location ST_POINT(4326));

INSERT INTO SpatialLocations VALUES(1, new ST_POINT('POINT(58.641759 -3.068672)',4326));

INSERT INTO SpatialLocations VALUES(2, new ST_POINT('POINT(50.05935 -5.708054)',4326));

SELECT location.ST_AsEWKT() FROM SpatialLocations;

Gives:

Example 2

Note change to exclude SRID 4326 from INSERT of points:

drop table SpatialLocations;

CREATE COLUMN TABLE SpatialLocations(id integer, location ST_POINT(4326));

INSERT INTO SpatialLocations VALUES(1, new ST_POINT(58.641759, -3.068672));

INSERT INTO SpatialLocations VALUES(2, new ST_POINT(50.05935, -5.708054));

SELECT location.ST_AsEWKT() FROM SpatialLocations;

Gives:

Which indicates that provided the column is created with associated SRID this "sticks".

[former_member206741 · Accepted solution]

Hi Jon-Paul,

thank you for the examples. I would like to put them into one of the cook books we discussed.

So in test 1 you are doing a actual calculation on a ellipsoid, why do you think it is 30km off?

In the second example you came across another bug as the definition of the SRS 100000432 is not correct in revision 70!

It should have "planar degree" as its Linear UoM and not "meter" this is why it produces the same results as SRS 0 as it is not detected that the radians must be converted to meter first.
The fix is already made and scheduled for the next revision.

In the third test you are using the default SRS 0 as you already found out which does a plain euclidian distance calculation.

Cheers

Gerrit

[former_member182500 · Accepted solution]

Hi Gerrit,

Following has been a very useful site for me which is a lovely little reference for geo distance calculations (I implement some of the examples in Javascript with success).

Calculate distance and bearing between two Latitude/Longitude points using Haversine formula in Java...

Anyhow, plugging John O'Groats (top of UK) and Land's End (bottom left UK) gives 969.3km using the great circle distance ellipsoid formula:

970Km I believe is the generally recognised distance between these two points.  Just wondering why 996Km with SRID 4326.

[former_member182500 · Accepted solution]

Any chance a temporary fix to SRID 1000004326 column LINEAR_UNIT_OF_MEASURE is as straight-forward as an SQL update setting as planar degree?

[Former Member · Accepted solution]

Thx,  I tried those 2 combos again but still SRID shows as 0.   Grrrrrr

Gerrit - Any other ideas to get this working for me, since works for Jon-Paul? 

[former_member206741 · Accepted solution]

Hi Jon-Paul,

The difference is caused by the SRS definition. In 4326 an ellipsoid is being defined with the parameter inverse flattening thus we calculate distances not on a perfect sphere but on the actual ellipsoid, which is more accurate according to the SRS definition.

I found a site where you can play around with it: http://www.movable-type.co.uk/scripts/latlong-vincenty.html

Fortunately it gives the same result 🙂

Of course, this calculation is more complex than computing distances on a sphere. If this accuracy is sufficient for your needs you must define a custom SRS with no flattening, then we do a simpler calculation internally. Sadly, user defined SRS are not enabled in revision 70 and will come with a future revision.

Cheers

Gerrit

[former_member182500 · Accepted solution]

Hi Gerrit,

Thanks for the clarification, the Vincenty ellipsoid formula is a little more elegant than the Haversine, and from the Moveable Types link:

"Vincenty’s formula is accurate to within 0.5mm, or 0.000015″ (!), on the ellipsoid being used. Calculations based on a spherical model, such as the (much simpler) Haversine, are accurate to around 0.3% (which is still good enough for most purposes, of course)"

I thought I'd make comparison between the two formula and Hana SRID 4326 on a much shorter distance - this time Stonehenge, and the distance between the northmost stone and the southmost stone.  Coordinates:

Northmost 51.179,-1.826206

Southmost 51.17871,-1.82622

With the Haversine formula, 32.26m

With the Vincenty, 32.27m

With Hana Spatial SRID 4326, 32.30m

SELECT NEW ST_Point('POINT(51.179 -1.826206)',4326).ST_Distance(NEW ST_Point('POINT(51.17871 -1.82622)',4326)) FROM dummy

That will suffice for me

[former_member206741 · Accepted solution]

Hi,

Yes there is something you can do to fix it.

In general a SRS cannot be updated, thus you need to drop it and re-create it. If there are tables/columns existing that are using this system you need to drop them first.

Once you have done that you can re-create the system 1000004326 with the attached SQL.

*Please note, that this will not work with a user defined SRS! The system will allow you to create them but the metadata will not be correctly considered in calculations!*

Cheers

Gerrit

srs.sql.txt.zip

[former_member182500 · Accepted solution]

Many thanks for the SQL fix.  However just tried to execute and apparently only user SYS can create.  I executed with user SYSTEM.

Could not execute 'CREATE SPATIAL REFERENCE SYSTEM "WGS 84 (planar)" identified by 1000004326 type planar snap to grid ...' in 49 ms 911 µs .

SAP DBTech JDBC: [256]: sql processing error: only SYS user can create spatial reference system

[former_member206741 · Accepted solution]

oh wow, I was getting ahead of myself. The syntax will be released when we enable user defined srs. Can you please issue the following sql and try again:

>>

alter system alter configuration ('indexserver.ini','SYSTEM') set ('sql','user_can_create_srs') = 'Yes' with reconfigure;

<<

Cheers

Gerrit

[former_member182500 · Accepted solution]

Worked like a charm.  Now have SRID 1000004326 with an updated linear unit of measure as planar degree, and was able to successfully create the table with location referencing 1000004326, insert the points and select from, as per your script.  Thanks!

[aditya_karanth2]

Hi Colleagues,

I was able to use the ST_WITHIN function in my SQL queries. My query is as below.

select * from cities where CO_ORD.ST_Within(new ST_Polygon( 'POLYGON ((41.77131167976406  -80.859375,

1. 34.30714385628804 -79.62890625,
2. 38.41055825094609  -65.7421875,
3. 41.77131167976406  -80.859375))' )) = 1

However, I was wondering how to put this logic into a stored procedure and make the logic input ready so that I can pass the polygon co-ordinated from my UI and select the cities falling within the polygon?

Request your assistance here.

Regards,

Aditya.

[Former Member]

One example how you can do it (tested with Rev92)

drop schema TST cascade;
create schema TST;
create column table TST.SHAPES (
    id integer,
    shape ST_Geometry
);
INSERT INTO TST.SHAPES VALUES (1, new ST_Point(0.5, 0.5));
INSERT INTO TST.SHAPES VALUES (2, new ST_Point(0, 0));
INSERT INTO TST.SHAPES VALUES (3, new ST_Point(1, 1));

create procedure TST.WithinShape(in myShape String) as
begin
    SELECT * from TST.SHAPES
    where shape.ST_Within( ST_GeomFromText(:myShape) ) = 1;
end;

CALL TST.WithinShape( 'Polygon((0 0, 0 1, 1 1, 1 0, 0 0))' );

[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.

[former_member182500]

Just blogged about our experiences this week, many thanks to all.

[former_member283321]

In the SQL console, I was trying to execute the below statement:

select MANDT,

GEOLOCATION.ST_AsWKt() as GEOLOCATION

from Store

where MANDT = '001'

and GEOLOCATION.ST_Within(new ST_Polygon('POLYGON (0 0, 0 1, 1 1, 1 0, 0 0)'))
= 1;

But I got the error:

SAP DBTech JDBC: [2048]:
column store error: search table error: [1999] general error (no further
information available)

I can execute the
following statement successfully :

select new
ST_POINT(0.05, 0.05).ST_Within(new ST_Polygon( 'POLYGON ((0 0, 0 1, 1 1, 1 0, 0
0))' )) from dummy;

I don't know why.

[Former Member]

Hi Duke

To make this a bit easier for anyone wanting to look into this error of yours do you mind sending the 'Store' table  definition as well as an example entry, preferably as a SQL Create table & INSERT statements.    One record should be sufficient.

Cheers

Aron

[former_member206741]

Hi Duke,

oh the error message is really bad. We improved them a lot and they will be out soon, if they would have been out already you would see this:

>>

Invalid Coordinate Sequence: expected "(" ....

<<

This is because the WKT is maleformed, you must have two brackets for a polygon as you did in your dummy selection.

>>

GEOLOCATION.ST_Within(new ST_Polygon('POLYGON (0 0, 0 1, 1 1, 1 0, 0 0)'))

<<

After adding the brackets it should work such as:

>>

GEOLOCATION.ST_Within(new ST_Polygon('POLYGON ((0 0, 0 1, 1 1, 1 0, 0 0))'))

<<

Cheers

Gerrit

[former_member283321]

Aron,

The defination of table is quite simple, it has a key field MANDT only, then I just used the statement altr talbe to add a column with type ST_POINT.

[former_member283321]

Hi Gerrit,

I adjusted statement according to your comments , but I still get the same error message.

Regards,

Duke

[former_member283321]

I found the error is that GEOLOCATION is ST_POINT with SRID 4326 which cannot be handled by ST_Within method.

[former_member182500]

Hi Aron,

Regarding your test of ST_DISTANCE - taken from the spatial reference guide:

"The ST_Distance method computes the shortest distance between two geometries. For planar spatial reference systems, the distance is calculated as the Cartesian distance within the plane, computed in the linear units of measure for the associated spatial reference system. For round-Earth spatial reference systems, the distance is computed taking the curvature of the Earth's surface into account using the ellipsoid parameters in the spatial reference system definition."

• I inserted my geometric data specifying SRID (Spatial Refeference Identifier) 4326 - This equates to WSG84.

• From table ST_SPATIAL_REFERENCE_SYSTEMS I see that it is a "round earth" reference system, with the linear unit of measure as meter,

This to me signifies that when I use ST_DISTANCE (linear measure) I already get back my result in metres, and performing a few tests this would seem correct.

[former_member182500]

Just to double check.....the above example is where I have specifically indicated the data as SRID 4326.  Let's do some direct tests.

Let's take the distance from John o' Groats, Highland KW1 to Land's End.

• For John o'Groats, coordinates are 58.641759,-3.068672
• For Land's End, coordinates are 50.05935,-5.708054

With ST_DISTANCE

SELECT NEW ST_Point(58.641759,-3.068672).ST_Distance( NEW ST_Point(50.05935,-5.708054) ) FROM dummy

I get 8.979090690612793.  This is however with direct provision of values where the SRID is not specified and hence I assume it is using the default of Off course we well know distance from John o'Groats to Land's End is more than 8 metres, else we'd all walk/cycle it no sweat!

Assuming this is decimal degrees, let's convert using the following formula:

distance [m] = 6378137.0 [Earth radius m] * Pi * distance [degree] / 180.0

thus

distance =  6378137.0 * 3.14159 * 8.979090690612793 / 180.0

thus

distance = 999546.959 metres or 999.54Km.

Just double checking my math.....Also need to figure out how to specify SRID 4326 else I assume the default SRID 0 is being used.

[Former Member]

I'm not convince yet.

I don't think the SAP pdf is very clear on this.

For example:

select NEW ST_Point(  100, 100 ).ST_Distance(NEW ST_Point(  100.0005, 100.0005)) from dummy;

Results: 0.0007066726684570312

Are you suggesting the results are in Metres?  It seems very low.

Using the following 2 websites I manually get a distance of 0.06270 km

Convert Latitude/Longitude to Decimal

Calculate distance and bearing between two Latitude/Longitude points using Haversine formula in Java...

What am I missing?

[former_member182500]

Yes, but what reference system are you using - you don't specify this when creating your points or when executing the DISTANCE function, so you values and calculation will reference default SRID 0.

[Former Member]

Not bad

The websites below give  969.3 km  so your new formula isn't far off.

[former_member182500]

Only(!) 30Km out

Still trying to determine if we are working within the SQLConsole with hard-coded geographic

values how we specify the SRID (or indeed can we change the default SRID).

In my case I always want to work with 4326.  In the Insert clause you can specifiy data represents 4326, as an example:

INSERT INTO "SCHEMA"."package" VALUES(1, new ST_POLYGON('POLYGON((6.892857208251808 46.47508312878526, 6.892847162786849 46.47506597958532, 6.892975863871317 46.47503129729259, 6.892980220749916 46.47504694818233, 6.892857208251808 46.47508312878526))',4326));

If working within the console for testing, I don't want default SRID 0 (which I understand is for geometric than geographic representation). 

It's not easy to determine from the reference guide how you may specify the SRID with ST_POINT, ST_DISTANCE etc.  Or indeed change the default.  Looking at at.

[former_member182500]

With regard to SRID, consider the following:

SELECT NEW ST_Point(58.641759,-3.068672).ST_AsEWKT() FROM dummy

This returns:

SRID=0;POINT (58.641759000000000 -3.068672000000000)

Which tells me that by default all values and computations reference default SRID 0.

I thought we might have been able to do something along the lines of the following (according to other non-hana examples I have seen), doesn't like the additional argument for SRID

SELECT NEW ST_Point(58.641759,-3.068672,4326).ST_Distance( NEW ST_Point(50.05935,-5.708054,4326)) FROM dummy;

or something like below, according to format used for creating my polygon shapes via INSERT:

SELECT NEW ST_Point('POINT(58.641759,-3.068672)',4326).ST_Distance( NEW ST_Point('POINT(50.05935,-5.708054)',4326)) FROM dummy;

[Former Member]

I also tried the follow which doesn't seem to produce any different results.

CREATE COLUMN TABLE SpatialLocations(

id integer, location ST_POINT(4326));

INSERT INTO SpatialLocations VALUES(1, new ST_POINT('POINT(100 100)'));

INSERT INTO SpatialLocations VALUES(2, new ST_POINT('POINT(100.0005 100.0005)'));

INSERT INTO SpatialLocations VALUES(3, new ST_POINT('POINT(100 100)',4326));

INSERT INTO SpatialLocations VALUES(4, new ST_POINT('POINT(100.0005 100.0005)',4326));

select A.location.ST_AsGeoJSON(), B.location.ST_AsGeoJSON() , A.location.ST_Distance(B.location) as "ST_Distance"

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

select A.location.ST_AsGeoJSON(), B.location.ST_AsGeoJSON() , A.location.ST_Distance(B.location) as "ST_Distance"

from SpatialLocations A , SpatialLocations B where A.id = 3 and B.id = 4;

Adding 4326 to the insert didn't seem to make any difference also to the SRID assigned:

select location, location.ST_SRID() from SpatialLocations;

LOCATION                                                                 LOCATION.ST_SRID()

010100000000000000000059400000000000005940          0

010100000079E926310800594079E9263108005940          0

010100000000000000000059400000000000005940          0

010100000079E926310800594079E9263108005940          0

NOTE: I tried with 1000004326  & 4326 will similar results.    We must be missing a trick. 

[former_member182500]

That's a good test Aron.  I quote from page 7 of the spatial reference guide:

"By default, the database server adds the following spatial reference systems to a new database:

Default - SRID 0

This is the default spatial reference system used when constructing a geometry and the SRID is not specified in the SQL and is not present in the value being loaded."

You have specified the SRID when inserting the values - so the SRID should be "present in the value being loaded" when performing your SELECT.

Only questionable part is the "SRID is not specified in the SQL".  I would not have thought this necessary as you have explicitly specified this with the values upon insertion as I have done.

The "trick" is - How do we specify the SRID in SQL? 

With the examples I have posted earlier, trying to emulate other spatial SQL implementations, I have had no luck specifying SRID 4326 as a third argument.

Looking at page 53 - ST_SRID Method

"Returns the SRID of the geometry".

But on page 95, it states:

"Retrieves or modifies the spatial reference system associated with the geometry value.".

I have tried chaining ST_SRID(4326) to ST_POINT and ST_DISTANCE without success.

And if only there was a system configuration value we could change to set default from 0 to 4326.  I had a quick look but couldn't find anything.

We'll get there......

[former_member206741]

Hi Aron, Hi Jon-Paul,

You came across a tough question; the SRS handling is not trivial.

There are two important meta data items for a spatial column: its spatial reference system and the used unit of measure. The spatial reference system is linked to the units of measure, if you look at them with:

>>

select * from "PUBLIC"."ST_SPATIAL_REFERENCE_SYSTEMS";

<<

You will see two units columns: LINEAR and ANGULAR Units of Measure (UoM), you can find them here:

>>

select * from "PUBLIC"."ST_UNITS_OF_MEASURE";

<<

So the ST_Distance function is using the LINEAR UoM, which is for 4326 meters.

In order to invoke it you must assure that both points used in the distance calculation are in the same SRS. In the next revision we added SRS deduction, so you don’t need to specify it in every parameter as we look it up from the column but this is not available in revision 70, thus it must be specified such as:

>>

create column table spatialtest

(

       location st_point(4326)

);

insert into spatialtest values (new ST_Point(9.283844, 48.544462));

insert into spatialtest values (new ST_Point(9.285121, 48.544924));

insert into spatialtest values (new ST_Point(9.278437, 48.543582));

select

       location.st_distance(ST_GeomFromEWKT('SRID=4326;POINT(9.283844 48.544462)'))

from

       spatialtest ;

<<

This will give you the distances in meters.

If the SRS is not specified the default 0 is taken, which results in a euclidian distance calculation, Thus you get: 0,0007066726684570312 for:

>>
select NEW ST_Point(100, 100).ST_Distance(NEW ST_Point(100.0005,100.0005)) from dummy;
<<

Cheers

Gerrit

[former_member182500]

Gerrit, very good point regarding same SRS.  A useful example seeing explicit use of SRID within method.  Column deduction will be welcomed.

[Former Member]

Thanks Gerrit.

Simliar to my comment further down SRID still does not appear to be saved on Insert for me using your example. After using your Inserts I see:

SELECT location.ST_AsEWKT() FROM spatialtest; 

So because SRID is still stored as 0  then I get no valid results with your example:

select location.st_distance(ST_GeomFromEWKT('SRID=4326;POINT(9.283844 48.544462)') from spatialtest ;

Unless I specify SRID again with the following:

select new ST_POINT(location,4326).st_distance(ST_GeomFromEWKT('SRID=4326;POINT(9.283844 48.544462)'))from spatialtest ;

Any ideas?

Jon-Paul - Did all of Gerrits example work for you?

[former_member182500]

Hi Aron,

Followed Gerrit's example to the letter and get SRID metadata of 4326 "in-column" as indicated:

P.S. I checked, have exactly the same appliance and studio release as yourself.

HANA server 1.00.70.00.386119

Studio Version: 1.0.7000, Build id:  386119

[Former Member]

Thanks for trying it out.  I'm glad it works for you.  That's weird that we are getting different results.

Interestingly I have insufficient authorisation to view (select *) from ST_SPATIAL_REFERENCE_SYSTEMS, and it will take an eternity for me to get access, so I wonder if there is something missing in my assigned roles that might be causing the problem.

There's nothing mentioned in the PDF about any specific roles needed but.....

[JWiseman]

hi Jon-Paul,

thanks for this great post!

below is some syntax for a haversine db function for hana for testing. it's not 100% due to the inaccuracies of the calc, but it does get close with 966 and change km for the above example.

you can change the units of output as per the syntax below as well.

cheers,

jamie

-- units to use: e.g. use 3956 for output in miles
-- rPd:= 0.017453293;            // rad per degree (PI/180 where PI = 3.1415926535)
-- rMi:= 3956;                   // radius in miles 
-- rKm:= 6371;                   // radius in kilometers 
-- rFt:= 20895592 (rMi * 5282);  // radius in feet 
-- rM:= 6371000 (rKm * 1000);    // radius in meters 

CREATE FUNCTION DISTBYUNIT(LAT1 DECIMAL(13,10), LON1 DECIMAL(13,10), LAT2 DECIMAL(13,10), LON2 DECIMAL(13,10), UNIT FLOAT)
RETURNS DIST DOUBLE
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
DIST := :UNIT * 2 * ATAN(SQRT(SIN((LAT2-LAT1)*0.017453293/2) * SIN((LAT2-LAT1)*0.017453293/2) + COS(LAT1*0.017453293) * COS(LAT1*0.017453293) * SIN((LON2-LON1)*0.017453293/2) * SIN((LON2-LON1)*0.017453293/2)) / SQRT(1-SIN((LAT2-LAT1)*0.017453293/2) * SIN((LAT2-LAT1)*0.017453293/2) + COS(LAT1*0.017453293) * COS(LAT2*0.017453293) * SIN((LON2-LON1)*0.017453293/2) * SIN((LON2-LON1)*0.017453293/2)));
END;

-- function input example

DISTBYUNIT(58.641759, -3.068672, 50.05935, -5.708054, 6371) AS DIST_KM

--result

966.3523928691291

[former_member182500]

Hi Jamie,

Thanks for the example, Ive been using similar geo formula solutions in JavaScript and it's nice to see a variation in a defined function.

It's also why Im passionate about the spatial engine as a feature-rich toolkit doing all the heavy lifting for us, so we can focus on the application than being concerned about ATAN2 and SIN.

[Former Member]

Just for your info I launched  a AWS SP7 HANA instance in the weekend and the SQL also worked for me with SYSTEM user id, so it now strongly leads me to think something in authorization may be causing me the issue on a more heavily restricted client SP7 instance. 

[Former Member]

I have an existing table with LATITUDE and LONGITUDE columns and want to use them to create a new ST_POINT column. From there, I hope to take advantage of the new geospatial functions.

This sounded like a simple extension to Gerrit's example above but it turns out there's a little more to it.

As Gerrit says, to use latitudes and longitudes, you must specify the SRID of 4326 when you create the column:

alter table GHCND_STATIONS add( LOCATION st_point(4326) );

However, in my case, it was also necessary to specify the SRID during the update:

update GHCND_STATIONS

set LOCATION = new st_point( 'POINT(' || LONGITUDE || ' ' || LATITUDE ||')',4326)

Garrit's INSERT statements worked without the SRID but when I tried this in an UPDATE statement, the resulting ST_Point has an SRID of -1 and attempts to use them in distance calculations return NULL.

Performance was disappointing.  The table in question contains 91,000 weather stations. The query below returns stations within 50 km of a specified lat/long and takes almost 11 seconds to run (on an AWS HANA):

SELECT ID, name, longitude, latitude, location.ST_AsEWKT() As EWKT,

location.st_distance(ST_GeomFromEWKT('SRID=4326;POINT(-94.1167 29.7000)'))/1000 as Dist_KM

FROM GHCND_STATIONS

where location.st_distance(ST_GeomFromEWKT('SRID=4326;POINT(-94.1167 29.7000)'))/1000 < 50

Almost all the time went into WHERE clause.

[former_member206741]

Hi Aron,

could you please provide me the details of the users restriction for which it doesn't work? I couldn't reproduce the issue.

Cheers

Gerrit

[former_member206741]

Hi Michael,

thank you for your example. I looked into it and yes the performance is surely not acceptable. The reason for this is that the query is not getting optimized correctly, in detail the predicate is not evaluated at an early stage which leads to a full materialization of all points as an intermediate result. I found the issue and we will bring it to the next revision.

There is a more "friendly" way to express the query by using the ST_WithinDistance predicate:

>>

WHERE location.ST_WithinDistance(ST_GeomFromEWKT('SRID=4326;POINT(-94.1167 29.7000)'),50000) = 1;

<<

However, it is suffering from the same problem currently. But in principle it is more optimizer friendly.

If you want to have an intermediate solution you can use ST_Intersects in combination with ST_Buffer which will execute much faster (on my machine with 100.000 points ca. 100ms).

>>

WHERE location.ST_Intersects(ST_GeomFromEWKT('SRID=4326;POINT(-94.1167 29.7000)').ST_Buffer(0.5)) = 1;

<<

Let me know if this works for you.

Cheers

Gerrit

[Former Member]

Thanks for the information. I look forward to improved optimization.

The immediate solution you proposed is behaving as if ST_Buffer wasn't working. The test point happens to match exactly one record in the database and this is the only record returned. If I move the test point .0001 degrees, no results are returned.

My reading of the doc is that ST_Buffer(0.5) creates a half-meter buffer, so I tried some variants such as ST_Buffer(50000, 'meter').  I also tried putting the buffer on the location column. None of this made any difference.

Here is one example (the intent is to return all points within 50km of a reference point):

WHERE location.ST_Intersects(ST_GeomFromEWKT('SRID=4326;POINT(-94.1167 29.7000)').ST_Buffer(50000, 'meter')) = 1;

[former_member182500]

Hi Aron,

Many thanks for taking a look.  I can confirm that ST_AsGeoJSON does lose geometric precision.

Can also confirm there is no storage precision loss - ST_AsText, in addition to ST_AsWKT returns full precision - what goes into the database comes out:

select SHAPE.ST_AsText() as "WKT" from "_SYS_BIC"."hpl.missioncontrol.models/AT_SCENARIO_TERRAIN"

Returns the like of:

POLYGON ((6.892805365019621 46.475136880333316,6.892739484137811 46.475160973073280,6.892705440891889 46.475123420221337,6.892792591997461 46.475100608648830,6.892805365019621 46.475136880333316))

Interestingly I found some threads talking specifically about the precision of ST_AsGeoJSOn #4396 (OGR 1.8 and earlier truncates geometry precision) – GDAL

"It seems clear that the precision is being truncated in the geojson. I would presume that is leading to some sort of geometric degeneracy. I see that GDAL/OGR trunk (but likely not 1.8.x) has COORDINATE_PRECISION layer creation option and defaults to 15 decimal places. So you might want to upgrade to trunk and try with that."

It's only an assumption to which I'd welcome official comment, however I wonder if SAP has implemented the spatial capability with a forked version of an open source spatial features library that has not had this precision fix?  Wild speculation at this point.

At this moment I dont see any hardship with precision loss in GeoJSON as we can form our own JSON with values from the "text based" methods.

Required precision would need to be determined on a case by case basis - a trade off between precision accuracy and data manipulation, storage and transfer. 

[Former Member]

Thanks for checking and confirming.  Hopefully SAP will give an official comment. 

As a side note I found this link which suggests that the Postgres  add on 'PostGis' supports up to 15 decimals with the similar named ST_AsGeoJSON function. Hopefully HANA will be support imporved accuracy in a subsequent revision, if it's not already available in some hidden system setting.

[former_member206741]

Hi,

Interesting coincidence! But it is not the case, we wrote it ourselves and made the bug ourselves as well…

Cheers

Gerrit

[former_member206741]

Hi Aron,

yes we will. Promised!

Gerrit

[former_member182500]

Aron MacDonald responded to above with below (collating here as info bucket for SPS07 spatial specific) - thanks Aron.

Hi Jon-Paul,

That's a very interesting observation.  I hadn't noticed that.

It probably doesn't cause much of an issue zoomed out to show the world, but zoomed in at street level the accuracy lost may be important.

I think the INSERT is ok because when using ST_AsWKT()  it returns it the same level of detail on may shapes..

You can also compare with the following SQL:

select NEW ST_Point(  -115.80622866283737, 50.396875473355024 ).ST_AsGeoJSON()  from dummy;

Returns: {"type": "Point", "coordinates": [-115.806,50.3969]}

select NEW ST_Point(  -115.80622866283737, 50.396875473355024 ).ST_AsWKT()  from dummy;

Returns: POINT (-115.806228662837370 50.396875473355024)

I was then curious about the accuracy lost by ST_AsGeoJSON() . Unfortunately there isn't a ST_DISTANCE_SPHERE function, but fortunately there is still ST_DISTANCE (which I think is  returns distance in degrees).  I think then with a rough and ready  formula to converting degrees into miles (1 degree = 60 minutes,  1 minute = 1 Mile, 1 Mile = 1852 Metres)  then I might be able to calculate the accuracy lost. [where would we be without Google]

So perhaps the SQL to check the inaccuracy of ST_AsGeoJSON()  is:

select NEW ST_Point(  -115.80622866283737, 50.396875473355024 ).ST_Distance(NEW ST_Point(  -115.8062, 50.3969)) * 60 * 1852 from dummy;

Returns: 4.2388916015625  metres

Interestingly the ST_AsGeoJSON() rounding  is also dependant on distance from 0.

e.g.

select NEW ST_Point(  1.00005, 1.00005 ).ST_AsGeoJSON()  from dummy;

Returns: {"type": "Point", "coordinates": [1.00005,1.00005]}

select NEW ST_Point(  100.00005, 100.000005 ).ST_AsGeoJSON()  from dummy;

Returns: {"type": "Point", "coordinates": [100,100]}

It looks like HANA ST_AsGeoJSON()  function handles a max 6 digits length. hmmmmm

I wonder if there is some undocumented HANA feature to increase this???

------------------------------------------------------------------------------------------------------

So IF my calculation is correct then the maximum default inaccuracy of ST_AsGeoJSON()  might be:

select NEW ST_Point(  100, 100 ).ST_Distance(NEW ST_Point(  100.0005, 100.0005)) * 60 * 1852 from dummy;

Result: 78.52546691894531  metres

So looks like I can't rely on ST_AsGeoJSON()  to plot fruit trees after all

If I need to improve accuracy I may need to use ST_AsWKT()  and write some custom java script to reformat into GeoJson manually. 

If you think I've made some glaringly mistake in my observation or distance formulation then I really welcome the feedback.

Thanks

Aron

[former_member206741]

Hi Jon-Paul,

You came across a bug

The precision loss in ST_AsGeoJSON() was a bug in the GeoJson formatter, that is already found and fixed. Regretfully, there is no workaround you can deploy to make it work…

This fix is planned to be shipped with revision 72. Sorry, for the inconvenience.

Cheers

Gerrit

[former_member182500]

OK Gerrit, good to know, thanks for the update.

[former_member182500]

I guess the precision loss with ST_AsGeoJSON() didn't make it to Rev. 72 (as all values get truncated with this in my AWS Rev 72)?

This is an example of what can happen when you get precision loss.

With precision

Nice "organic" shapes in red/yellow

With precision loss

Blocky and/or corrupted polygons when redrawn.

[former_member182500]

For those that are interested here's a JSFiddle to convert WKT to JSON when working with google maps

jQuery + google maps + WKT to JSON - JSFiddle

(originally another JSFiddle altered for my polygon, sorry cant find link to original)

Now I have precision back when using WKT from Hana then convert to JSON when working with google map paths.

[former_member182500]

(Note - extracted from original post at )

I've found that performing a ST_AsGeoJSON returns a truncated coordinate.

For example you have an INSERT into ST_GEOMETRY of 115.80622866283737, with ST_AsGeoJSON you get back something like 115.80622.

Either that or the shape is being truncated upon INSERT according to some stipulation of the SRID.

[former_member182500]

Latest update - Unable to natively instance and consume spatial data within hana SQLScripts or information views.

However consumption alone within XSJS is relatively straight-forward via the much-loved prepared statement

Where I have constant test longitude and latitude for test purposes can of course always be replaced by elements within an array for example.  As you will see it's not too sophisticated, simply offering possibility to inspect returned result set.  Look forward to enhancements that allow creation of variable instances of type ST_GEOMETRY.

I very much welcome correction or additions as it adds to the knowledge base.

function testSpatial(){

       var query,

            pstmt,

            conn = $.db.getConnection("abc.def.services::conn");

       var geo = {

            longitude: 1.23456,

            latitude:  76.54321,

       };

       var messageText = '';

       try {

            query = 'select ID from "SCHEMA"."SPATIALSHAPES" where SHAPE.ST_Contains( new                ST_Point(' + geo.longitude + ',' + geo.latitude + ') ) = 1';

            pstmt = conn.prepareStatement(query);

            var rs = pstmt.executeQuery();

            while (rs.next()) {

                 messageText = rs.getString(1);

            }

            var bodyContent = JSON.stringify({

                   "data": messageText

             });

            $.response.contentType = "application/json";

            $.response.setBody(bodyContent);

            $.response.status = $.net.http.OK;

       } catch(e) {

            $.response.status = $.net.http.INTERNAL_SERVER_ERROR;

            $.response.setBody(e.message);

       } finally {

            conn.close();

       }

}

testSpatial();

Note I have concatenated the longitude and latitude, for the life of me I could not use placeholder ? and pstmt.setDecimal(1,geo.longitude) and pstmt.setDecimal(2,geo.latitude) for both longitude and latitude - worked for one or the other, but not both - where both used I kept getting "invalid number" error on prepared statement - explanation welcomed

[Former Member]

I played with it a bit and got pstmt.setDecimal   working for both if the query looks like:

query = 'select ID from SCHEMA.SPATIALSHAPES where SHAPE.ST_Contains( new ST_Point( (select TO_DECIMAL(?, 9, 6)  from dummy) ,(select TO_DECIMAL(?, 9, 6)  from dummy) ) ) = 1';

I guess some type conversion is going wrong behind the scenes.  Bug or design feature  I wonder?

I'd stick with your concat option though.  It's much easier to read 

[former_member182500]

Yeah, thanks for joining the spatial self-help group, welcome!

You experienced the type conversion issue yourself?  Thanks for the workaround, and yes, I'll keep it simple with concat.

[Former Member]

Yep, I hit the same error as you but I'm just guessing it's type related based on some simple tests, the associated error messages I received and the workaround I mentioned. Quite frustrating but also Fun in equal measure

[Former Member]

Hi, I've been using this approach with my application.

But after the update to SP8, it now throws an internal server error.

So I got the code and tested in the SQL console in Hana Studio, and it shows the following error:

SAP DBTech JDBC: [264]: invalid datatype: __eqwithnull__ Cannot compare Locator and Locator

Debugging even further it was caused by the statement in the join:

S."StateShape".ST_Contains( NEW ST_POINT (I."xCoordinate", I."yCoordinate")) = 1

Do you have any workarounds on this? Thanks!

[former_member182500]

I wanted to process a geospatial dataset containing latitude and longitude references, comparing each of those to a given lat/lng reference via inbound parameters for instance, and planned to do this with SQLscript.  Seemed reasonable.

• Unable to define inbound parameters with a spatial type.

• Following fails, so I dont see a way of working with a simple local variable.

          declare lvGeo ST_POLYGON;

• Following also fails within SQLScript ("Could not create catalog object: invalid datatype; ST_GEOMETRY")
  

          create local temporary table #test_table(pointFrom ST_GEOMETRY, pointTo ST_GEOMETRY );

          or

          create global temporary table test_table(pointFrom ST_GEOMETRY, pointTo ST_GEOMETRY );

• I can however create a global table type successfully from the SQL console
  

     CREATE TYPE "ABC"."def.data::GEO.GeoFromTo" AS TABLE ( "GEOFROM" ST_GEOMETRY, "GEOTO" ST_GEOMETRY );

          and can do the following to instance my geospatial type into say an "out" table variable:

          SELECT NEW ST_POLYGON() FROM DUMMY;

I could also create a dummy table with one row of several geospatial containers to work with, however to me that would appear to necessitate working with 2 cursors, one for the large gepspatial dataset and another for the reference/comparison dataset, as I can only work with these special types with table columns?

Currently there are workarounds to define the model outside the preferred CDS yet limitations on how to consume the data easily.

Developers would welcome any examples of consumption of datasets containing geospatial data types within SQLScript or indeed analytic or calculation views that utilise geospatial methods like ST_DISTANCE.

Any other ideas or correction most most welcome, thanks.

[former_member206741]

Hi Jon-Paul,

Regretfully, SQL-Script is not enabled at present but we are working on it now.

The first step is to allow spatial functions in procedures; parameters however must be passed in a serialized format.

This example is not working on revision 70, but in our internal systems. I will let you know in which revision it will be available to you:

>>

CREATE TYPE tt_shapes AS TABLE (shape st_geometry);

CREATE PROCEDURE GetMyShapes(IN in_prm VARCHAR(255), OUT out_shapes tt_shapes) LANGUAGE SQLSCRIPT READS SQL DATA AS

BEGIN

out_shapes = SELECT shape FROM shapes WHERE shape.ST_Within(new ST_Polygon(:in_prm)) = 1;

END;

<<

Would this already be useful for you?

Cheers

Gerrit

[former_member182500]

Hi Gerrit, and welcome to the thread!  It's really appreciated to have one of the SAP Hana Spatial team contributing.

I would happily take the above solution, I'm sure this would be a welcome addition

[former_member182500]

Want to add some geospatial data - an example here (key ID, ST_GEOMETRY).  Note I can also specify the SRID here if I want the data to specifically use the WSG84 system (and not the system default).

Note this polygon is created from longitude and latitude values, in WKT format, with the longitude first.

INSERT INTO SpatialShapes VALUES(1, new ST_POLYGON('POLYGON((7.392836 45.475097, 7.893152 45.475018, 7.893134 45.474962, 7.892817 45.475057, 7.392836 45.475097))',4326));

[former_member182500]

As of SPS07 we are unable to add columns of a spatial type either via CDS (.hdbdd) or .hdbtable.  If supported by analytic or calculation views I would dearly love to see use-case examples.

(Note - Thomas Jung kindly commented on another thread "They are planned to be added to HDBDD (CDS) in the future. I can't say if that will be SP8 or SP9 yet.")

Nevertheless, to add a spatial type column to an existing table via the SQLConsole here's an example - note this specifically indicates the column contains spatial data referencing the 4326 WSG84 system.

ALTER TABLE SPATIALSHAPES ADD (geolocation ST_POINT(4326));

[former_member182500]

There are 3 spatial reference systems defined, these help us to identify and describe our spatial world, like modelling our Earth to describe the ellipsoid (it's no perfect circle!) when working with geographic data,such as the semi major axis and inverse flattening:

select * from ST_SPATIAL_REFERENCE_SYSTEMS;

[former_member182500]

With spatial data we can describe real world objects like addresses, buildings, rivers, roads and countries with artefact types like points, lines and polygons.  Take a look at page 9 onwards of the Reference guide which details the spatial data types.