Transcript PostGIS - Installation to Use

An OSGeo Training workshop
at
Centre For Space Science And Techonology
Education In Assia And The Pacific
12th – 14th January 2011
Dehradun
PostgreSQL/PostGIS
Santosh Gaikwad,
Salim Ali Centre For Ornithology And Natural History (SACON),
Hyderabad
[email protected]
What is Spatial data?

Data which describes either location or shape
e.g.House or Fire Hydrant location
Roads, Rivers, Pipelines, Power lines
Forests, Parks, Municipalities, Lakes
What is a Spatial Database?
Database that:
• Stores spatial objects
• Manipulates spatial objects just like other objects in
the database
Three aspects
• Spatial data types
• Spatial indexing
• Spatial functions
What is PostGIS?

PostGIS turns the PostgreSQL Database
Management System into a spatial database by
adding adding support for the three features:
spatial types, indexes, and functions
Why choose PostgreSQL?
PostgreSQL has:
•
•
•
•
•
•
•
Proven reliability and respect
No cost (open source)
Supports most of the SQL standard
Ability to add new data-types
TOAST - no limit on column size
GiST index / Index extensions
Easy to add custom functions
Why not Shapefiles?



Files require special software to read and write
Concurrent users can cause corruption
Complicated questions require complicated
software to answer
What applications support PostGIS?
Open Source/ Free
•Loading/Extracting
• Shp2Pgsql
• ogr2ogr
• Dxf2PostGIS
•Web-Based
• Mapserver
• GeoServer (Java-based WFS /
WMS -server )
• SharpMap SDK - for ASP.NET 2.0
• MapGuide Open Source (using
FDO)
•Desktop
• uDig
• QGIS
• mezoGIS
• OpenJUMP
• OpenEV
• SharpMap SDK for Microsoft.NET
2.0
• ZigGIS for ArcGIS/ArcObjects.NET
• GvSIG
Closed /Proprietary
•Loading/Extracting
• Safe FME Desktop
Translator/Converter
•Web-Based
• Ionic Red Spider (now ERDAS)
• Cadcorp GeognoSIS
• Iwan Mapserver
• MapDotNet Server
• MapGuide Enterprise (using FDO)
• ESRI ArcGIS Server 9.3+
•Desktop
• Cadcorp SIS
• Microimages TNTmips GIS
• ESRI ArcGIS 9.3+
• Manifold
• GeoConcept
• MapInfo (v10)
• AutoCAD Map 3D (using FDO)
Practical PostgreSQL/Postgis




Installation
Creating a Spatial Database
How to Spatially Enable an Existing Database
Loading spatial data
•
•
•
•
•
•



Command prompt (shp2pgsql)
(GUI) shp2pgsql loader
OpenJUMP
QGIS (SPIT)
GDAL
Geokettle
Loading data from non-spatial sources
Viewing the data
Querying the data
PostgreSQL/PostGIS Installation
OpenGeo Suite


It is the complete , OGC standards- compliant
web mapping platform built on powerful, cuttingedge, open source geospatial components.
It is the bundle of following software
•
•
•
•
•


PostgreSQL/PostGIS
GeoServer
GeoWebCache
OpenLayers
GeoExt
PostgreSQL/PostGIS database run on 54321 port
http://opengeo.org
OpenGeo Installation
Creating Spatial Database
(using template_postgis)
Creating Spatial Database
(without template_postgis)





Create a new database
Connect to the database
Load/run the PostGIS extension (postgis.sql)
Earlier version has lwpostgis.sql
Load/run the PostGIS spatial reference systems
(spatial_ref_sys.sql)
Metadata Tables

spatial_ref_sys:
defines all the spatial reference systems known
to the database.

geometry_columns:
provides a listing of all “features” and the basic
details of those features.
Metadata Tables
Loading Shape Files

shp2pgsql [opts] shapefile tablename > file.sql
• Shp2pgsql –s 32644 –D C:\churches.shp
churches(table) > chueches.sql



Read in .shp file
Write out .sql file
Load .sql file into PostgreSQL
• using psql
• using PgAdmin
Command Line Options
-D = Use “dump” format
-i = Do not use “bigint”, even for long numbers
-I = Create a GiST index on the geometry column
-g = Specify the name of the geometry column
-s <#> = Use this SRID
-W <encoding> = Use this character encoding
-a = Run in append mode
Loading Shape Files

psql
–d postgis
–U postgres
–f bc_data.sql
psql –d database –U postgres –f file.sql

To convert database table to shapefiles


pgsql2shp -f "test.shp" -u postgres -p 5432 -P
password database schema.table
QGIS Installation
OpenJUMP
•
•
1.
2.
3.
OpenJUMP is Open Source GIS software written
in Java Programming language
Installation:
Install Java
Install OpenJUMP
Install PostGIS database driver (Put
PostGIS132.jar file in /lib/ext folder of
OpenJUMP)
Java Installation
OpenJUMP Installation
Geometry Input and Output

Well-known text (WKT)
•
•
•

Well-known binary (WKB)
•
•
•

ST_GeomFromKML(text) returns geometry
ST_AsKML(geometry) returns text
GeoJSON
•

ST_GeomFromGML(text) returns geometry
ST_AsGML(geometry) returns text
Keyhole Mark-up Language (KML)
•
•

ST_GeomFromWKB(bytea) returns geometry
ST_AsBinary(geometry) returns bytea
ST_AsEWKB(geometry) returns bytea
Geographic Mark-up Language (GML)
•
•

ST_GeomFromText(text) returns geometry
ST_AsText(geometry) returns text
ST_AsEWKT(geometry) returns text
ST_AsGeoJSON(geometry) returns text
Scalable Vector Graphics (SVG)
•
ST_AsSVG(geometry) returns text
Geoprocessing with PostGIS
PostGIS functions (~700) are available through SQL
Coordinate transformation
Identify
Buffer
Touches
Crosses
Within
Overlaps
Contains
Area
Length
Point on surface
Return geometry as SVG
Many, many, many more
Viewing Data in PostGIS

Quick desktop viewing options
•
•
•
•
•

uDig
QGIS
gvSIG
CadCorp SIS*
FME Viewer*
Web based application options
• MapGuide
• Mapserver
• Geoserver
uDig Installation
Loading data from non spatial
sources




Create table using pgAdminIII
Copy data to the database using copy
command
Create the Geometry field using
AddGeometryColumn() spatial function
Populate the Geometry field using the
Longitude and Latitude fields
Querying the data

Calculate area in Hectare
• Load area_slums.shp into database with SRID
32644
• Add the area field with data type double
precision
• Update the area field using spatial function
ST_area()
• UPDATE area_slums set
area=ST_area(geometry)/10000;
Querying the data

What is the total length of all roads in
Rajahmundry corporation in Kilometers
• Add rjyroads_Names.shp data to database
with SRID 32644
• SELECT sum(ST_length(the_geom))/1000 as
kilometers from rjyroads_names;
Querying the data

Which the biggest slums in Rajahmundry
Corporation by area in Hectare
• Add area_slums.shp data to the database with
SRID 32644
• SELECT max(ST_area(geometry))/10000 from
area_slums;
Querying the data

Find churches within a radius of distance
from slums
• Use ST_dwithin() function
• SELECT distinct c.name,c.address from
area_slums as s , churches as c WHERE
ST_dwithin(s.geometry, c.the_geom, 200) =
TRUE;