How do I plot PostGIS geometries using geom_sf?

1
votes

Data source is PostgreSQL database with PostGIS geometry types. I have no problem querying the data I want directly from SQL into a data.frame using the RPostgreSQL package.

Since R complains about raw geom types, I use ST_AsText() on the geoms in the SQL query so that they are stored as character type in a data.frame. I have three types: POINT, LINESTRING, and POLYGON. Note that my data is geometry (e.g., Cartesian x,y,z coordinates), not map-projected geography.

Had a bit of trouble making my MWE because I had to separate each LINESTRING and POLYGON element with a newline character otherwise r thought I was missing parentheses. Possibly a bug? Anyway, here is the MWE:

tmpdf <- data.frame(id = seq(1,6,1),
                point = c("POINT(-186.552366 -69.56887)","POINT(151.826334 -19.119682)","POINT(-189.013302 -67.107934)","POINT(-195.575798 59.220114)","POINT(103.01777 62.501362)","POINT(135.009938 55.118554)"),
                linestr = c("LINESTRING(186.547856450189 -46.4485209972227,182.523886136587 -87.3045772259105,170.606614118262 -126.59055812607,151.254014475596 -162.79672548354,125.209796759794 -194.531696591426,93.4748256519081 -220.575914307228,65.3272038227373 -235.621131625452,36.2687622716732 -244.435913500765,-9.05803921416017 -248.900213788796,-54.3848406999935 -244.435913500765,-97.9697586242894 -231.214573193132,-138.137849033921 -209.744281559306,-173.34547474097 -180.850129866096,-202.239626434179 -145.642504159047,-221.510885247941 -109.588514788964,-228.270643805406 -87.3045772259111,-232.294614119009 -46.4485209972234,-228.270643805406 -5.59246476853568,-216.353371787081 33.6935161316239,-197.000772144416 69.8996834890942,-170.956554428614 101.63465459698,-139.221583320728 127.678872312782,-103.015415963258 147.031471955448,-63.7294350630982 158.948743973773,-22.8733788344104 162.972714287376,17.9826773942774 158.948743973774,57.2686582944371 147.031471955449,93.4748256519074 127.678872312783,125.209796759793 101.634654596981,151.254014475596 69.8996834890953,170.606614118262 33.6935161316251,182.523886136587 -5.59246476853451,186.547856450189 -46.4485209972227)",
                            "LINESTRING(186.547856450189 -46.4485209972227,182.523886136587 -87.3045772259105,170.606614118262 -126.59055812607,151.254014475596 -162.79672548354,125.209796759794 -194.531696591426,93.4748256519081 -220.575914307228,65.3272038227373 -235.621131625452,36.2687622716732 -244.435913500765,-9.05803921416017 -248.900213788796,-54.3848406999935 -244.435913500765,-97.9697586242894 -231.214573193132,-138.137849033921 -209.744281559306,-173.34547474097 -180.850129866096,-202.239626434179 -145.642504159047,-221.510885247941 -109.588514788964,-228.270643805406 -87.3045772259111,-232.294614119009 -46.4485209972234,-228.270643805406 -5.59246476853568,-216.353371787081 33.6935161316239,-197.000772144416 69.8996834890942,-170.956554428614 101.63465459698,-139.221583320728 127.678872312782,-103.015415963258 147.031471955448,-63.7294350630982 158.948743973773,-22.8733788344104 162.972714287376,17.9826773942774 158.948743973774,57.2686582944371 147.031471955449,93.4748256519074 127.678872312783,125.209796759793 101.634654596981,151.254014475596 69.8996834890953,170.606614118262 33.6935161316251,182.523886136587 -5.59246476853451,186.547856450189 -46.4485209972227)",
                            "LINESTRING(186.547856450189 -46.4485209972227,182.523886136587 -87.3045772259105,170.606614118262 -126.59055812607,151.254014475596 -162.79672548354,125.209796759794 -194.531696591426,93.4748256519081 -220.575914307228,65.3272038227373 -235.621131625452,36.2687622716732 -244.435913500765,-9.05803921416017 -248.900213788796,-54.3848406999935 -244.435913500765,-97.9697586242894 -231.214573193132,-138.137849033921 -209.744281559306,-173.34547474097 -180.850129866096,-202.239626434179 -145.642504159047,-221.510885247941 -109.588514788964,-228.270643805406 -87.3045772259111,-232.294614119009 -46.4485209972234,-228.270643805406 -5.59246476853568,-216.353371787081 33.6935161316239,-197.000772144416 69.8996834890942,-170.956554428614 101.63465459698,-139.221583320728 127.678872312782,-103.015415963258 147.031471955448,-63.7294350630982 158.948743973773,-22.8733788344104 162.972714287376,17.9826773942774 158.948743973774,57.2686582944371 147.031471955449,93.4748256519074 127.678872312783,125.209796759793 101.634654596981,151.254014475596 69.8996834890953,170.606614118262 33.6935161316251,182.523886136587 -5.59246476853451,186.547856450189 -46.4485209972227)",
                            "LINESTRING(186.547856450189 -46.4485209972227,182.523886136587 -87.3045772259105,170.606614118262 -126.59055812607,151.254014475596 -162.79672548354,125.209796759794 -194.531696591426,93.4748256519081 -220.575914307228,65.3272038227373 -235.621131625452,36.2687622716732 -244.435913500765,-9.05803921416017 -248.900213788796,-54.3848406999935 -244.435913500765,-97.9697586242894 -231.214573193132,-138.137849033921 -209.744281559306,-173.34547474097 -180.850129866096,-202.239626434179 -145.642504159047,-221.510885247941 -109.588514788964,-228.270643805406 -87.3045772259111,-232.294614119009 -46.4485209972234,-228.270643805406 -5.59246476853568,-216.353371787081 33.6935161316239,-197.000772144416 69.8996834890942,-170.956554428614 101.63465459698,-139.221583320728 127.678872312782,-103.015415963258 147.031471955448,-63.7294350630982 158.948743973773,-22.8733788344104 162.972714287376,17.9826773942774 158.948743973774,57.2686582944371 147.031471955449,93.4748256519074 127.678872312783,125.209796759793 101.634654596981,151.254014475596 69.8996834890953,170.606614118262 33.6935161316251,182.523886136587 -5.59246476853451,186.547856450189 -46.4485209972227)",
                            "LINESTRING(186.547856450189 -46.4485209972227,182.523886136587 -87.3045772259105,170.606614118262 -126.59055812607,151.254014475596 -162.79672548354,125.209796759794 -194.531696591426,93.4748256519081 -220.575914307228,65.3272038227373 -235.621131625452,36.2687622716732 -244.435913500765,-9.05803921416017 -248.900213788796,-54.3848406999935 -244.435913500765,-97.9697586242894 -231.214573193132,-138.137849033921 -209.744281559306,-173.34547474097 -180.850129866096,-202.239626434179 -145.642504159047,-221.510885247941 -109.588514788964,-228.270643805406 -87.3045772259111,-232.294614119009 -46.4485209972234,-228.270643805406 -5.59246476853568,-216.353371787081 33.6935161316239,-197.000772144416 69.8996834890942,-170.956554428614 101.63465459698,-139.221583320728 127.678872312782,-103.015415963258 147.031471955448,-63.7294350630982 158.948743973773,-22.8733788344104 162.972714287376,17.9826773942774 158.948743973774,57.2686582944371 147.031471955449,93.4748256519074 127.678872312783,125.209796759793 101.634654596981,151.254014475596 69.8996834890953,170.606614118262 33.6935161316251,182.523886136587 -5.59246476853451,186.547856450189 -46.4485209972227)",
                            "LINESTRING(186.547856450189 -46.4485209972227,182.523886136587 -87.3045772259105,170.606614118262 -126.59055812607,151.254014475596 -162.79672548354,125.209796759794 -194.531696591426,93.4748256519081 -220.575914307228,65.3272038227373 -235.621131625452,36.2687622716732 -244.435913500765,-9.05803921416017 -248.900213788796,-54.3848406999935 -244.435913500765,-97.9697586242894 -231.214573193132,-138.137849033921 -209.744281559306,-173.34547474097 -180.850129866096,-202.239626434179 -145.642504159047,-221.510885247941 -109.588514788964,-228.270643805406 -87.3045772259111,-232.294614119009 -46.4485209972234,-228.270643805406 -5.59246476853568,-216.353371787081 33.6935161316239,-197.000772144416 69.8996834890942,-170.956554428614 101.63465459698,-139.221583320728 127.678872312782,-103.015415963258 147.031471955448,-63.7294350630982 158.948743973773,-22.8733788344104 162.972714287376,17.9826773942774 158.948743973774,57.2686582944371 147.031471955449,93.4748256519074 127.678872312783,125.209796759793 101.634654596981,151.254014475596 69.8996834890953,170.606614118262 33.6935161316251,182.523886136587 -5.59246476853451,186.547856450189 -46.4485209972227)"),
                polygon = c("POLYGON((186.547856450189 -46.4485209972227,182.523886136587 -87.3045772259105,170.606614118262 -126.59055812607,151.254014475596 -162.79672548354,125.209796759794 -194.531696591426,93.4748256519081 -220.575914307228,65.3272038227373 -235.621131625452,36.2687622716732 -244.435913500765,-9.05803921416017 -248.900213788796,-54.3848406999935 -244.435913500765,-97.9697586242894 -231.214573193132,-138.137849033921 -209.744281559306,-173.34547474097 -180.850129866096,-202.239626434179 -145.642504159047,-221.510885247941 -109.588514788964,-228.270643805406 -87.3045772259111,-232.294614119009 -46.4485209972234,-228.270643805406 -5.59246476853568,-216.353371787081 33.6935161316239,-197.000772144416 69.8996834890942,-170.956554428614 101.63465459698,-139.221583320728 127.678872312782,-103.015415963258 147.031471955448,-63.7294350630982 158.948743973773,-22.8733788344104 162.972714287376,17.9826773942774 158.948743973774,57.2686582944371 147.031471955449,93.4748256519074 127.678872312783,125.209796759793 101.634654596981,151.254014475596 69.8996834890953,170.606614118262 33.6935161316251,182.523886136587 -5.59246476853451,186.547856450189 -46.4485209972227))", 
                            "POLYGON((186.547856450189 -46.4485209972227,182.523886136587 -87.3045772259105,170.606614118262 -126.59055812607,151.254014475596 -162.79672548354,125.209796759794 -194.531696591426,93.4748256519081 -220.575914307228,65.3272038227373 -235.621131625452,36.2687622716732 -244.435913500765,-9.05803921416017 -248.900213788796,-54.3848406999935 -244.435913500765,-97.9697586242894 -231.214573193132,-138.137849033921 -209.744281559306,-173.34547474097 -180.850129866096,-202.239626434179 -145.642504159047,-221.510885247941 -109.588514788964,-228.270643805406 -87.3045772259111,-232.294614119009 -46.4485209972234,-228.270643805406 -5.59246476853568,-216.353371787081 33.6935161316239,-197.000772144416 69.8996834890942,-170.956554428614 101.63465459698,-139.221583320728 127.678872312782,-103.015415963258 147.031471955448,-63.7294350630982 158.948743973773,-22.8733788344104 162.972714287376,17.9826773942774 158.948743973774,57.2686582944371 147.031471955449,93.4748256519074 127.678872312783,125.209796759793 101.634654596981,151.254014475596 69.8996834890953,170.606614118262 33.6935161316251,182.523886136587 -5.59246476853451,186.547856450189 -46.4485209972227))",
                            "POLYGON((186.547856450189 -46.4485209972227,182.523886136587 -87.3045772259105,170.606614118262 -126.59055812607,151.254014475596 -162.79672548354,125.209796759794 -194.531696591426,93.4748256519081 -220.575914307228,65.3272038227373 -235.621131625452,36.2687622716732 -244.435913500765,-9.05803921416017 -248.900213788796,-54.3848406999935 -244.435913500765,-97.9697586242894 -231.214573193132,-138.137849033921 -209.744281559306,-173.34547474097 -180.850129866096,-202.239626434179 -145.642504159047,-221.510885247941 -109.588514788964,-228.270643805406 -87.3045772259111,-232.294614119009 -46.4485209972234,-228.270643805406 -5.59246476853568,-216.353371787081 33.6935161316239,-197.000772144416 69.8996834890942,-170.956554428614 101.63465459698,-139.221583320728 127.678872312782,-103.015415963258 147.031471955448,-63.7294350630982 158.948743973773,-22.8733788344104 162.972714287376,17.9826773942774 158.948743973774,57.2686582944371 147.031471955449,93.4748256519074 127.678872312783,125.209796759793 101.634654596981,151.254014475596 69.8996834890953,170.606614118262 33.6935161316251,182.523886136587 -5.59246476853451,186.547856450189 -46.4485209972227))",
                            "POLYGON((186.547856450189 -46.4485209972227,182.523886136587 -87.3045772259105,170.606614118262 -126.59055812607,151.254014475596 -162.79672548354,125.209796759794 -194.531696591426,93.4748256519081 -220.575914307228,65.3272038227373 -235.621131625452,36.2687622716732 -244.435913500765,-9.05803921416017 -248.900213788796,-54.3848406999935 -244.435913500765,-97.9697586242894 -231.214573193132,-138.137849033921 -209.744281559306,-173.34547474097 -180.850129866096,-202.239626434179 -145.642504159047,-221.510885247941 -109.588514788964,-228.270643805406 -87.3045772259111,-232.294614119009 -46.4485209972234,-228.270643805406 -5.59246476853568,-216.353371787081 33.6935161316239,-197.000772144416 69.8996834890942,-170.956554428614 101.63465459698,-139.221583320728 127.678872312782,-103.015415963258 147.031471955448,-63.7294350630982 158.948743973773,-22.8733788344104 162.972714287376,17.9826773942774 158.948743973774,57.2686582944371 147.031471955449,93.4748256519074 127.678872312783,125.209796759793 101.634654596981,151.254014475596 69.8996834890953,170.606614118262 33.6935161316251,182.523886136587 -5.59246476853451,186.547856450189 -46.4485209972227))",
                            "POLYGON((186.547856450189 -46.4485209972227,182.523886136587 -87.3045772259105,170.606614118262 -126.59055812607,151.254014475596 -162.79672548354,125.209796759794 -194.531696591426,93.4748256519081 -220.575914307228,65.3272038227373 -235.621131625452,36.2687622716732 -244.435913500765,-9.05803921416017 -248.900213788796,-54.3848406999935 -244.435913500765,-97.9697586242894 -231.214573193132,-138.137849033921 -209.744281559306,-173.34547474097 -180.850129866096,-202.239626434179 -145.642504159047,-221.510885247941 -109.588514788964,-228.270643805406 -87.3045772259111,-232.294614119009 -46.4485209972234,-228.270643805406 -5.59246476853568,-216.353371787081 33.6935161316239,-197.000772144416 69.8996834890942,-170.956554428614 101.63465459698,-139.221583320728 127.678872312782,-103.015415963258 147.031471955448,-63.7294350630982 158.948743973773,-22.8733788344104 162.972714287376,17.9826773942774 158.948743973774,57.2686582944371 147.031471955449,93.4748256519074 127.678872312783,125.209796759793 101.634654596981,151.254014475596 69.8996834890953,170.606614118262 33.6935161316251,182.523886136587 -5.59246476853451,186.547856450189 -46.4485209972227))",
                            "POLYGON((186.547856450189 -46.4485209972227,182.523886136587 -87.3045772259105,170.606614118262 -126.59055812607,151.254014475596 -162.79672548354,125.209796759794 -194.531696591426,93.4748256519081 -220.575914307228,65.3272038227373 -235.621131625452,36.2687622716732 -244.435913500765,-9.05803921416017 -248.900213788796,-54.3848406999935 -244.435913500765,-97.9697586242894 -231.214573193132,-138.137849033921 -209.744281559306,-173.34547474097 -180.850129866096,-202.239626434179 -145.642504159047,-221.510885247941 -109.588514788964,-228.270643805406 -87.3045772259111,-232.294614119009 -46.4485209972234,-228.270643805406 -5.59246476853568,-216.353371787081 33.6935161316239,-197.000772144416 69.8996834890942,-170.956554428614 101.63465459698,-139.221583320728 127.678872312782,-103.015415963258 147.031471955448,-63.7294350630982 158.948743973773,-22.8733788344104 162.972714287376,17.9826773942774 158.948743973774,57.2686582944371 147.031471955449,93.4748256519074 127.678872312783,125.209796759793 101.634654596981,151.254014475596 69.8996834890953,170.606614118262 33.6935161316251,182.523886136587 -5.59246476853451,186.547856450189 -46.4485209972227))"), stringsAsFactors = F)

