dmuthami

Spider Diagram on PostGIS and Viewed on ArcGIS for Desktop (If Esri Business Analyst for Desktop is Inaccessible)

Blog Post created by dmuthami on Sep 21, 2014

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

 

capture.png

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

capture.png

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;

 

capture.png

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;

capture.png

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;

capture.png

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

 

capture.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

capture.png

  • Load Satellite Sites

 

shp2pgsql -d -D -s 4326 -I   Satellite_Sites.shp gisadmin.satellite_sites | psql  -U

gisadmin -d futures

 

capture.png

 

  • Load Counties by issuing below command

 

shp2pgsql -d -D -s 4326 -I   Counties.shp gisadmin.counties | psql  -U gisadmin -d futures

 

capture.png

 

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;

capture.png

 

b. Also do a list on one of the databases

 

\d central_sites

 

capture.png

 

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

 

capture.png

 

            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

 

capture.png

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);

 

capture.png

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

 

capture.png

b) Under Database Connections click “Add Database Connection

 

c) Ensure your connection window is as below

 

capture.png

 

Click Ok.

 

Rename the conection “gisadmin@localhost@futures-PG.sde”

 

d) Load the spatial views onto ArcMap

capture.png

 

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.

 

capture.png

 

6.Appendix

 

6.1. Back-up and Recovery Procedures

 

a) Backup commands for the database

 

      pg_dump -U postgres -Fc futures > futures.dump

 

capture.png

 

b) Restore commands for the futures database

 

a. # create from normal template and enable postgis.

 

createdb -U postgres  -E UTF8 futures

capture.png

b. #log into database and run the below command

 

psql -U postgres futures

 

create extension postgis;

capture.png

c. #Create GISadmin role

 

CREATE ROLE gisadmin  LOGIN ENCRYPTED PASSWORD 'gisadmin' NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;

capture.png

d. #Create schema

 

#change to futures database on terminal

 

\c futures

 

CREATE SCHEMA gisadmin AUTHORIZATION gisadmin;

capture.png

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;

 

capture.png

f. #Restore public schema and data of futures database

 

pg_restore -U postgres -n public -d futures futures.dump

capture.png

f. #Restore gisadmin schema and data of futures database

 

pg_restore -U gisadmin  -p 5432  -d futures -n gisadmin

futures30092014.dump

capture.png

 

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.

 

capture.png

a) PGDATA environment variable

capture.png

 

b) PGHOME environment variable

 

capture.png

c) PGHOST environment variable

 

capture.png

d) PGLIB environment variable

 

capture.png

e) Path environment variable

capture.png

  1. 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”

 

  1. 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.

Outcomes