1.Introduction
Notes: Ensure you install PostgreSQL 9.2.x and PostGIS 2.x. The specific version used for this set-up is;
a) PostgreSQL 9.2.2 Build: 136133
b) PostGIS 2.1.3
This article was necessitated by the fact that we lacked access to Esri Business Analyst for ArcGIS Desktop. Should you have access to Esri Business Analyst for ArcGIS Desktop then there is no need for you to go through the below steps.
2. Create PostGIS Database
a) Run below command on terminal
createdb -U postgres -T postgis_21_template -E UTF8 futures
b) Log in to the database now named “futures” by issuing below command
psql -U postgres futures
a) Enable PostGIS functionality
No need to enable since when creating futures we cloned from a database template named postgis_21_template which is PostGIS enabled and template for this instance of Postgres installed.
However, if you create database then it might be required of you to issue below command to enable PostGIS functionality on your database;
create extension postgis;
b) Confirm PostGIS functionality by issuing below command
\d
If you see” spatial_ref_sys” then you are ok.
3.Configure Data Owner User
3.1. Create User for Data Repository
a) Log into PL/SQL as a user with permissions to create other roles in the DBMS. This is usually the postgres super user
psql – U postgres
b) Execute the CREATE ROLE command in PL/SQL.
CREATE ROLE gisadmin LOGIN ENCRYPTED PASSWORD 'gisad***' NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;
3.2. Create Schema
a) Switch database to futures
\c futures
b) Execute the CREATE SCHEMA command to create a schema for the user in the database where the #geodatabase is stored. database of interest now is futures
CREATE SCHEMA gisadmin AUTHORIZATION gisadmin;
3.3. Grant USAGE and CREATE on the schema to the user.
a) This is required to allow the user to create log file tables. #configuration options for PostgreSQL for more information on log file tables.
GRANT USAGE ON SCHEMA gisadmin TO gisadmin;
GRANT CREATE ON SCHEMA gisadmin TO gisadmin;
3.4. Grant USAGE permissions on the schema (gisadmin)
a) Grant USAGE permissions on the schema to any other role or group that needs to access the data in #the user's schema.
GRANT USAGE ON SCHEMA gisadmin TO public;
4.Load Shapefiles into “Futures” Database
a) Need to execute Shp2pgsql command
b) Change directory containing Shapefiles of interest by issuing below command. You might be required to exit postgres terminal
a. Exit postgres terminal by issuing below command
\q
b. Change to shapefiles directory by issuing below command
cd /D E:\GIS Data\Future Group 2\SHP
c. List files to confirm by issuing below command
dir
c) Load data into PostGIS database now named “Futures” using UNIX pipes on the terminal. We shall be loading two Shapefiles namely
- Central_Sites.shp and
- Satellite_Sites.shp
a. Issue the below commands
- Load Central Sites
shp2pgsql -d -D -s 4326 -I Central_Sites.shp gisadmin.central_sites | psql -U
gisadmin -d futures
- Load Satellite Sites
shp2pgsql -d -D -s 4326 -I Satellite_Sites.shp gisadmin.satellite_sites | psql -U
gisadmin -d futures
- Load Counties by issuing below command
shp2pgsql -d -D -s 4326 -I Counties.shp gisadmin.counties | psql -U gisadmin -d futures
d) Confirm Shape files loaded into the database
a. Issue below commands to list tables after logging into futures database
psql -U gisadmin futures
\d
The tables are listed below;
b. Also do a list on one of the databases
\d central_sites
5.Create Spider Diagrams hub Lines in PostgreSQL
a) Issue below command to create spider lines /hub lines connecting the central sites and the hub lines
Select s.*, c.central_id as nodal_id, ST_Makeline(s.geom, c.geom)
from satellite_sites s, central_sites c
where s.central_id = c.central_id;
b) Create view from above sql query for creating spider lines
CREATE VIEW spider_diagram_view AS
Select s.*, c.central_id as nodal_id, ST_Makeline(s.geom, c.geom)
from satellite_sites s, central_sites c
where s.central_id = c.central_id;
c) List tables in futures database by issuing below command
\d
The view spider_diagram_view is listed alongside other user defined and system tables
5.1. Create Table named “Spider_diagrams” in “futures” database
a) Create a table spider_diagram to house the query results of spider diagram query. Issue below command
select * into spider_diagram from (select s.*, c.central_id as nodal_id, ST_Makeline(s.geom, c.geom)
from satellite_sites s, central_sites c
where s.central_id = c.central_id) as foo where s.central_id = c.central_id) as foo
b) Drop existing points geometry
a. Log is a s super user into the PostgreSQL instance by issuing below command
psql -U postgres futures
b. Drop existing points geometry column by issuing below command
5.2. Test Spider Diagram from PostGIS in ArcMap
a) Modify Spider_diagram table to allow ArcMap load it
NB. Remember to login into futures database as user gisadmin
a. Add column called “geom” by issuing below command
SELECT AddGeometryColumn (’gisadmin’,’spider_diagram’,’geom’,4326,’MULTILINESTRING’,2);
b. Alter geom column to pick geometries from existing linestring non-recognizable by ArcMap
ALTER TABLE spider_diagram ALTER COLUMN geom TYPE geometry(MULTILINESTRING,4326) USING ST_Multi(st_makeline);
c. Export spider_diagram table to shapefile by issuing below command
pgsql2shp -u gisadmin -P ******** -f spider_diagram2.shp -g geom futures gisadmin.spider_diagram
b) Under Database Connections click “Add Database Connection”
c) Ensure your connection window is as below
Click Ok.
Rename the conection “gisadmin@localhost@futures-PG.sde”
d) Load the spatial views onto ArcMap
a. The highlighted spatial views are;
- Futures.gisadmin.central_sites
- Futures.gisadmin.satellite_sites
- Futures.gisadmin.counties
b. Load them onto map by dragging and dropping them. NB. Load the shapefile named spider_diagram2 as well as this is the one container the spikes or hubs connecting central sites and satellite sites.
6.Appendix
6.1. Back-up and Recovery Procedures
a) Backup commands for the database
pg_dump -U postgres -Fc futures > futures.dump
b) Restore commands for the futures database
a. # create from normal template and enable postgis.
createdb -U postgres -E UTF8 futures
b. #log into database and run the below command
psql -U postgres futures
create extension postgis;
c. #Create GISadmin role
CREATE ROLE gisadmin LOGIN ENCRYPTED PASSWORD 'gisadmin' NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;
d. #Create schema
#change to futures database on terminal
\c futures
CREATE SCHEMA gisadmin AUTHORIZATION gisadmin;
e. #Grant USAGE and CREATE on the schema to the user.
GRANT USAGE ON SCHEMA gisadmin TO gisadmin;
GRANT CREATE ON SCHEMA gisadmin TO gisadmin;
#Grant USAGE permissions on the schema (gisadmin)
GRANT USAGE ON SCHEMA gisadmin TO public;
f. #Restore public schema and data of futures database
pg_restore -U postgres -n public -d futures futures.dump
f. #Restore gisadmin schema and data of futures database
pg_restore -U gisadmin -p 5432 -d futures -n gisadmin
futures30092014.dump
6.2. Environment Variables Set for PostgreSQL to work with Terminal
Set below user defined Environment variable in System Variables panel in Environment Variables window. If you find other PostgreSQL and PostGIS related variables leave them as they are.
a) PGDATA environment variable
b) PGHOME environment variable
c) PGHOST environment variable
d) PGLIB environment variable
e) Path environment variable
- a. Add the path to PostgreSQL bin folder to the variable value. For this case the path is “C:\Program Files\PostgreSQL\9.2\bin”
- b. Confirm path to PostgreSQL bin folder by copying to editor and checking
1.1. Source Files
a) Download from arcgis.com a zip file with the following files;
a. Input folder with the following
- Satellite sites
- Central Sites
- 47 Counties of Kenya
b. Results folder with expected spider diagram
- Spider diagram as points
b) Download a PostgreSQL dump from here. You may restore this in any PostgreSQL database with PostGIS instance running as per aforementioned requirements in the introduction.
Nice