Setting up postGIS

From University of Washington - Ubicomp Research Page
Jump to: navigation, search
  • Download postGIS source code from
  • Extract and build
    sudo make install
  • Install the proj projection library from |
  • Extract and build
    sudo make install
  • Install the GOES gemoetry engine from |
  • Extract and build
    [extract bzip2 -d filename]
    sudo make install
  • Install postGIS for Ubuntu from synaptic
  • Create a DB in postgreSQL, and install postGIS
    su postgres
    psql -h -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:
    -- 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;
    * Can change distance to mahalanobis distance if needed