Postgis / exporting data as a geojson

[SebastiendOrnano]

I have created a custom component "geojson" to export data from a postgis database. It works, but I had to introduce a script to go back to my source file and I don't understand why. I am looking to find a proper way to do it.

I based my component s a variant of the "csv" component where "When csv is used as a header component (without a shell), it will trigger a download of the CSV file directly on page load."
In my case, in my source file "plot_main_display_4.sql" (with a shell) I have a table to list of the land plots and I created an custom_actions :
JSON('{"name":"Export","tooltip":"Exorter sous forme de fichier geojson","link":"/e_project_plots/plot_geojson_export_single_0.sql?plot_id={id}","icon":"file-download"}') AS custom_actions,

So from plot_main_display_4.sql, I open a file without header "plot_geojson_export_single_0.sql" which charge the geojson component. But, to make it works, I had to add a script to launch the auto-download and to redirect the user to theplot_main_display.sqlpage. Furthermore, I had to find a trick to avoid the "=" sign to be encoded. (Without the script, it open a page with a button and after click a blank page as the component was used inside a page (after calling the shell component))

If anybody can explain to me where I am wrong, thanks in advance !

plot_geojson_export_single_0.sql :

SET project_id = (SELECT project_id FROM project_plots WHERE plot_id=$plot_id::INTEGER);
SET redirect_page = '/e_project_plots/plot_main_display_4.sql?project_id';

SELECT
    'geojson'                               AS component,
    'Exporter un fichier geojson'           AS title,
    'plot_id_' || $plot_id                  AS filename,
    $redirect_page::TEXT                    AS redirect_page,
    $project_id::INTEGER                    AS project_id,
(SELECT
  jsonb_build_object(
    'type', 'Feature',
    'geometry', ST_AsGeoJSON(plot_geom)::jsonb,
    'properties', jsonb_build_object(
      'plot_id', plot_id,
      'project_id', project_id,
      'plot_title', plot_title,
      'plot_description', plot_description,
      'building_id', building_id,
      'plot_code_postal', plot_code_postal,
      'plot_code_land_registry', plot_code_land_registry,
      'plot_code_insee', plot_code_insee
    )
  )::text FROM project_plots WHERE plot_id = $plot_id::integer) AS content;

geojson.handlebars :

<div class="my-2 {{class}}" {{#if id}}id="{{id}}"{{/if}}>
        <a href="data:application/geo+json;charset=UTF-8,{{url_encode this.content}}"
        id="auto-download-geojson"
        download="{{default filename title}}.geojson"
        {{default title "Download"}}
        </a>
        <script>
            document.addEventListener('DOMContentLoaded', function() {
                document.getElementById('auto-download-geojson').click();
                window.location.href = '{{redirect_page}}={{project_id}}';
            });
        </script>
</div>

[lovasoa]

Hi !

You don't need a custom component for this, just use the built it download or json+http_header components:

• https://sql-page.com/component.sql?component=http_header
  • https://developer.mozilla.org/en-US/docs/Web/HTTP/Reference/Headers/Content-Disposition
• https://sql-page.com/component.sql?component=json

select
    'http_header' as component,
    format('attachment; filename="plot_id_%s.geojson"', $plot_id) as "Content-Disposition";
select 'json' as component,
    json_build_object(
      'type', 'Feature',
      ...
    ) as contents;

[SebastiendOrnano]

Thanks !

I discovered a postgis function which simplify the query :

SELECT
    'http_header' AS component,
    format('attachment; filename="plot_id_%s.geojson"', $plot_id) AS "Content-Disposition";

SELECT
    'json' AS component,
    (
      SELECT ST_AsGeoJSON(pp.*)::jsonb
      FROM project_plots pp
      WHERE pp.plot_id = $plot_id::integer
    ) AS contents;

[lovasoa]

you don't need the nested selects:

SELECT
    'http_header' AS component,
    format('attachment; filename="plot_id_%s.geojson"', $plot_id) AS "Content-Disposition";

SELECT
    'json' AS component,
    ST_AsGeoJSON(pp.*)::jsonb AS contents
FROM project_plots pp
WHERE pp.plot_id = $plot_id::integer;