The functions given below are the ones which a user of PostGIS Raster is likely to need and which are currently available in PostGIS Raster. There are other functions which are required support functions to the raster objects which are not of use to a general user.
raster is a new PostGIS type for storing and analyzing raster data.
For loading rasters from raster files please refer to Section 10.1, “Loading and Creating Rasters”
For the examples in this reference we will be using a raster table of dummy rasters - Formed with the following code
CREATE TABLE dummy_rast(rid integer, rast raster);
INSERT INTO dummy_rast(rid, rast)
VALUES (1,
('01' -- little endian (uint8 ndr)
||
'0000' -- version (uint16 0)
||
'0000' -- nBands (uint16 0)
||
'0000000000000040' -- scaleX (float64 2)
||
'0000000000000840' -- scaleY (float64 3)
||
'000000000000E03F' -- ipX (float64 0.5)
||
'000000000000E03F' -- ipY (float64 0.5)
||
'0000000000000000' -- skewX (float64 0)
||
'0000000000000000' -- skewY (float64 0)
||
'00000000' -- SRID (int32 0)
||
'0A00' -- width (uint16 10)
||
'1400' -- height (uint16 20)
)::raster
),
-- Raster: 5 x 5 pixels, 3 bands, PT_8BUI pixel type, NODATA = 0
(2, ('01000003009A9999999999A93F9A9999999999A9BF000000E02B274A' ||
'41000000007719564100000000000000000000000000000000FFFFFFFF050005000400FDFEFDFEFEFDFEFEFDF9FAFEF' ||
'EFCF9FBFDFEFEFDFCFAFEFEFE04004E627AADD16076B4F9FE6370A9F5FE59637AB0E54F58617087040046566487A1506CA2E3FA5A6CAFFBFE4D566DA4CB3E454C5665')::raster);
This section lists the PostgreSQL data types specifically created to support raster functionality.
exclude_nodata_value is set to false, then all pixels include nodata pixels are considered to intersect and return value. If exclude_nodata_value is not passed in then reads it from metadata of raster.
NODATA value of a given band's pixel specified by a columnx and rowy or a geometric point expressed in the same spatial reference coordinate system as the raster.
NODATA values around a given band's pixel specified by either a columnX and rowY or a geometric point expressed in the same spatial reference coordinate system as the raster.
crop is not specified or TRUE, the output raster is cropped. If touched is set to TRUE, then touched pixels are included, otherwise only if the center of the pixel is in the geometry it is included.
TRUE if A's bounding box intersects B's bounding box.
TRUE if A's bounding box is to the left of B's.
TRUE if A's bounding box is to the right of B's.
TRUE if A's bounding box is the same as B's. Uses double precision bounding box.
TRUE if A's bounding box is contained by B's. Uses double precision bounding box.
TRUE if A's bounding box is the same as B's.
TRUE if A's bounding box is contains B's. Uses double precision bounding box.
This section documents various gotchas and tips related to PostGIS Raster.
When GDAL opens a file, GDAL eagerly scans the directory of that file to build a catalog of other files. If this directory contains many files (e.g. thousands, millions), opening that file becomes extremely slow (especially if that file happens to be on a network drive such as NFS).
To control this behavior, GDAL provides the following environment variable: GDAL_DISABLE_READDIR_ON_OPEN. Set GDAL_DISABLE_READDIR_ON_OPEN to TRUE to disable directory scanning.
In Ubuntu (and assuming you are using PostgreSQL's packages for Ubuntu), GDAL_DISABLE_READDIR_ON_OPEN can be set in /etc/postgresql/POSTGRESQL_VERSION/CLUSTER_NAME/environment (where POSTGRESQL_VERSION is the version of PostgreSQL, e.g. 9.6 and CLUSTER_NAME is the name of the cluster, e.g. maindb). You can also set PostGIS environment variables here as well.
# environment variables for postmaster process
# This file has the same syntax as postgresql.conf:
# VARIABLE = simple_value
# VARIABLE2 = 'any value!'
# I. e. you need to enclose any value which does not only consist of letters,
# numbers, and '-', '_', '.' in single quotes. Shell commands are not
# evaluated.
POSTGIS_GDAL_ENABLED_DRIVERS = 'ENABLE_ALL'
POSTGIS_ENABLE_OUTDB_RASTERS = 1
GDAL_DISABLE_READDIR_ON_OPEN = 'TRUE'
The maximum number of open files permitted by Linux and PostgreSQL are typically conservative (typically 1024 open files per process) given the assumption that the system is consumed by human users. For Out-DB Rasters, a single valid query can easily exceed this limit (e.g. a dataset of 10 year's worth of rasters with one raster for each day containing minimum and maximum temperatures and we want to know the absolute min and max value for a pixel in that dataset).
The easiest change to make is the following PostgreSQL setting: max_files_per_process. The default is set to 1000, which is far too low for Out-DB Rasters. A safe starting value could be 65536 but this really depends on your datasets and the queries run against those datasets. This setting can only be made on server start and probably only in the PostgreSQL configuration file (e.g. /etc/postgresql/POSTGRESQL_VERSION/CLUSTER_NAME/postgresql.conf in Ubuntu environments).
...
# - Kernel Resource Usage -
max_files_per_process = 65536 # min 25
# (change requires restart)
...
The major change to make is the Linux kernel's open files limits. There are two parts to this:
Maximum number of open files for the entire system
Maximum number of open files per process
You can inspect the current maximum number of open files for the entire system with the following example:
$ sysctl -a | grep fs.file-max
fs.file-max = 131072
If the value returned is not large enough, add a file to /etc/sysctl.d/ as per the following example:
$ echo "fs.file-max = 6145324" >> /etc/sysctl.d/fs.conf
$ cat /etc/sysctl.d/fs.conf
fs.file-max = 6145324
$ sysctl -p --system
* Applying /etc/sysctl.d/fs.conf ...
fs.file-max = 2097152
* Applying /etc/sysctl.conf ...
$ sysctl -a | grep fs.file-max
fs.file-max = 6145324
We need to increase the maximum number of open files per process for the PostgreSQL server processes.
To see what the current PostgreSQL service processes are using for maximum number of open files, do as per the following example (make sure to have PostgreSQL running):
$ ps aux | grep postgres
postgres 31713 0.0 0.4 179012 17564 pts/0 S Dec26 0:03 /home/dustymugs/devel/postgresql/sandbox/10/usr/local/bin/postgres -D /home/dustymugs/devel/postgresql/sandbox/10/pgdata
postgres 31716 0.0 0.8 179776 33632 ? Ss Dec26 0:01 postgres: checkpointer process
postgres 31717 0.0 0.2 179144 9416 ? Ss Dec26 0:05 postgres: writer process
postgres 31718 0.0 0.2 179012 8708 ? Ss Dec26 0:06 postgres: wal writer process
postgres 31719 0.0 0.1 179568 7252 ? Ss Dec26 0:03 postgres: autovacuum launcher process
postgres 31720 0.0 0.1 34228 4124 ? Ss Dec26 0:09 postgres: stats collector process
postgres 31721 0.0 0.1 179308 6052 ? Ss Dec26 0:00 postgres: bgworker: logical replication launcher
$ cat /proc/31718/limits
Limit Soft Limit Hard Limit Units
Max cpu time unlimited unlimited seconds
Max file size unlimited unlimited bytes
Max data size unlimited unlimited bytes
Max stack size 8388608 unlimited bytes
Max core file size 0 unlimited bytes
Max resident set unlimited unlimited bytes
Max processes 15738 15738 processes
Max open files 1024 4096 files
Max locked memory 65536 65536 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 15738 15738 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
Max realtime timeout unlimited unlimited us
In the example above, we inspected the open files limit for Process 31718. It doesn't matter which PostgreSQL process, any of them will do. The response we are interested in is Max open files.
We want to increase Soft Limit and Hard Limit of Max open files to be greater than the value we specified for the PostgreSQL setting max_files_per_process. In our example, we set max_files_per_process to 65536.
In Ubuntu (and assuming you are using PostgreSQL's packages for Ubuntu), the easiest way to change the Soft Limit and Hard Limit is to edit /etc/init.d/postgresql (SysV) or /lib/systemd/system/postgresql*.service (systemd).
Let's first address the SysV Ubuntu case where we add ulimit -H -n 262144 and ulimit -n 131072 to /etc/init.d/postgresql.
...
case "$1" in
start|stop|restart|reload)
if [ "$1" = "start" ]; then
create_socket_directory
fi
if [ -z "`pg_lsclusters -h`" ]; then
log_warning_msg 'No PostgreSQL clusters exist; see "man pg_createcluster"'
exit 0
fi
ulimit -H -n 262144
ulimit -n 131072
for v in $versions; do
$1 $v || EXIT=$?
done
exit ${EXIT:-0}
;;
status)
...
Now to address the systemd Ubuntu case. We will add LimitNOFILE=131072 to every /lib/systemd/system/postgresql*.service file in the [Service] section.
...
[Service]
LimitNOFILE=131072
...
[Install]
WantedBy=multi-user.target
...
After making the necessary systemd changes, make sure to reload the daemon
systemctl daemon-reload