How to import OSM polygons data from PostGIS to R?

Question

I am new to the world of spatial analysis using R. Using this link I have downloaded OSM data in .osm.pbf format. Then I used osm2pgsql tool to get data in PostgreSQL (PostGIS extension). Now I have several tables in my database and I want to access the polygons table in R and then perform spatial analysis on the polygon vector data. I have been searching around allot but am not able to import required data in R. I found this tutorial quite similar to what I am looking for but its in Python. I want to access polygon data from PostGIS using R.

Therefore, essentially I would like to know interaction of R with PostGIS. Can anybody recommend me any book on this topic? Since I couldn't find a blog or tutorial so far that works for me on my Windows 10 64-bit machine.

Thanks for your time and looking forward for the suggestions.

really broad question, but this https://cran.r-project.org/web/packages/dplyr/vignettes/databases.html could help you started — MLavoie, Jan 06 '16 at 23:42
@MLavoie What I found so far is that the problem is with rgdal, I have to configure it in a way that it interacts with postgis. If you have any idea it would be helpful. Thanks. — Amir, Jan 07 '16 at 13:47
maybe this https://geospatial.commons.gc.cuny.edu/2014/01/14/load-postgis-geometries-in-r-without-rgdal/ — MLavoie, Jan 07 '16 at 14:08
Thanks MLavoie, using this link I am able to get the table in R but I have to construct SpatialPolygon out of it. This is hectic because of the format of the table I have. But if I use rgdal it would be allot easier as mentioned here: http://gis.stackexchange.com/questions/64950/working-with-postgis-data-in-r — Amir, Jan 07 '16 at 15:43
But since my OS is Windows 10 (64-bit), rgdal doesn't come with implicit driver support for postgis. I am working on it and will post the complete solution. Thanks for the time. :) — Amir, Jan 07 '16 at 15:45

score 1 · Accepted Answer · answered Jan 12 '16 at 17:19

I have still not found a way to get required data form PostGIS using rgdal package available in R. Probably it is because of my OS issues. (I am not exactly sure as I am not an expert). But I have found an alternative to rgdal and it has done exactly what I wanted it to do. The code is as following:

library(RPostgreSQL)
library(rgeos)
library(sp)

# Load data from the PostGIS server
conn = dbConnect(
  dbDriver("PostgreSQL"), dbname="dbNAME", host="localhost", port=5432, 
  user="username", password="pw"
)

strSQL = "SELECT osm_id, name, area, highway, railway, place, ST_AsText(way) AS wkt_geometry FROM table"
df = dbGetQuery(conn, strSQL)

#Geomtery column as R list
geo_col = df$wkt_geometry

polygon_list = suppressWarnings(lapply(geo_col, function(x){
x <- gsub("POLYGON\\(\\(", "", x)
x <- gsub("\\)", "", x)
x <- strsplit(x, ",")[[1]]

#Now each polygon has been parsed by removing POLYGON(( from the start and )) from the end
#Now for each POLYGON its xValues and yValues are to be extracted to for Polygon object
xy <- strsplit(x, " ")

v_xy = suppressWarnings(sapply(xy, function(p){        
  xValue = p[1]
  yValue = p[2]
  vec = c(xValue, yValue)
}))

#Now we have all x values in first column of v_xy and all y values in second column of v_xy
#Let us make the Polygon object now
p_xvalues = as.numeric(v_xy[1, ])
p_yvalues = as.numeric(v_xy[2, ])
p_object <- Polygon(cbind(p_xvalues, p_yvalues))      
}))

#Now we have all of the polygons in polygon object format
#Let us join it with main data frame, i.e. df
df$object_polygon <- polygon_list
#View(df)

#Now Let us form SpatialPolygons() object out of it
Ps_list = list()
for (i in seq(nrow(df))) {
  Ps_list[[i]] <- Polygons(polygon_list[i], ID=df[i,][1])
}
SPs = SpatialPolygons(Ps_list)

#Now FINALY its the time to form SpatialPolygonsDataFrame
row.names(df) = df$osm_id
SPDF = SpatialPolygonsDataFrame(Sr = SPs, data = df[, 1:6], match.ID = TRUE)

Therefore, essentially I had to write a parser to get the required data which readOGR() does it one line.

score 0 · Answer 2 · answered Jan 06 '16 at 22:35

0

I dont know R, but very familiar with postgis. Postgis is just SQL functions, if you can use Select you can access postgis,

So if you want access polygon just create new function in postgres.

But if you want display polygons you need check with R what function are available, i usually use Openlayer javascript api to show my results in the webpage.

answered Jan 06 '16 at 22:35

Juan Carlos Oropeza

47,252
12
78
118

Yes I know Leaflet and OpenLayers3 very well, but my current issue is to connect to postgis from R, probably using rgdal. Thanks for your time. – Amir Jan 07 '16 at 13:48
but what you mean `connect to postgis` ? why is different to connect to postgresql? – Juan Carlos Oropeza Jan 07 '16 at 14:08
because I have geometric (polygon) data, so I mentioned postgis. I can connect to postgres using sqldf or RPostgreSQL. But for spatial data I have to configure rgdal! – Amir Jan 07 '16 at 14:16
I see, well again Im not familiar with `R` and not sure if they have a way to receive geomety objects. But you can alway return the geometry object as a string instead, using [ST_AsText](http://postgis.net/docs/manual-1.4/ST_AsText.html) postgis function. That way you pass that string to Leaflet or Openlayer and let them handle the geometic part and your comunication is just like any postgres `SELECT` – Juan Carlos Oropeza Jan 07 '16 at 14:20
Yes I am working on it and will post complete solution as I find it. Thanks for your time bro :) – Amir Jan 07 '16 at 14:21

How to import OSM polygons data from PostGIS to R?

2 Answers2