Feature request: upload dataframes as spatial tables

[mclaeysb]

Hi - and thanks for the great work in this project!

I often work with database tables both with and without spatial data/geometries. Most often, I download them as dataframe using dbReadTable and then process them via dplyr (because I mostly work with the data features, not the geometries)). Downloading using dbReadTable results in a column with the ST geometry value as chr. It is my understanding that one can not upload a spatial table using pgInsert by pointing it at this column for the geometry - but that would be very handy for a lot of people I think.

Your opinions? Or am I missing an easier alternative? (I found out that one can't use dplyr on SpatialPointDataFrames)

Thanks!

[basille]

Hey Manuel, have you tried pgGetGeom? Is this what you're looking for?

[mclaeysb]

Hey @basille - Thanks for checking in!

I understand that pgGetGeom can read the PostGIS geometry of a PostGIS table and store it as a SpatialPoint spobject. But what I'm looking for is to read the PostGIS geometry stored in a character vector in a dataframe, and use it in pgInsert or something similar, to upload a dataframe with spatial properties. Any ideas?

[basille]

Maybe use rgeos::readWKT or wkb::readWKB to get there, but I'm still unsure of the situation you're describing. What would lead you to have a data frame with such a character column to start with? That is exactly what pgGetGeom is for: you get a SpatialPixels/Points/PolygonsDataFrame directly… Can you come up with a workable example?

[dnbucklin]

@basille : pgGetGeom only returns records with non-NULL geometries, which would be a reason not to use it (in addition to dplyr not supporting Spatial*DataFrames).

@mclaeysb : Since pgInsert also works with regular data.frame, it will work to insert the text representations of geometries into an existing table with GEOMETRY column in the database:

t<-dbReadTable(con, "table_with_geom")
# manipulate t and send back to the same table
pgInsert(con, "table_with_geom", t)

In a case like this, you'd probably want to use the upsert.using parameter almost always, to update existing records instead of appending all of them to the original table.

If you wanted to create a new database table from t, the current workaround would be to use pgInsert, and manually alter the geometry column, e.g:

t<-dbReadTable(con, "table_with_geom")
# manipulate t and send to a new table
pgInsert(con, "new_table_with_geom", t)
dbExecute(con, "ALTER TABLE new_table_with_geom ALTER COLUMN geom TYPE GEOMETRY('POINT',4326);")

The st_read/st_write functions from sf package could be another option, but I believe that would require some conversion steps to get the resulting simple features to a process-able state for dplyr (I'm not very experienced with these packages).

Depending on the need for new dependencies, I'll consider adding this functionality to the next rpostgis version.

[mclaeysb]

Thanks for the tip @dnbucklin ! The dbExecute statement does the trick. Would be nice to have this included in pgInsert indeed, but works for now.

[dnbucklin]

@mclaeysb Feature added to development version c671a3fdeb2fd887aea0335a2caead35b65ebbca, see df.geom argument of pgInsert.

Install using:

devtools::install_github("mablab/rpostgis", ref = "dev")