[Live-demo] Postgis questions

Regina Obe lr at pcorp.us
Sat Jan 23 12:36:49 PST 2016

That is a way of doing it too, but for PostgreSQL 9.4+, I would highly suggest using the 


ALTER SYSTEM approach.


ALTER SYSTEM settings get kept in a file called postgresql.auto.conf   and override whatever same settings are in postgresql.conf


The real benefit I've found with ALTER SYSTEM is that it's easy to keep track of what settings you changed from default since you can just look at the

postgresql.auto.conf and see.


The other benefit is when upgrade to PostgreSQL 9.5 on another server, I just copy this file to my 9.5 instance without worrying about all the other additional things that got added to postgresql.conf in 9.5.


They say you should never edit the postgresql.auto.conf directly, but I tend to get lazy and just directly type into it and then restart my database service.

>From what I've observed, It's perfectly okay to edit the file directly  (as long as you don't expect comments to be kept) and restart your service, before making any ALTER SYSTEM calls.  Since postgresql loads this file on each startup, so any ALTER SYSTEM you do after, will start with what got loaded and then write back all your custom settings back to the file.


Actually I think it might reload the file on each SELECT pg_reload_conf();  call so might actually be safe to just call that too instead of restart the service.


Hope that helps,





From: Live-demo [mailto:live-demo-bounces at lists.osgeo.org] On Behalf Of Pieter du Plooy
Sent: Saturday, January 23, 2016 2:44 PM
To: Brian M Hamlin <maplabs at light42.com>; live-demo at lists.osgeo.org
Subject: Re: [Live-demo] Postgis questions


Hi Brian

In Ubuntu 14.04, set the last part of /etc/postgresql/9.4/main/postgresql.conf (under the comment 
# Add settings for extensions here

as such:

postgis.gdal_enabled_drivers 'ENABLE_ALL'
postgis.enable_outdb_rasters TRUE

restart postgres

In your postgis enabled db, do the following:
select long_name from st_gdaldrivers();

and you will see a lot of gdal enabled drivers

such as:

 Virtual Raster
 National Imagery Transmission Format
 Erdas Imagine Images (.img)
 Arc/Info ASCII Grid
 DTED Elevation Raster
 Portable Network Graphics
 Graphics Interchange Format (.gif)
 X11 PixMap Format
 ILWIS Raster Map
 SRTMHGT File Format
 JPEG-2000 driver based on OpenJPEG library
 FIT Image
 JPEG-2000 part 1 (ISO/IEC 15444-1)
 OGC Web Map Service
 Idrisi Raster A.1
 Intergraph Raster
 Golden Software ASCII Grid (.grd)
 Golden Software Binary Grid (.grd)
 Golden Software 7 Binary Grid (.grd)
 R Object Data Store
 ESRI .hdr Labelled
 FARSITE v.4 Landscape File (.lcp)
 Azavea Raster Grid format
 USGS Optional ASCII DEM (and CDED)
 Epsilon wavelets
 SAGA GIS Binary Grid (.sdat)
 Kml Super Overlay
 ASCII Gridded XYZ
 HF2/HFZ heightfield raster
 Geospatial PDF
 ZMap Plus Grid
(34 rows)





On Sat, Jan 23, 2016 at 9:01 PM, Brian M Hamlin <maplabs at light42.com <mailto:maplabs at light42.com> > wrote:

Hi Pieter --


  oh, ok.. thats new to me..

  according to the PostGIS docs page..



  those variables can be set at all levels..

  cluster-wide -> postgresql.conf

  database-persistant ->  ALTER DATABASE ...

  database-temp, psql session temp  ->  set ....


also apparently SHELL with bash-style environment vars, as in .bashrc


I would think that all gdal drivers would be enabled by default, and that really you

need  raster out_db set...  so I look here



make sense ?


On Sat, 23 Jan 2016 19:51:42 +0200, Pieter du Plooy <pjduplooy at zpanelcp.com <mailto:pjduplooy at zpanelcp.com> > wrote:

Hi Brian

Thx for the info, but what I really need is where to 

postgis.gdal_enabled_drivers = 'ENABLE_ALL'
and postgis.enable_outdb_rasters=1
in Ubuntu 14.04, and system wide
I did it previously in my .bashrc, but for some reason its not
working now.


On Sat, Jan 23, 2016 at 6:58 PM, Brian M Hamlin <maplabs at light42.com <mailto:maplabs at light42.com> > wrote:

Hi Pieter --


  I have an a2 build running in a VM.


user at live95a:~$ locate raster.tif        <= there is an example file for a QGis plugin called this

user at live95a:~$ gdalinfo /path/to/raster.tif

   hmm non-trivial .. open qgis to let qgis do the work

   qgis shows the projection is EPSG:32633


user at live95a:~$  createdb raster_test

user at live95a:~$ psql -c 'create extension postgis' raster_test

user at live95a:~$ psql -c 'select postgis_full_version()' raster_test

    yes, RASTER is enabled

    use raster2pgsql to load a raster, as per PostGIS raster manual


user at live95a:~$ raster2pgsql -s 32633 -I -C /path/to/raster.tif  raster_test | less

    inspect output, yes looks valid


user at live95a:~$ raster2pgsql -s 32633 -I -C /path/to/raster.tif  raster_test | psql raster_test

user at live95a:~$ psql -c '\d raster_test'

    note rid, rast columns.. lots of other detail

    better command line would name the tables, split into tiles, etc...


  hth --Brian


On Sat, 23 Jan 2016 16:45:50 +0200, Pieter du Plooy <pjduplooy at zpanelcp.com <mailto:pjduplooy at zpanelcp.com> > wrote:

I am struggling to enable postgis gdal drivers and outdb rasters on Ubuntu (using postgis from the osgeolive nightly, so its for trusty.

How do I go about enabling them in Ubuntu Trusty?

I have tried setting it in .profile, as well as .bashrc systemwide etc..., but no luck.

Any help?




Live-demo mailing list
Live-demo at lists.osgeo.org <mailto:Live-demo at lists.osgeo.org> 
http://lists.osgeo.org/mailman/listinfo/live-demo <http://../hwebmail/services/go.php?url=http%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Flive-demo> 
http://live.osgeo.org <http://../hwebmail/services/go.php?url=http%3A%2F%2Flive.osgeo.org> 
http://wiki.osgeo.org/wiki/Live_GIS_Disc <http://../hwebmail/services/go.php?url=http%3A%2F%2Fwiki.osgeo.org%2Fwiki%2FLive_GIS_Disc>  


Brian M Hamlin
OSGeo California Chapter
blog.light42.com <http://blog.light42.com> 



Brian M Hamlin
OSGeo California Chapter
blog.light42.com <http://blog.light42.com> 



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/osgeolive/attachments/20160123/57ce0459/attachment.html>

More information about the Osgeolive mailing list