Find spatial reference of a geometry column

[markruddy]

Hi, is there an equivalent of the PostGIS statement:

SELECT ST_SRID(geom) FROM my_table;

In duckdb spatial extension? I'd like to be able to query the SRS of tables to troubleshoot spatial queries but can't find ST_SRID implemented(?) Is there an alternative approach to get this information through table properties?

Many thanks

[Maxxen]

Hi! duckdb spatial does not store the SRID inside the geometry type, its recommended to keep it in a separate column for now and in the future ST_Read might be able to output it as well. But for now you can use st_read_meta in combination with UNNEST to extract the projection information from a st_read compatible dataset.

[kbatchu]

Hi Maxxen, Can you please give an example of the sql query that uses the combination of st_read_meta and UNNEST to parse out the projection information for a geoparquet file? I am stuck here at this point unable to figure out how to parse the result. I am using this query:

The result I get is this and I am not sure how to parse the result in Javascript to extract the projection info.

Thanks

[Maxxen]

You can't use st_read_meta for parquet files, parquet files are handled by duckdbs parquet extension and so in this case to read geoparquet metadata you should use the parquet_kv_metadata function. Example:

SELECT decode(value) as col FROM parquet_kv_metadata('http://github.dev/opengeospatial/geoparquet/blob/main/examples/example.parquet');

That's going to give you the geoparquet metadata as a JSON.

[kbatchu]

Thank you. I got it now.