PostgreSQL/PostGIS

1. 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 santosh@osgeo.in

2. What is Spatial data? • Data which describes either location or shapee.g.House or Fire Hydrant location Roads, Rivers, Pipelines, Power lines Forests, Parks, Municipalities, Lakes

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

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

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

6. Why not Shapefiles? • Files require special software to read and write • Concurrent users can cause corruption • Complicated questions require complicated software to answer

7. What applications support PostGIS?

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

9. PostgreSQL/PostGIS Installation

10. OpenGeo Suite • It is the complete , OGC standards- compliant web mapping platform built on powerful, cutting-edge, 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

11. OpenGeo Installation

12. Creating Spatial Database (using template_postgis)

13. 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)

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

15. Metadata Tables

16. Loading Shape Files • shp2pgsql [opts] shapefiletablename > 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

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

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

19. QGIS Installation

20. OpenJUMP • 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)

21. Java Installation

22. OpenJUMP Installation

23. Geometry Input and Output • Well-known text (WKT) • ST_GeomFromText(text) returns geometry • ST_AsText(geometry) returns text • ST_AsEWKT(geometry) returns text • Well-known binary (WKB) • ST_GeomFromWKB(bytea) returns geometry • ST_AsBinary(geometry) returns bytea • ST_AsEWKB(geometry) returns bytea • Geographic Mark-up Language (GML) • ST_GeomFromGML(text) returns geometry • ST_AsGML(geometry) returns text • Keyhole Mark-up Language (KML) • ST_GeomFromKML(text) returns geometry • ST_AsKML(geometry) returns text • GeoJSON • ST_AsGeoJSON(geometry) returns text • Scalable Vector Graphics (SVG) • ST_AsSVG(geometry) returns text

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

25. Viewing Data in PostGIS • Quick desktop viewing options • uDig • QGIS • gvSIG • CadCorp SIS* • FME Viewer* • Web based application options • MapGuide • Mapserver • Geoserver

26. uDig Installation

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

28. 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;

29. 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;

30. 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;

31. 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;