Add new attribute in GeoJSON from PostGIS

Question

I got this code below on web to solve my problem (from here [ How to Export Feature GeoJSON from PostGIS ]). Basically this code get my PostGIS table and transform to GeoJSON. It is what I needed. Now I need to increment the code, however I cannot. I tried search on web, but without success.

The increment that I would like to do is to create a new "key" called "tags", with the tags of my table node that they are in other table, called node_tag.

This is the original PostGIS code:

SELECT jsonb_build_object(
    'type',     'FeatureCollection',
    'features', jsonb_agg(feature)
) AS row_to_json 
FROM (
    SELECT jsonb_build_object(
        'type',       'Feature',
        'geometry',   ST_AsGeoJSON(row.geom)::jsonb,
        'properties', to_jsonb(row) - 'geom' - 'visible'  -- what attributes don't appear
    ) AS feature
    FROM (
        SELECT * FROM node As p
        WHERE p.id = 1
    ) row
) features;

The code above generates this GeoJSON:

{
    'row_to_json': {
        'features': [
            {
                'geometry': {'coordinates': [[0, 0]], 'type': 'MultiPoint'},
                'properties': {'id': 1, 'visible': True},
                'type': 'Feature',
            }
        ],
        'type': 'FeatureCollection'
    }
}

This GeoJSON is good for me, when I don't need the tags, but now I would like to put the "tags" key...

The code to get the tags of a node, when node fk_node_id=1 is this:

SELECT id, k, v FROM node_tag WHERE fk_node_id = 1;

With base of the codes above, I can get the tags, for example:

SELECT jsonb_build_object(
    'tags', jsonb_agg(tags)
    ) AS row_to_json
FROM (
    SELECT to_jsonb(row_tags) - 'version' - 'fk_node_id' AS tags -- what attributes don't appear
    FROM (
        SELECT * FROM node_tag As pt
        WHERE pt.fk_node_id = 1
    ) row_tags
) features;

It generates this GeoJSON:

{
    "row_to_json": {
        "tags": [
            {"fk_node_version": 1, "k": "name", "id": 1, "v": "house"},
            {"fk_node_version": 1, "k": "building", "id": 2, "v": "yes"}
        ]
    }
}

The problem is... I would like to put the both GeoJSON together. It is what I am trying to do, but I can't. I would like a GeoJSON similar with this:

{
    'row_to_json': {
        'features': [
            {
                'geometry': {'coordinates': [[0, 0]], 'type': 'MultiPoint'},
                'properties': {'id': 1, 'visible': True},
                'type': 'Feature',
                'tags': [
                    {'k': 'name', 'id': 1, 'v': 'house', 'fk_node_id': 1},
                    {'k': 'building', 'id': 2, 'v': 'yes', 'fk_node_id': 1}               
                ]
            }
        ],
        'type': 'FeatureCollection'
    }
}

Someone have any idea?

score 2 · Accepted Answer · edited Nov 02 '17 at 21:19

2

I think you want something like this. I unwrapped some of the complexity.

SELECT jsonb_build_object(
    'type',       'FeatureCollection',
    'features',   jsonb_agg(jsonb_build_object(
        'type',       'Feature',
        'geometry',   ST_AsGeoJSON(node.geom)::jsonb,
        'properties', to_jsonb(node) - 'geom' - 'visible' - 'version',
        'tags',       tags.jsontags
    ))
) AS row_to_json
FROM node
CROSS JOIN LATERAL (
    SELECT jsonb_agg(row_tag) AS jsontags 
    FROM (
        SELECT id, k, v FROM node_tag 
        WHERE fk_node_id = node.id
    ) row_tag
) AS tags
WHERE id=1;

You may have to fix a GROUP BY but it should be easy. If it doesn't work, provide:

DDL
Data
Desired output.

edited Nov 02 '17 at 21:19

rmmariano

257
2
7

answered Oct 30 '17 at 18:39

Evan Carroll

7,071
2
32
58

1

That's a pretty cool answer. I have no idea if it works or not, but combining jsonb_build_object with CROSS JOIN LATERAL is a great idea. The SQL looks kind of bizarre, though :D – John Powell Oct 30 '17 at 21:40
Thank you so much, your code is awesome :D
When I used the pure code, it didn't work. I did few modifications to work, like create a new subquery inside the "FROM node_tag As pt". After that, the code worked.

Could I edit the code above with the version that worked with me?

Really, like John said, to use the jsonb_build_object with CROSS JOIN LATERAL was a great idea. :D
– rmmariano Oct 31 '17 at 17:23
1

@rmmariano have at it (you can make the edits), I got an upvote and I have no sample data just a half-functional mental version of PostgreSQL and what you may want. ;) – Evan Carroll Oct 31 '17 at 17:39
1

@EvanCarroll thank you for help. I have already done the modifications :D – rmmariano Nov 02 '17 at 21:20
1

You should be able to make it even shorter than that @rmmariano... Collapse in the inner CROSS JOIN LATERAL part: SELECT jsonb_agg(ROW(id,k,v)) FROM node_tag WHERE fk_node_id = node.id – Evan Carroll Nov 02 '17 at 21:21
1

Well, the code worked and I am beginner on PostGIS. haha I will attempt to do it shorter, if I can, I will edit again. :D ... I tested your code and it returned the keys of tags like "f1", "f2" and "f3", instead of "id", "k" and "v". Searching on web I found a similar code that I could to put the name of the keys. The code edited is: SELECT json_agg(json_build_object('id', id, 'k', k, 'v', v)) AS jsontags . Is it ok? – rmmariano Nov 02 '17 at 21:55
Ah, I see what you're doing, you can try that and benchmark it and see if it's slower or not. It's a little bit more explicit than the cast from the row to the JSON whatever syntax you prefer. – Evan Carroll Nov 02 '17 at 22:07

Add new attribute in GeoJSON from PostGIS

1 Answers1