PostGIS & ArcSDE (linux)

Installing SDE & PostGIS on a linux machine

OS Config

• RedHat (Linux gist 2.6.18-128.1.1.el5xen #1 SMP Mon Jan 26 14:34:58 EST 2009 i686 i686 i386 GNU/Linux)

  Red Hat Enterprise Linux Server release 5.3 (Tikanga)

• Running on a VMWare machine on top of WinXP

OS users

• postgres

  .bashrc contents:

PGDATA=/usr/local/pgsql/data
export PGDATA
PATH=$PATH:/usr/bin:/usr/local/bin
export PATH
LD_LIBRARY_PATH=/usr/lib:/usr/local/lib:/usr/local/pgsql/lib
export LD_LIBRARY_PATH

• sde

  .bashrc contents:

SDEHOME=/usr/local/sde/sdeexe93
export SDEHOME
PATH=$PATH:$SDEHOME/bin
export PATH
LD_LIBRARY_PATH=/usr/local/sde/sdeexe93/lib
export LD_LIBRARY_PATH

PostgreSQL data dir

mkdir /usr/local/pgsql/data
chown -R /usr/local/pgsql/data

Software

• PostgreSQL 8.3.7

  compiled with configure --with-perl --with-python

• Geos 3.1.0

  compiled with configure --enable-python --enable-ruby (not that I'll actually use ruby!)

• Proj.4 4.6.1

  standard build

• PostGIS 1.3.5

  compiled with configure --with-geos --with-pgsql --with-proj --prefix=/usr/local/pgsql

• ArcSDE 9.3

PostgreSQL stuff

pg_hba.conf contents

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
host    all         all         128.95.140.165/32          password
host    all         all         128.95.140.149/32          password
host    all         all         128.95.140.144/32          password
host    all         all         140.142.198.178/32          password
host    all         all         140.142.198.123/32          trust
host    all     all             192.168.0.0/24  md5
# IPv6 local connections:
#host    all         all         ::1/128               trust

users

• createuser sde
• createuser phurvitz

PostGIS stuff

copy /usr/local/lib/libgeos-3.1.0.so to /usr/local/pgsql/lib/libgeos-3.1.0.so

Installing PostGIS basics to the template database

Assures that new databases will be populated with PostGIS functionality

• createlang plpgsql template1
• psql -d template1 -f /usr/local/src/postgis-1.3.5/lwpostgis.sql
• psql -d template1 -f /usr/local/src/postgis-1.3.5/spatial_ref_sys.sql

ArcSDE schema

While logged in as postgres

psql
CREATE SCHEMA sde AUTHORIZATION sde;
GRANT ALL ON SCHEMA sde to sde;
GRANT USAGE ON SCHEMA sde TO public;

Created a new database:

create database msb owner phurvitz;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.geometry_columns TO sde;

msbdata user

Created in order to have a PostgreSQL user with no special privelege

ArcSDE stuff

Authorization key

Get an authorization key from Luke, save as /usr/local/sde/ArcGIS_Server_v93.ecp

sdesetup for a database

• as sde user:

  sdesetup -o install -d POSTGRESQL -D msb

  sdesetup -o update_key -d POSTGRESQL -l /usr/local/sde/ArcGIS_Server_v93.ecp -D msb

NOTE: do not perform the sdesetup routine on the template1 database!

Start the sde service

sdemon -o start -i esri_sde

Adding layers

1. Create a PostGIS table using standard methods, e.g.,

   CREATE TABLE gtest ( ID int4, NAME varchar(20) );

   SELECT AddGeometryColumn(’sde’, ’gtest’,’shape’,2926,’LINESTRING’,2);

2. Register this as a layer in ArcSDE

   sdelayer -o register -l s01_line,shape -D msb -e l3M -C id -k PG_GEOMETRY -t PG_GEOMETRY -G 2926 -u msbdata -p msbdata

   sdetable -o alter_reg -t s01_line -V multi -D msb -u msbdata -p msbdata -q -N

Linux stuff

Might need to open port 5151 if there is an iptables firewall:

/etc/sysconfig/iptables

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5151 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m udp -p udp --dport 5151 -j ACCEPT