diff --git a/README.md b/README.md index aa12b33..ef98ebf 100644 --- a/README.md +++ b/README.md @@ -6,9 +6,11 @@ This project allows you to track cell phones periodically. For instance every minute or every five minutes. You can watch the cell phone being tracked in real time using Google Maps and you can store and reload routes easily. The map display page is built using bootstrap which makes the page responsive and also uses bootswatch which gives you the choice of 17 different themes. There are 4 clients, iOS, Android, Windows Phone and Java ME. You have the choice of two server stacks. Either using: + 1. ASP.NET with SQL Server or + 2. using PHP with your choice of: * MySQL * PostgreSQL diff --git a/servers/php/postgresql/gpstracker-09-14-14.sql b/servers/php/postgresql/gpstracker-09-14-14.sql new file mode 100644 index 0000000..e4322f6 --- /dev/null +++ b/servers/php/postgresql/gpstracker-09-14-14.sql @@ -0,0 +1,100 @@ +-------------------------------------------------------------- +-- SQL for creating GpsTracker object in a PostgreSQL database +-------------------------------------------------------------- +-- CREATE USER gpstracker_user WITH PASSWORD 'gpstracker'; + +DROP VIEW IF EXISTS v_GetAllRoutesForMap; +DROP VIEW IF EXISTS v_GetRouteForMap; +DROP VIEW IF EXISTS v_GetRoutes; +DROP INDEX IF EXISTS sessionIDIndex; +DROP INDEX IF EXISTS phoneNumberIndex; +DROP INDEX IF EXISTS userNameIndex; + +DROP TABLE IF EXISTS gpslocations; + +CREATE TABLE gpslocations ( + GPSLocationID serial, + lastUpdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + latitude double precision NOT NULL DEFAULT '0.0', + longitude double precision NOT NULL DEFAULT '0.0', + phoneNumber varchar(50) NOT NULL DEFAULT '', + userName varchar(50) NOT NULL DEFAULT '', + sessionID varchar(50) NOT NULL DEFAULT '', + speed integer NOT NULL DEFAULT '0', + direction integer NOT NULL DEFAULT '0', + distance double precision NOT NULL DEFAULT '0.0', + gpsTime timestamp NOT NULL DEFAULT '0001-01-01 00:00:00', + locationMethod varchar(50) NOT NULL DEFAULT '', + accuracy integer NOT NULL DEFAULT '0', + extraInfo varchar(255) NOT NULL DEFAULT '', + eventType varchar(50) NOT NULL DEFAULT '', + PRIMARY KEY (GPSLocationID) +); + +CREATE INDEX sessionIDIndex ON gpslocations (sessionID); +CREATE INDEX phoneNumberIndex ON gpslocations (phoneNumber); +CREATE INDEX userNameIndex ON gpslocations (userName); + +----------------------------------------------- +-- Sample data for table gpslocations +----------------------------------------------- + +INSERT INTO gpslocations VALUES +(1,'2007-01-03 19:37:00',47.627327,-122.325691,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:37:00','na',137,'na','gpsTracker'), +(2,'2007-01-03 19:38:00',47.607258,-122.330077,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:38:00','na',137,'na','gpsTracker'), +(3,'2007-01-03 19:39:00',47.601703,-122.324670,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:39:00','na',137,'na','gpsTracker'), +(4,'0001-01-01 00:00:00',47.593757,-122.195074,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:40:00','na',137,'na','gpsTracker'), +(5,'2007-01-03 19:41:00',47.601397,-122.190353,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:41:00','na',137,'na','gpsTracker'), +(6,'2007-01-03 19:42:00',47.610020,-122.190697,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:42:00','na',137,'na','gpsTracker'), +(7,'2007-01-03 19:43:00',47.636631,-122.214558,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:43:00','na',137,'na','gpsTracker'), +(8,'2007-01-03 19:44:00',47.637961,-122.201769,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:44:00','na',137,'na','gpsTracker'), +(9,'2007-01-03 19:45:00',47.642935,-122.209579,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:45:00','na',137,'na','gpsTracker'); + + +----------------------------------------------- +-- v_GetAllRoutesForMap -- +----------------------------------------------- +CREATE OR REPLACE VIEW v_getallroutesformap AS + SELECT gpslocations.sessionid, + gpslocations.gpstime, + concat('{ "latitude":"', gpslocations.latitude::character varying, '", "longitude":"', gpslocations.longitude::character varying, '", "speed":"', gpslocations.speed::character varying, '", "direction":"', gpslocations.direction::character varying, '", "distance":"', gpslocations.distance::character varying, '", "locationMethod":"', gpslocations.locationmethod, '", "gpsTime":"', to_char(gpslocations.gpstime, 'Mon dd YYYY HH12:MIAM'::text), '", "userName":"', gpslocations.username, '", "phoneNumber":"', gpslocations.phonenumber, '", "sessionID":"', gpslocations.sessionid, '", "accuracy":"', gpslocations.accuracy::character varying, '", "extraInfo":"', gpslocations.extrainfo, '" }') AS json + FROM ( SELECT max(gpslocations_1.gpslocationid) AS id + FROM gpslocations gpslocations_1 + WHERE gpslocations_1.sessionid::text <> '0'::text AND char_length(gpslocations_1.sessionid::text) <> 0 AND gpslocations_1.gpstime <> '0001-01-01 00:00:00'::timestamp without time zone + GROUP BY gpslocations_1.sessionid) maxid + JOIN gpslocations ON gpslocations.gpslocationid = maxid.id + ORDER BY gpslocations.gpstime; + + +----------------------------------------------- +-- v_GetRouteForMap -- +----------------------------------------------- +CREATE Or REPLACE VIEW v_GetRouteForMap AS +SELECT + sessionid, + lastupdate, + CONCAT('{ "latitude":"', CAST(latitude AS VARCHAR),'", "longitude":"', CAST(longitude AS VARCHAR), '", "speed":"', CAST(speed AS VARCHAR), '", "direction":"', CAST(direction AS VARCHAR), '", "distance":"', CAST(distance AS VARCHAR), '", "locationMethod":"', locationMethod, '", "gpsTime":"', to_char(gpsTime, 'Mon dd YYYY HH12:MIAM'), '", "userName":"', userName, '", "phoneNumber":"', phoneNumber, '", "sessionID":"', CAST(sessionID AS VARCHAR), '", "accuracy":"', CAST(accuracy AS VARCHAR), '", "extraInfo":"', extraInfo, '" }') json + FROM gpslocations + WHERE gpstime != '0001-01-01 00:00:00' + ORDER BY lastupdate; + ; + +----------------------------------------------- +-- v_GetRoutes -- +----------------------------------------------- +CREATE OR REPLACE VIEW v_GetRoutes AS +select + CONCAT('{ "sessionID": "', CAST(sessionID AS VARCHAR), '", "userName": "', userName, '", "times": "(', to_char(startTime, 'Mon dd YYYY HH12:MIAM'), ' - ', to_char(endtime, 'Mon dd YYYY HH12:MIAM'), ')" }') json +from ( + select + distinct sessionid, userName, + MIN(gpsTime) startTime, + MAX(gpsTime) endtime + FROM gpslocations + group by sessionid,username + ORDER BY startTime DESC +) +AS routes +; + + diff --git a/servers/php/sqlite/gpstracker-09-14-14.sql b/servers/php/sqlite/gpstracker-09-14-14.sql new file mode 100644 index 0000000..866903f --- /dev/null +++ b/servers/php/sqlite/gpstracker-09-14-14.sql @@ -0,0 +1,124 @@ +-------------------------------------------------------------- +-- SQL for creating GpsTracker objext in a PostgreSQL database +-------------------------------------------------------------- +DROP VIEW IF EXISTS v_GetAllRoutesForMap; +DROP VIEW IF EXISTS v_GetRouteForMap; +DROP VIEW IF EXISTS v_GetRoutes; +DROP INDEX IF EXISTS sessionIDIndex; +DROP INDEX IF EXISTS phoneNumberIndex; +DROP INDEX IF EXISTS userNameIndex; + +DROP TABLE IF EXISTS gpslocations; + +CREATE TABLE gpslocations ( + GPSLocationID integer PRIMARY KEY autoincrement unique not null, + lastUpdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + latitude double precision NOT NULL DEFAULT '0.0', + longitude double precision NOT NULL DEFAULT '0.0', + phoneNumber varchar(50) NOT NULL DEFAULT '', + userName varchar(50) NOT NULL DEFAULT '', + sessionID varchar(50) NOT NULL DEFAULT '', + speed integer NOT NULL DEFAULT '0', + direction integer NOT NULL DEFAULT '0', + distance double precision NOT NULL DEFAULT '0.0', + gpsTime timestamp NOT NULL DEFAULT '0001-01-01 00:00:00', + locationMethod varchar(50) NOT NULL DEFAULT '', + accuracy integer NOT NULL DEFAULT '0', + extraInfo varchar(255) NOT NULL DEFAULT '', + eventType varchar(50) NOT NULL DEFAULT '' +); +CREATE INDEX sessionIDIndex ON gpslocations (sessionID); +CREATE INDEX phoneNumberIndex ON gpslocations (phoneNumber); +CREATE INDEX userNameIndex ON gpslocations (userName); + +----------------------------------------------- +-- Sample data for table gpslocations +----------------------------------------------- + +INSERT INTO gpslocations VALUES +(1,'2007-01-03 19:37:00',47.627327,-122.325691,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:37:00','na',137,'na','gpsTracker'), +(2,'2007-01-03 19:38:00',47.607258,-122.330077,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:38:00','na',137,'na','gpsTracker'), +(3,'2007-01-03 19:39:00',47.601703,-122.324670,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:39:00','na',137,'na','gpsTracker'), +(4,'0001-01-01 00:00:00',47.593757,-122.195074,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:40:00','na',137,'na','gpsTracker'), +(5,'2007-01-03 19:41:00',47.601397,-122.190353,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:41:00','na',137,'na','gpsTracker'), +(6,'2007-01-03 19:42:00',47.610020,-122.190697,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:42:00','na',137,'na','gpsTracker'), +(7,'2007-01-03 19:43:00',47.636631,-122.214558,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:43:00','na',137,'na','gpsTracker'), +(8,'2007-01-03 19:44:00',47.637961,-122.201769,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:44:00','na',137,'na','gpsTracker'), +(9,'2007-01-03 19:45:00',47.642935,-122.209579,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:45:00','na',137,'na','gpsTracker'); + + +----------------------------------------------- +-- v_GetAllRoutesForMap -- +----------------------------------------------- +CREATE VIEW v_GetAllRoutesForMap AS + SELECT + gpslocations.sessionid, + gpslocations.gpstime, + '{ "latitude":"' || CAST(latitude AS VARCHAR) || + '", "longitude":"' || CAST(longitude AS VARCHAR) || + '", "speed":"' || CAST(speed AS VARCHAR) || + '", "direction":"' || CAST(direction AS VARCHAR) || + '", "distance":"' || CAST(distance AS VARCHAR) || + '", "locationMethod":"' || locationMethod || + '", "gpsTime":"' || gpsTime || + '", "userName":"' || userName || + '", "phoneNumber":"' || phoneNumber || + '", "sessionID":"' || CAST(sessionID AS VARCHAR) || + '", "accuracy":"' || CAST(accuracy AS VARCHAR) || + '", "extraInfo":"' || extraInfo || '" }' json + FROM ( SELECT max(gpslocations_1.gpslocationid) AS id + FROM gpslocations gpslocations_1 + WHERE gpslocations_1.sessionid != '0' AND + length(gpslocations_1.sessionid) != 0 AND + gpslocations_1.gpstime != '0001-01-01 00:00:00' + GROUP BY gpslocations_1.sessionid) maxid + JOIN gpslocations ON gpslocations.gpslocationid = maxid.id + ORDER BY gpslocations.gpstime; + + + +----------------------------------------------- +-- v_GetRouteForMap -- +----------------------------------------------- +CREATE VIEW v_GetRouteForMap AS +SELECT + sessionid, + lastupdate, + '{ "latitude":"' || CAST(latitude AS VARCHAR) || + '", "longitude":"' || CAST(longitude AS VARCHAR) || + '", "speed":"' || CAST(speed AS VARCHAR) || + '", "direction":"' || CAST(direction AS VARCHAR) || + '", "distance":"' || CAST(distance AS VARCHAR) || + '", "locationMethod":"' || locationMethod || + '", "gpsTime":"' || gpsTime || + '", "userName":"' || userName || + '", "phoneNumber":"' || phoneNumber || + '", "sessionID":"' || CAST(sessionID AS VARCHAR) || + '", "accuracy":"' || CAST(accuracy AS VARCHAR) || + '", "extraInfo":"' || extraInfo || '" }' json + FROM gpslocations + WHERE gpstime != '0001-01-01 00:00:00' + ORDER BY lastupdate; + ; + +----------------------------------------------- +-- v_GetRoutes -- +----------------------------------------------- +CREATE VIEW v_GetRoutes AS +select + '{ "sessionID": "'|| CAST(sessionID AS VARCHAR) || + '", "userName": "' || userName || + '", "times": "(' || startTime || + ' - ' || endtime || ')" }' json +from ( + select + distinct sessionid, userName, + MIN(gpsTime) startTime, + MAX(gpsTime) endtime + FROM gpslocations + group by sessionid,username + ORDER BY startTime DESC +) +AS routes +; + diff --git a/servers/php/sqlite/gpstracker.sqlite b/servers/php/sqlite/gpstracker.sqlite new file mode 100644 index 0000000..2b15af5 --- /dev/null +++ b/servers/php/sqlite/gpstracker.sqlite Binary files differ