The previous queries returned basic information about the raster and returned collections with the data. In PostgreSQL, there are a series of functions that return geometries from queries. This section will cover a few of those functions.
A raster is comprised of a matrix of cells and values. These cells become georeferenced pixels in our raster data. Using PostgreSQL, you can query your raster data for a specific cell and get the polygon representation of that cell back. The following code shows you how:
cursor.execute("select rid, ST_asText(ST_PixelAsPolygon(rast,7,2)) from bigi;")
cursor.fetchall()
Using ST_PixelAsPolygons, you can pass the raster column, the column, and the row of a cell and get back polygon geometry for that cell. By wrapping the query in ST_AsText, you get back the WKT representation of the polygon instead of the binary.
The following is the result:
[(1,
'POLYGON((-46.629773 45.105389,-36.629773 45.105389,-36.629773
55.105389,-46.629773 55.105389,-46.629773 45.105389))')]
The previous output returned the rid (raster ID) of the pixel. Since you did not tile the raster when loading it into PostgreSQL, all the queries will return a rid of 1.
The previous query returned a polygon, but you can use functions to return points. Using ST_PixelAsPoints and ST_PixelAsCentroids, you can retrieve a point for every pixel in the raster dataset.
Using ST_PixelAsPoints, you can retrieve a point geometry representing the upper-left corner of each pixel. The query also returns the x and y of the cell and the value. The following code will show you how:
cursor.execute("SELECT x, y, val, ST_AsText(geom) FROM (SELECT (ST_PixelAsPoints(rast, 1)).* FROM bigi) as foo;")
cursor.fetchall()
The previous code has a two-part query. Starting after the FROM statement, the query selects the pixels as points for band 1. The first statement performs a select on the results and retrieves the point geometry, and the x, y, and value of the cell. ST_PixelAsPoints, by default, does not return data for cells with no values. You can pass the third parameter as false to return cells with no values.
The output of the previous query is an array with a row for each cell. Each row contains the x,y, value, and geometry. The results are shown as follows:
[(1, 1, 10.0, 'POINT(-106.629773 35.105389)'),
(2, 1, 10.0, 'POINT(-96.629773 35.105389)'),
(3, 1, 1.0, 'POINT(-86.629773 35.105389)'),
(4, 1, 10.0, 'POINT(-76.629773 35.105389)'),
(5, 1, 10.0, 'POINT(-66.629773 35.105389)'),
(6, 1, 10.0, 'POINT(-56.629773 35.105389)'),
(7, 1, 10.0, 'POINT(-46.629773 35.105389)'),
(1, 2, 1.0, 'POINT(-106.629773 45.105389)'),
(2, 2, 1.0, 'POINT(-96.629773 45.105389)'),
(3, 2, 1.0, 'POINT(-86.629773 45.105389)'),
(4, 2, 50.0, 'POINT(-76.629773 45.105389)'),
(5, 2, 10.0, 'POINT(-66.629773 45.105389)'),
(6, 2, 10.0, 'POINT(-56.629773 45.105389)'),
(7, 2, 50.0, 'POINT(-46.629773 45.105389)'),
(1, 3, 10.0, 'POINT(-106.629773 55.105389)'),
(2, 3, 1.0, 'POINT(-96.629773 55.105389)'),
(3, 3, 1.0, 'POINT(-86.629773 55.105389)'),
(4, 3, 51.0, 'POINT(-76.629773 55.105389)'),
(5, 3, 10.0, 'POINT(-66.629773 55.105389)'),
(6, 3, 10.0, 'POINT(-56.629773 55.105389)'),
(7, 3, 50.0, 'POINT(-46.629773 55.105389)'),
(1, 4, 1.0, 'POINT(-106.629773 65.105389)'),
(2, 4, 1.0, 'POINT(-96.629773 65.105389)'),
(3, 4, 1.0, 'POINT(-86.629773 65.105389)'),
(4, 4, 1.0, 'POINT(-76.629773 65.105389)'),
(5, 4, 50.0, 'POINT(-66.629773 65.105389)'),
(6, 4, 10.0, 'POINT(-56.629773 65.105389)'),
(7, 4, 50.0, 'POINT(-46.629773 65.105389)')]
Using ST_PixelAsCentroids, you can get a point that represents the centroid of the pixel or cell. The query is identical to the previous example and is shown as follows:
cursor.execute("SELECT x, y, val, ST_AsText(geom) FROM (SELECT (ST_PixelAsCentroids(rast, 1)).* FROM bigi) as foo;")
cursor.fetchall()
The previous query is in two parts. It first executes the ST_PixelAsCentroids function and then selects the x,y, value, and geometry from that result set. The output is shown as follows. Notice that the points are different than in the previous example:
[(1, 1, 10.0, 'POINT(-101.629773 40.105389)'),
(2, 1, 10.0, 'POINT(-91.629773 40.105389)'),
(3, 1, 1.0, 'POINT(-81.629773 40.105389)'),
(4, 1, 10.0, 'POINT(-71.629773 40.105389)'),
(5, 1, 10.0, 'POINT(-61.629773 40.105389)'),
(6, 1, 10.0, 'POINT(-51.629773 40.105389)'),
(7, 1, 10.0, 'POINT(-41.629773 40.105389)'),
(1, 2, 1.0, 'POINT(-101.629773 50.105389)'),
(2, 2, 1.0, 'POINT(-91.629773 50.105389)'),
(3, 2, 1.0, 'POINT(-81.629773 50.105389)'),
(4, 2, 50.0, 'POINT(-71.629773 50.105389)'),
(5, 2, 10.0, 'POINT(-61.629773 50.105389)'),
(6, 2, 10.0, 'POINT(-51.629773 50.105389)'),
(7, 2, 50.0, 'POINT(-41.629773 50.105389)'),
(1, 3, 10.0, 'POINT(-101.629773 60.105389)'),
(2, 3, 1.0, 'POINT(-91.629773 60.105389)'),
(3, 3, 1.0, 'POINT(-81.629773 60.105389)'),
(4, 3, 51.0, 'POINT(-71.629773 60.105389)'),
(5, 3, 10.0, 'POINT(-61.629773 60.105389)'),
(6, 3, 10.0, 'POINT(-51.629773 60.105389)'),
(7, 3, 50.0, 'POINT(-41.629773 60.105389)'),
(1, 4, 1.0, 'POINT(-101.629773 70.105389)'),
(2, 4, 1.0, 'POINT(-91.629773 70.105389)'),
(3, 4, 1.0, 'POINT(-81.629773 70.105389)'),
(4, 4, 1.0, 'POINT(-71.629773 70.105389)'),
(5, 4, 50.0, 'POINT(-61.629773 70.105389)'),
(6, 4, 10.0, 'POINT(-51.629773 70.105389)'),
(7, 4, 50.0, 'POINT(-41.629773 70.105389)')]
The previously mentioned functions returned geometry for all of the pixels in the raster dataset. Both of these functions have a corresponding function which allows you to specify a single pixel.
Removing the plural from centroids and points will allow you to specify single pixels, but will not return the x, y, and value. The following code shows you how to query a single pixel as a centroid:
cursor.execute("SELECT ST_AsText(ST_PixelAsCentroid(rast,4,1)) FROM bigi;")
cursor.fetchall()
The previous code uses ST_PixelAsCentroid and passes the raster, row, and column. The result is a single centroid point geometry for the cell which has been specified. The output is shown as follows:
[('POINT(-71.629773 40.105389)',)]
Wrapping the query in ST_AsText resulted in the output being returned in WKT.