Using Postgresql

Posted on by

1. create tablespace
$ mkdir -p /home/postgresql/data
$ sudo chown -R postres:postres /home/postgresql/data
$ sudo chmod -R og-rx /home/postgresql/data
$ sudo su – postgres
$ psql
postgres=# create tablespace newspace location ‘/home/postgresql/data’;

2. move a database to this new tablespace
use a php script from here to generate sql
$ sudo yum install php-pgsql
$ ./generate-mv-db.php
$ sudo su – postgres
$ psql -d mydb -f migrate_localhost_mydb_newspace.sql

3. install postgis
a. install proj4.7
$ sudo yum install proj
b. install geos 3.2.2
$ tar xvjf geos-3.2.2.tar.bz2
$ cd geos-3.2.2
$ ./configure –prefix=/usr
$ make && sudo make install
$ sudo ldconfig

must do this, otherwise postgis will fail to locate libgeos_c.so.1

c. install postgis-1.5.1.tar.gz
$ tar xvzf postgis-1.5.1.tar.gz
$ cd postgis-1.5.1
$ ./configure
$ make && sudo make install
d. create a spatially-enabled database
$ sudo su – postgres
$ createdb postgis_template -U postgres;
$ cd /usr/share/pgsql/contrib/postgis-1.5
$ createlang plpgsql postgis_template
$ psql -d postgis_template -f postgis.sql
$ psql -d postgis_template -f spatial_ref_sys.sql

Leave a Reply

Your email address will not be published. Required fields are marked *

*

To create code blocks or other preformatted text, indent by four spaces: 

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks: 

Here is some inline `code`.
For more help see http://daringfireball.net/projects/markdown/syntax

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>