Lab 7 - Raster Operations in PostGIS
Yikes! This last lab would have been exceptionally difficult if Dr. Morgan hadn't provided us with templates to fill in all of the complex scripts to finish the analysis. The scenario for the assignment entailed considering urban/residential vegetation cover as it relates to utility consumption for single-family homes.
One of the main student learning outcomes included importing raster data into PostgreSQL. The mechanics behind the steps I took to do so are described below (from my personal perspective):
1. Get the SRID of the raster dataset (using a Python script).
2. Import the raster dataset into PostgreSQL using the gdal-based utility called raster2pgsql.
a. The raster2pgsql utility is housed in a folder called bin in my working directory because we use a virtual machine for class. So this was the best way to connect/authenticate to my database in PostgreSQL. Similar to how we must also connect using the PostGIS Shapefile Import/Export tool for vector files.
b. I used a batch file titled import_rasters_sabillon.bat to authenticate my database connection and import the raster dataset through the raster2pgsql utility via a series of commands. These commands included information such as the port, database username, password, etc., as well as the current location of the raster dataset getting imported, its verified SRID code, and the location of my already created schema urban_veg, which is where I want to raster dataset to go into.
3. Accounted for directory structure when calling the .bat file in reference to where the raster2pgsql utility is located.
Also, we executed a series of queries to get some basic statistics of the our working raster dataset. Again, because I cannot provide screenshots of my scripts or SQL statements here in my blog because future students might cheat, instead see my short explanations below of what some of the queries returned:
- Returned the total number of rows (or in this case, tiles) in the urban_veg.naip raster table.
- Returned the total number of pixels in the urban_veg.naip raster table.
- Returned summary statistics (count, sum, mean, standard deviation, min, max) of band 2 from the urban_veg.naip raster table, ordered in a ‘Descending’ manner by the max value.
- Returned the summary statistics (count, sum, mean, standard deviation, min, max) for tile 2 in band 4.
Finally, we ran a script to convert the raster dataset to vector because currently we cannot view a raster PostGIS table from within ArcGIS. We ran a script that first resampled our raster dataset, and then converted it from pixels to polygons. My choropleth map below uses a resampled band 4 values (val).
One of the main student learning outcomes included importing raster data into PostgreSQL. The mechanics behind the steps I took to do so are described below (from my personal perspective):
1. Get the SRID of the raster dataset (using a Python script).
2. Import the raster dataset into PostgreSQL using the gdal-based utility called raster2pgsql.
a. The raster2pgsql utility is housed in a folder called bin in my working directory because we use a virtual machine for class. So this was the best way to connect/authenticate to my database in PostgreSQL. Similar to how we must also connect using the PostGIS Shapefile Import/Export tool for vector files.
b. I used a batch file titled import_rasters_sabillon.bat to authenticate my database connection and import the raster dataset through the raster2pgsql utility via a series of commands. These commands included information such as the port, database username, password, etc., as well as the current location of the raster dataset getting imported, its verified SRID code, and the location of my already created schema urban_veg, which is where I want to raster dataset to go into.
3. Accounted for directory structure when calling the .bat file in reference to where the raster2pgsql utility is located.
Also, we executed a series of queries to get some basic statistics of the our working raster dataset. Again, because I cannot provide screenshots of my scripts or SQL statements here in my blog because future students might cheat, instead see my short explanations below of what some of the queries returned:
- Returned the total number of rows (or in this case, tiles) in the urban_veg.naip raster table.
- Returned the total number of pixels in the urban_veg.naip raster table.
- Returned summary statistics (count, sum, mean, standard deviation, min, max) of band 2 from the urban_veg.naip raster table, ordered in a ‘Descending’ manner by the max value.
- Returned the summary statistics (count, sum, mean, standard deviation, min, max) for tile 2 in band 4.
Finally, we ran a script to convert the raster dataset to vector because currently we cannot view a raster PostGIS table from within ArcGIS. We ran a script that first resampled our raster dataset, and then converted it from pixels to polygons. My choropleth map below uses a resampled band 4 values (val).
Comments
Post a Comment