# 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:** 1\
**Showing post:** 10

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [January 20, 2023, 5:29pm UTC](https://discourse.julialang.org/t/spatial-join-with-dataframes/93241/10 "2023-01-20T17:29:11Z")

</div>

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):

```julia
# prep data & environment
using DataFrames, GeoDataFrames, ArchGDAL, FlexiJoins, IntervalSets

dfa = GeoDataFrames.read("a.csv")
dfb = GeoDataFrames.read("b.csv")

getbb(g) = begin
    gg = ArchGDAL.boundingbox(g)
    gg1 = ArchGDAL.getgeom(gg,0)
    x1, y2 = ArchGDAL.getpoint(gg1,0)
    x2, y1 = ArchGDAL.getpoint(gg1,2)
    (x1..x2, y1..y2)
end

getxy(p) = begin
    x,y = ArchGDAL.getpoint(p,0)
    (x, y)
end

```

```julia
# process data
dfa2 = transform(dfa, 
  :geometry => ByRow(getbb) => [:xinterval, :yinterval])
dfb2 = transform(dfb, 
  :geometry => ByRow(getxy) => [:x, :y])

subset!(
  innerjoin(
    (innerjoin((dfb2,dfa2), by_pred(:x, ∈, :xinterval)),dfa2), 
    by_pred(:y, ∈, :yinterval)
  ), [:geometry, :geometry_1] => (a, b) -> intersects.(a, b))

```

The results are similar to the ones from a crossjoin. The bounding boxes and intervals may be of further use possibly.

---

_[View the full topic](https://discourse.julialang.org/t/spatial-join-with-dataframes/93241)._