I have a PostGIS table with three columns (rid, rast, fileName), now I want to add a geometry column and populate the geometry column using the rast column. A single rast index may have multiple geometries. Normally to get geometry I use:-

SELECT geom FROM elevationData,LATERAL ST_PixelAsPoints(rast, 1)

How can I add and populate the geometry column? I am getting confused on how to use the WHERE clause in this scenario.

Current PostGIS Table


If your raster tiles are larger than 1x1, it doesn't make sense to add a geometry column to the same table. ST_PixelAsPoints will generate 900 points for each 30x30 raster tile, which means that you'll either need to store the points as a MultiPoint, or duplicate each raster tile 900 times.

The best options are to

  1. Keep the table with the raster only, and generate points on the fly if and when you need them, or
  2. Create a table with the points only (not the raster), with the rid column allowing you to join if and when you need to. For example:
CREATE TABLE rasterpoints AS
SELECT rid, (ST_PixelAsPoints(rast, 1)).*
FROM your_raster_table;

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.