ST_Read?

[yutannihilation]

The README says SedonaDB supports other file formats than GeoParquet.

📁 Format Flexibility: Supports legacy and modern file formats including GeoParquet, Shapefile, GeoJSON

I'm not sure whether this "support" means that SedonaDB itself can read these files, but I think it would be handy to load data within SQL if the data is small (e.g. we want to join a GeoJSON containing administrative boundaries). Also, probably the CLI needs this.

I know this violates the interoperability with SedonaSpark, so I'm not sure if you like the idea. Are there any plan to provide such a function?

[jiayuasu]

agree. The way it works with geojson now is via geopandas, which is not good.

This is how it works now in SedonaDB SQL: https://sedona.apache.org/sedonadb/latest/reference/sql/. I think we can add native support for shapefile as well.

"SELECT * FROM 'https://raw.githubusercontent.com/geoarrow/geoarrow-data/v0.2.0/natural-earth/files/natural-earth_cities_geo.parquet'").show()

Andy has a similar request on DataFusion: apache/datafusion#3773

SELECT * FROM read_parquet('https://raw.githubusercontent.com/geoarrow/geoarrow-data/v0.2.0/natural-earth/files/natural-earth_cities_geo.parquet)

In Sedona Spark:

SELECT * FROM csv.`test.csv`

SELECT * FROM parquet.`test.parquet`

ST_Read is more like a DuckDB's own syntax for GDAL. We can have a similar one for all cases but we don't need to maintain the same API as DuckDB like explicit spatial filters in ST_Read.

We need to figure this out for raster as well. CC @jesspav

[paleolimbot]

Thanks for opening!

There's a few things going on here:

• Data sources ("tables") are DataFusion TableProviders. TableProviders receive a "projection" (requested columns) and a filter expression.
• A common special case of the table provider is reading one or more "file"s (specifically, objects on an object store). These are implemented using the FileFormat API (for which a TableProvider can be constructed using a ListingTable). This is how we implement (Geo)Parquet (by wrapping the ParquetFileFormat), and it's what makes SELECT * FROM 'foofy.parquet' work in our SQL. I think technically it also powers COPY TO/FROM but I never actually remember the syntax for that long enough to use it.
• st_read() (or read_parquet()) are user-defined table functions. Table functions are just functions that accept scalar values and return a TableProvider. A slight hiccup is that they aren't async and need a fully resolved schema, so we have to have an Arc<Runtime> + block_on for most realistic applications, including constructing and returning a ListingTable
• We focused on providing read_xxx() functions in Python/R before SQL because they're easier to use and easier for a user to access the documentation while typing the code. Conceptually the arguments are the same.
• We want both SedonaDB and SedonaSpark to be great and are happy to merge great ideas to either one! (They have to start somewhere!)
• I'm working on feat(rust/sedona-datasource): Implement generic RecordBatchReader-based format #251 to make wrapping ArrowArrayStream-based formats (like GDAL!) easier. Basically, if you can get me an Arrow Schema and an Arrow record batch reader from a URI, you get the multi-file reader for free. I think I can have that ready tomorrow.
• All this applies equally to raster (it's just a column data type)

[yutannihilation]

This is how it works now in SedonaDB SQL: https://sedona.apache.org/sedonadb/latest/reference/sql/.

Oh, thanks, I simply didn't notice FROM clause already allows a path to a JSON/CSV file.

ST_Read is more like a DuckDB's own syntax for GDAL. We can have a similar one for all cases but we don't need to maintain the same API as DuckDB

Totally agreed!

• We focused on providing read_xxx() functions in Python/R before SQL because they're easier to use and easier for a user to access the documentation while typing the code. Conceptually the arguments are the same.

Thanks for clarifying. This sounds reasonable to me.