Lessons Learned from Experiments with PostGIS

1. Lessons Learned from Experiments with PostGIS Eleanor Tutteleanor@rhcda.comSt. Louis

2. What is PostGIS? • Free and Open Source • Adds support for geographic objects to PostgreSQL database (which is also open source) Eleanor Tutt | Lessons Learned from Experiments with PostGISProvidence NNIP Meeting | September 2012

3. Why Experiment ?? • Growing quantity of data • Increasingly complex analysis needs • Tired of seeing this in ESRI help files: Eleanor Tutt | Lessons Learned from Experiments with PostGISProvidence NNIP Meeting | September 2012

4. Continued Concerns • Other priorities besides shuffling data • Fear of reliance on a lesser-known platform Skills Identified on LinkedIn Profiles 191,000 61,000 3,000 Eleanor Tutt | Lessons Learned from Experiments with PostGISProvidence NNIP Meeting | September 2012

5. The experiment so far… Eleanor Tutt | Lessons Learned from Experiments with PostGISProvidence NNIP Meeting | September 2012

6. Frustrating - Projections • Need SRID to properly import data (and if you forget, you don’t get a warning) Eleanor Tutt | Lessons Learned from Experiments with PostGISProvidence NNIP Meeting | September 2012

7. Frustrating - Projections • SRID has to be in “spatial_ref_sys” table (and not all codes are there by default) Where’s my SRID??? Eleanor Tutt | Lessons Learned from Experiments with PostGISProvidence NNIP Meeting | September 2012

8. Frustrating – Projections • No “on the fly” projection – must be constantly aware of SRID and use ST_Transform any time SRIDs don’t match Eleanor Tutt | Lessons Learned from Experiments with PostGISProvidence NNIP Meeting | September 2012

9. Exciting – Increased Speed • ArcGIS takes up to 45 seconds to get past the splash screen (let alone adding data)… • …when half the time, my queries are simple and don’t require a “pretty map” (“Which bus routes are within a half mile of a site?”) Eleanor Tutt | Lessons Learned from Experiments with PostGISProvidence NNIP Meeting | September 2012

10. Exciting – New Analysis Capabilities • Drive time polygons/routings • Converting polygons to lines/splitting lines into line segments • So much more… Eleanor Tutt | Lessons Learned from Experiments with PostGISProvidence NNIP Meeting | September 2012

11. Exciting – ESRI + PostGIS! Eleanor Tutt | Lessons Learned from Experiments with PostGISProvidence NNIP Meeting | September 2012

12. ArcMap Query Layers + PostGIS Eleanor Tutt | Lessons Learned from Experiments with PostGISProvidence NNIP Meeting | September 2012

13. Side Benefits – Better Understanding of ArcGIS • Learning a different language helps you think in different ways • What is intuitive in one system may be possible, but easy to miss, in the other Eleanor Tutt | Lessons Learned from Experiments with PostGISProvidence NNIP Meeting | September 2012

14. Side Benefits – Opportunity for “Data Audit” “Export_Output_2.shp” “test_data.shp” “RHCDA20110418.shp” Eleanor Tutt | Lessons Learned from Experiments with PostGISProvidence NNIP Meeting | September 2012

15. Conclusions??? • Dealing with projections is incredibly frustrating – especially with data from two states • Learning curve is steep, but climb-able • We aren’t ready to leave ArcGIS, yet Eleanor Tutt | Lessons Learned from Experiments with PostGISProvidence NNIP Meeting | September 2012