6

So, I realize this question gets asked a lot, but the root of my question is related to the syntax of the SQL statement used to format the result table as GeoJSON. I am currently able to execute a SELECT * statement using this code which I adapted from an example I found online

SELECT row_to_json(fc)
FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
FROM (SELECT 'Feature' As type
   , ST_AsGeoJSON(lg.geom)::json As geometry
   , row_to_json((SELECT l FROM (SELECT id, filename, type, \"desc\") As l
     )) As properties
  FROM public.\"Peng\" As lg   ) As f )  As fc;

The majority of examples suggest using the json_build_object function, but the examples I've found are a bit opaque to me, particularly the nested statements and truncated variables. Conceptually, I know the statements are creating the features collection and then the nested features, but it's unclear how to reference my tables and fields correctly. In my case I'm trying to get the results of this query returned as JSON for my app: SELECT * FROM public."Peng", public."Address_Info" WHERE "Peng".id = "Address_Info".id; Eventually, I would be interested pulling specific fields, but for now I'd just be happy to understand how to adapt either my, or a json_build_object example to my use case. Additionally, any insight into how this syntax works, and the shortened variables and such.

3
  • 2
    I wrapped the ST_AsGeoJSON(RECORD) function into a moving aggregate UDF here: it returns a FeatureCollection as JSONB. You'd create a table with the desired properties and the geometry in a sub-query, as per the docs. Might be a handy function set if you need to create these regularly.
    – geozelot
    Jan 16, 2022 at 14:41
  • 1
    This answer should help.
    – geozelot
    Jan 16, 2022 at 14:48
  • Thanks for your comment and example. The syntax is still pretty confusing as it's not clear how the tables are being referenced, which ID is necessary, and how to format the WHERE clause. I'm completely lost with referencing the tables as single letters, I just want to pull a collection of points with some supplemental descriptive data from another table into a feature collection.
    – nizz0k
    Jan 16, 2022 at 16:14

1 Answer 1

2

You'd want to run:

SELECT JSONB_BUILD_OBJECT(     --aggregate features into FeatureCollection
         'type',     'FeatureCollection',
         'features', JSONB_AGG(features.feature)
       ) AS featurecollection
FROM   (
  --subquery to generate GeoJSON Features from "table_rows", aliased "AS features"
  SELECT JSONB_BUILD_OBJECT(   --create a Feature per row
           'type',       'Feature',
           'id',         table_rows.id,  --you don't need this, but you can have it
           'geometry',   ST_AsGeoJSON(table_rows.geom)::JSONB,
           'properties', TO_JSONB(table_rows.*) - 'geom' - 'id'  --aggregate all (table_rows.*) columns from "table_rows" into 'properties' object, but remove 'id' and 'geom'
         ) AS feature
  FROM   (
    --sub(sub)query to fetch desired rows from tables, aliased "AS table_rows"
    SELECT *  -- make sure this won't return two columns called "id"
    FROM   public."Peng" AS p   --example for aliasing (naming) a table for convenience: public."Peng" is now called p
    JOIN   public."Address_Info" AS i
      ON   p.id = i.id          --use the given aliases throughout the query

  ) AS table_rows

) AS features
;

or, if you had that mentioned function set installed:

SELECT ST_AsFeatureCollection(table_rows.*) AS geojson
FROM   (
    SELECT *
    FROM   public."Peng" AS p
    JOIN   public."Address_Info" AS i
      ON   p.id = i.id
) AS table_rows
;
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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