Using PostgreSQL, you can perform various queries on your data. In this section, you will learn how to query the raster for basic metadata and properties. This section will present a few of the many PostgreSQL functions that are available.
You can query the data for a basic text summary. The following code shows you how to use the ST_Summary() function:
cursor.execute("select ST_Summary(rast) from bigi;")
cursor.fetchall()
The summary function takes the raster data column as a parameter and returns a string containing the size of the raster, the bounding box, the number of bands, and if there are no data values in any of the bands. The following is the output from the previous code:
[('Raster of 7x4 pixels has 1 band and extent of BOX(-106.629773 35.105389,-36.629773 75.105389)\n band 1 of pixtype 8BUI is in-db with no NODATA value',)]
Parsing out the individual pieces of information from ST_Summary would be difficult. You can retrieve this information in a more machine-readable format by using the ST_Metadata function. You can use the following code to do so:
cursor.execute("select ST_MetaData(rast) from bigi")
cursor.fetchall()
The previous code queries the raster for the upper-left X value, the upper-left Y value, the width, the height, the scale of X, the scale of Y, the skew of X, the skew of Y, the SRID, and the number of bands in the raster. The output is shown as follows:
[('(-106.629773,35.105389,7,4,10,10,0,0,4326,1)',)]
The output allows you to select individual pieces of metadata by using index notation, which is a simpler solution to parsing the string provided by ST_Summary.
You can query for specific and individual attributes of the raster. To get the raster as a single polygon—instead of the two-point box described in the summary—you can use the following code:
cursor.execute("select ST_AsText(ST_Envelope(rast)) from bigi;")
cursor.fetchall()
The output of the previous code is the WKT for a vector-polygon-of the raster. It is shown as follows:
[('POLYGON((-106.629773 75.105389,-36.629773 75.105389,-36.629773 35.105389,-106.629773 35.105389,-106.629773 75.105389))',)]
The following code will query the height and width of the raster:
cursor.execute("select st_height(rast), st_Width(rast) from bigi;") #st_width
cursor.fetchall()
As you may recall from earlier in the chapter, the raster is 4x7, as shown in the output:
[(4, 7)]
Another piece of metadata that may come in handy is the pixel size. The following code will show you how:
cursor.execute("select ST_PixelWidth(rast), ST_PixelHeight(rast) from bigi;")
cursor.fetchall()
Using ST_PixelWidth and ST_PixelHeight, you will get the output as follows. This matches the height and width from when you created the raster earlier in the chapter:
[(10.0,10.0)]
You can query the raster for basic statistical information about the data within the cells for a specific band. ST_SummaryStats provides basic summary statistics for the data values. The following code shows you how to query:
cursor.execute("select ST_SummaryStats(rast) from bigi;")
cursor.fetchall()
The output of the previous code returns the count, sum, mean, standard deviation, min, and max for a raster band. You can pass the raster band by passing it as an integer in the second parameter, ST_SummaryStats(rast,3). If you do not specify the band, the default is 1. The output is shown as follows:
[('(28,431,15.3928571428571,18.5902034218377,1,51)',)]
You can also query for a histogram of the values in the raster, as shown in the following code:
cursor.execute("SELECT ST_Histogram(rast,1) from bigi;")
cursor.fetchall()
The previous code uses ST_Histogram and passes the raster column and a band. You can pass the number of bins as the third parameter or you can let the function decide. The result is shown as follows:
[('(1,9.33333333333333,10,0.357142857142857)',),
('(9.33333333333333,17.6666666666667,12,0.428571428571429)',),
('(17.6666666666667,26,0,0)',),
('(26,34.3333333333333,0,0)',),
('(34.3333333333333,42.6666666666667,0,0)',),
('(42.6666666666667,51,6,0.214285714285714)',)]
The previous output is an array of bins. Each bin contains the minimum value, the maximum value, the count, and the percentage.