search

duckdb / duckdb_spatial

MIT License
473 stars 35 forks source link

ST_Point2D and ordering of coordinates #416

Open CGenie opened 2 weeks ago

CGenie commented 2 weeks ago

Hello,

Maybe it's a silly question but I'm kind of lost on how to use ST_Point2D. Is it ST_Point2D(lat, lon) or ST_Point2D(lon, lat)? It seems postgis prefers the lon, lat ordering. However, it distinguishes between GEOMETRY (i.e. planar points) and GEOGRAPHY (i.e. spherical lat/lon) coordinates and, at least in the latter case, the lon, lat ordering is used.

DuckDB has the ST_Distance function https://duckdb.org/docs/extensions/spatial/functions.html#st_distance_sphere and the docs say:

The input is expected to be in WGS84 (EPSG:4326) coordinates, using a [latitude, longitude] axis order. There are 2 signatures for that function: ST_Distance(ST_POINT2D, ST_POINT2D) and ST_Distance(GEOMETRY, GEOMETRY).

Similarly, there are 2 functions: ST_Point2D which returns ST_POINT2D and ST_Point which returns GEOMETRY.

I made some tests some time ago, where I used this as a tool: https://www.calculator.net/distance-calculator.html

My experiments showed more correct results when using ST_Point2D(lat, lon) for the ST_Distance function than when using ST_Point2D(lon, lat).

Maxxen commented 2 weeks ago

Almost all functions, except if stated otherwise (like those suffixed with _sphere or _spheroid) do not care what axis order or coordinate system you use. In the planar case, it doesn't matter. DuckDB doesn't track or store the SRID of geometries so it is up to you to know what coordinate system you use and what the axis order convention is.

Like stated, ST_Distance_Sphere will only give correct results when the x is the latitude, and y is the longitude. If you plan to work with spherical distances you should construct your points in that order. In contrast you might find yourself working with geometries in e.g. EPSG:3857 in which case the CRS mandates that geometries should use easting/northing axis order, but since it is a planar coordinate system it doesn't actually matter when computing e.g. distances or areas.

Maxxen commented 2 weeks ago

You may want to read my posts in this thread https://github.com/duckdb/duckdb_spatial/issues/16#issuecomment-2327011935