Introduction to PostGIS

1. Introduction to PostGIS PostGIS Basics for the New User Paul Ramsey & Chris HodgsonRefractions Research

2. PostgreSQL • 1986 : Postgres project at Berkley • Successor to Ingres • Relational Model • Complex Objects • Extensibility • 1995 : Postgres95 adds SQL Support • 1996 : Open Source Community

3. PostGIS • PostgreSQL 7.1 (Tuple Toaster) • Real GIS Support • First Release in 2001 • OpenGIS “Simple Features for SQL” • Current Release 0.8.2

4. Why PostGIS? • Because Databases are Better than Files! • Unified Storage, Management, Access • SQL Everywhere • Transactional Integrity • Multiple Users, Multiple Edits

5. PostGIS Users • Data Handlers • Unified Access Language (SQL) • Unified Metadata Location • GlobeXplorer • i-cubed • Refractions

6. PostGIS Users • Real Time Systems Developers • Unified Access Language (SQL) • Standard Access Protocols • JDBC • ODBC

7. Team CIMAR : DARPA Grand Challenge

8. PostGIS Users • Spatial Infrastructures WMSClient JUMP Internet WMS / WFS LAN PostGIS WFSClient QGIS

9. PostgreSQL • Version 7.5 Native Windows Support

10. Windows PostgreSQL \bin Executables \include Include files for compilation \lib DLL shared library files \share Extensions env.bat Command prompt initdb.bat Initialize \data area pgstart.bat Start the database server

11. Create a Database • createdb postgis • Make the database • psql postgis • Connect to the database • create, insert, select • Try the database

12. Load PostGIS • PostgreSQL Extension • libpostgis.so • postgis.sql • Requires PL/PgSQL • createlang plpgsql postgis • psql -f postgis.sql postgis • psql -f spatial_ref_sys.sql postgis

13. POINT(5 5) Simple Spatial SQL POINT(0 5) POINT(0 0) POINT(5 0)

14. Load Shape Files • select count(*) from bc_roads • select count(*) from bc_voting_areas

15. bc_pubs

16. bc_roads

17. bc_hospitals

18. bc_municipality

19. bc_voting_areas

20. bc_voting_areas

21. Creating Spatial Indexes

22. Using Spatial Indexes select gid, name from bc_roads where crosses( the_geom, GeomFromText(‘LINESTRING(…)’,42102) )and the_geom && GeomFromText(‘LINESTRING(…)’,42102) select gid, name from bc_roads where crosses( the_geom, GeomFromText(‘LINESTRING(…)’,42102) )

23. Query Plans

24. #---------------------------------------------------------------------------#--------------------------------------------------------------------------- # CONNECTIONS AND AUTHENTICATION #--------------------------------------------------------------------------- # - Connection Settings - #listen_addresses = 'localhost' # what IP interface(s) to listen on; # defaults to localhost, '*' = any #port = 5432 max_connections = 100 #superuser_reserved_connections = 2 #rendezvous_name = '' # defaults to the computer name # - Security & Authentication - #authentication_timeout = 60 # 1-600, in seconds #ssl = false #password_encryption = true #db_user_namespace = false #--------------------------------------------------------------------------- # RESOURCE USAGE (except WAL) #--------------------------------------------------------------------------- # - Memory - shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each #work_mem = 1024 # min 64, size in KB #maintenance_work_mem = 16384 # min 1024, size in KB #max_stack_depth = 2048 # min 100, size in KB #vacuum_cost_page_hit = 1 # 0-10000 credits #vacuum_cost_page_miss = 10 # 0-10000 credits PostgreSQL Optimization

25. Data Integrity Valid Invalid

26. Distance Queries select sum(upbc) as unity_voters from bc_voting_areas where the_geom && setsrid(expand(‘POINT(…)’::geometry,2000) 42102 ) and distance( the_geom, geomfromtext(‘POINT(…)’, 42102) ) < 2000;

27. Spatial Joins • Associate two tables based on a spatial relationship, rather than an attribute relationship.

28. select m.name, sum(v.ndp) as ndp, sum(v.lib) as liberal, sum(v.gp) as green, sum(v.upbc) as unity, sum(v.vtotal) as total from bc_voting_areas v, bc_municipality m, where v.the_geom && m.the_geom and intersects(v.the_geom, m.the_geom) group by m.name order by m.name;

29. Overlays • Table on table overlays are possible with the Intersection() function. • Our example will only overlay one polygon with another table.

30. create table pg_voting_areas asselect intersection(v.the_geom, m.the_geom) as intersection_geom, area(v.the_geom) as va_area, v.*, m.name from bc_voting_areas v, bc_municipality m where v.the_geom && m.the_geom and intersects(v.the_geom, m.the_geom) and m.name = ‘PRINCE GEORGE’;

31. Coordinate Projection SRID=42102;MULTILINESTRING((1004687.04355194 594291.053764096,1004729.74799931 594258.821943696)) SRID=4326;MULTILINESTRING((-125.9341 50.3640700000001,-125.9335 50.36378))

32. Exercises &Questions