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

  -- 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>
  
  * Can change distance to mahalanobis distance if needed
  </ul>