1. Build your own MapQuest! Adding Spatial Smarts to PostgreSQL with PostGIS Paul RamseyRefractions Research

2. http://mq-mapgend.websys.aol.com:80/mqmapgend?MQMapGenRequest=FDR2dmwjDE%3byt29%26FDJnci4Jkqj%2cMMCJ%3aHOEvq%3bwy5lab%3a%29uaxlw1w%26%40%24%3a%26%40%24s%26wzx%26a8x%26ESEKGF%3dTPWIK%2crgqabx%26%3d2llarwh%40%24%3a%26%40%24%3a%26a2%3ahttp://mq-mapgend.websys.aol.com:80/mqmapgend?MQMapGenRequest=FDR2dmwjDE%3byt29%26FDJnci4Jkqj%2cMMCJ%3aHOEvq%3bwy5lab%3a%29uaxlw1w%26%40%24%3a%26%40%24s%26wzx%26a8x%26ESEKGF%3dTPWIK%2crgqabx%26%3d2llarwh%40%24%3a%26%40%24%3a%26a2%3a

3. On-the-Fly Cartography ColorfulPicture Cartography Engine GIS Data

4. GIS Data Don’t Panic • File Formats • Data Organization • Coordinate Projections • Data Access • Data Quality

5. File Formats • At least one per vendor • ESRI : Shape, Coverage, E00, GeoDatabase • CAD : DXF, DGN • Government : SDTS, TIGER, DLG • MapInfo : MIF/MID, TAB • Standards : GML • Open source conversion • OGR (gdal.maptools.org/ogr)

6. Data Organization • By Administrative Unit • Counties, States • By Gridding Unit • USGS 24K Quads, NTS Mapsheets • By Natural Unit • Watersheds

7. -114.068,49.000 1859612,511175 GeographicLongitude / Latitude Albers Equal AreaOrigin -126,0Parallels 58.5,50.0Offsets 1000000,0 Coordinate Projections

8. Coordinate Projections • United States • UTM (Universal Transverse Mercator) • “Stateplane” • Lambert • Transverse Mercator • Geographic • Always Find Out the Projection of Your Data • .prj file

9. Data Access • United States • Mostly Free Federal Data • www.geodata.gov • tiger.census.gov • www.fgdc.gov • transtats.bts.gov • geonames.usgs.gov • www.fws.gov/data • Some Free State Data • Little Free County / Municipal Data

10. Data Acce$$ • Commercial Providers • NavTeq • TeleAtlas ( + GDT ) • MapInfo • DigitalGlobe • SpaceImaging

11. Data Quality USGS DLG TIGER Census

12. Cartography Engine • Proprietary • ArcIMS • MapExtreme • Open Source • GeoServer • DeeGree • UMN Mapserver

13. UMN Mapserver • mapserver.gis.umn.edu • Multiple Format Support • Shape, TAB, Coverage, DGN, PostGIS, More… • Multiple Projection Support • Standards Support • OpenGIS Web Map Server • OpenGIS Web Feature Server • OpenGIS Styled Layer Descriptor

14. UMN Mapserver Format AProjection 1 Mapserver Format BProjection 1 Format CProjection 2 “Map File” ourmap.map

15. MAP EXTENT 1192130 379840 1199560 385520 SIZE 400 400 SHAPEPATH "/Users/pramsey/Sites" PROJECTION "init=epsg:42102" # BC Albers END LAYER NAME "Lakes" STATUS ON DATA "lakes" TYPE POLYGON PROJECTION "init=epsg:42102" # Geographic END CLASS NAME "Lakes" COLOR 205 223 255 END END END

16. UMN Mapserver & PostGIS PostGIS / PostgreSQLSpatialDatabase Mapserver “Map File” ourmap.map

17. This is your Database… • “Is there a Main Street?” • “How many patients does the prenatal center serve?” • “How many buildings in the city have more than two stories?” • “What is the total amount of cash withdrawals for customer X?”

