1 / 31

PostgreSQL/PostGIS

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.

saxon
Download Presentation

PostgreSQL/PostGIS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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;

More Related