3

I have a Carto database called drawn_items with the columns cartdb_id, the_geom, fsrn_db and area.

The following JavaScript code (which I will call code 1) inserts a new row into the carto table drawn _items when a polygon is drawn in leaflet (drawnItems feature group). It also calculates the polygon area (seeArea2) and inserts it into the column area.

function setData(e) {
    
    if(e.target && e.target.id == "submit") {
        
        // Get user input fsrn
        let enteredFSRN = document.getElementById("input_fsrn").value;
    
        

            // For each drawn layer
            drawnItems.eachLayer(function(layer) {

        let seeArea = L.GeometryUtil.geodesicArea(layer.getLatLngs()[0]);
        let seeArea2 = (seeArea*0.000247105).toFixed(2)
                  
                     // Create SQL expression to insert layer
                    let drawing = JSON.stringify(layer.toGeoJSON().geometry);
                    let sql = 
                        "INSERT INTO drawn_items (the_geom, fsrn_db, area) " + 
                        "VALUES (ST_SetSRID(ST_GeomFromGeoJSON('" + 
                        drawing + "'), 4326), '" + 
                        enteredFSRN + "', '" +
                        seeArea2 + "')";
                    console.log(sql);

                    // Send the data
                    fetch(urlSmall, {
                        method: "POST",
                        headers: {
                            "Content-Type": "application/x-www-form-urlencoded"
                        },
                        body: "q=" + encodeURI(sql)
                    })
                    .then(function(response) {
                        return response.json();
                    })
                    .then(function(data) {
                        console.log("Data saved:", data);
                    })
                    .catch(function(error) {
                        console.log("Problem saving the data:", error);
                    });
                
                // Transfer submitted drawing to the CARTO layer
                let newData = layer.toGeoJSON();
                newData.properties.fsrn_db = enteredFSRN;
               
                //L.geoJSON(newData, {onEachFeature: addPopup, style: myStyle}); myLocation.addLayer(layer);

                L.geoJSON(newData, {onEachFeature: addPopup2, style: myStyle}).addTo(myLocation);

            });

            // Clear drawn items layer
            drawnItems.closePopup();
            drawnItems.clearLayers();
           
    }
}

I also have a piece of JavaScript code that updates the geometry column (“the _geom”) in Carto when polygons (“myLocation” featureGroup) are edited in leaflet I will call this code 2.

function persistOnCartoDB(action, layers) {
      /*
        this function interacts with the Security Definer
        function previously defined in our CARTO account.
        Gets an action (update, insert, or delete) and a list
        of GeoJSON objects (the geometry objects only, to work
        with ST_GeomFromGeojson()) with which to change the table.
        see http://gis.stackexchange.com/questions/169219/invalid-geojson-when-inserting-data-to-a-cartodb-postgis-table-from-leaflet
      */
      var cartodb_ids = [];
      var geojsons = [];
      console.log(action + " persistOnCartoDB");

      switch (action) {
        case "UPDATE":
          console.log(myLocation.getLayers().length);
          if (myLocation.getLayers().length < 1) return;
          myLocation.eachLayer(function(layer) {
            cartodb_ids.push(layer.cartodb_id);
            geojsons.push("'" + JSON.stringify(layer.toGeoJSON().geometry) + "'");
          });
          break;

        /*case "INSERT":
          cartodb_ids.push(-1);
          //console.log("here is the geojsons");
          //console.log(geojsons);
          //console.log("'" + JSON.stringify(layers.toGeoJSON().geometry) + "'");
          geojsons.push("'" + JSON.stringify(layers.toGeoJSON().geometry) + "'");
          break;*/

        case "DELETE":
          layers.eachLayer(function(layer) {
            cartodb_ids.push(layer.cartodb_id);
            geojsons.push("''");
          });
          break;
      }

      //constructs the SQL statement
      var sql2 = "SELECT draw_items_upsert(ARRAY[";
      sql2 += cartodb_ids.join(",");
      sql2 += "],ARRAY[";
      sql2 += geojsons.join(",");
      sql2 += "]);";

      console.log("persisting... " + sql2);//optional/debugging
      //POST the SQL up to CARTO
      $.ajax({
        type: 'POST',
        url: 'https://haighd2.carto.com/api/v2/sql',
        crossDomain: true,
        data: {
          "q": sql2
        },
        dataType: 'json',
        success: function(responseData, textStatus, jqXHR) {
          console.log("Data saved");

          if (action == "INSERT")
            layers.cartodb_id = responseData.rows[0].cartodb_id;
        },
        error: function(responseData, textStatus, errorThrown) {
          console.log("Problem saving the data " + responseData);
        }
      });
    }

