Quick swag at extending join to support geospatial databases

[Brad Dixon]

Hi...

I work with geospatial data frequently using the PostGIS extensions to Postgres. My typical pattern is that I load large shapefiles and standard data sets at the command line and then use R+dplyr to work with the data hosted in Postgres. There are a few different types of special geospatial operations that can't be accommodated by dplyr. So far this appears to be:

1) joins with a boolean ON clause such as "JOIN table2 ON ST_DWithin( table1.loc, table2.loc, 500 )"

2) creating geospatial indexes: "CREATE INDEX table_field_idx USING GIST ON ( somefield )"

3) Casting data types. I bump in the need to cast between geometry and geography field types frequently.

I put together a hack-ed set of "geo-join" functions cribbed off of dplyr's join functions:

https://gist.github.com/rbdixon/3c84841afb9926ec6ccc

Any other PostGIS + dplyr users out there who find this useful? I'm considering starting a side-package to supplement dplyr's geo-smarts.

Thanks,

B

[Jasper Slingsby]

Hi Brad

I'm a little disappointed by the silence this received... 

I'd be over the moon!!! :) As would many other R-GIS users I'm sure!

Dealing with large GIS datasets in R (e.g. satellite data etc, or 30m rasters of Land Cover and Protected Areas in South Africa in my case) is majorly constrained by memory issues, so if it were easily done in calls to PostGIS (without having to learn PostGIS syntax) it would open up a new world for GIS in R!

I'm afraid I am just starting to learn PostGIS (and I'm an ecologist, not a software developer), so I'd be little use to you re development, but I'd most definitely cheer you on and preach your good work to the masses! I installed yr test package and will give you feedback if/when I make sense of what I'm doing.

While you're at it, functions to create PostGIS databases from GDAL supported spatial objects without passing them through R would be a MAJOR plus!!! My goal at the moment is to try and put something like this together with system() calls, but I'm not trained in these dark arts so I'm more likely to hurt myself than succeed...

All the best,

Jasper