PostGIS

1. PostGIS Demonstration using Road Data Using GIS in your project Rahul Vaidya

2. Contents • Overview • Why should I care? • PostgreSQL? PostGIS? • Development • Connecting • Accounts • Development Tools • Getting Data • Examples • PostGIS Table Structure • Spatial Queries • Making the queries • Projection • Resources

3. Overview • GIS Overview • 4/16 Eric Howard, Natalia Vinnik • Not just coordinates on a world map! • Spatially-relating data • Deriving relationships from this data • PostGIS • Free GIS database solution • Works with the PostgreSQL RDBMS

4. Why should I care? • Data to include in your projects! • Example: Parking Alert System • Relationship between GPS coordinates and parking type? • Table lookups! • JS polygon filter example • Data on database: • Road Maps • 2000 Census if requested • Your own data, with the POWER OF GIS!

5. PostgreSQL? PostGIS? • PostgreSQL • Store data in a database of interrelated tables • SensorBase uses a database • PostgreSQL is a powerful free database • PostGIS • Spatial Database Extension to PostgreSQL • Adds a library of spatially-related operations to PostgreSQL • What does this mean? • To answer this, look at ArcGIS

6. ArcMap ArcSDE Shapefiles Database: MySQL MS SQL Oracle

7. PostGIS shp2pg Shapefiles

8. PostGIS Summary • Functions to relate spatial geometries • Run queries that take advantage of relationships between geometries • Can import shapefiles • ESRI open standard, look around on the net

9. Development • PHP or Python • I have used PHP with the PostgreSQL module on leonia, it works. • CENS uses a lot of Python, so Python PostgreSQL support is most likely already there. If not, bug Sasank. • If something’s wrong on leonia, bug Sasank. • If something’s wrong on dragonfly, bug me or Mohammad.

10. Connecting • Host on CENS network, only directly accessible while on CS network. • Host: dragonfly.cens.ucla.edu • Database: cs219 • Port: 5432 (PostgreSQL port) • Username: cs219 • Password: uclacens • Can be accessed through shell if off campus by first connecting to leonia, then connecting to dragonfly

11. Accounts • Only web server we can use that can access the database server is leonia.cens.ucla.edu • If you want to use GIS data: • Request a leonia account from Sasank if you don’t already have one. • Send me an email (cc: Mohammad) requesting a dragonfly account for the group. • rahulnvaidya@gmail.com • What you get: • Access to the cs219 database using your username • Your own GIS database for your data

12. Development Tools • psql ( do “man psql” for docs) • Command line tool to access PostgreSQL database • On both leonia and dragonfly • uDig[link] • Free ArcMap alternative that can use PostGIS and shapefiles as data sources • Quirky, I couldn’t load road data • ArcGIS Explorer [link] • Lightweight free data viewer from ESRI • Can view shapefiles, but not PostGIS (why would they support a competitor?) • shp2pg [documentation link] • Export shapefiles to PostGIS • On dragonfly

13. Getting Data • Find shapefiles! Search the web. There are lots of GIS resources available. • Open it up in uDig or ArcGIS Explorer to get a feel of the data, its fields, the geographic range it covers, etc. • Transfer it to dragonfly via SCP • Use the shp2pg tool to import the data into PostGIS

14. Examples • Sample Data: Roads in Los Angeles • Number of entries: 254,352 • Imagine filtering through that with JavaScript. • Example 1[link] • SQL data enumerator • Example 2 [link] • Street name, city, and zip lookup in Google Maps

15. Examples (cont’d) • Modify and use my example 1 code to find out what fields are in a specific table. • AJAX used in example 2 • Quick and dirty tutorial (I used it) [link] • Basics: • After page is fully loaded, don’t want to reload full page to get updated data • Send request to server using special Javascript command • Receive result and modify a specific part of the page, leaving everything else the same • Most prominent example: GMail

16. PostGIS Table Structure • Two PostGIS tables: • geometry_columns • Information about the geometry types in the database • Sometimes not used, the roads example doesn’t use it • spatial_ref_sys • Information about the spatial reference system • Projection parameters transform the global coordinate system (3D) into 2D a representation, similar to projections in computer graphics

17. Spatial Queries • Comparisons between two geometries. • Geometry Types: • Geometry, Point, Curve, LineString, Surface, Polygon, GeometryCollection, MultiPoint, MultiCurve, MultiLineString, MultiSurface, MultiPolygon • Typical Geometry Operations • Intersects, Overlaps, Distance, Within, Touches • Geometry  Text Conversions • AsText, GeomFromText

18. Spatial Query Sample SELECT fename, fetype, city, zipl, Distance(the_geom,GeomFromText('POINT($lng $lat)',4326)) as distance FROM roads ORDER BY distance ASC LIMIT 1; • Deconstruction • Distance Function between two geometries, to locate closest road • Geometry 1: The road, a MultiLineString • Geometry 2: The clicked point, a Point • Set SRID of clicked point to 4326 (more on this later) • For some reason, geometry syntax in PostGIS is lng lat rather than the standard lat lng

19. Making the Queries • SQL • $dbconn = pg_connect("host=dragonfly.cens.ucla.edu port=5432 dbname=cs219 user=cs219 password=uclacens"); • $result = pg_query($dbconn, $yourquery); • Look at example code for very specific usage examples

20. Projections • SRID – Spatial Reference ID • A unique identifier for a projection system • Sample SRIDs: • 2225 – California zone 1 • 2226 – California zone 2 • 4326 – WGS 1984 (generic reference) [link] • Look at shapefiles in uDig/ArcGIS Explorer or use example 1 to see SRIDs for your data • The roads table uses 4326 for all roads • I would suggest keeping it simple and using 4326 for all custom data, but for downloaded data you will need to match SRID. • PostGIS complains if SRIDs do not match during a spatial query

21. Resources • Free US Census 2000 shapefiles [link] • FreeGIS.org [link] • PostGIS Documentation Links: • Data retrieval • Function reference • Ajax Tutorial [link]