Geolocation in the form and correction of marker position

[gwado]

What are you building with SQLPage ?
I'm looking for a way to record the geographical coordinates of an entity via a form, using the device's geolocation and manual correction for greater accuracy.

What is your problem?
Currently, it's not possible to record an entity's location via a map.

What are you currently doing ?
Currently, I can use a template to geolocate myself by clicking on a button. The longitude and latitude are then added to the form's longitude and latitude fields.
I can then display the entity on a map, on another page.

Describe the solution you'd like
It seems to me that it might be interesting to add a “geolocation” entry that would allow an entity to be automatically displayed on a map, while still being able to correct its position using the mouse/manually.

[lovasoa]

Hello! That would indeed be cool! Do you think you can make a pull request?

[gwado]

I'm not sure I'm doing it right.

At present, I call the template :

select
    localization' as component;

And add inputs Lat/Long in a form :

SELECT 'Latitude' AS label, 'latitude' AS name, 'number' AS type, 0.00000001 AS step, 6 as width;
SELECT 'Longitude' AS label, 'longitude' AS name, 'number' AS type, 0.00000001 AS step, 6 as width;

Template containing :

<button class="btn btn-outline-primary" id="geoloc-btn">Geolocate</button>

<script nonce="{{@csp_nonce}}">
    document.getElementById("geoloc-btn").addEventListener("click", function() {
        if (navigator.geolocation) {
            navigator.geolocation.getCurrentPosition(function(position) {
                var lat = position.coords.latitude;
                var lon = position.coords.longitude;

                document.querySelector('input[name="latitude"]').value = lat;
                document.querySelector('input[name="longitude"]').value = lon;
            }, function(error) {
                alert("Geolocation error: " + error.message);
            });
        } else {
            alert("Geolocation is not supported by this browser.");
        }
    });
</script>

It works.
But, this is just a proposal for a code base.

The goal could be to be able to add a marker/line/polygon and correct it.
The coordinates would be saved in geojson in the database. Currently, I see that the “spatialite” extension is not active by default. Under Linux, this seems relatively easy via apt, but under Windows it's more complex.

BUT, should I go for this slightly more complex idea or just make a geolocation-only marker?

As SQLPage processes data. I'd find it relevant to take spatial/geo data into account as well.
It's in my personal interest, but is it the same for SQLPage?

I can get on with the code in the next few days. Just slowly. :)

[gwado]

It's a better try, because it's possible to add different geometries (including multiple ones if you want to group points together, for example), edit them and delete them (to start again).
There's also a geolocation button.
I deliberately created (customizable) buttons because I find the library used (leaflet draw) difficult to handle on a small screen. It also allows me to keep the SQLPage design.
Some buttons can be automatically hidden/displayed to avoid visual overload. This is the case for editing and deleting, for example.

The coordinates retrieved are displayed in a hidden input to be created according to the documentation. Simply pass the corresponding id.

I don't know how to add several maps via SQL in order to have a map selector.
I don't know how to change the color of certain buttons after a particular action (a click, for example).
For these two problems, I had to hard-code the template.
Can you help?

Here's a visual preview, the integration documentation and the full code below.

Sorry, I know it was about a pull request, but it's better for me like that...

What do you think?

The sql code for the integration is below.

select
    'mapdraw' as component,
    '500' as height,
    '7' as zoom,
    'mapdrawCoordinates' as target;
SELECT
    'location' as type, 'btn-secondary' as class, 'Locate me' as tooltip, 'location' as id, 'map-search' as icon;
SELECT
    'point' as type, '' as class, 'Draw point(s)' as tooltip, 'map-pin' as icon;
SELECT
    'line' as type, '' as class, 'Draw polygon(s)' as tooltip, 'line' as icon;
SELECT
    'polygon' as type, '' as class, 'Draw polygon(s)' as tooltip, 'polygon' as icon;
SELECT
    'edit-shapes' as type, 'btn-warning' as class, 'Edit shapes' as tooltip, 'edit' as icon, true as autohide;
SELECT
    'delete-shapes' as type, 'btn-danger' as class, 'Delete shapes' as tooltip, 'trash' as icon, true as autohide;

SELECT 
    'form' as component,
    'Nouvel arbre' as title,
    'create_tree.sql?project_id=' || $project_id as action,
    'Ajouter un arbre' as validate;

SELECT 'Nom latin de l''arbre' AS label, 'latin_name' AS name;
SELECT 'Coordonnées' AS label, 'coordinates' AS name, 'mapdrawCoordinates' AS id, 'hidden' AS type;
SELECT 'Commentaire' AS label, 'note' AS name, 'textarea' AS type;

Here's the complete handlebar code (mapdraw.handlebars).

