3

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;
  • You have to change 2 things in your query: 1. add an alias 'geom' right after 1, 1, NULL)) – tilt Feb 3 '16 at 20:21
  • Thanks for the response, I tried your solution and now I get a different 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. If I recall, this is the same error that comes when I tried to directly turn the AsGeoJSON(ToRaster) – Simopaa Feb 4 '16 at 4:58
  • Please update your original question with what you now have. Also, note that the advice of @tilt to remove the brackets round (px) is incorrect. ST_PixelAsPolygons returns a set of records, so the (px).geom is essential to access the geometry. If you run the test example in my answer that you reference, you will see this is the case. – John Powell Feb 4 '16 at 7:21
  • Also it would be nice if you would accept the answer to the previous answer, assuming that was you who asked it. It took a fair amount of work and clearly answers the question as stated. – John Powell Feb 4 '16 at 7:37
  • @JohnBarça Yes, I tried with the code in your answer and it indeed works. I'm not the original asker, but a huge thank you to you anyway! – Simopaa Feb 4 '16 at 11:32
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;

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.