-- RXMSG table

CREATE TABLE rxmsg
(
  mid serial NOT NULL,
  vid INTEGER,
  reason INTEGER,
  rxdt DATE,
  txdt DATE,
  point geography,
  course INTEGER,
  speed INTEGER,
  inputs INTEGER,
  "timestamp" TIMESTAMP WITH TIME zone NOT NULL,
  CONSTRAINT myprimarykey_rxmsg PRIMARY KEY (mid),
  CONSTRAINT vehicles_rxmsg FOREIGN KEY (vid)
      REFERENCES vehicles (vid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE INDEX rxmsg_date_idx
  ON rxmsg
  USING btree
  (rxdt);
ALTER TABLE rxmsg CLUSTER ON rxmsg_date_idx;

CREATE INDEX rxmsg_point_date
  ON rxmsg
  USING gist
  (point, rxdt);

CREATE INDEX rxmsg_point_index
  ON rxmsg
  USING gist
  (point);

CREATE INDEX rxmsg_spatial_index
  ON rxmsg
  USING gist
  (point);

--RXMSG DATA

47675;2407;1048577;"2014-01-01";"2014-01-01";"0101000020E6100000083E062B4E334D402D0B26FE28BA3A40";0;0;66068;"2014-12-01 21:02:13.391+02"
88611;2348;524288;"2014-01-01";"2014-01-01";"0101000020E6100000FC53AA44D92F4D407845F0BF95C03A40";301;2;65812;"2014-12-01 21:10:07.299+02"
91953;2407;4;"2014-01-01";"2014-01-01";"0101000020E610000041D47D0052334D40C231CB9E04BA3A40";292;25;20;"2014-12-01 21:10:44.9+02"
11657;2404;4;"2014-01-01";"2014-01-01";"0101000020E6100000205ED72FD82F4D405680EF366FC03A40";112;20;20;"2014-12-01 20:55:20.798+02"
111241;2407;4;"2014-01-01";"2014-01-01";"0101000020E6100000E8C072840C344D407F4B00FE29B53A40";85;23;20;"2014-12-01 21:14:22.64+02"
7003;2402;4;"2014-01-01";"2014-01-01";"0101000020E6100000EAE8B81AD92F4D40DD0A613596C03A40";0;0;1556;"2014-12-01 20:54:27.598+02"
23932;2371;1090519040;"2014-01-01";"2014-01-01";"0101000020E6100000D314014EEF324D401C7E37DDB2BB3A40";314;14;20;"2014-12-01 20:57:40.019+02"
82322;2420;1048577;"2014-01-01";"2014-01-01";"0101000020E61000008BE07F2BD92F4D40B9347EE195C03A40";0;0;131092;"2014-12-01 21:08:52.385+02"
28915;2359;1048577;"2014-01-01";"2014-01-01";"0101000020E6100000EFE192E34E334D40C3F0113125BA3A40";0;1;393236;"2014-12-01 20:58:36.94+02"
102785;2407;4;"2014-01-01";"2014-01-01";"0101000020E610000005FA449E24334D40C9E53FA4DFBA3A40";315;28;20;"2014-12-01 21:12:47.96+02"
62399;2407;4;"2014-01-01";"2014-01-01";"0101000020E61000007D3ECA880B344D4085949F54FBB43A40";264;37;20;"2014-12-01 21:05:04.404+02"
21020;2397;4;"2014-01-01";"2014-01-01";"0101000020E6100000317898F6CD2F4D40B69E211CB3C03A40";121;16;20;"2014-12-01 20:57:06.558+02"
13605;2359;1090519040;"2014-01-01";"2014-01-01";"0101000020E6100000D7851F9C4F334D4050C422861DBA3A40";288;10;20;"2014-12-01 20:55:42.79+02"
44541;2359;4;"2014-01-01";"2014-01-01";"0101000020E610000003ED0E2906344D408E9257E718B43A40";85;40;20;"2014-12-01 21:01:37.822+02"
43924;2359;1090519040;"2014-01-01";"2014-01-01";"0101000020E610000048FDF50A0B344D404D327216F6B43A40";264;31;20;"2014-12-01 21:01:30.654+02"

-- VEHICLES table

CREATE TABLE vehicles
(
  vid INTEGER NOT NULL,
  cid INTEGER,
  vname text,
  reg_no text,
  name text,
  name_1st INTEGER,
  "timestamp" TIMESTAMP WITH TIME zone NOT NULL DEFAULT now(),
  CONSTRAINT vid PRIMARY KEY (vid)
)

CREATE INDEX vehicles_vid_idx
  ON vehicles
  USING btree
  (vid);

--vehicles data

2483;55;"329BDD";"329BDD";"858";1;"2014-11-07 05:32:43.191+02"
2484;;"TEST-TLT";"TEST-TLT";"TLT";0;"2014-11-07 05:32:43.191+02"
2485;;"TEST_MEL";"TEST_MEL";"MEL";0;"2014-11-07 05:32:43.191+02"
2368;55;"076THV";"076THV";"839";1;"2014-11-07 05:32:43.191+02"
2370;55;"075THV";"075THV";"836";1;"2014-11-07 05:32:43.191+02"
2371;55;"036THX";"036THX";"835";1;"2014-11-07 05:32:43.191+02"
2387;55;"436BDH";"436BDH";"823";1;"2014-11-07 05:32:43.191+02"
2389;55;"214BDH|808.";"214BDH";"808";1;"2014-11-07 05:32:43.191+02"
2390;55;"437BDH";"437BDH";"822";1;"2014-11-07 05:32:43.191+02"
2391;55;"245BDH";"245BDH";"812";1;"2014-11-07 05:32:43.191+02"
2392;55;"313BDD";"313BDD";"859";1;"2014-11-07 05:32:43.191+02"
2396;55;"031THX";"031THX";"837";1;"2014-11-07 05:32:43.191+02"
2397;55;"211BDH|805.";"211BDH";"805";1;"2014-11-07 05:32:43.191+02"
2398;55;"211BKR";"211BKR";"861";1;"2014-11-07 05:32:43.191+02"
2399;55;"210BDH|804.";"210BDH";"804";1;"2014-11-07 05:32:43.191+02"

--DRIVERS table

CREATE TABLE drivers
(
  id INTEGER NOT NULL,
  client_id INTEGER,
  ibutton_id BIGINT,
  name text,
  phone text,
  email text,
  date_activated DATE,
  date_deactivated DATE,
  vehicle_id INTEGER,
  "timestamp" TIMESTAMP WITH TIME zone NOT NULL DEFAULT now(),
  CONSTRAINT myprimarykey_drivers PRIMARY KEY (id)
)

CREATE INDEX drivers_date_activated_idx
  ON drivers
  USING btree
  (date_activated);

CREATE INDEX drivers_date_deactivated_idx
  ON drivers
  USING btree
  (date_deactivated);

CREATE INDEX drivers_vehicle_id_idx
  ON drivers
  USING btree
  (vehicle_id);

CREATE INDEX multi_date_index
  ON drivers
  USING gin
  (date_activated, date_deactivated);

--drivers data
1;55;241129493;"";"";"";"2013-12-04";"2013-12-04";2350;"2014-11-21 01:09:16.145+02"
2;1;241129495;"Urmas Ott";"";"";"2013-11-06";"2014-09-17";2391;"2014-11-21 01:09:16.461+02"


--STOPS table

CREATE TABLE stops
(
  id serial NOT NULL,
  utris_id text,
  utris_major INTEGER,
  utris_minor INTEGER,
  title text,
  lest_lat DOUBLE PRECISION,
  lest_lon DOUBLE PRECISION,
  point geography NOT NULL,
  is_checkpoint INTEGER NOT NULL DEFAULT 0,
  show_intercity INTEGER NOT NULL DEFAULT 0,
  date_created TIMESTAMP WITH TIME zone NOT NULL DEFAULT now(),
  date_updated TIMESTAMP WITH TIME zone NOT NULL DEFAULT now(),
  CONSTRAINT stops_point_pkey PRIMARY KEY (point),
  CONSTRAINT stops_utris_id_key UNIQUE (utris_id),
  CONSTRAINT stops_utris_major_utris_minor_key UNIQUE (utris_major, utris_minor)
)

CREATE INDEX stops_point_idx
  ON stops
  USING gist
  (point);
ALTER TABLE stops CLUSTER ON stops_point_idx;

--stops data

31;"7820034-1";7820034;1;"Favora";6470211;660265.3125;"0101000020E610000054E3E050E82B4D402F2EE1BFC4BC3A40";0;0;"2013-09-05 13:26:47+03";"2013-08-16 14:18:34+03"
226;"7820235-1";7820235;1;"Sepa turg";6470973;660454.5625;"0101000020E610000067E4580AC62C4D40893D5031BBBD3A40";0;0;"2013-09-05 13:26:47+03";"2013-08-16 14:18:34+03"
227;"7820236-1";7820236;1;"Sepa turg";6470927.5;660439.625;"0101000020E6100000699943D8B82C4D408F4A3268A8BD3A40";0;0;"2013-09-05 13:26:47+03";"2013-08-16 14:18:34+03"
238;"7820247-1";7820247;1;"Soodusmarket";6470173;659978;"0101000020E6100000F7A54697E02B4D40046783C381BB3A40";0;0;"2013-09-05 13:26:47+03";"2013-08-16 14:18:34+03"
290;"7820297-1";7820297;1;"Vangla";6470470;660483.6875;"0101000020E6100000E84F1ED4312C4D4099FA31C3C4BD3A40";0;0;"2013-09-05 13:26:47+03";"2013-08-16 14:18:34+03"
291;"7820298-1";7820298;1;"Vangla";6470466.5;660465.1875;"0101000020E6100000F083A705312C4D40F9F26FEAAFBD3A40";0;0;"2013-09-05 13:26:47+03";"2013-08-16 14:18:34+03"
313;"7820321-1";7820321;1;"Turu ring (Favora)";6470214;660236.5;"0101000020E61000002FFB338BE92B4D40C4799BABA4BC3A40";0;0;"2013-09-04 13:00:48+03";"2013-10-25 13:14:53+03"
319;"7820322-1";7820322;1;"Turu ring";6470189.5;660320;"0101000020E61000008106DD56E12B4D4071B160EA00BD3A40";0;0;"2013-10-25 13:15:56+03";"2013-10-25 13:15:56+03"
11;"7820014-1";7820014;1;"Annelinna keskus";6473334;661855.9375;"0101000020E6100000F99BB2266B2F4D4066C1B85A47C43A40";0;0;"2013-09-05 13:26:47+03";"2013-08-16 14:18:34+03"
12;"7820015-1";7820015;1;"Annelinna keskus";6473229.5;661908.875;"0101000020E6100000232864CA4B2F4D40AFF75BC37DC43A40";1;0;"2013-09-05 13:26:47+03";"2013-08-16 14:18:34+03"
13;"7820016-1";7820016;1;"Annemõisa";6474250;662361.9375;"0101000020E61000006647994372304D402FA2B8FFA7C63A40";0;0;"2013-09-05 13:26:47+03";"2014-04-10 10:34:10+03"
14;"7820017-1";7820017;1;"Annemõisa";6474059.5;662481.4375;"0101000020E6100000B27B9CCF38304D40FAE969E924C73A40";0;0;"2013-09-05 13:26:47+03";"2014-04-10 10:34:10+03"
45;"7820048-1";7820048;1;"Ihaste";6470522.5;662714.1875;"0101000020E6100000788AA643262C4D409E54484885C73A40";0;0;"2013-09-05 13:26:47+03";"2013-08-16 14:18:34+03"
46;"7820049-1";7820049;1;"Ihaste";6470517;662726.375;"0101000020E61000002369BD7F242C4D40CEE0ABA792C73A40";0;0;"2013-09-05 13:26:47+03";"2013-08-16 14:18:34+03"
47;"7820050-1";7820050;1;"Pallase pst.";6472076.5;662923.625;"0101000020E61000009542FC82EC2D4D40CA2BDDA0B7C83A40";0;0;"2013-09-05 13:26:47+03";"2013-08-16 14:18:34+03"