How to read PostgreSQL spatial types supplied as strings

[stenon-olivier]

In PostgreSQL I can simply do, e.g.,

SELECT '(52.400931,13.05914)'::point;

but that fails in DuckDb, even when using point-2d instead. I was eventually able to wrangle it like so:

SELECT regexp_extract(location, '^\\(([-0-9.]+),*([-0-9.]+)\\)$', ['x', 'y'])::point_2d

(location being the name of that column), i.e., converting the string into a struct first. This is for a point, though; doing the same thing for more complex types like (multi-)polygons would be quite convoluted, if possible at all. Is there a simpler way that I missed?

Note: Reposted from the main duckdb repo because it is more appropriate for this one; the earlier one was closed by me.

[Maxxen]

I think the conventional way to do this for the GEOMETRY type is to use WKT syntax and a cast. E.g.
SELECT 'POINT (1 2)'::GEOMETRY

This works for the columnar types POINT_2D etc too, but as they are based on DuckDB's STRUCT and LIST types you should also be able to use struct/list-literal syntax. E.g. SELECT {'x': 1, 'y': 2}::POINT_2D; or SELECT [{'x': 1, 'y': 2}, {'x': 3, 'y': 4}]::LINESTRING_2D;

[Maxxen]

Actually, you don't even need the quotes for structs/lists, even SELECT [{x: 1, y: 2}, {x: 3, y: 4}]::LINESTRING_2D; works

[stenon-olivier]

The problem is that they are available to me exactly in the string form mentioned at the beginning, e.g., '(52.400931,13.05914)' (from an old data dump). But you pointed me in the right direction, i.e., this will do what I needed:

SELECT ('POINT ' || replace(location, ',', ' '))::geometry::point_2d

[Maxxen]

Ah sorry, I misunderstood the question! Yeah I don't think we have anything that converts from that format specifically, but Im glad you found a workaround! Ill think on it a bit to see if this is something we can support better in the future.

[stenon-olivier]

It's the text format for the "native" PostgreSQL spatial types. Probably not many people use them since we have PostGIS, though.