1 / 21

PostGIS - PowerPoint PPT Presentation

  • Updated 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
Postgis l.jpg


Demonstration using Road Data

Using GIS in your project

Rahul Vaidya

Contents l.jpg

  • 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

Overview l.jpg

  • 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 l.jpg
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 l.jpg
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

Slide6 l.jpg








Slide7 l.jpg




Postgis summary l.jpg
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

Development l.jpg

  • 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.

Connecting l.jpg

  • 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

Accounts l.jpg

  • 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 l.jpg
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 l.jpg
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

Examples l.jpg

  • 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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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

Projections l.jpg

  • 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

Resources l.jpg

  • Free US Census 2000 shapefiles [link]

  • [link]

  • PostGIS Documentation Links:

    • Data retrieval

    • Function reference

  • Ajax Tutorial [link]