# Spatial join with dataframes

**URL:** <https://discourse.julialang.org/t/spatial-join-with-dataframes/93241>\
**Category:** General Usage\
**Tags:** dataframes, geo\
**Created:** [January 20, 2023, 1:07am UTC](https://discourse.julialang.org/t/spatial-join-with-dataframes/93241 "2023-01-20T01:07:03Z")\
**Posts on this page:** 8\
**Page:** 2

<div class="post-metadata">

**Author:** ![evetion](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/evetion/32/22679_2.png) [@evetion](https://discourse.julialang.org/u/evetion)\
**Post date:** [January 25, 2023, 8:48pm UTC](https://discourse.julialang.org/t/spatial-join-with-dataframes/93241/21 "2023-01-25T20:48:04Z")

</div>

> [@Dan](#):
>
> Here is an attempt to use FlexiJoins by first finding a bounding box for each geometry. This reduces the quadratic complexity of the query (depending on how annoying the geometries are):

That’s a very nice example! Might I suggest to use [GeoInterface](https://juliageo.org/GeoInterface.jl/stable/) for most of these operations? In that way, this would also work on Shapefile geometries, etc.

```julia
GeoInterface.x(point) # to get the x coordinate, which is a shortcut for:
GeoInterface.getcoord(point, 1) # for x, or without the 1 for a generator

GeoInterface.extent(geom) # Gives you an Extents.Extent object, which is a NamedTuple
Extent(X = (1.89038215436677e6, 1.89038215436677e6), Y = (5.50127974222017e6, 5.50127974222017e6))

```

---

<div class="post-metadata">

**Author:** ![evetion](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/evetion/32/22679_2.png) [@evetion](https://discourse.julialang.org/u/evetion)\
**Post date:** [January 25, 2023, 8:52pm UTC](https://discourse.julialang.org/t/spatial-join-with-dataframes/93241/22 "2023-01-25T20:52:06Z")

</div>

Well you’re reading about the cutting edge right here. 😉 So not very far I’m afraid, but given that we have great Table like packages, geometry packages and spatial index packages, it seems we should be pretty close, we just need to find a way for them to work together.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [January 25, 2023, 9:24pm UTC](https://discourse.julialang.org/t/spatial-join-with-dataframes/93241/23 "2023-01-25T21:24:32Z")

</div>

> [@evetion](#):
>
> Ah, I indeed didn’t fully understand your question. I took it as a suggestion for spatial intersections/geometry, and I suggested more. I think these can’t do this spatial join themselves, but they can be a provider for the predicate function in a naive, non-optimized (n^2) way. The only fast way is doing spatial indexing, which is essentially an optimized version of the boundingbox approach by [Dan](https://discourse.julialang.org/t/spatial-join-with-dataframes/93241/10) later in this topic.

Yes sure. In my idea the linked scripts were potentially usable as a predicate from flexijoins.  
In this sense I was addressing @aplavin , who asked for the use of efficient spatial search algorithms to be employed within flexijoins  
In particular, in my quick reading, [this part](https://discourse.julialang.org/t/efficiently-check-if-points-are-contained-in-polygons/74415/8) seemed more interesting to me, which is perhaps better than O(n^2).

“GMT.jl can do this pretty quickly. When one reads a shape file (or other OGR formats) we get a vector of GMTdataset that has, among others, a boundingbox field for each polygon. So we can do the quick scan through to see if points are, or not, inside each of the polygon’s BB and if yes than use the `gmtselect` module that gets us the point-in-polygon answer.”

---

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [January 26, 2023, 4:43am UTC](https://discourse.julialang.org/t/spatial-join-with-dataframes/93241/24 "2023-01-26T04:43:09Z")

</div>

> [@evetion](#):
>
> Is the `loop_over_side=1` documented somewhere? Without it you get an error that there’s no method `swap_sides(f)`.

No, and I’m not sure if this argument is really needed - so maybe it’s just a temporary workaround, in the future it won’t be required.

> [@evetion](#):
>
> That would be great! I guess it could be another Mode? Instead of the predicate getting `element \* element in the Nested Mode, it would require a mode that does element \* vector. The vector being the AcceleratedArray column in a table.

The ideal way would be to utilize columns that are AcceleratedArrays automatically, for the operations they support. At first, it could require manual mode selection, as you say.

Currently, reusing data preparation (like sort/tree building/hashing) in multiple joins with the same dataset is nontrivial and undocumented - through possible, and I use this functionality from time to time. AcceleratedArrays can make this cleaner and more straightforward (also more explicit?).

---

<div class="post-metadata">

**Author:** ![asinghvi17](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/asinghvi17/32/8272_2.png) [@asinghvi17](https://discourse.julialang.org/u/asinghvi17)\
**Post date:** [June 8, 2024, 9:48pm UTC](https://discourse.julialang.org/t/spatial-join-with-dataframes/93241/25 "2024-06-08T21:48:31Z")

</div>

GeometryOps.jl offers this in combination with FlexiJoins today!

> **[Spatial joins | GeometryOps.jl](https://juliageo.org/GeometryOps.jl/dev/tutorials/spatial_joins)**
>
> A VitePress Site

---

<div class="post-metadata">

**Author:** ![tlow22](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tlow22/32/16332_2.png) [@tlow22](https://discourse.julialang.org/u/tlow22)\
**Post date:** [August 18, 2024, 5:34pm UTC](https://discourse.julialang.org/t/spatial-join-with-dataframes/93241/26 "2024-08-18T17:34:22Z")

</div>

You the man, love the 1 year later followup (seriously)

---

<div class="post-metadata">

**Author:** ![jade\_mackay](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jade_mackay/32/6659_2.png) [@jade\_mackay](https://discourse.julialang.org/u/jade_mackay)\
**Post date:** [September 6, 2024, 5:15am UTC](https://discourse.julialang.org/t/spatial-join-with-dataframes/93241/27 "2024-09-06T05:15:50Z")

</div>

Hello @asinghvi17!

I can confirm that this satisfies what was probably my motivating example with

```julia
julia> @time joined_df = FlexiJoins.innerjoin(
           (df_sites, df_links),
           by_pred(:geometry, GO.within, :geometry)
       )
  0.815245 seconds (3.20 M allocations: 244.915 MiB, 8.85% gc time)
4472×4 DataFrame
  Row │ geometry site_ref geometry_1 link_id 
      │ IGeometr… String IGeometry… String  
──────┼─────────────────────────────────────────────────────────────
    1 │ Geometry: wkbPoint 02900020 Geometry: wkbPolygon 10062
...

```

and with flying colours!

Where inputs are

```julia
julia> df_sites
2042×2 DataFrame
  Row │ geometry site_ref 
      │ IGeometr… String   
──────┼──────────────────────────────
    1 │ Geometry: wkbPoint 01000007
...

```

and

```julia
julia> df_links
438406×2 DataFrame
    Row │ geometry link_id 
        │ IGeometr… String  
────────┼───────────────────────────────
      1 │ Geometry: wkbPolygon 1
...

```

constituting 438406 buffered linestrings (thanks GeoInterface).

Imports:

```julia
import GeoInterface as GI, GeometryOps as GO, GeoFormatTypes as GFT
using FlexiJoins, DataFrames, GeoDataFrames
using ArchGDAL

```

Thanks @asinghvi17 !  
And thanks also @evetion, @aplavin, and others!

 ![fig](https://global.discourse-cdn.com/julialang/original/3X/7/6/767255b8236a09eb22e56d0111d68d0b5a359dc4.png)

---

<div class="post-metadata">

**Author:** ![TimG](https://avatars.discourse-cdn.com/v4/letter/t/82dd89/32.png) [@TimG](https://discourse.julialang.org/u/TimG)\
**Post date:** [September 6, 2024, 7:54am UTC](https://discourse.julialang.org/t/spatial-join-with-dataframes/93241/28 "2024-09-06T07:54:11Z")

</div>

This can also now also be achieved with [geojoin](https://juliaearth.github.io/GeoStatsDocs/stable/queries/#GeoTables.geojoin) in GeoStats.jl.

e.g.

```julia
# df is a dataframe containing X and Y coordinates on the British National Grid (EPSG27700)
# georef converts these coords to a geometry
# geojoin joins two geometries based on the given predicate 
geotable1 = georef(df, ("X", "Y"); crs=EPSG{27700})
geotable2 = GeoIO.load("MyShapefile.shp")
newtable = geojoin(geotable1, geotable2, kind=:left, pred=((g1, g2) -> intersects(g1, g2)))

```

[Previous page](https://discourse.julialang.org/t/spatial-join-with-dataframes/93241.md?page=1)