Setting up postGIS

• Download postGIS source code from http://postgis.refractions.net/download/
• Extract and build

  ./configure
  make
  sudo make install
  

• Install the proj projection library from | http://download.osgeo.org/proj/proj-4.6.1.tar.gz
• Extract and build

  ./configure
  make
  sudo make install
  

• Install the GOES gemoetry engine from | http://trac.osgeo.org/geos/
• Extract and build

  [extract bzip2 -d filename]
  ./configure
  make
  sudo make install
  

• Install postGIS for Ubuntu from synaptic
• Create a DB in postgreSQL, and install postGIS

  su postgres
  psql -h ubi.cs.washington.edu -W -d jobDB -U jobAdmin
       CREATE DATABASE "postgis"
         WITH OWNER = "jobAdmin"
              ENCODING = 'UTF8';
  psql -d postgis -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql
  

• Add shape files to DB

  cd /projects/ubicomp99/postgis/bus/data
  # shp2pgsql -D myshape.shp mytable mytestdb | psql mytestdb
  # shp2pgdwl -s [washington coordinate plane] [shape file] [tablename] | psql -d [dbname]
  shp2pgsql -s 2926 Busstop busstop | psql -d postgis
  shp2pgsql -s 2926 routes busroute | psql -d postgis
  shp2pgsql -s 2926 moving_stops moving_stops | psql -d postgis
  

  psql -d postgis -f bus_stop.sql <mysql> -- moving_stop_gid: 1 - N (for every moving stop get 2x points) -- busstop_id: DB busstop id number -- nn_dist: distance from point to busstop in feet -- nn_gid: unique_id for busstop

  
  -- Query Detail: -- http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor_generic -- moving_stops are pauses in the trip as defined by the moving_stop shape file (example data trace)

  -- (pgis_fn_nn(b.the_geom, 1000, 2, 10, 'busstop', 'true', 'gid', 'the_geom')).* -- pgis_fn_nn(geom1 geometry, distguess double precision, numnn integer, maxslices integer, lookupset varchar(150), swhere varchar(5000), sgid2field varchar(100), sgeom2field varchar(100)) -- geometry (b.the_geom - busstop gemoetry field) -- distguess distance in feet (1000) from the specified point -- numnn number of matches to find (2 is number of bus stops to find per moving_stop, max data points to return) -- maxslices If you have a super large data set where your distguess is very large and you want a small set of points. You set this to be greater than 1 so you iterate over the distguess (disguess*1/maxslices to ... disguess*maxslices/maxslices). The larger this is the longer the query takes if it has to expand fully out to your distguess -- lookupset table that you want to search on (busstops) -- swhere true -- sgid2field unique_id name in busstops (gid) -- sgeom2field name of the geometry field (the_geom)

  SELECT g1.gid as moving_stop_gid, b.busstop_id, g1.nn_dist, g1.nn_gid FROM (SELECT b.gid,

     (pgis_fn_nn(b.the_geom, 1000, 20, 1, 'busstop', 'true', 'gid', 'the_geom')).*
     FROM (SELECT * FROM moving_stops) b) As g1, busstop b
     WHERE b.gid = g1.nn_gid
     ORDER BY moving_stop_gid ASC, nn_dist ASC;
  

  </mysql>