PostGIS GeoJSON Export – Reformat SQL Statement for Exporting GeoJSON from PostGIS Table

geojsonpostgispostgresqlsql

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.

Best Answer

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
;

Related Solutions

[GIS] the best approach to using IQueryFilter.WhereClause in ArcObjects

Use the methods available on the ISQLSyntax interface to make your code workspace-independent.

As the help on IQueryFilter.WhereClause explains, use the ISQLSyntax.GetSpecialCharacter method to return the delimited identifier prefix and suffix specific to your workspace and add them to your column identifiers.

Example:

ISQLSyntax sqlSyntax = (ISQLSyntax)((IDataset)table).Workspace;
string fieldPrefixDelimiter = sqlSyntax.GetSpecialCharacter(
    esriSQLSpecialCharacters.esriSQL_DelimitedIdentifierPrefix);
string fieldSuffixDelimiter = sqlSyntax.GetSpecialCharacter(
    esriSQLSpecialCharacters.esriSQL_DelimitedIdentifierSuffix);
whereClause = String.Format("{0}{1}{2} IN({3})",
    fieldPrefixDelimiter,
    fieldName,
    fieldSuffixDelimiter,
    delimitedValueString);

Of course you would still have to check field types to build the part after the operator -- delimitedValueString is produced by some other logic that checks the field type and builds up a list of values to put in the IN statement.

That part is implementation specific as to what field types you want to support. In my case I just wrap strings and GUIDs in single-quotes, pass numeric types through unchanged, and throw an error for other unsupported field types (I wouldn't know how to or want to handle date fields for example).

[GIS] Changing Buffer table geometry dynamically in PostGIS using plain SQL

You can use a view, but you can also use triggers to automatically update your buffer table when you modify the original point table. This is really useful if you don't want to regenerate the buffers every time you view your table, as buffer computation is a cpu-intensive task.

Here is a full code sample which implements it : a point table and a point_buffer table which is automatically updated based on the point table modifications.

You can test it with QGIS : open both tables, enter edit mode on the point table. Move a point or change the buffer_distance value, and every time you save, the buffer layer will be updated.

enjoy :)

drop table if exists point;
create table point (
    gid serial primary key
    , point_name varchar
    , buffer_distance double precision
    , the_geom geometry
);

drop table if exists point_buffer;
create table point_buffer (
    gid serial primary key
    , point_gid integer
    , the_geom geometry
);

select populate_geometry_columns();

insert into 
    point (point_name, buffer_distance, the_geom) 
select
    'point ' || n::varchar as point_name
    , random() * 100 + min_buf as buffer_distance
    , st_setsrid(st_point(random() * 10000 + x0, random() * 10000 + y0), 2154) as the_geom
from
        generate_series(1, 1000) as n
        , (values (10)) as foox(x0)
        , (values (10)) as fooy(y0)
        , (values (10)) as buf(min_buf);

-- insert values into point_buffer
insert into
    point_buffer (point_gid, the_geom)
select
    gid as point_gid
    , st_buffer(the_geom, buffer_distance)
from
    point;

-- update all point_buffer
update
    point_buffer as pb
set
    the_geom = st_buffer(p.the_geom, p.buffer_distance)
from
    point as p
where
    p.gid = pb.point_gid;

-- add trigger to automate insert / delete / update
create or replace function update_point_buffer() returns trigger as
$$
begin
    -- delete
    IF (TG_OP = 'DELETE') THEN
        delete from point_buffer as pb where point_gid = OLD.gid;
        return OLD;
    -- insert
    ELSIF (TG_OP = 'INSERT') THEN
        insert into
            point_buffer (point_gid, the_geom)
        select
            NEW.gid as point_gid
            , st_buffer(NEW.the_geom, NEW.buffer_distance);
        return NEW;
    -- update
    else
        update
            point_buffer as pb
        set
            the_geom = st_buffer(NEW.the_geom, NEW.buffer_distance)
        where
            pb.gid = NEW.gid;
        return NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_point_point_buffer ON point;
CREATE TRIGGER trg_point_point_buffer AFTER DELETE OR INSERT OR UPDATE ON point
    FOR EACH ROW EXECUTE PROCEDURE update_point_buffer();

/* use it */

-- insert
insert into 
    point (point_name, buffer_distance, the_geom)
select
    'added point to test trigger' as point_name
    , random() * 100 + min_buf as buffer_distance
    , st_setsrid(st_point(random() * 10000 + x0, random() * 10000 + y0), 2154) as the_geom
from
        (values (10)) as foox(x0)
        , (values (10)) as fooy(y0)
        , (values (10)) as buf(min_buf);

select
    st_astext(pb.the_geom)
    , *
from 
    point_buffer as pb
join
    point as p
on
    p.gid = pb.point_gid
where
    p.point_name = 'added point to test trigger';

-- update
update 
    point as p
set
    the_geom = st_setsrid(st_point(0, 0), 2154)
    , buffer_distance = 1
where
    p.point_name = 'added point to test trigger';

-- check point_buffer
select
    st_astext(pb.the_geom)
    , *
from 
    point_buffer as pb
join
    point as p
on
    p.gid = pb.point_gid
where
    p.point_name = 'added point to test trigger';

-- delete
delete from
    point as p
where
    p.point_name = 'added point to test trigger';

-- check point_buffer
select
    *
from
    point_buffer as pb
where
    point_gid = 1001;

Best Answer

Related Solutions

[GIS] the best approach to using IQueryFilter.WhereClause in ArcObjects

[GIS] Changing Buffer table geometry dynamically in PostGIS using plain SQL

Related Question