<!-- Include Leaflet CSS -->
<link rel="stylesheet" href="https://unpkg.com/[email protected]/dist/leaflet.css" />
<!-- Include Leaflet Draw CSS -->
<link rel="stylesheet" href="https://unpkg.com/[email protected]/dist/leaflet.draw.css" />

<style>
    /* Initially hide the Leaflet drawing toolbar */
    .leaflet-draw {
        display: none;
    }
</style>

<div class="card my-3 {{class}}" {{#if id}}id="{{id}}" {{/if}}>
    <div class="card-body">
        <!-- Map container with a default height of 350px -->
        <div class="leaflet" style="height: {{default height 350}}px;" data-zoom="{{default zoom 5}}" id="map">
            <div class="d-flex justify-content-center h-100 align-items-center">
                <div class="spinner-border" role="status" style="width: 3rem; height: 3rem;">
                    <span class="visually-hidden">Loading map...</span>
                </div>
            </div>
        </div>
        <!-- Form containing buttons for user interaction -->
        <div id="form" class="my-3">
            {{#each_row}}
            {{#if type}}
            <button class="btn {{class}} {{#if autohide}}invisible{{/if}}" data-bs-toggle="tooltip"
                data-bs-placement="top" title="{{tooltip}}" id="{{type}}">{{~icon_img (default icon 'map')~}}</button>
            {{/if}}
            {{/each_row}}
        </div>
    </div>
</div>

<!-- Include Leaflet JS -->
<script nonce="{{@csp_nonce}}" src="https://unpkg.com/[email protected]/dist/leaflet.js"></script>
<!-- Include Leaflet Draw JS -->
<script nonce="{{@csp_nonce}}" src="https://unpkg.com/[email protected]/dist/leaflet.draw.js"></script>

<script nonce="{{@csp_nonce}}">
    // Set the initial center of the map to Paris coordinates
    var mapCenter = [48.8566, 2.3522];
    var isEditing = false; // Variable to track whether editing mode is enabled

    // Initialize the map, disable zoom control, and set the initial view
    var map = L.map("map", { zoomControl: false }).setView(mapCenter, {{ default zoom 5}});

    // Add OpenStreetMap layer to the map
    var osmLayer = L.tileLayer("https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png", {
        attribution: '&copy; <a href="http://osm.org/copyright">OpenStreetMap</a> contributors',
        maxNativeZoom: 18,
        maxZoom: 25,
    });

    // Add Google Satellite layer to the map
    var googleSatelliteLayer = L.tileLayer("https://{s}.google.com/vt/lyrs=s,h&x={x}&y={y}&z={z}", {
        attribution: "© Google",
        maxNativeZoom: 18,
        maxZoom: 25,
        subdomains: ["mt0", "mt1", "mt2", "mt3"],
    }).addTo(map);

    // Add CyclOSM layer for cycling paths
    var CyclOSM = L.tileLayer("https://{s}.tile-cyclosm.openstreetmap.fr/cyclosm/{z}/{x}/{y}.png", {
        maxNativeZoom: 18,
        maxZoom: 25,
        attribution: '<a href="https://github.com/cyclosm/cyclosm-cartocss-style/releases" title="CyclOSM - Open Bicycle render">CyclOSM</a> | Map data: &copy; <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors',
    });

    // Add TopPlusOpen_Grey layer in grayscale
    var TopPlusOpen_Grey = L.tileLayer("http://sgx.geodatenzentrum.de/wmts_topplus_open/tile/1.0.0/web_grau/default/WEBMERCATOR/{z}/{y}/{x}.png", {
        maxNativeZoom: 18,
        maxZoom: 25,
        attribution: 'Map data: &copy; <a href="http://www.govdata.de/dl-de/by-2-0">dl-de/by-2-0</a>',
    });

    // Add Geoportail France orthophoto layer
    var GeoportailFrance_orthos = L.tileLayer("https://wxs.ign.fr/{apikey}/geoportail/wmts?REQUEST=GetTile&SERVICE=WMTS&VERSION=1.0.0&STYLE={style}&TILEMATRIXSET=PM&FORMAT={format}&LAYER=ORTHOIMAGERY.ORTHOPHOTOS&TILEMATRIX={z}&TILEROW={y}&TILECOL={x}", {
        attribution: '<a target="_blank" href="https://www.geoportail.gouv.fr/">Geoportail France</a>',
        maxNativeZoom: 18,
        maxZoom: 25,
        apikey: "chooseyourapikey",
        format: "image/jpeg",
        style: "normal",
    });

    // Group all base layers for easy selection in layer control
    var baseLayers = {
        "OpenStreetMap": osmLayer,
        "Google Satellite": googleSatelliteLayer,
        "Cycling Paths (CyclOSM)": CyclOSM,
        "Grayscale (Top Plus)": TopPlusOpen_Grey,
        "Geoportail Orthophoto": GeoportailFrance_orthos,
    };

    // Add layer control to the map
    L.control.layers(baseLayers).addTo(map);

    // Add drawing control and initialize with editing tools enabled
    var drawnItems = new L.FeatureGroup();
    map.addLayer(drawnItems);

    var drawControl = new L.Control.Draw({
        edit: {
            featureGroup: drawnItems,
            remove: true, // Allow removing features
            edit: true // Allow editing existing features
        },
        draw: {
            polygon: false,
            polyline: false,
            rectangle: false,
            circle: false,
            marker: false,
        }
    });
    map.addControl(drawControl);
    L.drawLocal.edit.handlers.edit.tooltip.subtext = '';
    L.drawLocal.edit.handlers.edit.tooltip.text = '';

    // Function to update the coordinates field with drawn geometries
    function updateCoordinates() {
        var points = [];
        var lines = [];
        var polygons = [];

        // Gather coordinates from all drawn layers
        drawnItems.eachLayer(function (layer) {
            var geojson = layer.toGeoJSON();
            switch (geojson.geometry.type) {
                case "Point":
                    points.push(geojson.geometry.coordinates);
                    break;
                case "LineString":
                    lines.push(geojson.geometry.coordinates);
                    break;
                case "Polygon":
                    polygons.push(geojson.geometry.coordinates);
                    break;
            }
        });

        // Determine the GeoJSON structure based on the number of features
        var geoJsonResult;

        if (points.length + lines.length + polygons.length > 1) {
            // Create a GeoJSON feature collection if there are multiple features
            var featureCollection = {
                type: "FeatureCollection",
                features: []
            };

            if (points.length > 0) {
                featureCollection.features.push({
                    type: "Feature",
                    geometry: {
                        type: points.length > 1 ? "MultiPoint" : "Point",
                        coordinates: points.length > 1 ? points : points[0]
                    }
                });
            }

            if (lines.length > 0) {
                featureCollection.features.push({
                    type: "Feature",
                    geometry: {
                        type: lines.length > 1 ? "MultiLineString" : "LineString",
                        coordinates: lines.length > 1 ? lines : lines[0]
                    }
                });
            }

            if (polygons.length > 0) {
                featureCollection.features.push({
                    type: "Feature",
                    geometry: {
                        type: polygons.length > 1 ? "MultiPolygon" : "Polygon",
                        coordinates: polygons.length > 1 ? polygons : polygons[0]
                    }
                });
            }

            geoJsonResult = featureCollection;
        } else {
            // Create a single feature if there is only one
            if (points.length > 0) {
                geoJsonResult = {
                    type: "Feature",
                    geometry: {
                        type: "Point",
                        coordinates: points[0]
                    }
                };
            } else if (lines.length > 0) {
                geoJsonResult = {
                    type: "Feature",
                    geometry: {
                        type: "LineString",
                        coordinates: lines[0]
                    }
                };
            } else if (polygons.length > 0) {
                geoJsonResult = {
                    type: "Feature",
                    geometry: {
                        type: "Polygon",
                        coordinates: polygons[0]
                    }
                };
            }
        }

        // Update the coordinates input field with the GeoJSON string
        document.getElementById('{{target}}').value = JSON.stringify(geoJsonResult);

        // Show or hide the edit and delete buttons based on the presence of features
        if (geoJsonResult && (geoJsonResult.type === "FeatureCollection" && geoJsonResult.features.length > 0 || geoJsonResult.type === "Feature")) {
            document.getElementById('edit-shapes').classList.remove('invisible');
            document.getElementById('delete-shapes').classList.remove('invisible');
        } else {
            document.getElementById('edit-shapes').classList.add('invisible');
            document.getElementById('delete-shapes').classList.add('invisible');
        }
    }

    // Function to clear drawn items that don't match the selected geometry type
    function clearUnselectedItems(type) {
        drawnItems.eachLayer(function (layer) {
            var geometryType = layer.toGeoJSON().geometry.type;

            // Determine if the current layer should be removed
            var shouldRemove = false;
            switch (type) {
                case 'marker':
                    shouldRemove = geometryType !== 'Point';
                    break;
                case 'polyline':
                    shouldRemove = geometryType !== 'LineString';
                    break;
                case 'polygon':
                    shouldRemove = geometryType !== 'Polygon';
                    break;
            }

            // Remove the layer if it doesn't match the selected type
            if (shouldRemove) {
                drawnItems.removeLayer(layer);
            }
        });
    }

    // Function to clear all drawn items
    function clearAll() {
        drawnItems.eachLayer(function (layer) {
            drawnItems.removeLayer(layer);
        });
        updateCoordinates();
    }

    // Event listener for when a new shape is drawn on the map
    map.on(L.Draw.Event.CREATED, function (event) {
        var layer = event.layer;
        drawnItems.addLayer(layer);
        updateCoordinates(); // Update coordinates after adding the new shape
    });

    // Event listener for editing features
    map.on(L.Draw.Event.EDITED, function (event) {
        if (isEditing) {
            updateCoordinates(); // Update coordinates after editing
        }
    });

    // Event listener for removing features
    map.on(L.Draw.Event.DELETED, function (event) {
        if (isEditing) {
            updateCoordinates(); // Update coordinates after deletion
        }
    });

    // Handler for the "Draw Point" button
    document.getElementById('point').onclick = function () {
        map.removeControl(drawControl); // Temporarily remove the draw control
        clearUnselectedItems('marker'); // Clear non-point items
        drawControl.setDrawingOptions({
            marker: true,
            polyline: false,
            polygon: false
        });
        map.addControl(drawControl); // Re-add the draw control
        drawControl._toolbars.draw._modes.marker.handler.enable(); // Enable point drawing mode
    };

    // Handler for the "Draw Line" button
    document.getElementById('line').onclick = function () {
        map.removeControl(drawControl); // Temporarily remove the draw control
        clearUnselectedItems('polyline'); // Clear non-line items
        drawControl.setDrawingOptions({
            marker: false,
            polyline: true,
            polygon: false
        });
        map.addControl(drawControl); // Re-add the draw control
        drawControl._toolbars.draw._modes.polyline.handler.enable(); // Enable line drawing mode
    };

    // Handler for the "Draw Polygon" button
    document.getElementById('polygon').onclick = function () {
        map.removeControl(drawControl); // Temporarily remove the draw control
        clearUnselectedItems('polygon'); // Clear non-polygon items
        drawControl.setDrawingOptions({
            marker: false,
            polyline: false,
            polygon: true
        });
        map.addControl(drawControl); // Re-add the draw control
        drawControl._toolbars.draw._modes.polygon.handler.enable(); // Enable polygon drawing mode
    };

    // Handler for the "Location" button
    document.getElementById('location').onclick = function () {
        map.locate({ setView: true, maxZoom: 18 }); // Trigger geolocation and set map view
        map.on('locationerror', function () {
            alert("Geolocation failed."); // Alert user on geolocation failure
        });
    };

    // Handler for the "Edit Shapes" button
    document.getElementById('edit-shapes').onclick = function () {
        var button = this;
        if (isEditing) {
            // If already in editing mode, finalize editing
            map.removeControl(drawControl); // Remove drawing control
            updateCoordinates(); // Update coordinates
            button.classList.remove('btn-success');
            button.classList.add('btn-warning');
            isEditing = false;
        } else {
            // Enable editing mode
            map.removeControl(drawControl); // Remove drawing control
            drawControl.setDrawingOptions({
                marker: false,
                polyline: false,
                polygon: false
            });
            map.addControl(drawControl); // Re-add drawing control
            drawControl._toolbars.edit._modes.edit.handler.enable(); // Enable editing mode
            button.classList.remove('btn-warning');
            button.classList.add('btn-success');
            isEditing = true;
        }
    };

    // Handler for the "Delete Shapes" button
    document.getElementById('delete-shapes').onclick = function () {
        // Show a confirmation dialog
        var userConfirmed = confirm("Are you sure you want to delete all shapes?");
        if (userConfirmed) {
            clearAll(); // Call function to clear all drawn items
            document.getElementById('delete-shapes').classList.add('invisible'); // Hide the button
        }
        // If the user cancels, do nothing
    };
</script>

[lovasoa]

Hi, and congratulations for the great work you seem to have done already !

I don't know how to add several maps via SQL in order to have a map selector.

I'm not sur what you want to do precisely, but the general question seems to be: how to add row-level parameters to a custom component, and use them from javascript ?

The easiest is probably to use the stringify handlebars helper:

<script nonce="{{@csp_nonce}}">
function handleDatabaseRow(row) {
  alert("I received a row: " + JSON.stringify(row));
}

{{#each_row}}
handleDatabaseRow({{{stringify this}}});
{{/each_row}}
</script>

More info in the docs: https://sql.datapage.app/custom_components.sql
This has the advantage of being simple and handling potentially data from the database in a streaming fashion. This has the disadvantage that it cannot be used with untrusted string data. select 'me' as component; select '</script>' as x; would break it.

An more complex, slower, but safer alternative is this:

<script nonce="{{@csp_nonce}}" data-rows="
[
    {{~#each_row~}}
        {{~#if (gt @row_index 0)}},{{/if~}}
        {{~stringify this~}}
    {{~/each_row~}}
]
">
const data = JSON.parse(document.currentScript.dataset.rows);
console.log(data);
</script>

I don't know how to change the color of certain buttons after a particular action (a click, for example).

You can change the color of a button by using bootstrap css classes from javascript, but I see you already know how to do that. You can trigger this on click:

button.onclick = () => {
  button.classList.remove('btn-warning');
  button.classList.add('btn-success');
}