postgis l.
Skip this Video
Loading SlideShow in 5 Seconds..
PostGIS PowerPoint Presentation
Download Presentation

Loading in 2 Seconds...

play fullscreen
1 / 21

PostGIS - PowerPoint PPT Presentation

  • Uploaded on

PostGIS. Demonstration using Road Data Using GIS in your project Rahul Vaidya. Contents. Overview Why should I care? PostgreSQL ? PostGIS ? Development Connecting Accounts Development Tools Getting Data Examples PostGIS Table Structure Spatial Queries Making the queries

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'PostGIS' - LionelDale

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript


Demonstration using Road Data

Using GIS in your project

Rahul Vaidya

  • 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
  • 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
why should i care
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!
postgresql postgis
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












postgis summary
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
  • 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.
  • Host on CENS network, only directly accessible while on CS network.
    • Host:
    • 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
  • Only web server we can use that can access the database server is
  • 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.
    • What you get:
      • Access to the cs219 database using your username
      • Your own GIS database for your data
development tools
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
getting data
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
  • 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
examples cont d
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
postgis table structure
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
spatial queries
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
spatial query sample
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
making the queries
Making the Queries
  • SQL
    • $dbconn = pg_connect(" port=5432 dbname=cs219 user=cs219 password=uclacens");
    • $result = pg_query($dbconn, $yourquery);
  • Look at example code for very specific usage examples
  • 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
  • Free US Census 2000 shapefiles [link]
  • [link]
  • PostGIS Documentation Links:
    • Data retrieval
    • Function reference
  • Ajax Tutorial [link]