Practical PostGIS

1
Practical PostGIS
Tobin Bradley Mecklenburg County GIS
2
Lecture Outline

• What is PostGIS?
• What is PostgreSQL?
• Loading Data
• Accessing Data
• Geoprocessing with PostGIS
• Example 1 Feature on Feature Selection
• Example 2 Bus Stops Near Parcel
• Example 3 Mapping and Web Services
• Some Tips
• Links

3
What is PostGIS?

• Adds support for geographic objects to the
  PostgreSQL object-relational database
• Released under the GNU GPL
• Follows OpenGIS simple features specification
  (certified compliant)
• Geoprocessing functions available through SQL API

4
What is PostgreSQL?

• Powerful, open source relational database system
• BSD License
• 15 years of active development
• Supports all major platforms
• ACID compliant, strong ANSI-SQL compliance
• Supports foreign keys, joins, views, triggers,
  and stored procedures
• Lots of users
• Government
• UN, CDC, State Department, Department of Labor
• Industry
• Apple, Fujitsu, BASF, Cisco, Skype, Sun,
  SourceForge
• Infrastructure
• Entire .ORG domain name registry

5
Loading Data

• Loading shapefile data is a snap!
• Load data with shp2pgsql
• Load data with OGR
• Load data with SPIT (QGIS Tool)
• Load data with ESRI's Data Interoperability
  extension ()
• Load data with SQL

6
Accessing Data

• SQL (text output)
• ODBC driver
• Native drivers available for most languages
  (Python, Ruby, Java, PHP, .NET, etc.)
• Mapping (graphic output)
• MapServer
• uDig
• QGIS
• Grass
• FME (ArcGIS through Data Interoperability
  extenion)
• Growing list of software
• OGC Web Services
• MapServer
• Geoserver

7
Geoprocessing with PostGIS

• PostGIS functions (330) 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

8
Example 1 Feature on Feature Selection

• Select voting precinct of parcel 11111111
• 309,694 Parcels, 195 Voting Precincts

9
Example 2 Bus Stops Near Parcel

• Select bus stops within 1 mile of parcel
  11111111
• 309,694 Parcels, 3820 Bus Stops

10
Example 3 Mapping Web Services

• Web Sites, Web Services, and uDig

11
Some Tips

• Make sure spatial indexes are built
• CREATE INDEX idx_index_name ON table USING GIST
  (the_geom GIST_GEOMETRY_OPS)
• VACUUM ANALYZE table
• Use the bounding box to speed spatial queries
• boe.the_geom tax.the_geom
• Assign SRID's to all layers
• Don't use PostGIS version bundled with PostgreSQL
• Visit PostGIS web site for the latest release

12
Links

• PostgreSQL
• http//www.postgresql.org/
• PostGIS
• http//postgis.refractions.net/
• The Programming Consultant Newsletter
• http//thepcnews.blogspot.com/