postGIS: ST_MakeEnvelope() why does only one of those similar queries work?

Question

why does one of those similar queries work?

Im trying to create a BBOX polygon and save it into a geom column.

Yet only one approach works but I can not insert different values.

This query works without a problem:

res <- DBI::dbExecute(con,
                        "INSERT INTO graphs (
                      left_xmin,
                      bottom_ymin,
                      right_xmax,
                      top_ymax,
                      filename,
                      BBOX_Diagonale,
                      file, bbox) VALUES ($1,$2, $3, $4,$5, $6,$7,ST_GeomFromText(ST_AsText(ST_MakeEnvelope(11.29589921,  47.5205857,  12.21999367, 48.14282422, 4326) )));",
                        list(round(bbox_dimensions[[1]],digits=8),
                             round(bbox_dimensions[[2]],digits=8),
                             round(bbox_dimensions[[3]],digits=8),
                             round(bbox_dimensions[[4]],digits=8),
                             filename_save,
                             bbox_diagonale,
                             paste0( "\\x",paste(data, collapse = ""))))

But his one does not.

res <- DBI::dbExecute(con,
                        "INSERT INTO graphs (
                      left_xmin,
                      bottom_ymin,
                      right_xmax,
                      top_ymax,
                      filename,
                      BBOX_Diagonale,
                      file, bbox) VALUES ($1,$2, $3, $4,$5, $6,$7,$8);",
                        list(round(bbox_dimensions[[1]],digits=8),
                             round(bbox_dimensions[[2]],digits=8),
                             round(bbox_dimensions[[3]],digits=8),
                             round(bbox_dimensions[[4]],digits=8),
                             filename_save,
                             bbox_diagonale,
                             paste0( "\\x",paste(data, collapse = "")),
                             paste0("ST_GeomFromText(ST_AsText(ST_MakeEnvelope(11.29589921,  47.5205857,  12.21999367, 48.14282422, 4326) )))")))
  

I would like to be able to replace the vales „11.29589921, 47.5205857, 12.21999367…“ with a bbox object.

For that I thought I would use a paste0 function to create a string. Yet I get this error:

Error: Failed to fetch row: ERROR:  parse error - invalid geometry
HINT:  "ST" <-- parse error at position 2 within geometry

The bbox column was created with:

SELECT AddGeometryColumn('graphs', 'bbox', 4326, 'POLYGON', 2 );

And if understand the query correctly especially the part with bbox polygon.. it produces geo data so it should work... but it does not :)

Answer 1

I do it like this now:

res <- DBI::dbExecute(con,
                          paste("INSERT INTO graphs (
                      left_xmin,
                      bottom_ymin,
                      right_xmax,
                      top_ymax,
                      filename,
                      BBOX_Diagonale,
                      file, bbox) VALUES ($1,$2, $3, $4,$5, $6,$7,ST_GeomFromText(ST_AsText(ST_MakeEnvelope(",
                                round(bbox_dimensions[[1]],digits=8),",",
                                round(bbox_dimensions[[2]],digits=8),",",
                                round(bbox_dimensions[[3]],digits=8),",", 
                                round(bbox_dimensions[[3]],digits=8),
                                ", 4326) )));"),
                          list(round(bbox_dimensions[[1]],digits=8),
                               round(bbox_dimensions[[2]],digits=8),
                               round(bbox_dimensions[[3]],digits=8),
                               round(bbox_dimensions[[4]],digits=8),
                               filename_save,
                               bbox_diagonale,
                               paste0( "\\x",paste(data, collapse = ""))))