Geometry in daft?

[amitschang]

Hey all! As someone who's occasionally had a use-case for geometry in dataframes (geospark/geopandas) it seems like Geometry types are a good fit also for Daft, given its existing support of multimodal data and its expression API in rust enabling efficient operations. A lot of projects seem to have separate packages or extensions for geometry support, but I wonder if it would be nice "out-of-the-box" with daft.

As it turns out, there are decent looking crates available for geometry(with geographic functions as well) both in pure rust and bindings for GEOS and PROJ (at https://github.com/georust). Since I'm also interested in learning more about the internals of daft, and contributing, I thought I'd go about adding a geometry type and enough operations to showcase how it works and learn something along the way.

Is this of interest?

Here is a real session as an example, starting with a parquet file with binary encoded geometry in a specific column: (adapted from data from https://github.com/geopandas/geodatasets):

>>> import daft
>>> from daft import col
>>> df = daft.read_parquet('/tmp/chicago-airbnb.parquet').select('community', 'num_crimes', 'geom').with_column('geom', col('geom').geo.
decode())
>>> df.show(3)
╭─────────────┬────────────┬────────────╮                                                                                               
│ community   ┆ num_crimes ┆ geom       │                                                                                               
│ ---         ┆ ---        ┆ ---        │
│ Utf8        ┆ Int64      ┆ Geometry   │
╞═════════════╪════════════╪════════════╡
│ DOUGLAS     ┆ 5013       ┆ <Geometry> │
├╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤
│ OAKLAND     ┆ 1306       ┆ <Geometry> │
├╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤
│ FULLER PARK ┆ 1764       ┆ <Geometry> │
╰─────────────┴────────────┴────────────╯

(Showing first 3 rows)
>>> df.select(col('geom').geo.encode(text=True)).show(3)
╭────────────────────────────────╮                                                                                                      
│ geom                           │                                                                                                      
│ ---                            │
│ Utf8                           │
╞════════════════════════════════╡
│ POLYGON((-87.60914087617012 4… │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ POLYGON((-87.592152838785 41.… │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ POLYGON((-87.62879823732874 4… │
╰────────────────────────────────╯

(Showing first 3 rows)
>>> df.select(col('geom').geo.area()).show(3)
╭────────────────────────╮                                                                                                              
│ geom                   │                                                                                                              
│ ---                    │
│ Float64                │
╞════════════════════════╡
│ 0.0004632396386967219  │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ 0.00017028325324268715 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ 0.00020046991616370875 │
╰────────────────────────╯

(Showing first 3 rows)
>>> df2 = df.sort('num_crimes', True).limit(1)
>>> df.join(df2, daft.lit(1)).select('community', 'right.community', col('geom').geo.distance(col('right.geom')).alias('poly_dist'), col
('geom').geo.centroid().geo.distance(col('right.geom').geo.centroid()).alias('centroid_dist')).sort('centroid_dist').show(10)
╭────────────────────┬─────────────────┬──────────────────────┬──────────────────────╮                                                  
│ community          ┆ right.community ┆ poly_dist            ┆ centroid_dist        │                                                  
│ ---                ┆ ---             ┆ ---                  ┆ ---                  │                                                  
│ Utf8               ┆ Utf8            ┆ Float64              ┆ Float64              │                                                  
╞════════════════════╪═════════════════╪══════════════════════╪══════════════════════╡                                                  
│ AUSTIN             ┆ AUSTIN          ┆ 0                    ┆ 0                    │                                                  
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ BELMONT CRAGIN     ┆ AUSTIN          ┆ 0                    ┆ 0.03324546556430101  │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ WEST GARFIELD PARK ┆ AUSTIN          ┆ 0                    ┆ 0.036352505945250586 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ HERMOSA            ┆ AUSTIN          ┆ 0                    ┆ 0.0414704680152373   │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ HUMBOLDT PARK      ┆ AUSTIN          ┆ 0                    ┆ 0.04261307331173538  │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ MONTCLARE          ┆ AUSTIN          ┆ 0                    ┆ 0.049580516093383625 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ NORTH LAWNDALE     ┆ AUSTIN          ┆ 0                    ┆ 0.057062347294397624 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ EAST GARFIELD PARK ┆ AUSTIN          ┆ 0.018797218262773596 ┆ 0.059195837761422704 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ PORTAGE PARK       ┆ AUSTIN          ┆ 0.02185354132017426  ┆ 0.05992755850551571  │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ DUNNING            ┆ AUSTIN          ┆ 0.011149895260400616 ┆ 0.06773359570805364  │
╰────────────────────┴─────────────────┴──────────────────────┴──────────────────────╯

(Showing first 10 rows)
>>>

The way this does it is similar (I think) to how duckdb does its non-native (simple features) geometry (https://github.com/duckdb/duckdb_spatial/blob/main/docs/internals.md), e.g. storing the well-known-binary (WKB) in the physical backing and doing serde on operation. Comes with a cost, but presumably this kind of geometry is flexible and would sit alongside native types (which would support only one geo type per column, iso mixed like this does).

Sorry for the long post! Thanks for looking 😄

[desmondcheongzx]

Definitely agree that to be truly multimodal we need geospatial support!

The fastest way to do this would be to bring in functionality via a third party library like GDAL or JTS.

A slightly less ergonomic approach, that users should already be able to do, is pull in these libraries and use them in user defined functions. In this case, geospatial workloads would work, but not be particularly fast or stable (lots of potential blowups memory-wise unless the system is aware of it and plans accordingly).

What I think would be a competitive advantage in terms of performance and stability, but which would happen much further down the road, would be native geospatial support. Native datatypes and internal representations, native kernels for geospatial expressions, native joins and indices on top of geospatial data. From experience (I was one of two engineers at Databricks working on geospatial), it's a whole can of worms that I don't think we can dedicate the effort to for the time being.

[amitschang]

@desmondcheongzx, thanks! I totally agree that native support for geometry types is an ultimate goal, but also much heavier lift indeed. I thought that eventually geoarrow might be part of the solution there given daft integration with arrow.

However, even then I wonder if the generic geometry type support might still be useful, its advantage being flexibility in representing multiple geometries and implementation-wise probably for a while will have more coverage in algorithms. Until such time as full native support would you see this type as a stop-gap? It seems more ergonomic, reusable, and probably more performant than python UDFs and simpler integration with SQL.

The way I've done (demonstrated above) is relatively low friction by defining a Geometry logical type backed by List(Uint8) (similar to Image type), representing WKB, then using geozero to decode/encode to a geo-type and geo library (https://github.com/georust/geo) methods on the resulting objects using ScalarUDF (also similar to image operations). Is this close to what you were thinking with bringing functionality via third party libraries? Or maybe missing an opportunity?

[desmondcheongzx]

Oh!! I somehow misread your post and didn't realize you already had a working version up and running. This is awesome @amitschang!

Backing Geometry by bytes that represent WKB (or maybe EWKB) makes sense to me as a starting point. If I understand your intent, we'd ingest WKB/WKT/GeoJSON etc into WKB, do some operations, then re-export into the desired format.

Any interest in opening up a PR and collaborating further?

It seems more ergonomic, reusable, and probably more performant than python UDFs and simpler integration with SQL.

Definitely agree with this. I also wouldn't be opposed to further integration with other external geospatial libraries. For example, we already have a user using H3 indices, but while custom H3 implementation > external H3 library, it's also true that external H3 library > H3 in UDFs.

[amitschang]

@desmondcheongzx, yeah, thats right! The working version I have is as you describe, basically the Geometry type is a "validated" wkb. To me this represents step 1, getting from text/wkb and some operations. Then later building out more operations and expanded I/O, e.g. adding geojson, shapefiles, etc. That is probably where more spatial library support could come in as well.

I'd definitely be into making a PR, I wanted to make sure that this was even a reasonable approach and fits within the broader vision. Before a PR I should do some cleanup in terms of naming etc. If you are interested in working together on this it would be great to get feedback 😄

BTW here is where I have my drafting version https://github.com/amitschang/Daft/tree/geotype