postgis_ubuntu.txt

# initialize and install postgresql
sudo apt-get update
sudo apt-get -y install postgresql postgresql-client postgresql-contrib

# basics
sudo apt-get -y install git make gcc g++ cmake build-essential curl


# move postgre data to mnt
sudo mkdir /mnt/postgre_data
sudo chown postgres /mnt/postgre_data/
sudo chmod -R 700 /mnt/postgre_data/
sudo pg_dropcluster 9.1 main --stop
sudo pg_createcluster 9.1 main -d /mnt/postgre_data/
sudo su postgres
pg_ctlcluster 9.1 main start
exit


# java 7
add-apt-repository -y ppa:webupd8team/java
apt-get update
apt-get install -y oracle-java7-installer

#osmosis
mkdir projects
cd projects
wget http://bretth.dev.openstreetmap.org/osmosis-build/osmosis-latest.tgz
mkdir osmosis
tar xvzf osmosis-latest.tgz -C osmosis
chmod a+x osmosis/bin/osmosis
rm osmosis-latest.tgz
cd


# python
sudo apt-get -y install python-pip python-dev build-essential 
sudo pip install --upgrade pip 
sudo pip install ipython
sudo apt-get -y install python-psycopg2

# zsh
sudo apt-get -y install zsh
git clone git://github.com/robbyrussell/oh-my-zsh.git ~/.oh-my-zsh
cp ~/.zshrc ~/.zshrc.orig
cp ~/.oh-my-zsh/templates/zshrc.zsh-template ~/.zshrc
sudo chsh -s /bin/zsh

# vim
git clone https://github.com/gmarik/vundle.git ~/.vim/bundle/vundle

# scala
wget http://www.scala-lang.org/downloads/distrib/files/scala-2.10.0.tgz
tar zxf scala-2.10.0.tgz
sudo mv scala-2.10.0 /usr/share/scala
sudo ln -s /usr/share/scala/bin/scala /usr/bin/scala
sudo ln -s /usr/share/scala/bin/scalac /usr/bin/scalac
sudo ln -s /usr/share/scala/bin/fsc /usr/bin/fsc
sudo ln -s /usr/share/scala/bin/sbaz /usr/bin/sbaz
sudo ln -s /usr/share/scala/bin/sbaz-setup /usr/bin/sbaz-setup
sudo ln -s /usr/share/scala/bin/scaladoc /usr/bin/scaladoc
sudo ln -s /usr/share/scala/bin/scalap /usr/bin/scalap

# sbt
wget http://scalasbt.artifactoryonline.com/scalasbt/sbt-native-packages/org/scala-sbt/sbt//0.12.4/sbt.deb 
sudo dpkg -i sbt.deb

echo 'scalaVersion := "2.10.1"' >> ~/.sbt/global.sbt

# titan
mkdir -p /home/ubuntu/alexandre/DATA/gdb
wget http://s3.thinkaurelius.com/downloads/titan/titan-all-0.3.1.zip
unzip titan-all-0.3.1.zip
rm titan-all-0.3.1.zip
mv titan-all-0.3.1 titan
cd titan
git clone https://gist.github.com/53473be51ac7babc9701.git config/tit-yaml
mv config/tit-yaml/cassandra.yml config/cassandra.yml
rm -rf config/tit-yaml
git clone https://gist.github.com/26fc9705d87a1decd47e.git config/tit-cas-batch
./bin/titan.sh config/titan-server-rexster.xml config/tit-cas-batch/tit-cas-batch.properties




# create db for osmosis
sudo su - postgres
createdb osm
createlang plpgsql osm

psql -d osm -f /home/ubuntu/projects/hstore.sql
look up
psql -d osm -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
psql -d osm -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql
psql -d osm -f /home/ubuntu/projects/osmosis/script/pgsnapshot_schema_0.6.sql
psql -d osm -f /home/ubuntu/projects/osmosis/script/pgsnapshot_schema_0.6_action.sql