18. This is your Database on Spatial… • “What is the total length of Main Street?” • “What is the total number of patients within 3 miles of the prenatal center?” • “How many buildings of more than 2 stories are along the parade route?” • “Have all the cash withdrawals for customer X been within 50 miles of her primary residence?”

19. PostGIS / PostgreSQL • Extension to PostgreSQL • src/contrib/ • GEOMETRY • Point, Line, Polygon, Multi* • Functions • Indexes • http://postgis.refractions.net

20. PostGIS Types • POINT • LINESTRING • POLYGON • MULTIPOINT • MULTILINESTRING • MULTIPOLYGON • GEOMETRYCOLLECTION . ..

21. PostGIS Types id | geometry | name ------+---------------------------+-------------- 1294 | POINT(1199437.8 382509.2) | Brighton Ave 1295 | POINT(1199369.3 382741.7) | York Pl 1296 | POINT(1199494.8 382751) | Prospect Pl

22. PostGIS Functions • Single Geometry • Float : Length(Geometry) • Float : Area(Geometry) • Integer : NumGeometries(Geometry) • Integer : NumPoints(Geometry) • Geometry : Buffer(Geometry,Float) • Point : PointN(Geometry) • Text : AsText(Geometry) • ByteA : AsBinary(Geometry)

23. PostGIS Functions • Multiple Geometry • Float : Distance(Geometry,Geometry) • Boolean : Touches(Geometry,Geometry) • Boolean : Intersects(Geometry,Geometry) • Boolean : Disjoint(Geometry,Geometry) • Geometry : Intersection(Geometry,Geometry) • Geometry : GeomUnion(Geometry,Geometry) • Geometry : Difference(Geometry,Geometry)

24. PostGIS Indexes • Geometries Require Special Index • Sortable in two dimensions • Cover range of values • R-Tree Index (GiST Implementation) • Linear Time Picksplit Algorithm • http://postgis.refractions.net/rtree.pdf

27. Get on with it!! Get on with it!!

28. Our Architecture PostGIS / PostgreSQLSpatialDatabase Mapserver Map File ourmap.map GIS Data

29. Our Steps • Install / Setup • Mapserver, PostGIS • GDAL, OGR, PROJ4, GD • Acquire GIS Data • Load GIS Data  PostGIS • Write Map File for Mapserver • … • Profit!

30. Acquire Data • For demo, hunted down Oregon data: 1981636 Jul 28 12:02 OR_deci.zip 564501 Jul 28 11:55 county.zip 34729355 Jan 9 2003 hydrogm020.tar.gz 62246216 Jul 28 11:55 gdt.zip 1853534 Jul 28 11:55 railroads.zip 3293501 Jul 28 12:42 s_12my03.zip

31. Load Data • Load names data with perl script: "OR","4H Camp","locale","Coos",41,011,"431355N","1235945W",43.23194,-123.99583,"","",,,216,,"","Dora" INSERT INTO names VALUES (1,'OR','4H Camp','locale','Coos', 'SRID=4269;POINT(-123.99583 43.23194)', 216,NULL,'Dora’);

32. Load Shape Files • PostGIS includes Shape loader utility: shp2pgsql -s 200000 \counties.shp counties | psql demo-oregon

33. Write Map File • Good computer cartography is the artful combination of elements, styles, colors and data at appropriate scales. • MapQuest is an example of excellent cartography. • So is a National Geographic Atlas of the World.

34. LAYER TEMPLATE template.html DUMP TRUE NAME "States" METADATA "wms_title" "States" END STATUS ON DATA "the_geom from states" TYPE POLYGON CONNECTIONTYPE POSTGIS CONNECTION "user=pramsey dbname=demo-oregon" PROJECTION "init=epsg:4269" END CLASSITEM "admin_name" CLASS NAME "States" COLOR 255 255 230 OUTLINECOLOR 20 20 20 EXPRESSION "Oregon" END CLASS NAME "States" COLOR 240 240 240 OUTLINECOLOR 20 20 20 END END

36. Demonstration • iBook G3 800Mhz 128MB • PostGIS / PostgreSQL, Mapserver, Apache • Roads • States • Counties • Geographical Names • Water Features