Postgis / working with polygons (example)

[SebastiendOrnano]

I wanted to add a land plot module in my app COOP to visualize the limits of the plot where a building is located. That means a map showing a polygon. But the PostGIS example in the SQLpage doc shows only how to work with points. I thought it will be difficult (at least, for me, as I am new to PostGIS and still a beginner with SQL) to introduce the data of polygons. But no. I am truly impressed by the duo SQLpage + PostGIS. It is amazing what you can do, easily, when working with spatial data when combining those two !

Step 1 :
I draw the polygon in umap. Then right click on the polygon -> copy as GeoJson to produce MyPolygon.geojson

Step 2 :
Create an extension postgis in postgresql and a table "polygons"

CREATE EXTENSION  IF NOT EXISTS postgis;

-- Create a table with a postgis polygon_geometry column
CREATE TABLE IF NOT EXISTS polygons (
    polygon_id serial primary key NOT NULL,
    polygon_title text NOT NULL,
    polygon_geom geometry NULL,
    polygon_description text NULL,
    created_at timestamp without time zone NULL DEFAULT CURRENT_TIMESTAMP
);

Step 3 :
create polygons_form.sql (I used the example of csv file upload from the SQLpage doc about the form component. I thought first that the file upload was limited to csv file but as far it is a text file with a structure, you can work with any type of file. I tested with a GeoJson file, but the process could , probably, be adapted to other formats such as KML, GPSX...)

select 
    'form'                      AS component,
    'geojson data'              AS title,
    'Load data'                 AS validate,
    'geojson_upload.sql'        AS action;
select 
    'geojson_input'               AS name,
    'file'                        AS type,
    'application/geo+json'        AS accept,
    'Geojson'                     AS label,
    'Upload a geojson file'       AS description,
    TRUE                          AS required;

Step 4 :
create geojson_upload.sql (this example works with a simple geojson polygon file. It should be possible to code a more sophisticate process to deal with multiple polygons or to extract data from KML or GPSX files)

WITH file_content AS (
    SELECT 
        sqlpage.read_file_as_text(sqlpage.uploaded_file_path('geojson_input'))::jsonb AS geojson
),
INSERT_data AS (
    INSERT INTO polygonss (polygon_title, polygon_geom)
    SELECT
        COALESCE(geojson->'properties'->>'name', 'Polygon with no name),
        ST_Force3D(ST_SetSRID(ST_GeomFromGeoJSON(geojson->>'geometry'), 4326))
    FROM file_content
    RETURNING polygon_id
);

Step 5 :
create polygon_map.sql

SELECT 
    'map'                                                AS component,
    'Land plots map'                                     AS title,
    16                                                   AS zoom,
    'https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png' AS tile_source;
SELECT 
    polygon_title                       AS title,
    'blue'                              AS color,
    'Land plot localisation'            AS description,
    ST_AsGeoJSON(polygon_geom)          AS geojson
    FROM polygons
    WHERE polygon_id=$polygon_id::INTEGER;