ROMA/Install

Install guide is still in progress-- Mostly complete, but needs more information. See bottom for details.

  apt-get install postgresql-8.3 postgresql-8.3-postgis sun-java6-jdk libapache2-mod-fcgid libcgi-fast-perl \
   libdbd-pg-perl libdbi-perl libfcgi-perl libtimedate-perl perl htop screen

 sysctl -w kernel.shmmax=268435456
 nano /etc/sysctl.conf
 <insert the same>

  nano /etc/postgresql/8.3/main/postgresql.conf
   shared_buffers = 128MB
   maintenance_work_mem = 256MB

 su postgres
 createuser planet
 createdb -E UTF8 -O planet planet
 createlang plpgsql planet
  
  psql
   ALTER USER planet WITH PASSWORD 'planet';
   \q

 nano /etc/postgresql/8.3/main/pg_hba.conf
  #This requires a password for the planet user.
  # After local   all         postgres                          ident sameuser
  #add
  local	planet	planet	password

test the login

  psql -U planet
  #enter planet as the password, and will let you in
   \q
   #Exited out..

Now we are gonna have some FUN! Let's make the database tables. But first, we need to get the table prepared

  psql -U planet -d planet -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql

And let's go into postgres, change the ownership

  psql -U planet
    ALTER TABLE geometry_columns OWNER TO planet; 
    ALTER TABLE spatial_ref_sys OWNER TO planet;

Let's confirm it worked. type \dt

 planet=# \dt
             List of relations
 Schema |       Name       | Type  | Owner
 --------+------------------+-------+--------
  public | geometry_columns | table | planet
  public | spatial_ref_sys  | table | planet
 (2 rows)

If it looks like the above, you are good to start creating the tables! Copy and paste everything in from the line below

 -- Database creation script for the simple PostgreSQL schema.
 
 -- Drop all tables if they exist.
 DROP TABLE IF EXISTS nodes;
 DROP TABLE IF EXISTS node_tags;
 DROP TABLE IF EXISTS ways;
 DROP TABLE IF EXISTS way_nodes;
 DROP TABLE IF EXISTS way_tags;
 DROP TABLE IF EXISTS relations;
 DROP TABLE IF EXISTS relation_members;
 DROP TABLE IF EXISTS relation_tags;
 DROP TABLE IF EXISTS schema_info;
 
 
 -- Create a table which will contain a single row defining the current schema version.
 CREATE TABLE schema_info (
     version integer NOT NULL
 );
 
 
 -- Create a table for nodes.
 CREATE TABLE nodes (
     id bigint NOT NULL,
     user_name text NOT NULL,
     tstamp timestamp without time zone NOT NULL
 );
 -- Add a postgis point column holding the location of the node.
 SELECT AddGeometryColumn('nodes', 'geom', 4326, 'POINT', 2);
 
 -- Create a table for node tags.
 CREATE TABLE node_tags (
     node_id bigint NOT NULL,
     k text NOT NULL,
     v text NOT NULL
 );
 
 
 -- Create a table for ways.
 CREATE TABLE ways (
     id bigint NOT NULL,
     user_name text NOT NULL,
     tstamp timestamp without time zone NOT NULL
 );
 
 -- Create a table for representing way to node relationships.
 CREATE TABLE way_nodes (
     way_id bigint NOT NULL,
     node_id bigint NOT NULL,
     sequence_id smallint NOT NULL
);
 
 
 -- Create a table for way tags.
 CREATE TABLE way_tags (
     way_id bigint NOT NULL,
     k text NOT NULL,
     v text
 );
   
 
 -- Create a table for relations.
 CREATE TABLE relations (
     id bigint NOT NULL,
     user_name text NOT NULL,
     tstamp timestamp without time zone NOT NULL
 );
 
 -- Create a table for representing relation member relationships.
 CREATE TABLE relation_members (
     relation_id bigint NOT NULL,
     member_id bigint NOT NULL,
     member_role text NOT NULL,
     member_type smallint NOT NULL
 );
 
 
 -- Create a table for relation tags.
 CREATE TABLE relation_tags (
     relation_id bigint NOT NULL,
     k text NOT NULL,
     v text NOT NULL
 );  
 -- Configure the schema version.
 INSERT INTO schema_info (version) VALUES (1);

 -- Add primary keys to tables. 
 ALTER TABLE ONLY schema_info ADD CONSTRAINT pk_schema_info PRIMARY KEY (version);
 ALTER TABLE ONLY nodes ADD CONSTRAINT pk_nodes PRIMARY KEY (id);
 ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);
 ALTER TABLE ONLY way_nodes ADD CONSTRAINT pk_way_nodes PRIMARY KEY (way_id, sequence_id);
 ALTER TABLE ONLY relations ADD CONSTRAINT pk_relations PRIMARY KEY (id);
 -- Add indexes to tables.
 CREATE INDEX idx_node_tags_node_id ON node_tags USING btree (node_id);
 CREATE INDEX idx_nodes_geom ON nodes USING gist (geom);
 CREATE INDEX idx_way_tags_way_id ON way_tags USING btree (way_id);
 CREATE INDEX idx_way_nodes_node_id ON way_nodes USING btree (node_id);
 CREATE INDEX idx_relation_tags_relation_id ON relation_tags USING btree (relation_id);
 -- These are to make the database MUCH faster --
 CREATE INDEX idx_ways_big on way_nodes using btree (way_id);
 CREATE INDEX idx_nodes_big on way_nodes using btree (node_id);

And let's import the database!

 screen
  osmosis --read-xml file=planet.tar.gz --write-pgsql host="localhost" user="planet" database="planet" password="planet"

CTRL A+D to detach the screen htop to watch pretty graphs and install munin if you want to see how hard your system gets thrashed

Todo for finishing the wiki:

1. Get DB synced
2. Post edited FCGi script
3. Setup ram-disk for temp table

  mkdir /mnt/ramfs
  mkdir /mnt/ramfs/pgdata
  chown postgres:postgres /mnt/ramfs/pgdata
  chmod 777 /mnt/ramfs/pgdata

Configure Postgres to use Ram for the temp tables, call it "fastspace"

  su postgres
   psql
     CREATE TABLESPACE fastspace LOCATION '/mnt/ramfs/pgdata';
     GRANT CREATE ON TABLESPACE fastspace TO planet;

1. Setup Apache
2. Setup osmosis automatic updates