Converting X and Y cordinates to Latitude and Longitude

[Former Member]

Hi All,

I got a request to convert X and Y coordinates to Latitude and Longitude.

Is this possible in Webi or via SQL querry.

Regards

Jagdeep Singh

[former_member311928]

I guess it is possible with HANA Spatial functions. Maybe these hints will help you...

I have created X, Y coordinate within World Geodetic System 84 (https://en.wikipedia.org/wiki/World_Geodetic_System#WGS84) from Longitude and Latitude, like this:

ALTERTABLE TBL_KYSTV ADD (STGEO ST_GEOMETRY (4326));

UPDATE TBL_KYSTV SET STGEO = new ST_POINT('POINT('||TBL_KYSTV.LONGITUDE||' '||TBL_KYSTV.LATITUDE||')',4326);

Where 4326 is the SRID (http://spatialreference.org/ref/epsg/wgs-84/)

I guess you should seek to do this operation in the opposite order.

Look at HANA Spatial documentation: https://help.sap.com/viewer/cbbbfc20871e4559abfd45a78ad58c02/1.0.12/en-US/7a27fad8787c10148c589fe6bf...

[Former Member]

i am having data in the form of X and Y co-ordinates

I want to convert these Co - ordinates to Latitude and longitude, and to put this data into the Table and use that table to display data in the Report.

example

X axis Y axis

460776.1125 2905247

[former_member311928]

Since you have X and Y you want to convert to Longitude and Latitude, I assume your X and Y coordinates corresponds to a place on earth. If so, your coordinates are probably of type WGS 84 (planar), that means SRID = 1000004326.

I have not tried it myself, but I guess this will do the work:

LONGITUDE = new ST_POINT('POINT('||X_COORDINATE||' '||Y_COORDINATE||')',1000004326).ST_Transform(4326).ST_X()

LATITUDE = new ST_POINT('POINT('||X_COORDINATE||' '||Y_COORDINATE||')',1000004326).ST_Transform(4326).ST_Y()

ST_X() and ST_Y() are functions to extract X and Y coordinate of ST_POINT respectively. And since the point was transformed to SRID 4326 (ST_Transform(4326)), the ST_POINT is of the type that holds longitude and latidude as its coordinates

Output type, that is the type of LONGITUDE and LATITUDE will be DOUBLE.

[Former Member]

I am having the data into the Oracle DB in the form X and Y axis and i need to put the data into the form of Latitude and Longitude.

The functions you specified are not valid in oracle.

could you please help me in this.

steps you can share or SQL query to fetch the data in the form of Latitude and longitude.

[former_member311928]

Ok, then you should probably do some reading on spatial data in Oracle. https://docs.oracle.com/en/database/oracle/oracle-database/12.2/spatl/spatial-concepts.html#GUID-67E...

Similar principles probably apply, but you will have to learn the Oracle syntax for this.

Although spatial is not the most standard SQL, it is a part of HANA extension of SQL. SQL is not a standard, and varies from db to db, so Oracle should have implemented this differently.