Import table from R into PostgreSQL DB

Question

I loaded some spatial data from my PostgreSQL DB into R with the help of the RPostgreSQL-package and ST_AsText:

dbGetQuery(con, "SELECT id, ST_AsText(geom) FROM table;")

After having done some analyses I want to go back that way. My geom column is stil formated as character / WKT. Unfortunately dbWriteTable doesn't accept similar arguments as dbGetQuery.

What is the best way to import spatial data from R to PostgreSQL?

Up to now the only way I found, is importing the data into the DB and using ST_GeomFromText in an additional step to get my geometry data type.

andschar · Accepted Answer · 2016-07-26 08:00:21Z

2

I created a table on my DB by using dbWriteTable() and the postGIStools-package to INSERT the data (it must be a SpatialPolygonsDataFrame).

## Create an empty table on DB
dbWriteTable(con, name=c("public", "<table>"), value=(dataframe[-c(1:nrow(dataframe)), ]))
require(postGIStools)
## INSERT INTO
postgis_insert(con, df=SpatialPolygonsDataFrame, tbl="table", geom_name="st_astext")
dbSendQuery(con, "ALTER TABLE <table> RENAME st_astext TO geom;")
dbSendQuery(con, "ALTER TABLE <table> ALTER COLUMN geom TYPE geometry;")

answered Jul 26, 2016 at 8:00

andschar

4,1722 gold badges31 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Import table from R into PostgreSQL DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related