Convert a raster to GeoJSON polygons in PostGIS

Question

I'm trying to convert a raster to a polygon and export it from PostGIS as GeoJSON. Specifically I want each pixel to be a polygon with associated the raster value.

PostGIS seems to have a few different functions for converting rasters to polygons, I'm using the ST_PixelAsPolygons function because I want each pixel to be represented as its own polygon.

Example:

SELECT ST_PixelAsPolygon(rast) FROM tbl;

However this returns a set of records for each raster and I can't easily convert this to GeoJSON.

Does anyone know of a simple way to do this?

Answer 1

If you look at the example on the man page for ST_PixelAsPolygons you will see how you can access the geometries using table_alias.geom syntax (similar in spirit to how ST_Dump works to turn a set or records into individual rows). Following on from that example, you can pass (gv).geom to the ST_AsGeoJSON function, eg,

SELECT ST_AsGeoJSON((gv).geom) 
  FROM 
      (SELECT ST_PixelAsPolygons(
          ST_SetValue(
              ST_SetValue(
                  ST_AddBand(
                    ST_MakeEmptyRaster(2, 2, 0, 0, 0.001, -0.001, 0.001, 0.001, 4269), 
                                       '8BUI'::text, 1, 0), 
                    2, 2, 10), 
            1, 1, NULL)
     ) gv 
 ) foo;

which will return the three rows:

{"type":"Polygon","coordinates":[[[0.001,0.001],[0.002,0.002],[0.003,0.001],[0.002,0],[0.001,0.001]]]}

{"type":"Polygon","coordinates":[[[0.001,-0.001],[0.002,0],[0.003,-0.001],[0.002,-0.002],[0.001,-0.001]]]}

{"type":"Polygon","coordinates":[[[0.002,0],[0.003,0.001],[0.004,0],[0.003,-0.001],[0.002,0]]]}

If you wish to turn this into a FeatureCollection, you can use a combination of row_to_json, array_agg and array_to_json to group the rows together into an array and add in the "type":"FeatureCollection" before the features array. The general idea is that you select the 'Feature', the GeoJSON representation of the geometry and the properties in one subquery, and then use array_to_json and array_agg to group this into an array of features, with 'FeatureCollection' as type before the array. Finally, you select row_to_json to group this all together inside {}.

Note you have to do the nasty subselect on the properties -- in your case the raster pixel values -- to avoid the labels being f1, f2, etc. See this Postgres Online article for more information.

SELECT row_to_json(fc) 
  FROM 
    (SELECT 'FeatureCollection' as type, array_to_json(array_agg(feats)) as features 
      FROM 
         (SELECT 'Feature' as type, 
                 st_asgeojson((gv).geom)::json as geometry, 
                 row_to_json((SELECT props FROM (SELECT (gv).val as value) as props )) as properties  
            FROM 
                (SELECT 
                    ST_PixelAsPolygons(
                       ST_SetValue(
                         ST_SetValue(
                             ST_AddBand(
                                 ST_MakeEmptyRaster(2, 2, 0, 0, 0.001, -0.001, 0.001, 0.001, 4269), 
                            '8BUI'::text, 1, 0),
                        2, 2, 10), 
                     1, 1, NULL)
               ) gv 
          ) json
    ) feats 
) fc;

which now produces:

{"type":"FeatureCollection", 
     "features":[
           {"type":"Feature",
                "geometry":{"type":"Polygon","coordinates":[[[0.001,0.001],[0.002,0.002],[0.003,0.001],[0.002,0],[0.001,0.001]]]},
                "properties":{"value":1}
           },
           {"type":"Feature",
                "geometry":{"type":"Polygon","coordinates":[[[0.001,-0.001],[0.002,0],[0.003,-0.001],[0.002,-0.002],[0.001,-0.001]]]},
                "properties":{"value":1}
            },
            {"type":"Feature",
                "geometry":{"type":"Polygon","coordinates":[[[0.002,0],[0.003,0.001],[0.004,0],[0.003,-0.001],[0.002,0]]]},
                "properties":{"value":10}
            }
     ]
}

Note: You need to cast the ST_AsGeoJSON to JSON, with the :: operator, or else you will end up with all the quotes being escaped, as \" in the output.