All the points are different, the linestrings are all the same, and the polygons are all the same (and same as the linestrings, just as a polygon instead).

Now, I would like to visualize these geometries using ggplot.

I was hoping to use the 'simple features' aspect of ggplot (see geom_sf), but it appears to be either deprecated or not yet implemented. I do see a reference to a geom_sf but it appears to be in development still...

Both geom_map and geom_polygon require x and y variables. Is there a fast way to extract x and y coordinates from geometry WKT objects?

1
rggplot2postgisspatial
You need the dev version of ggplot2 to use geom_sf and for the rest you can convert your data.frame to sf and then use geom_sf. Use st_as_sf to convert e.g st_as_sf(tmpdf, wkt = "polygon") for the polygon, st_as_sf(tmpdf, wkt = "linestr"), etc - dickoa
Thanks, I figured that out just as you commented. This is fantastic new functionality! - Brian D
Yes, sf is really a game changer, love it and can't wait to use the stars package to have similar support for raster data. You can see this for direct support for PostGIS using dbplyr syntax github.com/r-dbi/DBI/issues/203 github.com/r-dbi/RPostgres/issues/86 - dickoa
note that geom_sf apparently ignores scale_y_reverse() transformation. - Brian D

1 Answers

0
votes

It is somewhat straightforward to plot geometries with ggplot2 (but not use geom_sf) if you use a few extra libraries that convert WKT to SpatialPoints/SpatialPolygons and extract coordinates:

