Error while inserting data to column of data type ST_GEOMETRY()

[former_member702610]

Hi All,

I need your urgent help to solve an issue. I am trying to create one location dimension HANA view so that it can be consumed in SAC stories and enriched with geo map. I have one table containing data of Longitude and Latitude with Key as country code. I created one table "GEO_MAP" with Country code as key and Loc_dim column of type ST_GEOMETRY.

While trying to insert data in "GEO_MAP" table, getting below error:

SAP DBTech JDBC: [669]: spatial error: "my_schema"."GEO_MAP": line 11 col 1 (at pos 385): exception 1600310: '1.50000000000042.500000000000' is not a valid coordinate at position 6 of WKT POINT(1.50000000000042.500000000000) at "st_geometry" function

I am using logic "LOCATION_DIM" = new ST_GEOMETRY('POINT(' || "LONGITUDE" || '' || "LATITUDE" || ')',4326).ST_TRANSFORM(3857) to get the data.

Could you please help me in fixing this issue.

Regards,

Shruti

[mfath · Accepted solution]

Hej Shruti,

it seems like you are just missing a blank/space in your query:

'POINT(' || "LONGITUDE" || '[must be a blank in here to separate the values]' || "LATITUDE" || '

Regards, Markus

[former_member702610 · Accepted solution]

Hi Markus,

Thanks for providing your inputs. It solved my issue. But after adding space (which is correct), I am getting a new error "transformation error: tolerance condition error at "st_transform" function (at pos 0)".Same issue occurred while trying to apply similar logic in calculated column.

It would be so grateful if you can provide your valuable inputs.

Regards,

Shruti

[stefan_uhrig · Accepted solution]

Hi Shruti,

When transforming GPS-coordinates to spatial reference system 3857, the latitude must be in the range [-85.06,85.06]. Points with a latitude outside this range cannot be displayed on maps based on spatial reference system 3857 (WebMercator).

You can either set the geometry to NULL in those cases or you can clamp the latitude to the valid range:

SELECT
  CASE
    WHEN ABS(LATITUDE) > 85.06 THEN NULL
    ELSE ST_GeomFromText('POINT(' || LONGITUDE || ' ' || LATITUDE ||')', 4326).ST_Transform(3857)
  END
FROM TAB;

SELECT
  CASE
    WHEN LATITUDE > 85.06 THEN ST_GeomFromText('POINT(' || LONGITUDE || ' 85.06)', 4326).ST_Transform(3857)
    WHEN LATITUDE < -85.06 THEN ST_GeomFromText('POINT(' || LONGITUDE || ' -85.06)', 4326).ST_Transform(3857)
    ELSE ST_GeomFromText('POINT(' || LONGITUDE || ' ' || LATITUDE ||')', 4326).ST_Transform(3857)
  END
FROM TAB;

[former_member702610 · Accepted solution]

Thanks Stefan for your valuable inputs. It resolved my issue.