An OSGeo Training workshop at Centre For

An OSGeo Training workshop at Centre For Space Science And Techonology Education In Assia And The Pacific 12 th – 14 th January 2011 Dehradun Postgre. SQL/Post. GIS Santosh Gaikwad, Salim Ali Centre For Ornithology And Natural History (SACON), Hyderabad santosh@osgeo. in

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 Post. GIS? Post. GIS turns the Postgre. SQL Database Management System into a spatial database by adding support for the three features: spatial types, indexes, and functions

Why choose Postgre. SQL? Postgre. SQL 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 • Gi. ST 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 Post. GIS? Open Source/ Free • Loading/Extracting • Shp 2 Pgsql • ogr 2 ogr • Dxf 2 Post. GIS • Web-Based • Mapserver • Geo. Server (Java-based WFS / WMS -server ) • Sharp. Map SDK - for ASP. NET 2. 0 • Map. Guide Open Source (using FDO) • Desktop • u. Dig • QGIS • mezo. GIS • Open. JUMP • Open. EV • Sharp. Map SDK for Microsoft. NET 2. 0 • Zig. GIS for Arc. GIS/Arc. Objects. NET • Gv. SIG Closed /Proprietary • Loading/Extracting • Safe FME Desktop Translator/Converter • Web-Based • Ionic Red Spider (now ERDAS) • Cadcorp Geogno. SIS • Iwan Mapserver • Map. Dot. Net Server • Map. Guide Enterprise (using FDO) • ESRI Arc. GIS Server 9. 3+ • Desktop • Cadcorp SIS • Microimages TNTmips GIS • ESRI Arc. GIS 9. 3+ • Manifold • Geo. Concept • Map. Info (v 10) • Auto. CAD Map 3 D (using FDO)

Practical Postgre. SQL/Postgis Installation Creating a Spatial Database How to Spatially Enable an Existing Database Loading spatial data • • • Command prompt (shp 2 pgsql) (GUI) shp 2 pgsql loader Open. JUMP QGIS (SPIT) GDAL Geokettle Loading data from non-spatial sources Viewing the data Querying the data

Postgre. SQL/Post. GIS Installation

Open. Geo 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 • • • Postgre. SQL/Post. GIS Geo. Server Geo. Web. Cache Open. Layers Geo. Ext Postgre. SQL/Post. GIS database run on 54321 port http: //opengeo. org

Open. Geo Installation

Creating Spatial Database (using template_postgis)

Creating Spatial Database (without template_postgis) Create a new database Connect to the database Load/run the Post. GIS extension (postgis. sql) Earlier version has lwpostgis. sql Load/run the Post. GIS 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 shp 2 pgsql [opts] shapefile tablename > file. sql • Shp 2 pgsql –s 32644 –D C: churches. shp churches(table) > chueches. sql Read in. shp file Write out. sql file Load. sql file into Postgre. SQL • using psql • using Pg. Admin

Command Line Options -D = Use “dump” format -i = Do not use “bigint”, even for long numbers -I = Create a Gi. ST index on the geometry column -g = Specify the name of the geometry column -s <#> = Use this SRID -W = 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 pgsql 2 shp -f "test. shp" -u postgres -p 5432 -P password database schema. table

QGIS Installation

Open. JUMP • • 1. 2. 3. Open. JUMP is Open Source GIS software written in Java Programming language Installation: Install Java Install Open. JUMP Install Post. GIS database driver (Put Post. GIS 132. jar file in /lib/ext folder of Open. JUMP)

Java Installation

Open. JUMP Installation

Geometry Input and Output Well-known text (WKT) • ST_Geom. From. Text(text) returns geometry • ST_As. Text(geometry) returns text • ST_As. EWKT(geometry) returns text Well-known binary (WKB) • ST_Geom. From. WKB(bytea) returns geometry • ST_As. Binary(geometry) returns bytea • ST_As. EWKB(geometry) returns bytea Geographic Mark-up Language (GML) • ST_Geom. From. GML(text) returns geometry • ST_As. GML(geometry) returns text Keyhole Mark-up Language (KML) • ST_Geom. From. KML(text) returns geometry • ST_As. KML(geometry) returns text Geo. JSON • ST_As. Geo. JSON(geometry) returns text Scalable Vector Graphics (SVG) • ST_As. SVG(geometry) returns text

Geoprocessing with Post. GIS 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 more

Viewing Data in Post. GIS Quick desktop viewing options • • • u. Dig QGIS gv. SIG Cad. Corp SIS* FME Viewer* Web based application options • • • Map. Guide Mapserver Geoserver

u. Dig Installation

Loading data from non spatial sources Create table using pg. Admin. III Copy data to the database using copy command Create the Geometry field using Add. Geometry. Column() 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;