How to modify PostGIS query to add fields to GeoJSON properties?

Question

So, I'm building a simple mapping application with Leaflet and PostGIS stored data. I've succeeded in querying my layer and delivering the results to my client application but I'm struggling modifying the query to add additional fields to the properties. I have based my query on this helpful post:

"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, photo, type) As l
  )) As properties
.\"Peng\" As lg   ) As f )  As fc;";

The query produces valid GeoJSON with the expected properties of id, photo, and type.

The issue I'm having is adding my column names to (SELECT id, photo, type, desc) produces SQL syntax error 42601. The error occurs at whichever position I try to add an additional table column to the inner select statement. Googling suggests that this an issue with mixing dynamic and regular SQL. Unfortunately, my SQL skills are not quite where they need to be to resolve this.

So, how do I add additional columns the select statement so that I can add more properties values to my GeoJSON? My goal is to add several more properties to populate a dashboard and some popupcontent.

EDIT

So, a sample of my JSON object is here:

{"type":"FeatureCollection","features":[{"type":"Feature","geometry":{"type":"Point","coordinates":[8.697130556,50.123908333,108.5]},"properties":{"id":3,"photo":"/Users/Nizz0k/Desktop/Peng/IMG_3475.jpeg","type":"Quote"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[8.691429401,50.115307684,105.7]},"properties":{"id":4,"photo":"/Users/Nizz0k/Desktop/Peng/IMG_3025.jpeg","type":"Quote"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[8.694072891,50.119541651,111.5]},"properties":{"id":5,"photo":"/Users/Nizz0k/Desktop/Peng/IMG_1274.jpeg","type":"Drawing and Tag"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[8.697102778,50.119533333,112.8]},"properties":{"id":7,"photo":"/Users/Nizz0k/Desktop/Peng/IMG_4975.jpeg","type":null}},{"type":"Feature","geometry":{"type":"Point","coordinates":[8.729794444,50.113963889,99.6]},"properties":{"id":9,"photo":"/Users/Nizz0k/Desktop/Peng/IMG_5149.jpeg","type":null}},

The full result pastes into geojson.io without issues. My goal is to add more values to the properties object within the JSON.

Create table Statement is here:

CREATE TABLE IF NOT EXISTS public."Peng"
(
    id integer NOT NULL DEFAULT nextval('"Peng_id_seq"'::regclass),
    geom geometry(PointZ,4326),
    photo character varying(254) COLLATE pg_catalog."default",
    filename character varying(254) COLLATE pg_catalog."default",
    directory character varying(254) COLLATE pg_catalog."default",
    altitude numeric,
    direction numeric,
    longitude character varying(254) COLLATE pg_catalog."default",
    latitude character varying(254) COLLATE pg_catalog."default",
    "timestamp" character varying(24) COLLATE pg_catalog."default",
    weight bigint,
    type character varying(244) COLLATE pg_catalog."default",
    "desc" character varying(244) COLLATE pg_catalog."default",
    surface character varying(244) COLLATE pg_catalog."default",
    CONSTRAINT "Peng_pkey" PRIMARY KEY (id)
)

Ultimately, I want to add the "desc" and "surface" columns to the JSON. I will need to add more data later, so I'd really like to understand the syntax here.

Could you add a sample data and the expected result? Preferably in a fiddle with create table statements. — Jim Jones, Aug 06 '21 at 09:54
So, I've pulled the data into Postgres via QGIS. How do I get you the create table statement? And is there a preferred fiddle software for this? — nizz0k, Aug 06 '21 at 10:02
if you have pgadmin installed you can get the create table statements from there. You can always create a minimal reproducible example just to make your point. .. or at least add a link to the shapefile you used. But most importantly: the exact expected result set. Otherwise is highly unlikely you'll get any decent answer. — Jim Jones, Aug 06 '21 at 10:25
I am more than happy to accept your answer if you want to post it. Here the issue was fairly obtuse and your comments helped me see something that the SQL error was not really helpful with. — nizz0k, Aug 06 '21 at 12:28
I really appreciate the offer, but the work was all yours (the citation in your answer suffices). However, this answer might interest you: https://stackoverflow.com/a/60036769/2275388 if you want to import data without QGIS. Quite handy for scripting ;) happy coding! — Jim Jones, Aug 06 '21 at 13:10
QGIS is a part of my current workflow, which I'm trying to automate with time. I need to import geotagged images, ground truth them visually, add data, etc. At the moment, this plays well enough with the other pieces involved that I'm happy. Eventually I should do some python with this. Will for sure check the link. Thanks! — nizz0k, Aug 06 '21 at 13:16
I see. I also never got rid of QGIS, although PostGIS does pretty much everything I need :) In the answer I explain in detail how to import shapfiles into postgres, and you certainly can add it to a python script. cheers — Jim Jones, Aug 06 '21 at 13:19
Well, I'm pretty happy with being able to leverage on PGIS in QGIS and I need to leverage on PGIS more, but I'm not ready to give up a program with some heads-up display capabilties for tedious editing tasks. This is a bit of a pet project, so I expect iteration and changes to the stack to continue. thanks again — nizz0k, Aug 06 '21 at 13:26

score 1 · Answer 1 · answered Aug 06 '21 at 12:00

1

Kudos to Jim Jones for suggesting looking into the create table statement. Closer analysis showed that the 'desc' field was wrapped in quotes. Adding the field with double-quotes solved the error.

answered Aug 06 '21 at 12:00

nizz0k

471
1
8
23

How to modify PostGIS query to add fields to GeoJSON properties?

1 Answers1