Join columns, separate items with with \n, and export as geojson?

(1) By Gilles on 2020-05-15 17:08:06 [source]

Hello,

Before I look how to write this in Python, I was wondering if SQLite had the following internal features:

Join some columns so that I only get a single column through SELECT, where each original item should have a n added so they're still separate
Export the table as geojson instead of CVS.

The goal is to turn the following table into a geojson file, where items in the <desc>…</desc> field must be separated with n:

CREATE TABLE camping(id INTEGER PRIMARY KEY,name TEXT,latitude TEXT,longitude TEXT,phone TEXT,www TEXT,email TEXT);

→

{ "geometry": { "coordinates": [ 2.1239, 48.123 ], "type": "Point" }, "properties": { "description": "email=blah@blah.comnphone=123-4567", "name": "Some place" }, "type": "Feature" }, { "geometry": { "coordinates": [ 2.1239, 48.123 ], "type": "Point" }, "properties": { "description": "www=www.acme.com", "name": "Some other place" }, "type": "Feature" },

etc.

Thank you.

(2) By Clemens Ladisch (cladisch) on 2020-05-16 07:26:14 in reply to 1 [link] [source]

Join some columns so that I only get a single column through SELECT, where each original item should have a \n added so they're still separate

This is what the group_concat function does, as long as you convert the columns to rows.

To create JSON-encoded text, use the JSON functions:

SELECT json_object(
    'geometry', json_object(
        'coordinates', json_array(longitude, latitude),
        'type', 'Point'),
    'properties', json_object(
        'description', (SELECT group_concat(p, char(10))
                        FROM (SELECT 'email=' || email AS p
                              UNION ALL
                              SELECT 'www='   || www
                              UNION ALL
                              SELECT 'phone=' || phone)),
        'name', name),
    'type', 'Feature')
FROM camping;