# populate db using osmosis
mkdir projects/pbf
cd projects/pbf
wget http://download.geofabrik.de/europe/france/ile-de-france-latest.osm.pbf
cd ..
osmosis/bin/osmosis --rbf file="pbf/ile-de-france-latest.osm.pbf" --wp user="postgres" database="osm"






















# build tools for pgrouting
sudo apt-get -y install libboost-all-dev 
sudo apt-get -y install postgresql-server-dev-9.1
sudo apt-get -y install libcgal*


# pgrouting
cd
wget https://github.com/pgRouting/pgrouting/archive/v2.0.0-beta.tar.gz
gzip -d v2.0.0-beta.tar.gz
tar -xvf v2.0.0-beta.tar
rm v2.0.0-beta.tar
cd pgrouting-2.0.0-beta/
mkdir build
cd build
cmake -DWITH_DD=ON ..
make
sudo make install

# setting the database for pgrouting

sudo su postgres
createdb -T template1 template_postgis
psql template_postgis -c "create language plpgsql"
psql template_postgis -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
psql template_postgis -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql
createdb -T template_postgis template_pgrouting
psql template_pgrouting -f /usr/share/postgresql/9.1/contrib/pgrouting-2.0/pgrouting.sql
createdb -U postgres -T template_pgrouting routing


# osm2pgrouting
cd /home/ubuntu/projects
sudo apt-get -y install libghc-postgresql-libpq-dev
sudo apt-get -y install libexpat1-dev
git clone https://github.com/pgRouting/osm2pgrouting.git
cd  osm2pgrouting
make
    #sudo vim /etc/postgresql/9.1/main/pg_hba.conf
    #change md2 and peer to md5 as in
    #http://gis.stackexchange.com/questions/1910/osm2pgrouting-connection-failed

./osm2pgrouting/osm2pgrouting -file "pbf/map.osm" -conf "osm2pgrouting/mapconfig.xml" -dbname routing -user postgres -clean


select pgr_createTopology('ways', 0.000001, 'the_geom', 'gid');
SELECT * FROM pgr_dijkstra('SELECT gid as id, source, target, length as cost FROM ways', 9,10,false, false);




create table network(gid serial, osm_id integer, name varchar, the_geom geometry, source integer, target integer, length float);


CREATE OR REPLACE FUNCTION compute_network() RETURNS text as $$
DECLARE
streetRecord record;
wayRecord record;
pointCount integer;
pointIndex integer;
geomFragment record;
BEGIN
    -- for each street
    FOR streetRecord in 
        select way, osm_id, name from planet_osm_line where highway is not null  LOOP

        -- for each street in the region of interest
        SELECT * from planet_osm_ways where id = streetRecord.osm_id into wayRecord; 
        FOR pointIndex in array_lower(wayRecord.nodes, 1)..array_upper(wayRecord.nodes,1)-1 LOOP
            RAISE NOTICE 'Inserting name % source %, target %', streetRecord.name, wayRecord.nodes[pointIndex], wayRecord.nodes[pointIndex+1];

            select st_makeline(st_pointn(streetRecord.way, pointIndex), st_pointn(streetRecord.way, pointIndex+1)) as way into geomFragment;

            insert into network(osm_id, name, the_geom, source, target, length) values(streetRecord.osm_id, streetRecord.name, geomFragment.way, wayRecord.nodes[pointIndex], wayRecord.nodes[pointIndex+1], st_length(geomFragment.way));
        END LOOP;
    END LOOP;
return 'Done';
END;
$$ LANGUAGE 'plpgsql';

select * from compute_network();





# osm2pgsql
sudo add-apt-repository -y ppa:kakrueger/openstreetmap
sudo apt-get update
sudo apt-get -y install osm2pgsql

# populate posgis database
mkdir projects
mkdir projects/pbf
cd projects/pbf
wget http://download.geofabrik.de/europe/france/ile-de-france-latest.osm.pbf
sudo su postgres
osm2pgsql -s ile-de-france-latest.osm.pbf