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

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

Hello,

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

1. 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

2. 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.com\nphone=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]

> 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]:

~~~sql
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;
~~~

[group_concat]: https://www.sqlite.org/lang_aggfunc.html#groupconcat
[JSON functions]: https://www.sqlite.org/json1.html