postgresql - R & Postgis: ERROR: Geometry type (Polygon) does not match column type (MultiPolygon)

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
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
Importing data and reading 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"))
#> 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...
Code to rename the geometry column
st_geometry(nc) <- "geom" 
Output (i.e. "geometry" has been changed into "geom")
#> 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)}
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
Code to convert geometry type from MULTIPOLYGON to POLYGON
nc <- st_cast(nc, "POLYGON")
#> Warning in st_cast.sf(nc, "POLYGON"): repeating attributes for all sub-
#> geometries for which they may not be constant
Output: the geometry is now of type POLYGON
unique(st_geometry_type(nc))
#> [1] POLYGON
#> 18 Levels: GEOMETRY POINT LINESTRING POLYGON MULTIPOINT ... TRIANGLE
Last check (please see the geom column)
#> 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)}
                Your answer look simple and promising but it seems I can't download a more recent version of the package 'sf' 1.0-5 ;(
– zakros
                Feb 1, 2022 at 15:11
                @zakros, thanks for your feedback. Could you explain a little more about the kind of problems you encounter when you try to install the development version of sf? Do you work with windows or MacOS?
– lovalery
                Feb 1, 2022 at 15:15
                I get many warning lines ending with this error message: In i.p(...) :   installation of package ‘C:/Users/USER~1/AppData/Local/Temp/Rtmpe8ru7f/file2a8074054ffd/sf_1.0-6.tar.gz’ had non-zero exit status. I'm working with Windows but this issue with sf may remain a problem since the shiny app I'm developping will have to be deployed on my shiny server...
– zakros
                Feb 1, 2022 at 15:45
                @zakros, thanks for the clarification. If you are working on Windows, the first two lines of code in the reprex indicated as comments should normally work... I also sometimes get the same error message as yours when I try to update some libraries. In this case, (i) I close all open files in R, (ii) I close R, (iii) I open R again, and (4), in this case, I run: library(devtools) ; install_github("r-spatial/sf") ... and it should work... I keep my fingers crossed for you :-)
– lovalery
                Feb 1, 2022 at 15:57
                Yes, I see. You just need to change the geometry type using the st_cast() function from the sf library. It's up to you, either try it yourself or add an excerpt of your data (e.g. using dput(head(created_polygon))) so I can help you with that.
– lovalery
                Feb 1, 2022 at 16:31
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.