[postgis-devel] ST_AsFlatGeobuf / ST_FromFlatGeobuf ?

[Paul Ramsey]

> On Sep 27, 2020, at 10:17 AM, Björn Harrtell <bjorn.harrtell at gmail.com> wrote:
> 
> Paul,
> 
> I have been trying to wrap my head around how an analogue to jsonb_to_record would be useful for FlatGeobuf and can't figure it out. In fact, I have trouble coming up with anything *really* useful as user exposed PostGIS functions. The problem is that there is no way to work with parts of a binary buffer except vis the generic file system aux functions which does not feel right to use for stuff like this.

Well, yes, the fact that your format is "streamable" gets left on the table here. That doesn't obviate the utility of being able to consume a complete recordset in one "slurp", assuming the format itself has traction. Really we should prototype this with an ST_GeomFromGeoJSON() function that eats complete FeatureCollections.

> A plain ST_AsFlatGeobuf aggregate and a setof record returning ST_FromFlatGeobuf could provide some usefulness perhaps but would be limited by max tuple size and not really the desired "building blocks".

Well, I think if you want the format to have wider usage, the ST_AsFlagGeoBuf is pretty key, as it opens up the ability to generate output to every language that can connect to Postgres. Similarly the ingest takes the problem of "how do I read this" away for anyone with a running Postgres/PostGIS. 

> Optimally I would want to be able to process a FlatGeobuf binary from a steam into rows and vice versa but the only way that I can think of to do that is to make COPY accept and produce FlatGeobuf.

Yeah, I think you're overthinking this a bit :) people who need to stream into the database can use ogr2ogr. 

P

> 
> Did you have something in mind that I'm not seeing?
> 
> Den ons 23 sep. 2020 kl 17:22 skrev Paul Ramsey <pramsey at cleverelephant.ca>:
> 
> 
> > On Sep 23, 2020, at 12:34 AM, Björn Harrtell <bjorn.harrtell at gmail.com> wrote:
> > 
> > Is flatgeobuf an alternative to shapefile, to MVT, or both? Do you
> > expect people to use it to extract full datasets or only parts of it
> > at a time?
> 
> I think it's a worthy addition, and having the format in PostGIS will promote usage (orobouros) because that makes the format available to all connecting languages, systems, and it doesn't need to be reimplemented in 50 locations.
> 
> > One thing I'm not sure about is `ST_FromFlatGeobuf`. I would expect
> > that to return a table/tuple, but AFAIK there is no way to change the
> > output format of a function based on the input, so I think I would
> > expect something like a fgb2pgsql cli and not a SQL function.
> 
> See jsonb_to_record(jsonb), I think this is doable for geojson as well as [flat]geobuf.
> 
> P.
>