My question is how can I amend code 2 so that it recalculates the geodesic area of the amended polygons and updates the “area” column in drawn_items carto database when polygons in myLocation are edited?

Obviously the answer could involve a new or amended SQL query similar to code 1 however the SQL query in code 2 is structured in a completely different fashion, does it need to be refactored somehow?

Edit: Regarding "draw_items_upsert()" yes it’s a security definer entered into the SQL terminal in Carto, that allows Leaflet permission to edit the Carto database. I added "area" to “sql := 'WITH n(cartodb_id,the_geom, area) AS (VALUES ';” but not sure if it needs to be added elsewhere as well. It looks like this:

DROP FUNCTION IF EXISTS draw_items_upsert(int[], text[], numeric[]);

-- Returns a set of op,cartodb_id values where op means:
--
-- deleted: -1
-- updated: 0
-- inserted: 1
--
CREATE OR REPLACE FUNCTION draw_items_upsert(
cartodb_ids integer[],
geojsons text[],
area_updates numeric[])
RETURNS TABLE(op int, cartodb_id int, area numeric)

LANGUAGE plpgsql SECURITY DEFINER
RETURNS NULL ON NULL INPUT
AS $$
DECLARE
sql text;
BEGIN

sql := 'WITH n(cartodb_id,the_geom,area) AS (VALUES ';

--Iterate over the values
FOR i in 1 .. array_upper(geojsons, 1)
LOOP
IF i > 1 THEN sql := sql || ','; END IF;
sql :=sql || '('||cartodb_ids[i]||','  

