1

The ST_AsGeoJSON function with the following query:

SELECT ST_AsGeoJSON(t.*) FROM vw_ph t (ph, capture_datetime) LIMIT 1;

Returns the following:

  {
     "type":"Feature",
     "geometry":{
        "type":"Point",
        "coordinates":[
           -88.987035,
           30.161476
        ]
     },
     "properties":{
        "id":1,
        "capture_datetime":"2020-08-26T19:51:05.575-05:00",
        "ph":16,
        "boat_datetime":"2020-08-26T19:51:05.538-05:00",
        "latitude":30.161476,
        "longitude":-88.987035,
        "an_point_geom":{
           "type":"Point",
           "coordinates":[
              -88.987035,
              30.161476
           ]
        }
     }
  }

I am trying to get a geojson output without having the geometry column an_point_geom repeated in the properties field as below as it will add unnecessary size in a large query and polygon geometry.

The closest I have been to this is with the query below.

SELECT ST_AsGeoJSON((t.ph , t.capture_datetime, t.an_point_geom, t.boat_datetime)) FROM vw_ph t LIMIT 1;

However, this returns the result without the geometry column in properties but the columns are named f1, f2... which is not desirable.

  {
     "type":"Feature",
     "geometry":{
        "type":"Point",
        "coordinates":[
           -88.987035,
           30.161476
        ]
     },
     "properties":{
        "f1":16,
        "f2":"2020-08-26T19:51:05.575-05:00",
        "f4":"2020-08-26T19:51:05.538-05:00"
     }
  }

Is there any way to exclude the geometry column from properties without another query?

I am using PostGIS 3.0.2 in PostgreSQL 9.6.

1

Could it be that you have two geometry columns? With just one, it should not be repeated in the properties.

Anyways, you can keep using the complete record t.*, but filter out columns before:

SELECT ST_AsGeoJSON(t2.*)
FROM 
  (SELECT id, name, geom_aaa --Filter the columns here
    FROM
      (VALUES
      (1, 'one', 'POINT(1 1)'::geometry,'POINT(10 10)'::geometry),
      (2, 'two', 'POINT(2 2)', 'POINT(20 20)'),
      (3, 'three', 'POINT(3 3)', 'POINT(30 30)'))
    AS t(id, name, geom_aaa, geom_bbb)) 
 AS t2;
                                                  st_asgeojson
-----------------------------------------------------------------------------------------------------------------
 {"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]}, "properties": {"id": 1, "name": "one"}}
 {"type": "Feature", "geometry": {"type":"Point","coordinates":[2,2]}, "properties": {"id": 2, "name": "two"}}
 {"type": "Feature", "geometry": {"type":"Point","coordinates":[3,3]}, "properties": {"id": 3, "name": "three"}}
| improve this answer | |
  • I had another geometry column! – wondim Aug 27 at 15:34

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.