PostGIS convert point->raster->GeoJSON

Question

My question is a follow up to Convert a raster to GeoJSON polygons in PostGIS.

I'm trying to accomplish querying point data from the database, and then turn that into a raster for calculating (average of a field), and finally get a GeoJSON output.

My query is as follows:

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((px).geom)::json As geometry 
                , row_to_json((SELECT props FROM (SELECT (px).id as Id, (px).particles as Particles) as props )) As properties
               FROM 
                (SELECT ST_PixelAsPolygons(
                    ST_SetValue(
                        ST_SetValue(
                            ST_AddBand(
                                ST_AsRaster(sd.point,5, 5),
                            '8BUI'::text, 1, 0),
                        2, 2, 10),
                    1, 1, NULL)) 
                FROM sensordata as sd) px 
                 ) f 
    ) fc;

This results in an error:

pg_query(): Query failed: ERROR: column px.geom does not exist LINE 5: ST_AsGeoJSON((px).geom)::json As geometry

I think it's something obvious, but I just can't seem to figure out what's up.

After attempting the solution proposed by Tilt I get a new kind of error message:

ERROR: function st_asgeojson(record) does not exist LINE 5: ST_AsGeoJSON(px.geom)::json As geometry ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Here's the final working query.

WITH pixelrecords AS (
        SELECT ST_PixelAsPolygons(
            ST_SetValue(
            ST_SetValue(
                ST_AddBand(
                ST_AsRaster(data.point,5, 5),
                '8BUI'::text, 1, 0),
            2, 2, 10),
            1, 1, NULL)
    ) px, data.id as Id, data.particles as Particles
            FROM sensordata as data
        )
        ,features AS (
        SELECT 'Feature' As type,
            ST_AsGeoJSON((px).geom)::json As geometry 
            --This will likely go wrong since there is no id or Particles in the px record, only val, geom, x and y
            --You can better get the values from the original sensordata records and transfer them along with the polygon
            ,row_to_json((SELECT props FROM (SELECT (Id), (Particles)) as props )) As properties
               FROM 
            pixelrecords px 
        )

        SELECT 
            'FeatureCollection' As type, 
            array_to_json(array_agg(f)) As features
        FROM features f;

Answer 1

I recreated your query to see where it goes wrong. The last error you came across is because of the faulty reference of px instead of geom (partly my mistake because I gave the hint of aliasing with geom) but there would likely have been more errors after that. Here's the query I created out of it, as you can see the sensordata has been replaced with just one imaginary point.

WITH pixelrecords AS (
    SELECT ST_PixelAsPolygons(
        ST_SetValue(
        ST_SetValue(
            ST_AddBand(
            ST_AsRaster(ST_MakePoint(10,10),5, 5),
            '8BUI'::text, 1, 0),
        2, 2, 10),
        1, 1, NULL)) px

)
--Debug option: Check the contents from px
--SELECT (px).* FROM pixelrecords;
,features AS (
SELECT 'Feature' As type,
    ST_AsGeoJSON((px).geom)::json As geometry 
    --This will likely go wrong since there is no id or Particles in the px record, only "val, geom, x and y"
    --You can better get the values from the original sensordata records and transfer them along with the polygon
    --, row_to_json((SELECT props FROM (SELECT (px).id as Id, (px).particles as Particles) as props )) As properties
       FROM 
    pixelrecords px 
)

SELECT 
'FeatureCollection' As type, 
array_to_json(array_agg(f)) As features
FROM features f;