library(rgeos)
library(sp)

# read WKT points into spatial object
tmpdf$pts <- lapply(tmpdf$point, FUN = function(x) readWKT(x))

# extract point coordinates
tmpdf$ptx <- coordinates(tmpdf$pts)[c(T,F)]
tmpdf$pty <- coordinates(tmpdf$pts)[c(F,T)]

ggplot(tmpdf) + 
  geom_point(aes(x=ptx, y=pty))

Not as clear how to extract coordinates from polygon objects though and the only way I was able to do it seems really clunky. One problem being that the functions are not vectorized, so they all need to be wrapped with apply functions. 

# read WKT polygons into spatial object
tmpdf$pol <- lapply(tmpdf$polygon, FUN = function(x) readWKT(x))

# extract coordinates from the first polygon only
test <- tmpdf[1,"pol"]
test2 <- sapply(test, FUN=function(x) x@polygons)
test3 <- sapply(test2, FUN=function(x) x@Polygons)
test4 <- lapply(test3, FUN=function(x) x@coords)

ggplot() + 
  geom_point(data=tmpdf, aes(x=ptx, y=pty)) +
  geom_polygon(data=data.frame(test4[[1]]), aes(x=x, y=y), fill="transparent", color="blue")

Points and Polygon in ggplot2

The above only works because I know that each of the polygons are equivalent. I'd have to do more work to figure out how to do this for different polygons.

**** UPDATE!

Through some trial and error, I was able to use geom_sf successfully. Functions on this page are very helpful. 

# install the dev version of ggplot2 (2.2.1.9000)
devtools::install_github("tidyverse/ggplot2")

# convert wkt to spatial (sf)
tmpdf$sppt <- st_as_sfc(tmpdf$point)
tmpdf$spls <- st_as_sfc(tmpdf$linestr)
tmpdf$sppg <- st_as_sfc(tmpdf$polygon) 

# regular plotting works
plot(tmpdf$sppt)
plot(tmpdf$spls)
plot(tmpdf$sppg)

# ggplot too!
ggplot(tmpdf) + 
  geom_sf(aes(geometry=sppt))

geom_sf solution