English

How To: Set up PostGIS in an ArcSDE geodatabase on Linux

Summary

Instructions provided describe how to install PostgreSQL, PROJ4, GEOS, PostGIS, and the ArcSDE component. PROJ4 and GEOS are components necessary to run PostGIS.

ArcSDE and PostGIS are installed into the same database.

Procedure

Install PostgreSQL

For PostGIS to be installed on PostgreSQL on a Linux platform, the PostgreSQL installation has to be made either from source or with Red Hat Package Manager (RPM) installation packages, including the postgresql-devel RPM. The PostgreSQL RPMs included on the ArcSDE installation DVD include all RPM files. The files can be obtained from the ArcSDE installation DVD or directly from the PostgreSQL Web site.

This article addresses an installation using the RPM installation packages. To install using source files, consult the PostgreSQL documentation.
  1. Create a folder to house the RPMs and source code for all four applications; throughout this document that location will be identified as: /usr/postgres.
  2. Place the PostgreSQL RPM files in /usr/postgres.
    Note:
    If RPM installation packages were downloaded from the Web site, they will likely have to be copied to the /usr/postgres location. For example: 
    
    cp -r /net/�/rpm/PostgreSQL8.3 /usr/postgres
  3. Execute the RPMs' installation packages. Execute them one by one or all together as in the example:
    rpm -ivh postgresql-8.3.0* postgresql-libs-8.3.0* postgresql-server-8.3.0* 
    postgresql-devel-8.3.0* postgresql-docs-8.3.0*
    warning: postgresql-8.3.0-1PGDG.rhel4.i686.rpm: V3 DSA
    signature: NOKEY, key ID 442df0f8
    Preparing...                ########################################### [100%]
       1:postgresql-libs        ########################################### [ 20%]
       2:postgresql             ########################################### [ 40%]
       3:postgresql-server      ########################################### [ 60%]
       4:postgresql-devel       ########################################### [ 80%]
       5:postgresql-docs        ########################################### [100%]
    
  4. The execution of the RPMs created a Linux user called postgres. However, this user does not have a password set. Therefore, log in as the root user and set the password for the postgres user.
    [root@machine etc]# passwd postgres
    Changing password for user postgres.
    New UNIX password:
    BAD PASSWORD: it is based on a dictionary word
    Retype new UNIX password:
    passwd: all authentication tokens updated successfully.
    
  5. Log in as the postgres user, open the postgres user’s profile, and add environment variables. Notice: The .bash_profile file, to which the environment variables are added, is a hidden file. Use ls -al to list all files, even hidden ones.
    [root@machine etc]# su – postgres
    bash-3.00$ pwd
    /var/lib/pgsql 
    
    bash-3.00$ ls -al
    total 24
    
    bash-3.00$ vi .bash_profile
    PGDATA=/var/lib/pgsql/data - already present in the file
    
    export PGDATA
    LD_LIBRARY_PATH=/usr/lib:/usr/lib/pgsql
    
    export LD_LIBRARY_PATH
    PATH=/usr/bin:$PATH
    
    export PATH
    
    Note:
    If using a shell other than the bash shell, alter the appropriate profile file and variables for the shell used.
    
  6. Initialize the PostgreSQL cluster as the postgres user. For example:
    bash-3.00$ /usr/bin/initdb -D /var/lib/pgsql/data
  7. Alter two PostgreSQL configuration files before starting the postgres process.
    cd /var/lib/pgsql/data
    In the postgresql.conf file, alter the listen_addresses parameter from
    #listen_addresses = 'localhost'
    to
    listen_addresses = '*'
    In the pg_hba.conf file, add a line enabling remote machine connections. For example:
    host    all         all         10.0.0.0/8            md5
    Note:
    These configuration files may need to have other parameters altered to fit the system environment.
    
  8. Start the PostgreSQL process as the postgres user.
    bash-3.00$ /usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start
    server starting
    Postgres is up and running

Build and install PROJ4

  1. Download the source code from http://download.osgeo.org/proj/proj-4.6.0.tar.gz and place it in /usr/postgres.
    Copy the .tar file from the download location to the /usr/postgres location. For example:
    cp -r .../proj-4.6.0.tar.gz /usr/postgres
  2. Untar the tar ball in the /usr/postgres location.
    [user@machine postgres]# tar -zxf proj-4.6.0.tar.gz
    [user@machine postgres]# ls
    proj-4.6.0 proj-4.6.0.tar.gz
  3. Change to the newly untarred directory. For example:
    # cd proj-4.6.0
  4. Create a configuration script.
    [user@machine proj-4.6.0]# ./configure
    Note:
    This process may take a long time.
    
  5. Run the make command to create the executables.
    [user@machine proj-4.6.0]# make
  6. To test the outcome of the make operation, run make check, which tests if the executables are correct and have all necessary dependent files before installing.
    [user@machine proj-4.6.0]# make check
    TEST OK
    TEST OK
    TEST OK
  7. Run make install to install PROJ4 into /usr/local/lib. This step needs to be performed as the root user.
    [root@machine proj-4.6.0]# make install

