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

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

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

134 εμφανίσεις

Rapid migration of shapefiles into
PostgreSQL spatial database

By Jeff Smith

Why use a Spatial Database?


Speed



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


Querying



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

http://www.bostongis.com/?content_name=postgis_tut01

http://www.postgresql.org/

Simple installation tutorial

Installing PostGIS

http://postgis.refractions.net/

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

shp2pgsql


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


“shp2pgsql

s <SRID> <SHAPEFILE> <TABLENAME>”


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


High
-
level programming language



Process


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
http://spatialreference.org/ref/epsg/


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
-
READ]”



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%
\
shp2pgsql
-
s %SRID% %DIRECTORY%
\
%record% %name% >
%DIRECTORY%
\
%name%.sql”


“shp2pgsql

s <SRID> <SHAPEFILE> <TABLENAME> > <FILENAME>.sql”



Read next record and repeat loop


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



“&end”

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

File>Open>tables.sql
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
script

Viewing PostgreSQL table in ArcMap


zigGIS

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


Currently editing of PostGIS layers
from ArcMap is unavailable

http://code.google.com/p/ziggis/

Installing ZigGIS


Download .zip file and
run setup.exe


After installing start
ArcMap and go to
Tools>Customize…


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
installation

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.



Questions?