Adding and populating Geometry column from an existing table

Question

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.

Answer 1

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;