Build and install GEOS

  1. Download the source code from http://geos.refractions.net/downloads/,
    select geos-3.0.0.tar.bz2, and place it in /usr/postgres.

    Be sure to copy the .tar file from the download location to the /usr/postgres location. For example:
    cp -r .../ geos-3.0.0.tar.bz2 /usr/postgres
  2. Untar the tar ball.
    [user@machine postgres]# tar -jxf geos-3.0.0.tar.bz2
    geos-3.0.0 geos-3.0.0.tar.bz2
  3. Change to the newly untarred directory.
    # cd geos-3.0.0
  4. Create a configuration script.
    [user@machine geos-3.0.0]# ./configure
    Note:
    This process may take a long time.
    
  5. Run make to create the executables.
    [user@machine geos-3.0.0]# make
  6. To test the outcome of the make operation, run make check, which tests if the executables are correct and have all necessary dependent files before installing.
    [user@machine geos-3.0.0]# make check
    Tests summary:
    - passed: 402
    PASS: geos_unit
    ==================
    All 1 tests passed
    ==================
  7. Run make install to install PROJ4 into /usr/local/lib. This step needs to be performed as the root user.
    [root@machine geos-3.0.0]# make install
  8. Update the dynamic link so that libproj.so and libgeos.so can be found in /usr/local/lib. This step is not recorded in the PostGIS installation documentation but unless it is performed, PostGIS will not be able to access these files and will error out when running make check or executing the SQL file that creates the PostGIS system tables and functions (lwpostgis.sql).
    [root@machine geos-3.0.0]# echo "/usr/local/lib" > /etc/ld.so.conf.d/proj4-geos.conf
    
    [root@machine geos-3.0.0]# ldconfig -v|grep -e 'libproj*\|libgeos*'
    ldconfig: Path `/usr/lib/mysql' given more than once
    libgeos-3.0.0.so -> libgeos.so
    libgeos_c.so.1 -> libgeos_c.so.1.4.1
    libproj.so.0 -> libproj.so.0.5.4
    libproc-3.2.3.so -> libproc-3.2.3.so

Build and Install PostGIS 1.3.2

  1. Download the PostGIS source code using command line or a Web browser and place it in /usr/postgres.

    Using command line:
    wget http://postgis.refractions.net/download/postgis-1.3.2.tar.gz

    Using a Web browser:

    Go to the Refractions Research Web site, view the versions of PostGIS available, and alter the verison number in the URL to 1.3.2.

    After downloading the tar ball from the Web site, be sure to copy the .tar file from the download location to the /usr/postgres location.
    cp -r .../proj-4.6.0.tar.gz /usr/postgres
  2. As the postgres user, untar the tar ball.
    bash-3.00$ tar -zxf postgis-1.3.2tar.gz
    postgis-1.3.2 postgis-1.3.2tar.gz
  3. Change to the newly untarred directory.
    bash-3.00$ cd postgis-1.3.2
  4. Create a configuration script.
    bash-3.00$ ./configure --prefix=/usr/local
     SUMMARY
     -------------------------------------------------------
    
     HOST_OS: linux-gnu
    
       PGSQL: /usr/bin/pg_config
        GEOS: /usr/local/bin/geos-config (with C-API)
              (ldflags: -L/usr/local/lib)
        PROJ: prefix=/usr/local libdir=/usr/local/lib
       ICONV: 1
    
     PORTNAME: linux
       PREFIX: /usr/local
      EPREFIX: ${prefix}
          DOC: ${prefix}/share/doc
         DATA: ${datarootdir}
          MAN: ${datarootdir}/man
          BIN: ${exec_prefix}/bin
          EXT: ${exec_prefix}/lib (${exec_prefix}/lib)
     -------------------------------------------------------
    
    Note:
    This process may take a long time.
    
  5. Run make to create the executables.
    bash-3.00$ make
  6. To test the outcome of the make operation, run make check, which tests if the executables are correct and have all necessary dependent files before installing. This operation installs the lqpostgis.sql and creates a database.
    bash-3.00$ make check
    Note:
    If step 8 in the previous section was not performed, this test will be unsuccessful.
    
    This step also fails if using PostGIS 1.3.3, because version 1.3.3 expects to find the executables in a bin directory under the PostGIS installation location, which does not yet exist.
  7. Run make install to install the PostGIS into /usr/local/lib. This step needs to be performed as the root user.
    [root@machine postgis-1.3.2]# make install

Create the database, roles, and schemas

The following steps create a database and necessary database objects to store ST_Geometry and Geometry data.
  1. Log into psql as the postgres user.
    bash-3.00$ psql
    Welcome to psql 8.3.0, the PostgreSQL interactive terminal.
  2. Create the sde database role.
    postgres=# create role sde login password 'sde' superuser noinherit createdb;
    CREATE ROLE
  3. Create the database owned by the sde role.
    postgres=# create database stpg owner sde;
    CREATE DATABASE
  4. Grant privileges on the database to public and the sde role.
    postgres=# grant all on database stpg to public;
    GRANT
    postgres=# grant all on database stpg to sde;
    GRANT
  5. Create the sde schema and grant privileges on it.
    postgres=# \c stpg
    You are now connected to database "stpg".
    stpg=# create schema sde authorization sde;
    CREATE SCHEMA
    stpg=# grant all on schema sde to sde;
    GRANT
    stpg=# grant usage on schema sde to public;
    GRANT
  6. Create the plsql language.
    stpg=# create language plpgsql;
    CREATE LANGUAGE
  7. Execute the lwpostgis.sql script to create the PostGIS system tables and functions in the database.
    wrap;font-family:Arial'><b>Code:</b>
    stpg=# \i /usr/local/share/lwpostgis.sql;
  8. Grant the sde role privileges to the PostGIS system table public.geometry_columns.
    stpg=# grant select, insert, update, delete on table public.geometry_columns to sde;
    GRANT

Install the ArcSDE component

  1. Create an sde operating system (OS) user on the Linux machine.
    adduser sde 
    passwd sde
    
  2. Log in as the sde OS user and add environment variables for it.
    [sde@machine ~]$ pwd
    /home/sde
    [sde@machine ~]$ vi .bash_profile
    
    SDEHOME=/home/sde/sdeexe
    export SDEHOME
    PATH=$PATH:$HOME/bin:$SDEHOME/bin
    export PATH
    LD_LIBRARY_PATH=$SDEHOME/lib:/usr/lib
    export LD_LIBRARY_PATH
    
  3. Install the ArcSDE component.

    For complete instructions, consult the ArcSDE for PostgreSQL Installation Guide found on the ArcSDE installation media.
    [sde@machine ~]$ /…/pg/arcsde/install -load
    yes
    mount point: DVD location or wherever you are installing from
    install directory: enter to accept /home/sde
    Package numbers to load: enter to accept all
    yes
    no
    
  4. As the root user, copy the st_geometry.so, libsg.so, and libpe.so files from $SDEHOME to the lib directory in the postgres location.
    [root@machine pgsql]# cp /home/sde/sdeexe/lib/st_geometry.so /usr/lib/pgsql
    [root@machine pgsql]# cp /home/sde/sdeexe/lib/libsg.so /usr/lib/pgsql
    [root@machine pgsql]# cp /home/sde/sdeexe/lib/libpe.so /usr/lib/pgsql
    
  5. As the sde user, execute sdesetup to create the geodatabase and authorize it with the license file.
    [sde@machine ~]$ sdesetup -o install -d POSTGRESQL -u sde -p sde 
    -D db_name -l /home/sde/Server93_Ent_Adv.ecp
    
  6. Edit the ArcSDE and OS service files to include the port numbers used by ArcSDE.

    Log in as the sde user to edit the $SDEHOME/etc/services.sde file.
    bash-3.00$ vi SDEHOME/etc/services.sde
    pg_stpg                 5152/tcp
    pg_stpg                 5152/udp
    

    Log in as the root user to edit the system /etc/services file.
    bash-3.00$ vi /etc/services
    
    # Local services
    pg_stpg         5152/tcp
    pg_stpg         5152/udp
    
  7. Edit the dbinit.sde file. If the database is named anything other than sde, indicate the name of the database in the dbinit.sde file. Omitting this step prevents the ArcSDE service from starting.
    bash-3.00$ vi SDEHOME/etc/dbinit.sde
    set SDE_DATABASE=db_name
  8. Log in as the OS sde user and start the ArcSDE process.
    sdemon -o start -i 5152 -p sde