PostGIS "spatially enables" the PostgreSQL server - FTP Directory ...

musicincurableΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 5 χρόνια και 5 μήνες)

147 εμφανίσεις

Rapid migration of shapefiles into
PostgreSQL spatial database

By Jeff Smith

Why use a Spatial Database?


databases are optimized for
serving up large volumes of repetitive data,
and spatial data fits this description perfectly

Multiuser support

benefit of remote
access via a standard interface (JDBC,
ODBC, PERL/DBI, and others). Security
benefits. Good format for serving data over


spatial databases allow you to
perform spatial queries (buffers,
intersections, etc.)

Why PostgreSQL?

World’s most advanced open source database

Supports most (if not all) of the features
of its commercial competitors

PostGIS spatial add
on for PostgreSQL,
and is considered one of the major pillars
of the open source GIS community

Developed at Berkeley

Installing PostgreSQL

The Windows
binary distribution of
PostgreSQL is easy
to install

Make sure to check the
PostGIS Spatial Extensions,
PgAdmin III, psql options
during installation

Simple installation tutorial

Installing PostGIS

PostGIS adds support for geographic objects to the PostgreSQL object
relational database. In effect,
PostGIS "spatially enables" the
PostgreSQL server
, allowing it to be used as a backend spatial database
for geographic information systems (GIS), much like ESRI's SDE or Oracle's
Spatial extension.

PostGIS is included in the Windows
binary of PostgreSQL, but it is a good
idea to get the latest installer after
PostgreSQL installation

Getting shapefiles into PostgreSQL


Executable utility that comes with
PostgreSQL and is located in bin directory
of install folder

Takes in shapefiles and outputs SQL
scripts (.sql)

Using shp2pgsql

Open command line and change driectory
to bin folder of PostgreSQL install

Convert shapefile to .sql with command



This executes shp2pgsql and dumps the output to the screen

Add “ > <FILENAME>.sql” onto this command to output to a .sql file

This works great but can take a lot of time if you have
many shapefiles to convert

Solution: Time for some scripting

AML Script for batch processing
shapefiles using shp2pgsql

Why use AML?

Simple to use

level programming language


Convert all shapefiles in specified folder to
.sql files one by one using shp2pgsql.exe

Shapefile to .sql Exporter Code

Prompt user for path to folder

Make sure not to put a “
” at end of path

This becomes %DIRECTORY% variable

Prompt user for projection of the files in
the form of a European Petroleum Survey
Group Spatial Reference Identifier

EPSD SRID’s may be looked up at

This becomes %SRID% variable

Shapefile to .sql Exporter Code

Create list of all .shp files in folder

“&workspace %DIRECTORY%”

“&sv numshp = [listfile *.shp
file inputs.list]”

Open list in “READ" mode

“&sv filenum = [open %DIRECTORY%
inputs.list openstat

Start reading records (shapefile names) from list

“&sv record = [read %filenum% readstat]”

“&do &while %readstat% = 0”

Trim the .shp off then end of names and uses this for the tablename of the PostgreSQL table that
will be created

“&sv name = [BEFORE %record% .]”

Call out of arc environment to shp2pgsql.exe and process shapefile

“&system %DIRECTORY%
%record% %name% >



Read next record and repeat loop

“&sv record = [read %filenum% readstat]”


Preparing Folder of Shapefiles

Move shp2pgsql.exe into the folder
containing files to be processed

Make sure all shapefiles are in same projection and
find out EPSG SRID number for that projection

Running the AML script

When the AML script was
executed 20 .sql files
were created

Preparing .sql files to be batch
loaded into PostgreSQL

Need to combine all .sql files into
one massive .sql file that can be
loaded all at once

Time for some fancy command
line action

tables.sql is 16.9mb once all 20
shapefiles have been copied to it

Load tables.sql into spatially
enabled databse in PostgreSQL

pgAdmin III is a GUI
that is installed with
PostgreSQL and can
be used to load a .sql
file into a database

Execute tables.sql

Right click a spatially
enabled database and
select CREATE script

and execute script

Check if the SQL script worked

Refresh pgAdmin III and you should see new
tables in the database for which you ran the

Viewing PostgreSQL table in ArcMap


is an ArcGIS plug
that allows loading and viewing
of PostGIS layers

Currently editing of PostGIS layers
from ArcMap is unavailable

Installing ZigGIS

Download .zip file and
run setup.exe

After installing start
ArcMap and go to

Then click the
Commands tab and
find ZigGIS

Next Drag “Add
PostGIS Layer” to
ArcMap’s toolbar

Configure zig file

Go to directory where
ZigGIS is installed
and open example.zig
in a text editor

Change values of
server, port, user, and
password to values
used in PostgreSQL

View PostgreSQL Layers in ArcMap

Click PostgreSQL icon in ArcMap’s toolbar

Point to zig file with correct information
for your PostgreSQL installation

Select layers you would like to add to
ArcMap and press ok

Viewing PostgreSQL Layers in ArcMap

Thank You!

Additional Referenced Sources

Davis, Scott.
GIS for Web Developers: Adding Where to YOur
Web Applications
. Raleigh, NC: The Pragmatic Bookshelf, 2007.

ARC Macro Language
. 2nd. Cambridge: GeoInformation
International, 1997.