Yup, I'd considered that and re-loaded with absolute path and still
choked. But adding the tile size as well was the key.
Now I get, for example:
select rid, st_value(rast, 1, 1, 1) from slope where rid = 44;
rid | st_value
-----+-------------------
44 | 0.374891400337219
(1 row)
Big thanks to Bborie for taking the time to help!
So in summary for others who may run into this problem,
1. In the installation instructions
<http://postgis.net/docs/postgis_installation.html#install_short_version> it
is not entirely clear where one would set the environment variables:
"As of PostGIS 2.1.3, out-of-db rasters and all raster drivers are
disabled by default. In order to re-enable these, you need to set the
following environment variables: POSTGIS_GDAL_ENABLED_DRIVERS and
POSTGIS_ENABLE_OUTDB_RASTERS in the server environment."
The variables need to be set in the environment in which postgresql starts.
So if starting PostGIS from the command line, include the env vars
before starting postmaster e.g.,
POSTGIS_ENABLE_OUTDB_RASTERS=1 POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL \
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &
or in a shell script alter the 'start' and 'restart' commands, e.g.,
su - $PGUSER -c "POSTGIS_ENABLE_OUTDB_RASTERS=1 \
POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL $DAEMON \
-D '$PGDATA' &" >>$PGLOG 2>&1
2. Make sure and load rasters using absolute pathnames AND specify a
tile size, e.g.,
raster2pgsql -I -C -e -Y -F -d -R -s 2926 -t 1000x1000 \
/home/user/slope_ps.tif gis.slope | psql osm_test
-P.
**************************************************************
Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
Urban Form Lab | 1107 NE 45th Street, Suite 535 | Box 354802
University of Washington, Seattle, Washington 98195-4802, USA
[email protected] | http://gis.washington.edu/phurvitz
"What is essential is invisible to the eye." -de Saint-Exupéry
**************************************************************
On 2/25/2015 12:56, Bborie Park wrote:
Going back a few messages, it looks like you're specifying these out-db
rasters with relative paths. Use absolute paths.
Instead of:
raster2pgsql -I -C -e -Y -F -d -R -s 2926 ./slope/slope_ps.tif
gis.slope | psql osm_test
Use:
raster2pgsql -I -C -e -Y -F -d -R -s 2926
/ABSOLUTE/PATH/TO/slope/slope_ps.tif gis.slope | psql osm_test
PostgreSQL cannot access paths that were relative to the current working
directory when calling raster2pgsql.
Also, specify a tile size.
-bborie
On Wed, Feb 25, 2015 at 12:51 PM, Phil Hurvitz <[email protected]
<mailto:[email protected]>> wrote:
Thank you, Bborie. This seems to be a partial solution (at least now
PostGIS isn't complaining about the out-db raster). But now I get a
different problem, which is that attempting to access the out-db
raster makes the connection choke:
select st_summarystats(rast) from slope;
The connection to the server was lost. Attempting reset: Failed.
!>
-P.
******************************__******************************__**
Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
Urban Form Lab | 1107 NE 45th Street, Suite 535 | Box 354802
University of Washington, Seattle, Washington 98195-4802, USA
[email protected] <mailto:[email protected]> |
http://gis.washington.edu/__phurvitz
<http://gis.washington.edu/phurvitz>
"What is essential is invisible to the eye." -de Saint-Exupéry
******************************__******************************__**
On 2/25/2015 12:41, Bborie Park wrote:
Ah. I use a script similar to this.
In the following block...
start)
...
su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
...
You'll want to modify the su line to be like:
su - $PGUSER -c "POSTGIS_ENABLE_OUTDB_RASTERS=__1
POSTGIS_GDAL_ENABLED_DRIVERS=____ENABLE_ALL $DAEMON -D '$PGDATA' &"
>>$PGLOG 2>&1
See if that works...
-bborie
On Wed, Feb 25, 2015 at 12:29 PM, Phil Hurvitz <[email protected]
<mailto:[email protected]>
<mailto:[email protected] <mailto:[email protected]>>> wrote:
Certainly!
! /bin/sh
# chkconfig: 2345 98 02
# description: PostgreSQL RDBMS
# This is an example of a start/stop script for SysV-style
init, such
# as is used on Linux systems. You should edit some of the
variables
# and maybe the 'echo' commands.
#
# Place this file at /etc/init.d/postgresql (or
# /etc/rc.d/init.d/postgresql) and make symlinks to
# /etc/rc.d/rc0.d/K02postgresql
# /etc/rc.d/rc1.d/K02postgresql
# /etc/rc.d/rc2.d/K02postgresql
# /etc/rc.d/rc3.d/S98postgresql
# /etc/rc.d/rc4.d/S98postgresql
# /etc/rc.d/rc5.d/S98postgresql
# Or, if you have chkconfig, simply:
# chkconfig --add postgresql
#
# Proper init scripts on Linux systems normally require
setting lock
# and pid files under /var/run as well as reacting to network
# settings, so you should treat this with care.
# Original author: Ryan Kirkpatrick <[email protected]
<mailto:[email protected]>
<mailto:[email protected] <mailto:[email protected]>>>
# contrib/start-scripts/linux
## EDIT FROM HERE
# Installation prefix
prefix=/usr/local/pgsql
# Data directory
PGDATA="/usr/local/pgsql/data"
POSTGIS_GDAL_ENABLED_DRIVERS=____ENABLE_ALL
# Who to run the postmaster as, usually "postgres". (NOT
"root")
PGUSER=postgres
# Where to keep a log file
PGLOG="$PGDATA/serverlog"
# It's often a good idea to protect the postmaster from
being killed
by the
# OOM killer (which will tend to preferentially kill the
postmaster
because
# of the way it accounts for shared memory). Setting the
OOM_SCORE_ADJ value
# to -1000 will disable OOM kill altogether. If you enable
this,
you probably
# want to compile PostgreSQL with
"-DLINUX_OOM_SCORE_ADJ=0", so that
# individual backends can still be killed by the OOM killer.
#OOM_SCORE_ADJ=-1000
# Older Linux kernels may not have
/proc/self/oom_score_adj, but instead
# /proc/self/oom_adj, which works similarly except the
disable value
is -17.
# For such a system, enable this and compile with
"-DLINUX_OOM_ADJ=0".
#OOM_ADJ=-17
POSTGIS_ENABLE_OUTDB_RASTERS=1
POSTGIS_GDAL_ENABLED_DRIVERS=____ENABLE_ALL
## STOP EDITING HERE
# The path that is to be used for the script
PATH=/usr/local/sbin:/usr/____local/bin:/sbin:/bin:/usr/____sbin:/usr/bin
# What to use to start up the postmaster. (If you want the
script
to wait
# until the server has started, you could use "pg_ctl start
-w" here.
# But without -w, pg_ctl adds no value.)
DAEMON="$prefix/bin/____postmaster"
# What to use to shut down the postmaster
PGCTL="$prefix/bin/pg_ctl"
set -e
# Only start if we can find the postmaster.
test -x $DAEMON ||
{
echo "$DAEMON not found"
if [ "$1" = "stop" ]
then exit 0
else exit 5
fi
}
# Parse command line parameters.
case $1 in
start)
echo -n "Starting PostgreSQL: "
test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" >
/proc/self/oom_score_adj
test x"$OOM_ADJ" != x && echo "$OOM_ADJ" >
/proc/self/oom_adj
su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
echo "ok"
;;
stop)
echo -n "Stopping PostgreSQL: "
su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast"
echo "ok"
;;
restart)
echo -n "Restarting PostgreSQL: "
su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m
fast -w"
test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" >
/proc/self/oom_score_adj
test x"$OOM_ADJ" != x && echo "$OOM_ADJ" >
/proc/self/oom_adj
su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
echo "ok"
;;
reload)
echo -n "Reload PostgreSQL: "
su - $PGUSER -c "$PGCTL reload -D '$PGDATA' -s"
echo "ok"
;;
status)
su - $PGUSER -c "$PGCTL status -D '$PGDATA'"
;;
*)
# Print help
echo "Usage: $0
{start|stop|restart|reload|____status}" 1>&2
exit 1
;;
esac
exit 0
-P.
******************************____****************************__**__**
Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
Urban Form Lab | 1107 NE 45th Street, Suite 535 | Box 354802
University of Washington, Seattle, Washington 98195-4802, USA
[email protected] <mailto:[email protected]>
<mailto:[email protected].__edu
<mailto:[email protected]>> |
http://gis.washington.edu/____phurvitz
<http://gis.washington.edu/__phurvitz>
<http://gis.washington.edu/__phurvitz
<http://gis.washington.edu/phurvitz>>
"What is essential is invisible to the eye." -de Saint-Exupéry
******************************____****************************__**__**
On 2/25/2015 12:25, Bborie Park wrote:
Can you post the shell script?
On Wed, Feb 25, 2015 at 11:50 AM, Phil Hurvitz
<[email protected] <mailto:[email protected]>
<mailto:[email protected] <mailto:[email protected]>>
<mailto:[email protected] <mailto:[email protected]>
<mailto:[email protected] <mailto:[email protected]>>>> wrote:
Thanks Bborie, I am starting with a shell script,
and have
added the
env vars to that script, but am still unable to
access the
out-db
rasters.
-P.
******************************______**************************__**__**__**
Philip M. Hurvitz, PhD | Research Assistant
Professor | UW-CBE
Urban Form Lab | 1107 NE 45th Street, Suite 535
| Box 354802
University of Washington, Seattle, Washington
98195-4802, USA
[email protected] <mailto:[email protected]>
<mailto:[email protected].__edu
<mailto:[email protected]>>
<mailto:[email protected].
<mailto:[email protected].>____edu
<mailto:[email protected].__edu
<mailto:[email protected]>>> |
http://gis.washington.edu/______phurvitz
<http://gis.washington.edu/____phurvitz>
<http://gis.washington.edu/____phurvitz
<http://gis.washington.edu/__phurvitz>>
<http://gis.washington.edu/____phurvitz
<http://gis.washington.edu/__phurvitz>
<http://gis.washington.edu/__phurvitz
<http://gis.washington.edu/phurvitz>>>
"What is essential is invisible to the eye." -de
Saint-Exupéry
******************************______**************************__**__**__**
On 2/25/2015 11:45, Bborie Park wrote:
The env variables need to be within the
environment of
the postgres
process. How are you starting postgres?
Shell script? Then you should be able to add the
variables to
that script.
Direct invocation of posrgres on the command
line? You
need to
have the
variables before the command
VAR=1 postgres ...
-bborie
On Feb 25, 2015 11:40 AM, "Phil Hurvitz"
<[email protected] <mailto:[email protected]>
<mailto:[email protected] <mailto:[email protected]>>
<mailto:[email protected]
<mailto:[email protected]> <mailto:[email protected]
<mailto:[email protected]>>>
<mailto:[email protected]
<mailto:[email protected]> <mailto:[email protected]
<mailto:[email protected]>>
<mailto:[email protected] <mailto:[email protected]>
<mailto:[email protected] <mailto:[email protected]>>>>> wrote:
Thanks Bborie, I built from the tarball
rather
than using
an rpm;
does that still mean I should be adding the
environment
variables to
/etc/sysconfig/pgsql/________postgresql
So for overkill I added the env vars to
that file
as well
as to the
init script, restarted PostgreSQL, and it
seems I
still cannot
access the out-db raster.
I added a slope raster using:
raster2pgsql -I -C -e -Y -F -d -R -s 2926
./slope/slope_ps.tif
gis.slope | psql osm_test
I can get metadata (sorry for the ugly
text wrapping):
select rid, (foo.md <http://foo.md>
<http://foo.md>
<http://foo.md> <http://foo.md>).* from
(select rid,
st_Metadata(rast) as md from slope) as foo;
rid | upperleftx | upperlefty |
width | height |
scalex | scaley | skewx |
skewy |
srid |
numbands
-----+------------------+-----________-------------+-------+--__--__--__--__+-----------------__-+--__----__----__---------+--__-----+__------__-+----__--+---__-------
1 | 835161.301005914 | 758483.868026069 |
31935 | 34649 |
32.8083333333333 | -32.8083333333333 |
0 |
0 | 2926
| 1
But cannot access values:
select st_summarystats(rast) from slope;
ERROR: rt_raster_load_offline_data:
Access to offline
bands disabled
CONTEXT: SQL function "st_summarystats"
statement 1
select st_value(rast, 1, 1, 1) from slope;
ERROR: rt_raster_load_offline_data:
Access to offline
bands disabled
-P.
******************************________************************__**__**__**__**
Philip M. Hurvitz, PhD | Research Assistant
Professor | UW-CBE
Urban Form Lab | 1107 NE 45th Street,
Suite 535
| Box 354802
University of Washington, Seattle, Washington
98195-4802, USA
[email protected] <mailto:[email protected]>
<mailto:[email protected].__edu
<mailto:[email protected]>>
<mailto:[email protected].
<mailto:[email protected].>____edu
<mailto:[email protected].__edu
<mailto:[email protected]>>>
<mailto:[email protected]
<mailto:[email protected]>.
<mailto:[email protected]
<mailto:[email protected]>.__>____edu
<mailto:[email protected].
<mailto:[email protected].>____edu
<mailto:[email protected].__edu
<mailto:[email protected]>>>> |
http://gis.washington.edu/________phurvitz
<http://gis.washington.edu/______phurvitz>
<http://gis.washington.edu/______phurvitz
<http://gis.washington.edu/____phurvitz>>
<http://gis.washington.edu/______phurvitz
<http://gis.washington.edu/____phurvitz>
<http://gis.washington.edu/____phurvitz
<http://gis.washington.edu/__phurvitz>>>
<http://gis.washington.edu/______phurvitz
<http://gis.washington.edu/____phurvitz>
<http://gis.washington.edu/____phurvitz
<http://gis.washington.edu/__phurvitz>>
<http://gis.washington.edu/____phurvitz
<http://gis.washington.edu/__phurvitz>
<http://gis.washington.edu/__phurvitz
<http://gis.washington.edu/phurvitz>>>>
"What is essential is invisible to the
eye." -de
Saint-Exupéry
******************************________************************__**__**__**__**
Bborie Park [email protected]
<mailto:[email protected]>
<mailto:[email protected] <mailto:[email protected]>>
<mailto:[email protected]
<mailto:[email protected]>
<mailto:[email protected]
<mailto:[email protected]>>> <mailto:[email protected]
<mailto:[email protected]>
<mailto:[email protected] <mailto:[email protected]>>
<mailto:[email protected]
<mailto:[email protected]> <mailto:[email protected]
<mailto:[email protected]>>>>
Wed Feb 25 10:46:59 PST 2015
>
Philip,
POSTGIS_ENABLE_OUTDB_RASTERS=1 is an
environment
variable not to
be in
postgresql.conf.
The same is true for
POSTGIS_GDAL_ENABLED_DRIVERS=________ENABLE_ALL
https://wiki.postgresql.org/________wiki/PostgreSQL_on_RedHat_________Linux
<https://wiki.postgresql.org/______wiki/PostgreSQL_on_RedHat_______Linux>
<https://wiki.postgresql.org/______wiki/PostgreSQL_on_RedHat_______Linux
<https://wiki.postgresql.org/____wiki/PostgreSQL_on_RedHat_____Linux>>
<https://wiki.postgresql.org/______wiki/PostgreSQL_on_RedHat_______Linux
<https://wiki.postgresql.org/____wiki/PostgreSQL_on_RedHat_____Linux>
<https://wiki.postgresql.org/____wiki/PostgreSQL_on_RedHat_____Linux
<https://wiki.postgresql.org/__wiki/PostgreSQL_on_RedHat___Linux>>>
<https://wiki.postgresql.org/______wiki/PostgreSQL_on_RedHat_______Linux
<https://wiki.postgresql.org/____wiki/PostgreSQL_on_RedHat_____Linux>
<https://wiki.postgresql.org/____wiki/PostgreSQL_on_RedHat_____Linux
<https://wiki.postgresql.org/__wiki/PostgreSQL_on_RedHat___Linux>>
<https://wiki.postgresql.org/____wiki/PostgreSQL_on_RedHat_____Linux
<https://wiki.postgresql.org/__wiki/PostgreSQL_on_RedHat___Linux>
<https://wiki.postgresql.org/__wiki/PostgreSQL_on_RedHat___Linux
<https://wiki.postgresql.org/wiki/PostgreSQL_on_RedHat_Linux>>>>
Based upon the above, it looks like
you should add
POSTGIS_ENABLE_OUTDB_RASTERS=1
POSTGIS_GDAL_ENABLED_DRIVERS=________ENABLE_ALL
to
/etc/sysconfig/pgsql/________postgresql
The above assumes you're using the
packages
provided by
PostgreSQL.
http://www.postgresql.org/________download/linux/redhat/
<http://www.postgresql.org/______download/linux/redhat/>
<http://www.postgresql.org/______download/linux/redhat/
<http://www.postgresql.org/____download/linux/redhat/>>
<http://www.postgresql.org/______download/linux/redhat/
<http://www.postgresql.org/____download/linux/redhat/>
<http://www.postgresql.org/____download/linux/redhat/
<http://www.postgresql.org/__download/linux/redhat/>>>
<http://www.postgresql.org/______download/linux/redhat/
<http://www.postgresql.org/____download/linux/redhat/>
<http://www.postgresql.org/____download/linux/redhat/
<http://www.postgresql.org/__download/linux/redhat/>>
<http://www.postgresql.org/____download/linux/redhat/
<http://www.postgresql.org/__download/linux/redhat/>
<http://www.postgresql.org/__download/linux/redhat/
<http://www.postgresql.org/download/linux/redhat/>>>>
-bborie
On 2/25/2015 10:10, Phil Hurvitz wrote:
Hi all, I am having trouble starting
PostGIS with
out-db raster
support
In my
/usr/local/pgsql/data/________postgresql.conf file I
include the
line
POSTGIS_ENABLE_OUTDB_RASTERS=1
after which PostGIS won't start (service
postgresql start).
Software is
postgis_full_version
------------------------------________------------------------__--__--__--__-----------
POSTGIS="2.1.3 r12547"
GEOS="3.4.2-CAPI-1.8.2 r3921"
PROJ="Rel. 4.7.1,
23 September 2009" GDAL="GDAL 1.11.2,
released
2015/02/10"
LIBXML="2.7.6" TOPOLOGY RASTER
Also PostGIS won't start when I specify
POSTGIS_GDAL_ENABLED_DRIVERS=________ENABLE_ALL
Any help would be appreciated!
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
