vallettea

sudo mkdir /home/ubuntu/working/postgre_data
sudo chown postgres /home/ubuntu/working/postgre_data/
sudo chmod -R 700 /home/ubuntu/working/postgre_data/
sudo pg_dropcluster 9.2 main --stop
sudo pg_createcluster 9.2 main -d /home/ubuntu/working/postgre_data/

sudo su postgres
pg_ctlcluster 9.2 main start
exit

vallettea

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

# basics
sudo apt-get -y install git
sudo apt-get -y install git-flow
sudo apt-get -y install curl
sudo apt-get -y install unzip

vallettea

sudo yum -y update
sudo ln -sf /usr/share/zoneinfo/Europe/Paris /etc/localtime
sudo yum install -y gcc make gcc-c++

sudo yum -y install readline-devel
sudo yum -y install zlib-devel

wget http://ftp.postgresql.org/pub/source/v9.2.2/postgresql-9.2.2.tar.bz2
bzip2 -d postgresql-9.2.2.tar.bz2
tar -xf postgresql-9.2.2.tar

vallettea

wget --no-cookies --header "Cookie: gpw_e24=http%3A%2F%2Fwww.oracle.com" "http://download.oracle.com/otn-pub/java/jdk/7u21-b11/jdk-7u21-linux-x64.rpm"

# Install Java
sudo rpm -i jdk-7u21-linux-x64.rpm 

# Check if the default java version is set to sun jdk
java -version

# If not then lets create one more alternative for Java for Sun JDK

vallettea

# 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

vallettea

select w.id, st_x(n.geom), st_y(n.geom), w.tags from ways as w INNER join nodes as n on n.id = w.nodes[1] where w.tags->'leisure'='swimming_pool';

\copy (select w.id, st_x(n.geom), st_y(n.geom), w.tags from ways as w INNER join nodes as n on n.id = w.nodes[1] where w.tags->'leisure'='swimming_pool') To 'pools.csv' With Delimiter ';' CSV;


\copy (select id, tags, st_astext(linestring), nodes from ways as w where w.tags->'railway'='rail') To 'rails.csv' With Delimiter ';' CSV;


\copy (select n.uid, st_x(n.coordinates), st_y(n.coordinates), date_part('hour',date_time), extract(DOW from date_time), n.nb_vehicles,n.severity,n.nb_casualties from accidents as n) To 'accidents.csv' With Delimiter ';' CSV;

vallettea

-- create an index for your tables, this will create a rtree

CREATE INDEX idx_ny_fires ON NYC_FireAccidents USING GIST(geom);
CREATE INDEX idx_ny_building ON mappluto_13v2 USING GIST(wkb_geometry);

-- check if one single association is fast

EXPLAIN ANALYZE SELECT f.unique_key, b.ogc_fid
FROM  NYC_fireaccidents f, mappluto_13v2 b 
WHERE ST_Distance_Sphere( ST_Transform(f.geom,4269), ST_Transform(b.wkb_geometry,4269)) < 50 

vallettea

  class GeomJsonSerializer extends JsonSerializer[T <: Geometry] {
    def serialize[T <: Geometry](geom: T, json: JsonGenerator, provider: SerializerProvider) {
      json.writeString(jtsToGeoJson(geom))
    }
  }
  
  
  [error] /Users/vallette/projects/safesignal/util-core/src/main/scala/net/snips/util/StringUtils.scala:63: ']' expected but '<:' found.
[error]   class GeomJsonSerializer extends JsonSerializer[T <: Geometry] {
[error]                                                     ^

vallettea

# Install VirtualBox https://www.virtualbox.org/wiki/Downloads
# create your home bin dir & add it to your PATH
mkdir -p ~/bin
curl -o ~/bin/boot2docker -k "https://raw.github.com/boot2docker/boot2docker/master/boot2docker"
export PATH=$HOME/bin:$PATH
mkdir -p ~/.boot2docker
# create the vm in ~/VirtualBox VMs and the disk in ~/.boot2docker
boot2docker init 
# load the VM in the background, call `boot2docker down` when done, password is "tcuser"
boot2docker up 

vallettea

# connect to the db
psql -d idf -U postgres

# get first few nodes having tags
select * from nodes where array_length(akeys(tags),1) >0 limit 5;


# get nodes having tag key "highway"
select * from nodes where tags ? 'highway' limit 10;