The code below enables me to add polygons I draw on a leaflet map to tables in my database. However I have some difficulties since the geometry column of my table is called "geom" and the geometry column created with the leaflet map is called "geometry".
I get a warning message like this:
"Error in : Failed to initialise COPY: ERREUR: the column "geometry" does not exists"
ui <- fluidPage(leafletOutput("map"))
server <- function(input, output, session){
output$map <- renderLeaflet({
leaflet() %>%
addTiles(group = "OSM") %>%
addDrawToolbar(targetLayerId = NULL, targetGroup = NULL,
polygonOptions = drawPolygonOptions(showArea = TRUE, metric=TRUE, shapeOptions=drawShapeOptions(fillColor="aquamarine", fillOpacity=0.8, clickable = TRUE), repeatMode=FALSE),
polylineOptions = FALSE,
circleOptions = FALSE,
rectangleOptions = FALSE,
markerOptions = FALSE,
circleMarkerOptions = FALSE,
singleFeature = FALSE,
editOptions = FALSE)})
observeEvent(input$map_draw_new_feature, {
polygon_coordinates <- input$map_draw_new_feature$geometry$coordinates[[1]]
drawn_polygon <- Polygon(do.call(rbind,lapply(polygon_coordinates,function(x){c(x[[1]][1],x[[2]][1])})))
sps <- SpatialPolygons(list(Polygons(list(drawn_polygon),"drawn_polygon")))
created_polygon <- st_as_sf(sps, CRS("+proj=longlat +datum=WGS84"))
st_write(created_polygon , pool, "mytable", append = TRUE)
})
}
shinyApp(ui, server)
How can I solve this without altering the column in my database ?
EDIT:
I tried something ugly like this:
created_polygon$geom <- created_polygon$geometry
created_polygon<- created_polygon%>% st_drop_geometry()
But I get this warning message
"Warning: Error in : COPY returned error: ERROR: Geometry type (Polygon) does not match column type (MultiPolygon)"
Guessing that created_polygon is an sf object, you can rename the geometry column using st_geometry(nc) <- "geom".
Please, find below a reprex.
NB: you need to have the version of sf 1.0.6 or higher (if not, please uncomment the first rows of the reprex)
Reprex
nc object (i.e. the geometry column is named "geometry")#> library(devtools)
#> install_github("r-spatial/sf")
library(sf)
nc <- st_read(system.file("shape/nc.shp", package="sf"))
nc
#> Simple feature collection with 100 features and 14 fields
#> Geometry type: MULTIPOLYGON
#> Dimension: XY
#> Bounding box: xmin: -84.32385 ymin: 33.88199 xmax: -75.45698 ymax: 36.58965
#> Geodetic CRS: NAD27
#> First 10 features:
#> AREA PERIMETER CNTY_ CNTY_ID NAME FIPS FIPSNO CRESS_ID BIR74 SID74
#> 1 0.114 1.442 1825 1825 Ashe 37009 37009 5 1091 1
#> 2 0.061 1.231 1827 1827 Alleghany 37005 37005 3 487 0
#> 3 0.143 1.630 1828 1828 Surry 37171 37171 86 3188 5
#> 4 0.070 2.968 1831 1831 Currituck 37053 37053 27 508 1
#> 5 0.153 2.206 1832 1832 Northampton 37131 37131 66 1421 9
#> 6 0.097 1.670 1833 1833 Hertford 37091 37091 46 1452 7
#> 7 0.062 1.547 1834 1834 Camden 37029 37029 15 286 0
#> 8 0.091 1.284 1835 1835 Gates 37073 37073 37 420 0
#> 9 0.118 1.421 1836 1836 Warren 37185 37185 93 968 4
#> 10 0.124 1.428 1837 1837 Stokes 37169 37169 85 1612 1
#> NWBIR74 BIR79 SID79 NWBIR79 geometry
#> 1 10 1364 0 19 MULTIPOLYGON (((-81.47276 3...
#> 2 10 542 3 12 MULTIPOLYGON (((-81.23989 3...
#> 3 208 3616 6 260 MULTIPOLYGON (((-80.45634 3...
#> 4 123 830 2 145 MULTIPOLYGON (((-76.00897 3...
#> 5 1066 1606 3 1197 MULTIPOLYGON (((-77.21767 3...
#> 6 954 1838 5 1237 MULTIPOLYGON (((-76.74506 3...
#> 7 115 350 2 139 MULTIPOLYGON (((-76.00897 3...
#> 8 254 594 2 371 MULTIPOLYGON (((-76.56251 3...
#> 9 748 1190 2 844 MULTIPOLYGON (((-78.30876 3...
#> 10 160 2038 5 176 MULTIPOLYGON (((-80.02567 3...
geometry columnst_geometry(nc) <- "geom"
"geometry" has been changed into "geom")nc
#> Simple feature collection with 100 features and 14 fields
#> Geometry type: MULTIPOLYGON
#> Dimension: XY
#> Bounding box: xmin: -84.32385 ymin: 33.88199 xmax: -75.45698 ymax: 36.58965
#> Geodetic CRS: NAD27
#> First 10 features:
#> AREA PERIMETER CNTY_ CNTY_ID NAME FIPS FIPSNO CRESS_ID BIR74 SID74
#> 1 0.114 1.442 1825 1825 Ashe 37009 37009 5 1091 1
#> 2 0.061 1.231 1827 1827 Alleghany 37005 37005 3 487 0
#> 3 0.143 1.630 1828 1828 Surry 37171 37171 86 3188 5
#> 4 0.070 2.968 1831 1831 Currituck 37053 37053 27 508 1
#> 5 0.153 2.206 1832 1832 Northampton 37131 37131 66 1421 9
#> 6 0.097 1.670 1833 1833 Hertford 37091 37091 46 1452 7
#> 7 0.062 1.547 1834 1834 Camden 37029 37029 15 286 0
#> 8 0.091 1.284 1835 1835 Gates 37073 37073 37 420 0
#> 9 0.118 1.421 1836 1836 Warren 37185 37185 93 968 4
#> 10 0.124 1.428 1837 1837 Stokes 37169 37169 85 1612 1
#> NWBIR74 BIR79 SID79 NWBIR79 geom
#> 1 10 1364 0 19 MULTIPOLYGON (((-81.47276 3...
#> 2 10 542 3 12 MULTIPOLYGON (((-81.23989 3...
#> 3 208 3616 6 260 MULTIPOLYGON (((-80.45634 3...
#> 4 123 830 2 145 MULTIPOLYGON (((-76.00897 3...
#> 5 1066 1606 3 1197 MULTIPOLYGON (((-77.21767 3...
#> 6 954 1838 5 1237 MULTIPOLYGON (((-76.74506 3...
#> 7 115 350 2 139 MULTIPOLYGON (((-76.00897 3...
#> 8 254 594 2 371 MULTIPOLYGON (((-76.56251 3...
#> 9 748 1190 2 844 MULTIPOLYGON (((-78.30876 3...
#> 10 160 2038 5 176 MULTIPOLYGON (((-80.02567 3...
Created on 2022-02-01 by the reprex package (v2.0.1)
EDIT
As a follow-up to your comment, please find below the way to change the geometry type from MULTIPOLYGON to POLYGON
library(sf)
# Geometry type at the origin : MULTIPOLYGON
unique(st_geometry_type(nc))
#> [1] MULTIPOLYGON
#> 18 Levels: GEOMETRY POINT LINESTRING POLYGON MULTIPOINT ... TRIANGLE
MULTIPOLYGON to POLYGONnc <- st_cast(nc, "POLYGON")
#> Warning in st_cast.sf(nc, "POLYGON"): repeating attributes for all sub-
#> geometries for which they may not be constant
POLYGONunique(st_geometry_type(nc))
#> [1] POLYGON
#> 18 Levels: GEOMETRY POINT LINESTRING POLYGON MULTIPOINT ... TRIANGLE
geom column)nc
#> Simple feature collection with 108 features and 14 fields
#> Geometry type: POLYGON
#> Dimension: XY
#> Bounding box: xmin: -84.32385 ymin: 33.88199 xmax: -75.45698 ymax: 36.58965
#> Geodetic CRS: NAD27
#> First 10 features:
#> AREA PERIMETER CNTY_ CNTY_ID NAME FIPS FIPSNO CRESS_ID BIR74 SID74
#> 1 0.114 1.442 1825 1825 Ashe 37009 37009 5 1091 1
#> 2 0.061 1.231 1827 1827 Alleghany 37005 37005 3 487 0
#> 3 0.143 1.630 1828 1828 Surry 37171 37171 86 3188 5
#> 4 0.070 2.968 1831 1831 Currituck 37053 37053 27 508 1
#> 4.1 0.070 2.968 1831 1831 Currituck 37053 37053 27 508 1
#> 4.2 0.070 2.968 1831 1831 Currituck 37053 37053 27 508 1
#> 5 0.153 2.206 1832 1832 Northampton 37131 37131 66 1421 9
#> 6 0.097 1.670 1833 1833 Hertford 37091 37091 46 1452 7
#> 7 0.062 1.547 1834 1834 Camden 37029 37029 15 286 0
#> 8 0.091 1.284 1835 1835 Gates 37073 37073 37 420 0
#> NWBIR74 BIR79 SID79 NWBIR79 geom
#> 1 10 1364 0 19 POLYGON ((-81.47276 36.2343...
#> 2 10 542 3 12 POLYGON ((-81.23989 36.3653...
#> 3 208 3616 6 260 POLYGON ((-80.45634 36.2425...
#> 4 123 830 2 145 POLYGON ((-76.00897 36.3196...
#> 4.1 123 830 2 145 POLYGON ((-76.02717 36.5567...
#> 4.2 123 830 2 145 POLYGON ((-75.90199 36.5562...
#> 5 1066 1606 3 1197 POLYGON ((-77.21767 36.2409...
#> 6 954 1838 5 1237 POLYGON ((-76.74506 36.2339...
#> 7 115 350 2 139 POLYGON ((-76.00897 36.3196...
#> 8 254 594 2 371 POLYGON ((-76.56251 36.3405...
Created on 2022-02-01 by the reprex package (v2.0.1)