Facing trouble using PostGIS

App Building
,
1

Hello, I have a Postgres Database with PostGIS. I would like to add a list of items to a table that contains multiple columns among of which are the geo_location.

Here is an example of the query that works:

INSERT INTO establishments(name, address, geo_location, rating, photo_reference, gmaps_place_id, gmaps_type, gmaps_url, establishment_category_id, user_id, uuid) VALUES ('Sydney Opera House', 'Bennelong Point, Sydney NSW 2000, Australia', ST_GeogFromText('POINT(151.2152967 -33.8567844)'), 4.7, 'AfLeUgPKSB_9EQNmAxFTRtG59UXzg_u3fpBlujQQyEcrEaA-ie8AhHYkvYG83yXjFb-mN0yCLf5UGVbyRXc7BXtkqxnic-ZlnQIJwBsS7qLW_-5bteRNDlFRfKbab2kxXbWVml3G0uKguWLnc-A2NkzzGU7CQ3-YRd4tukB03cp6zrqzDI9G', 'ChIJ3S-JXmauEmsRUcIaWtf4MzE', 'tourist_attraction', 'https://maps.google.com/?cid=3545450935484072529', 1, 1, '5b02556d-f1e2-4d35-b032-677c3258633c')

So far I have tried both: GUI mode and SQL mode.

  • With GUI mode, I cannot make it work because Retool does not map the value as I want (I suppose this is the case, although, I might be incorrect). I have the following code:

    1. In a JS code block, I declare an array of items. Basically looks like:
    const establishmentsToAdd = [{
  name:"Sydney Opera House",
  address:"Bennelong Point, Sydney NSW 2000, Australia",
  geo_location: "ST_GeogFromText('POINT(151.2152967 -33.8567844)')",
  rating:4.7,  photo_reference:"AfLeUgNtX_XP2Jh7q1WgSvJYtSacZPOgFs25mSmNNIu7iO0xv8KYZ1QkXWZ8tOWuXIVn5CZUjUW0ejOjj7jCGVz4PEC8Kyq3EGX1IxAkCNLCw4-ehRgoBGKFwLHpL8QXowoQekDCWuUdj0X3G0xv67zgnvzbaba91byL_EVqMZG90OMoZMhN",
  gmaps_place_id:"ChIJ3S-JXmauEmsRUcIaWtf4MzE",
  gmaps_type:"tourist_attraction",
  gmaps_url:"https://maps.google.com/?cid=3545450935484072529",
  establishment_category_id:1,
  user_id:1,
  uuid: crypto.randomUUID()
}]
bulkAddEstablishments.trigger({additionalScope: {establishmentsToAdd}})
    1. Then, in a postgres code block, I have the GUI mode activated and in "Array of items to insert" I have ``{{establishmentsToAdd}}`

  • With SQL Mode, the same JS block, but in the second step I have (I know it looks ugly, but I tried many things...):

INSERT INTO establishments(name, address, geo_location, rating, photo_reference, gmaps_place_id, gmaps_type, gmaps_url, establishment_category_id, user_id, uuid) VALUES {{establishmentsToAdd.map((item) => {
  const { name, address, geo_location, rating, photo_reference, gmaps_place_id, gmaps_type, gmaps_url, establishment_category_id, user_id, uuid } = item;
  return `('${name}', '${address}', '${geo_location}', ${rating}, '${photo_reference}', '${gmaps_place_id}', '${gmaps_type}', '${gmaps_url}', ${establishment_category_id}, ${user_id}, '${uuid}')`;
}).join(',')}}

Any guidance on how can I do it? Thank you! :pray:

2

Update.

I activated 'Disabled prepared statement' as you can read in this thread comment

3

Now I had a small typo. It is working now :partying_face:. Basically read this thread comment

4

Another way to handle this is to make a Stored Procedure and pass the WKT as a string.

That way you can maintain the security advantage of using prepared statements.

1 Like