|| 'ST_SetSRID(ST_GeomFromGeoJSON(NULLIF('''|| geojsons[i] ||''','''')),4326)),'''|| area_updates[i] ||'''';

END LOOP;

sql := sql || '), do_update AS ('
|| 'UPDATE drawn_items p '
|| 'SET the_geom=n.the_geom FROM n WHERE p.cartodb_id = n.cartodb_id '
|| 'AND n.the_geom IS NOT NULL '



|| 'RETURNING p.cartodb_id ), do_delete AS ('
|| 'DELETE FROM drawn_items p WHERE p.cartodb_id IN ('
|| 'SELECT n.cartodb_id FROM n WHERE cartodb_id >= 0 AND '
|| ' n.the_geom IS NULL ) RETURNING p.cartodb_id ), do_insert AS ('
|| 'INSERT INTO drawn_items (the_geom)'
|| 'SELECT n.the_geom FROM n WHERE n.cartodb_id < 0 AND '
|| ' n.the_geom IS NOT NULL RETURNING cartodb_id ) '
|| 'SELECT 0,cartodb_id FROM do_update UNION ALL '
|| 'SELECT 1,cartodb_id FROM do_insert UNION ALL '
|| 'SELECT -1,cartodb_id FROM do_delete';

RAISE DEBUG '%', sql;

RETURN QUERY EXECUTE sql;

END;
$$;

--Grant access to the public user
GRANT EXECUTE ON FUNCTION draw_items_upsert(integer[],text[],numeric[]) TO publicuser;

Edit 2 : My attempt to implement your suggested code is as follows :

function calcArea(layer) {
    return (L.GeometryUtil.geodesicArea(layer.getLatLngs()[0])*0.000247105).toFixed(2)
}



      console.log(action + " persistOnCartoDB"+ calcArea);

      switch (action) {
        case "UPDATE":
          console.log(myLocation.getLayers().length);
          if (myLocation.getLayers().length < 1) return;

          myLocation.eachLayer(function(layer) {
            cartodb_ids.push(layer.cartodb_id);
            geojsons.push("'" + JSON.stringify(layer.toGeoJSON().geometry) + "'");
            area_updates.push([/*layer.cartodb_id,*/ calcArea(layer)]);//new code 
          });
          break;

        /*case "INSERT":
          cartodb_ids.push(-1);
          //console.log("here is the geojsons");
          //console.log(geojsons);
          //console.log("'" + JSON.stringify(layers.toGeoJSON().geometry) + "'");
          geojsons.push("'" + JSON.stringify(layers.toGeoJSON().geometry) + "'");
          break;*/

        case "DELETE":
          layers.eachLayer(function(layer) {
            cartodb_ids.push(layer.cartodb_id);
            geojsons.push("''");
          });
          break;
      }

      //constructs the SQL statement
      var sql2 = "SELECT draw_items_upsert(ARRAY[";
      sql2 += cartodb_ids.join(",");
      sql2 += "],ARRAY[";
      sql2 += geojsons.join(",");
      sql2 += "],ARRAY["
      sql2 += area_updates.join (",")
      sql2 += "]);";

      console.log("persisting... " + sql2);//optional/debugging
      //POST the SQL up to CARTO
      $.ajax({
        type: 'POST',
        url: 'https://haighd2.carto.com/api/v2/sql',
        crossDomain: true,
        data: {
          "q": sql2
        },
        dataType: 'json',
        success: function(responseData, textStatus, jqXHR) {
          console.log("Data saved");

          if (action == "INSERT")
            layers.cartodb_id = responseData.rows[0].cartodb_id;
        },
        error: function(responseData, textStatus, errorThrown) {
          console.log("Problem saving the data " + responseData);
        }
      });
    }

It appears to recalculate the area and create a third array of these values however I get a XHR POST console error and “problem saving data”, response: "error [ "error [ "syntax error at or near "'0.35'"" ]" ]". 0.35 being the first value in the ARRAY of areas (area_updates).

My logic was that previously the code generated SELECT draw_items_upsert then an ARRAY of the cartodb_ids followed by an ARRAY of the polygon geometries. So I’ve added a third ARRAY of the newly calculated areas into sql2 and updated the security definer to allow area in Carto to be changed. Obviously its not working so what have I missed?

1
  • 1
    Please do not edit your Question to place thanks for an answer. Doing this tampers with the Q/A model of GIS SE, and thanks are part of the "chit-chat" that is removed during normal Question maintenance.
    – Vince
    Feb 14, 2022 at 12:27

1 Answer 1

4

I think your identification of refactoring means you are on the right path. If there is a some commonalities between some code, see how you can abstract it enough to make it work with both functions. Sometimes when I am presented with a code that I don't know how to quite untangle yet, I will go ahead and write redundant code to get a picture of complete workflows. Then refactor because out commonalities because it makes maintenance and reading easier.

I can see a couple of solutions immediately without having a bit more knowledge of your project or schema. But you could try:

  1. Make the SQL query construction a factory function. Move all your query construction to its own function, pass in the arguments and return your SQL data object to be POSTed. This has the added benefit if you have bug, need to change the schema or even use another backend, the code for creating a SQL query is all located in the same place. This may be refactored a few times to create a clean interface without the need of a bunch of logic operators.
  2. The less intensive refactoring option would be to just to create a function that takes a feature and returns the area. Then just modify your SQL statement in both functions to call this common code and construct your query.

I would personally go the route of option #1.


It appears that your second function is also just taking JSON objects and updating the geometry field only, as suggested in the comment in your code too.

... and a list of GeoJSON objects (the geometry objects only ...

You could update "area" field if you edit the existing query in your second function, however, I think it is a call to a custom database function(?). I couldn't find any reference to draw_items_upsert() in Carto, and without it being a custom function a lot of the data formatting prior would lead to problem with regular queries.

You could also solve this by creating a JS array of each layer.cartodb_id and calculated area during the myLocation.eachLayer(function(layer) {...}) iteration. Then create a separate update query/queries for each layer and update the area in separate DB call. This is probably how I would approach this issue, you don't have to modify existing update function, and it has added clarity, and you most likely won't see a performance impact as the number of layers to update will likely be relatively low.

Some pseudocode:

function calcArea(layer) {
    return (L.GeometryUtil.geodesicArea(layer.getLatLngs()[0])*0.000247105).toFixed(2)
}

area_updates = []

myLocation.eachLayer(function(layer) {
    cartodb_ids.push(layer.cartodb_id);
    geojsons.push("'" + JSON.stringify(layer.toGeoJSON().geometry) + "'");
    area_updates.push([layer.cartodb_id, calcArea(layer)]); # Our new code.
});

for (let update of area_updates) {
    update_sql = "UPDATE drawn_items SET area=" + update[1] + " WHERE cartdb_id=" + update[0] + ";";
}

Your Answer

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

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