CODEWITHSUNDEEP
rggplot2postgisspatial
Brian D
Brian D

Reputation: 2728

How do I plot PostGIS geometries using geom_sf?

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?

Upvotes: 1

Views: 966

Answers (1)

Brian D
Brian D

Reputation: 2728

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

Upvotes: 0

Related Questions