Released nearly 15 years ago, PostGIS is an open source extension to Postgres that spatially enables your database server, and follows the OGC Simple Features for SQL specification. If you are coming from another standards based spatial database you may notice similarities in the spatial data types, naming conventions, and functionality.
You can easily install PostGIS using EDB StackBuilder and then connect to your database and run the create extension command to enable PostGIS. Now you can create tables with a spatial column, or use the AddGeometryColumn function to add a spatial column to an existing table. And don’t forget to add a spatial index to your spatial column using the standard create index syntax - ‘create index idx_regions_geom on hr.regions using gist (geom);'.
Learn more about the industry's favorite geospatial extension, PostGIS, in the webcast by Adam Wright, Getting Started with PostGIS.
Loading spatial data can be accomplished with standard SQL or by using a tool. For desktop application users with a repository of Esri shapefiles lying around, shp2pgsql is a simple tool for loading those shapefiles to Postgres. I have run into a number of databases out in the field with tables containing latitude/longitude values stored as numeric data types. In those cases, we can leverage the many type conversion functions such as st_geomfromtext in a INSERT or UPDATE statement to convert to a PostGIS geometry. Alternatively, we can use one of the st_make functions to make a geometry object, for example, ‘update regions set geom = st_setsrid(st_makepoint(lon,lat),4326) where regid = regid;’.
Once your data is in PostGIS, you can now answer formerly complex questions with simple SQL, such as ‘tell me the number of ATMs within one mile of a given point;’, or other questions like:
- Determine the length of any line such as a walking path using st_length.
- Determine the area of a polygon using st_area(geom).
- Tell if spatial object intersects with a protected area using st_intersects(geom,geom).
Those are just a few of the powerful functions available in PostGIS. There are many more available to answer complex location-based questions with just a little bit of SQL.
- Easily scale out your workload with Streaming Replication.
- Support multiple readers and writers with MVCC.
- Use triggers to track edit history or automatically extend your workflow.
- Mash up data from other data sources using Foreign Data Wrappers.
- Use table-partitioning to break large data sets down into more manageable pieces.
- Build custom functions using one of the many procedural languages in Postgres like PL/Python or PL/R.
Database professionals who are new to GIS technology should not expect to become experts after reading this blog post. But with any luck, I gave you an introduction to PostGIS and what it means to support a spatial database. There is also more information available in a webcast I delivered, Getting Started with PostGIS.
Adam Wright is a Database Consultant in Professional Services at EnterpriseDB.