Geospatial POINT(x y) longitude and latitude switched?

IngoM2
Contributor

 

Hi, 

pretty much everywhere the way to notate a geolocation is: 1) latitude 2) longitute

https://geohack.toolforge.org/geohack.php?pagename=Geographische_Koordinaten&language=de&params=46.2...

or also Google Maps

 

Is it just me who finds it quite irritating that the the Exasol POINT function uses it the other way around: 1) longitude 2) latitude ?? 

 

 

SELECT --Exasol: POINT(longitude, latitude)
round(ST_DISTANCE(ST_TRANSFORM(ST_SetSRID('POINT(13.412300 52.522499)',4326),2163) , ST_TRANSFORM(ST_SetSRID('POINT(13.369460 52.525009)',4326),2163)) / 1000, 2) km_dist_alex_hbf
/* Expected per https://www.distance.to/Berlin-Alexanderplatz/Berlin-Hauptbahnhof
  Distance: 1.81 mi (2.91 km)
  Driving route: 3.16 mi (5.09 km)
  */
,round( ST_DISTANCE(ST_TRANSFORM(ST_SetSRID('POINT(12.336960 52.606590 )',4326),2163) , ST_TRANSFORM(ST_SetSRID('POINT(13.369460 52.525009 )',4326),2163))/1000 ,2) km_dist_rn_hbf
 /* Expected
  Distance: 43.73 mi (70.37 km)
  Driving route: 50.67 mi (81.55 km) */
, round(ST_DISTANCE(ST_TRANSFORM(ST_SetSRID('POINT (13.36963 52.52493 )',4326),2163) , ST_TRANSFORM(ST_SetSRID('POINT (-0.1233 51.5309 )',4326),2163))/1000, 2) km_dist_ber_lon;

 

 

 

Result:

KM_DIST_ALEX_HBF|KM_DIST_RN_HBF|KM_DIST_BER_LON|
----------------+--------------+---------------+
2.91| 69.29| 969.39|

 

1 ACCEPTED SOLUTION

johannes
Contributor

It's not just Exasol. Exasol follows the SQL/MM Spatial Standard. And there, geometric objects (like points) are not only used for world-related objects, but also for arbitrary positions of objects (e.g. pixels of an image, positions within a room or building, ...). The constructor of a POINT has the paramenters x and y. And as we know it from math, the x axis goes horizontaly ---> and the y axis goes vertically ^. So, back to geo-data: The axis on earth which goes horizontaly is the longitude, and the y-axis is the latitude.

See also the PostGIS documentation: "For geodetic coordinates, X is longitude and Y is latitude" -- https://postgis.net/docs/ST_Point.html

PS: Here is a really nice article about that topic: https://macwright.com/lonlat/ (Exasol uses the WKT format)

View solution in original post

2 REPLIES 2

exa-Chris
Community Manager
Community Manager

I just love when Prof @johannes answers with Science

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

johannes
Contributor

It's not just Exasol. Exasol follows the SQL/MM Spatial Standard. And there, geometric objects (like points) are not only used for world-related objects, but also for arbitrary positions of objects (e.g. pixels of an image, positions within a room or building, ...). The constructor of a POINT has the paramenters x and y. And as we know it from math, the x axis goes horizontaly ---> and the y axis goes vertically ^. So, back to geo-data: The axis on earth which goes horizontaly is the longitude, and the y-axis is the latitude.

See also the PostGIS documentation: "For geodetic coordinates, X is longitude and Y is latitude" -- https://postgis.net/docs/ST_Point.html

PS: Here is a really nice article about that topic: https://macwright.com/lonlat/ (Exasol uses the WKT format)

View solution in original post