geoparquet optimized creation: how to set right SRID?

[aborruso]

Hi,
I have read the great doc "Best Practices for Distributing GeoParquet" and I have used this kind of query on my input file

copy (SELECT *
  FROM geo_table
  ORDER BY ST_Hilbert(
      geom,
      (
          SELECT ST_Extent(ST_Extent_Agg(COLUMNS(geom)))::BOX_2D
          FROM fsq
      )
  )) TO 'out.parquet' (FORMAT 'parquet', COMPRESSION 'zstd');

My input is an EPSG:3035 shapefile.

The geoparquet output file I create has the right coordinates values (the same of the input), but this file is wrong associated to EPSG:4326.
How do I get a correct metadata assignment? Am I required to convert everything to 4326 first?

Thank you

[Maxxen]

Hello!
Sorry, this isn't currently possible with the current GeoParquet support in DuckDB. Because geometry types don't track/store their projection information we can't convey it to the parquet writer, and we have to write the "default" according to the standard, which is 4326.

[cholmes]

@aborruso - there is a bit of a hack that you can do to get the metadata in, by just taking the DuckDB output and then writing a new output that sets the metadata right.

I do this in my geoparquet-tools library - if you use projected data it'll read the CRS, save it, and then rewrite the DuckDB output using pyarrow.

I have not added support in for other input formats, but you should be able to use gdal or duckdb to convert the shapefile to geoparquet, and then use mine to sort and write it out. Or feel free to just borrow the code / use pyarrow directly.

[aborruso]

I do this in my geoparquet-tools library - if you use projected data it'll read the CRS, save it, and then rewrite the DuckDB output using pyarrow.

Using gdal and some options, I have something similar to your great tool. For my goals, I only needed to

ogr2ogr -f "parquet" output.parquet input.shp -lco compression=zstd -lco SORT_BY_BBOX=YES  -lco ROW_GROUP_SIZE=100000

I love GDAL, and I have compiled it so that I have geoparquet format and ZSTD compression enabled.

[cholmes]

Nice. Sorry I didn't reply to your earlier message, but glad you found another way. Ogr is always a great option, and definitely has some of the best geoparquet support. I didn't use it since it can be a pain to seamlessly install on all platforms, and my hope is I can eventually drop the pyarrow dependency.

The sort_by_bbox option actually creates an interim geopackage to do the sort. An alternate workflow you can use is to use DuckDB to do the sort and then just use gdal with -t_srs (I think) to overwrite the crs and get the proper geoparquet metadata. But yours works great too, thanks for sharing! I'm going to try to include several options in the best practices pr.

[aborruso]

Hi @cholmes your tool is great, thank you.

However, let me understand if I have figured out how I can do it independently:

• first I must create in example a gpkg file with duckdb and set its right epsg;
• then I must use pyarrow to read the gpkg and convert it to geoparquet.

Is it right?

When duckdb is compatible with the geoparquet v1.1.0 specification, it will no longer be necessary and I can use duckdb directly. Am I right @Maxxen ?

Thank you