BESTMobile/android/db/create-new.sql

--create spatialite DB

--sudo -usudo -u postgres /usr/local/pgsql/bin/pg_dump --data-only --column-inserts mumbai_final | sed 's/false/0/g' | sed 's/true/1/g' | grep -v "^SET" >best-11-04-2012.sql
--gzip best-11-04-2012.sql
-- scp download db
--spatialite best-11-04-2012 <postgrres-best-11-04-2012.sql

--create spatialite DB

--sudo -usudo -u postgres /usr/local/pgsql/bin/pg_dump --data-only --column-inserts mumbai_final | sed 's/false/0/g' | sed 's/true/1/g' | grep -v "^SET" >best-11-04-2012.sql
--gzip best-11-04-2012.sql


delete from routes;
delete from stop_geo;
delete from stops_on_trip;
delete from trips_freq;
delete from schedule_rules;
delete from stop_names;
delete from trips;

stop_geo        stops_on_trip   trips_freq
schedule_rules  stop_names      trips

-- nohup postgres -D /usr/local/pgsql/data >>/var/log/postgres/server.log 2>&1 &
-- createdb gtmadrid
-- createlang plpgsql <yourdatabase>
-- /usr/local/pgsql$ psql -f  ./share/contrib/postgis-1.5/postgis.sql -d gtmadrid
-- cat ./share/contrib/postgis-1.5/postgis.sql | sudo -u postgres /usr/local/pgsql/bin/psql mumbai_final

drop table stop_names;
CREATE TABLE stop_names (
_stopID varchar(7) PRIMARY KEY not null,
_stopName varchar(44) not null
);

CREATE INDEX ix_stop_names_stopName ON stop_names ( _stopName ); 

drop table stop_geo;
CREATE TABLE stop_geo (
_stopID varchar(7) PRIMARY KEY not null,
_stopLoc GEOMETRY NOT NULL
);

CREATE INDEX ix_stop_geo_loc ON stop_geo GIST ( _stopLoc ); 

drop table stops_on_trip;
CREATE TABLE stops_on_trip (
_tripID varchar(30) not null,
_stopID varchar(7) not null,
_stopSeq integer NOT NULL,
_stopDept time with time zone NOT NULL,
_stopDeptOnNextDay boolean not NULL,
_stopArri time with time zone NOT NULL,
_stopArriOnNextDay boolean not NULL,
PRIMARY KEY(_tripID, _stopID, _stopSeq)
);

CREATE INDEX ix_stops_on_trip_tripID ON stops_on_trip (_tripID);
CREATE INDEX ix_stops_on_trip_stopID ON stops_on_trip (_stopID);
CREATE INDEX ix_stops_on_trip_stopSeq ON stops_on_trip (_stopSeq);

CREATE INDEX ix_stops_on_trip_stopDept ON stops_on_trip (_stopDept);
CREATE INDEX ix_stops_on_trip_stopArri ON stops_on_trip (_stopArri);
CREATE INDEX ix_stops_on_trip_stopDeptOnNextDay ON stops_on_trip (_stopDeptOnNextDay);
CREATE INDEX ix_stops_on_trip_stopArriOnNextDay ON stops_on_trip (_stopArriOnNextDay);
CREATE INDEX ix_stops_on_trip_stopDeptEx ON stops_on_trip (_stopDept,_stopDeptOnNextDay);
CREATE INDEX ix_stops_on_trip_stopArriEx ON stops_on_trip (_stopArri,_stopArriOnNextDay);

drop table trips;
CREATE TABLE trips (
_tripID varchar(30) NOT NULL,
_routeID varchar(4) not null,
_serviceID varchar(15) NOT NULL,
_tripHeadSign varchar(17) not null,	
PRIMARY KEY(_tripID)
);

CREATE INDEX ix_trips_routeID ON trips (_routeID);
CREATE INDEX ix_trips_serviceID ON trips (_serviceID);
CREATE INDEX ix_trips_tripHeadSign ON trips (_tripHeadSign);

drop table trips_freq;
CREATE TABLE trips_freq (
_tripID varchar(30) NOT NULL,
_start time with time zone NOT NULL,
_end time with time zone NOT NULL,
_end_next_day boolean not null,
_headways integer not null,
_freq integer default null
);

CREATE INDEX ix_trips_freq_tripID ON trips_freq (_tripID);

drop table routes;
CREATE TABLE routes (
_routeID varchar(4) not null,
_agencyID varchar(3) not null,
_routeName varchar(5) NOT NULL,
_routeLongName varchar(80) NOT NULL,	
_routeType varchar(6) not NULL,
PRIMARY KEY(_routeID)
);

CREATE INDEX ix_routes_agencyID ON routes (_agencyID);
CREATE INDEX ix_routes_routeName ON routes (_routeName);
CREATE INDEX ix_routes_routeType ON routes (_routeType);

drop table schedule_rules;
CREATE TABLE schedule_rules (
_serviceID varchar(15) NOT NULL,
_serviceDay varchar(3) not null,
_startPeriodDate DATE not null,
_endPeriodDate DATE not null,
PRIMARY KEY(_serviceID, _serviceDay)
);

CREATE INDEX ix_schedule_rules_serviceID ON schedule_rules (_serviceID);
CREATE INDEX ix_schedule_rules_serviceDay ON schedule_rules (_serviceDay);
CREATE INDEX ix_schedule_rules_startPeriodDate ON schedule_rules (_startPeriodDate);
CREATE INDEX ix_schedule_rules_endPeriodDate ON schedule_rules (_endPeriodDate);

-- //////////////////////////////////////