Difference between revisions of "PostGIS & ArcSDE (linux)"

From phurvitz
Jump to: navigation, search
(PostGIS stuff)
m (Protected "PostGIS & ArcSDE (linux)" [edit=sysop:move=sysop] [cascading])
 
(No difference)

Latest revision as of 16:52, 13 October 2009

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