search

duckdb / duckdb_spatial

MIT License
492 stars 41 forks source link

`ST_Transform` flips coords #295

Closed Loreith closed 7 months ago

Loreith commented 8 months ago

When transforming "EPSG:3577" to "EPSG:4326" (GDA94 to WGS84) ST_Transform gives results that are out of this world!

To reproduce:

select 'POINT(2049067.6836069894 -3152401.9342161086)'::geometry, st_transform('POINT(2049067.6836069894 -3152401.9342161086)'::geometry, 'EPSG:3577', 'EPSG:4326')

Results: POINT (2049067.6836069894 -3152401.9342161086), POINT (-27.49979860000002 153.08968229999996).

I'm testing this on 0.10.1 after having run FORCE INSTALL spatial for the latest version. Apologies if I'm missing something obvious!

jc9677 commented 8 months ago

There's an optional 'always_xy' boolean parameter for ST_Transform. You can pass 'true' after your coordinate crs parameters ...'EPSG:3577', 'EPSG:4326', true)

Reference: https://github.com/duckdb/duckdb_spatial/issues/179

On Wed, Mar 27, 2024, 1:53 AM Wren @.***> wrote:

When transforming "EPSG:3577" to "EPSG:4326" (GDA94 to WGS84) ST_Transform gives results that are out of this world!

To reproduce:

select 'POINT(2049067.6836069894 -3152401.9342161086)'::geometry, st_transform('POINT(2049067.6836069894 -3152401.9342161086)'::geometry, 'EPSG:3577', 'EPSG:4326')

Results: POINT (2049067.6836069894 -3152401.9342161086), POINT (-27.49979860000002 153.08968229999996).

I'm testing this on 0.10.1 after having run FORCE INSTALL spatial for the latest version. Apologies if I'm missing something obvious!

— Reply to this email directly, view it on GitHub https://github.com/duckdb/duckdb_spatial/issues/295, or unsubscribe https://github.com/notifications/unsubscribe-auth/APROP4WHK6EM3CDVYNNTZXTY2JNHNAVCNFSM6AAAAABFKHCQXOVHI2DSMVQWIX3LMV43ASLTON2WKOZSGIYDSOJTGM2DQMA . You are receiving this because you are subscribed to this thread.Message ID: @.***>

Maxxen commented 7 months ago

Like mentioned above this is expected, DuckDB doesn't currently care what the x and y coordinates represents and st_transform defaults to respecting the axis order defined by the CRS authority. You can pass a boolean as an optional third argument to force st_transform to always expect [lon/lat] axis order regardless of what projection you use. The new docs are still a bit rough around the edges but I've attempted to document this better here: . https://github.com/duckdb/duckdb_spatial/blob/main/docs/functions.md#st_transform

Loreith commented 7 months ago

Ah that makes sense, thanks both for explaining!