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? Any help gratefully accepted.

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?

2
  • I'm not sure how long-lived the access tokens in this API, but it is always a good idea to remove it before posting the picture - it is in the URL here (and in general, text is more convenient anyway).
    – Michael
    Mar 4 at 2:35
  • they last 24hrs so I can't see that its a problem
    – williamh
    Mar 4 at 9:15

1 Answer 1

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].

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.
    – williamh
    Mar 3 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.
    – Michael
    Mar 4 at 2:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.