3

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?

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.
  • 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
  • 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 2 '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 2 '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 2 '17 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.