0

I have a table called drawn_items with columns “the_geom”, “fsrn_db” , & “area” in Carto2 which is queried by SQL. Having recreated the table in Carto 3, in order to use the query, I must translate it from postGIS to Bigquery. “the geom” changes to “geom” and I think ST_GeomFromGeoJSON changes to ST_GEOGFROMGEOJSON. An access token must be concatenated to the end of the string and I know this is working from testing on another query.

SQL Query in postGIS for Carto 2 (working)

"INSERT INTO drawn_items (the_geom, fsrn_db, area) " + 
                        "VALUES (ST_SetSRID(ST_GeomFromGeoJSON('" + 
                        drawing + "'), 4326), '" + 
                        enteredFSRN + "', '" +
                        seeArea2 + "')";

Attempt at SQL Query in Bigquery for Carto 3

let sql = 
                        "INSERT INTO shared.drawn_items (geom, fsrn_db, area) " + 
                        "VALUES (ST_GEOGFROMGEOJSON('" + 
                        drawing + "'), '" + 
                        enteredFSRN + "', '" +
                        seeArea2 + "')"+access_token;

However, this does not work and I get a 400 error.

How should it be structured?

enter image description here

enter image description here

The location of the error referenced above as 1:215 is the first pair of coordinates, so it seems to me that ST_GEOGFROMGEOJSON is not translating the geoJSON into BigQuery properly?

Improve this question
0

1 Answer 1

Reset to default
1

The error suggests the problem is not related to GeoJson at all.

It says "Value has type STRING which cannot be inserted into column area, which has type BIGNUMERIC"

Highlights:

  1. you have a column named area of type BIGNUMERIC
  2. let's see what we are trying to insert into it
  3. it is '" + seeArea2 + "' - quoted seeArea2 value
  4. due to quotes this value is of type STRING, which cannot be inserted into BIGNUMERIC column

Most likely you just need to remove the quotes around seeArea2. Not likely, but depending on what is there you might also need to cast it to BIGNUMERIC some way.

P.S. bigquery is normally supported in StackOverflow, not GIS StackExchange, tag [google-bigquery].

Improve this answer
2
  • Thank you for the response, unfortunately removing the quotation marks did not eliminate the 400 error “missing query parameter” when the query is executed in leaflet. When I paste the query manually into a browser address bar it does add a record to the Carto 3 database although it hides all other records in the data preview screen. Commented Mar 3, 2022 at 17:45
  • Looks like a completely new error, that is Carto-specific. You probably had two completely separate issues, the "Value ... cannot be inserted" from BigQuery that I explained, and Missing parameter error from Carto API that I cannot help with. Commented Mar 4, 2022 at 2:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.