I have a series of points that I would like to convert to a raster, and then calculate the area of the raster as a proxy for "amount of area covered". I'm trying to do this using PostGIS rather than another language so that I can share my query easily with collaborators.
One annoyance is that my points are stored as json (not geojson).
id | points
---+--------
0 | [{'lat': 33.18424, 'lon': -100.0428, 'property': 'Group A'}, {'lat': 33.18424, 'lon': -100.0428, 'property': 'Group A'}, ...
1 | [{'lat': 33.18424, 'lon': -100.0423, 'property': 'Group A'}, {'lat': 33.18424, 'lon': -100.0458, 'property': 'Group A'}, ...
2 | [{'lat': 33.18436, 'lon': -100.0422, 'property': 'Group B'}, {'lat': 33.18420, 'lon': -100.0428, 'property': 'Group B'}, ...
So I have to do a little bit of work to get my points in a workable format.
SELECT ST_SetSRID(ST_Point((latlon->'lon')::text::numeric, (latlon->'lat')::text::numeric), 4326)::geometry as geom
FROM (SELECT JSON_ARRAY_ELEMENTS(points::json) as latlon
FROM my_table) as my_table
This works fine, but now I am having some trouble getting ST_AsRaster
to work for me. I want the pixels to represent a 10mx10m area however there are so many variants of ST_AsRaster
I am not sure how to specify this correctly. Here is my full query:
WITH my_geom AS (SELECT ST_SetSRID(ST_Point((latlon->'lon')::text::numeric, (latlon->'lat')::text::numeric), 4326)::geometry as geom
FROM (SELECT JSON_ARRAY_ELEMENTS(points::json) as latlon
FROM my_table) as my_table)
SELECT ST_AsRaster(geom, scalex=10.0, scaley=10.0) as my_raster
FROM my_geom
I think I may need to transform the point geometry SRID or otherwise somehow specify units (meters) for ST_AsRaster
but I am not sure this is possible.