ThisΒ assignmentΒ willΒ lookΒ atΒ anΒ openΒ datasetΒ ofΒ trajectories,Β andΒ yourΒ taskΒ isΒ toΒ createΒ Β tables,Β cleanΒ andΒ insertΒ data,Β andΒ toΒ makeΒ queriesΒ toΒ answerΒ someΒ questions.Β YourΒ Β PythonΒ programΒ willΒ handleΒ thisΒ functionality.Β TheΒ programΒ isΒ inspiredΒ byΒ theΒ socialΒ Β mediaΒ workoutΒ application Strava , Β Β whereΒ usersΒ canΒ trackΒ activitiesΒ likeΒ running,Β Β walking,Β bikingΒ etcΒ andΒ postΒ themΒ onlineΒ withΒ statsΒ aboutΒ theirΒ workout.
AlongΒ withΒ thisΒ assignmentΒ sheet,Β youΒ haveΒ beenΒ givenΒ severalΒ files:
- Dataset βΒ ModifiedΒ datasetΒ basedΒ onΒ GeolifeΒ GPSΒ TrajectoryΒ datasetΒ (pleaseΒ useΒ Β thisΒ dataset,Β insteadΒ ofΒ theΒ oneΒ fromΒ theΒ Β TheΒ datasetΒ fromΒ theΒ websiteΒ Β containsΒ someΒ filesΒ thatΒ mayΒ giveΒ youΒ anΒ error).
- py β Β Β aΒ PythonΒ classΒ thatΒ connectsΒ toΒ MySQLΒ onΒ theΒ virtualΒ Β machine.
- py βΒ aΒ PythonΒ classΒ withΒ examplesΒ onΒ howΒ toΒ createΒ tables,Β insert,Β Β fetchΒ andΒ deleteΒ dataΒ inΒ MySQL.
- txt β Β Β aΒ fileΒ containingΒ someΒ pipΒ packagesΒ thatΒ shouldΒ beΒ usedΒ Β inΒ thisΒ assignment.
- txt β Β Β aΒ fileΒ containingΒ theΒ IDΒ ofΒ allΒ usersΒ whoΒ haveΒ labeledΒ theirΒ Β data.Β ThisΒ isΒ foundΒ inΒ theΒ Dataset.zipΒ file.
- User-Guide-1.3.pdf β Β Β anΒ officialΒ userΒ guideΒ toΒ theΒ GeolifeΒ Β ThisΒ isΒ Β foundΒ inΒ theΒ Dataset.zipΒ file.
Β
ItΒ is strongly Β Β Β recommendedΒ thatΒ youΒ readΒ throughΒ andΒ understandΒ theΒ wholeΒ Β assignmentΒ sheetΒ beforeΒ youΒ startΒ solvingΒ theΒ tasks.Β ThereΒ areΒ alsoΒ someΒ tipsΒ atΒ theΒ Β bottomΒ ofΒ thisΒ assignment,Β whichΒ couldΒ beΒ veryΒ handy!Β Β
Β
1
Β
DatasetΒ βΒ GeolifeΒ GPSΒ TrajectoryΒ dataset
GeolifeΒ GPSΒ TrajectoryΒ dataset Β isΒ anΒ openΒ sourceΒ datasetΒ collectedΒ byΒ MicrosoftΒ fromΒ Β 2007-2011.Β ThisΒ datasetΒ recordedΒ aΒ broadΒ rangeΒ ofΒ usersβΒ outdoorΒ movements,Β Β includingΒ notΒ onlyΒ lifeΒ routinesΒ likeΒ goingΒ homeΒ andΒ goingΒ toΒ workΒ butΒ alsoΒ someΒ Β entertainmentsΒ andΒ sportsΒ activities,Β suchΒ asΒ shopping,Β sightseeing,Β dining,Β hiking,Β andΒ Β cycling.Β TheΒ userΒ dataΒ isΒ mainlyΒ fromΒ Beijing,Β China,Β butΒ alsoΒ fromΒ theΒ USΒ andΒ Europe.
TheΒ datasetΒ isΒ providedΒ inΒ the tdt4225-assignment2.zip Β Β Β Β Β Β Β Β Β Β Β and Β Β hasΒ someΒ Β differencesΒ fromΒ theΒ oneΒ online,Β forΒ theΒ purposeΒ ofΒ thisΒ assignment.Β ItΒ containsΒ 182Β Β usersΒ thatΒ haveΒ trackedΒ 18Β 669Β activitiesΒ inΒ total.Β EachΒ activityΒ containsΒ aΒ numberΒ ofΒ Β GPS-points,Β whichΒ weΒ callΒ TrackPoints.Β InΒ totalΒ thereΒ areΒ overΒ 24Β millionΒ trackpointsΒ inΒ Β thisΒ dataset.
Β
InΒ the tdt4225-assignment2.zip Β Β Β Β Β you Β Β willΒ findΒ theΒ officialΒ userΒ guideΒ forΒ theΒ Β dataset.Β BeΒ sureΒ toΒ readΒ throughΒ thisΒ beforeΒ youΒ startΒ theΒ task.Β HereΒ isΒ also Β aΒ shortΒ Β summaryΒ ofΒ theΒ dataset, understanding Β Β Β Β Β Β Β Β Β Β Β thisΒ isΒ crucialΒ forΒ completingΒ theΒ assignment:
TheΒ folderΒ namedΒ DataΒ containsΒ allΒ theΒ 182Β users,Β labeledΒ fromΒ β000βΒ toΒ β181β.Β EachΒ Β userΒ hasΒ aΒ Trajectory-folderΒ whereΒ eachΒ activityΒ isΒ storedΒ asΒ aΒ .pltΒ file.Β EachΒ .pltΒ fileΒ Β containsΒ severalΒ trackpoints.
Β
LineΒ 1β¦6Β inΒ theΒ .pltΒ filesΒ areΒ uselessΒ inΒ thisΒ dataset,Β andΒ canΒ beΒ ignored.Β TrackpointsΒ areΒ Β describedΒ inΒ followingΒ lines,Β oneΒ forΒ eachΒ line:
Β
FieldΒ 1:Β LatitudeΒ inΒ decimalΒ degrees.
FieldΒ 2:Β LongitudeΒ inΒ decimalΒ degrees.
FieldΒ 3:Β AllΒ setΒ toΒ 0Β forΒ thisΒ dataset,Β i.e.Β youΒ donβtΒ needΒ thisΒ field.
FieldΒ 4:Β AltitudeΒ inΒ feetΒ (-777Β ifΒ notΒ valid).
FieldΒ 5:Β DateΒ βΒ numberΒ ofΒ daysΒ (withΒ fractionalΒ part)Β thatΒ haveΒ passedΒ since
12/30/1899.
FieldΒ 6:Β DateΒ asΒ aΒ string.
FieldΒ 7:Β TimeΒ asΒ aΒ string.
NoteΒ thatΒ fieldΒ 5Β andΒ fieldΒ 6&7Β representΒ theΒ sameΒ date/timeΒ inΒ thisΒ dataset.Β YouΒ mayΒ Β useΒ eitherΒ ofΒ them.
Β
Example:
39.906631,Β 116.385564,Β 0,Β 492,Β 40097.5864583333,Β 2009-10-11,Β 14:04:30
39.906554,Β 116.385625,Β 0,Β 492,Β 40097.5865162037,Β 2009-10-11,Β 14:04:35
2
SomeΒ usersΒ haveΒ alsoΒ labeledΒ theirΒ dataΒ withΒ transportationΒ modes.Β PossibleΒ Β transportationΒ modesΒ are: walk, Β Β Β Β Β Β Β Β bike,Β bus,Β taxi,Β Β car,Β subway,Β train,Β airplane,Β boat,Β runΒ andΒ Β motorcycle .Β WeΒ haveΒ providedΒ youΒ withΒ theΒ idsΒ ofΒ theΒ usersΒ thatΒ haveΒ labeledΒ theirΒ dataΒ Β in labeled_ids.txt Β Β Β Β ,Β whichΒ willΒ beΒ usedΒ forΒ theΒ tasks.Β TheΒ labelsΒ containΒ startΒ timeΒ Β andΒ endΒ timeΒ (bothΒ dateΒ andΒ time)Β alongΒ withΒ theΒ transportationΒ modeΒ forΒ eachΒ activity.
| Β
Example: |
Β | Β |
| StartΒ TimeΒ Β | EndΒ TimeΒ Β | TransportationΒ ModeΒ Β |
| 2008/04/02Β 11:24:21 | 2008/04/02Β 11:50:45 | bus |
| 2008/04/03Β 01:07:03 | 2008/04/03Β 11:31:55 | train |
Β
Tables
Β
| UserΒ Β
idΒ βΒ stringΒ Β has_labelsΒ βΒ boolean Β Β |
ActivityΒ Β
idΒ βΒ int user_idΒ βΒ stringΒ (ForeignΒ Key)Β Β transportation_modeΒ βΒ stringΒ Β Β Β start_date_timeΒ βΒ datetimeΒ Β end_date_timeΒ βΒ datetime |
TrackPointΒ Β Β Β
idΒ Β βΒ int activity_idΒ βΒ intΒ (ForeignΒ Key)Β Β latΒ βΒ doubleΒ Β lonΒ βΒ doubleΒ Β altitudeΒ βΒ intΒ Β date_daysΒ βΒ doubleΒ Β date_timeΒ βΒ datetime Β Β |
Β
DatetimeΒ shouldΒ beΒ consistentΒ throughoutΒ yourΒ tables,Β e.g.Β inΒ theΒ format
YYYY-MM-DDΒ HH:MM:SS.
Β
Β
Β
SetupΒ database
TheseΒ stepsΒ willΒ guideΒ youΒ throughΒ howΒ toΒ connectΒ toΒ theΒ virtualΒ machineΒ fromΒ yourΒ Β computerΒ andΒ createΒ aΒ MySQL-databaseΒ userΒ withΒ privileges.
- First, youΒ needΒ toΒ useΒ NTNUβsΒ VPNΒ toΒ accessΒ theΒ virtualΒ Β LogΒ ontoΒ Β NTNU-VPNΒ withΒ yourΒ FeideΒ userΒ (checkΒ out this Β Β Β Β Β Β Β linkΒ ifΒ youΒ haveΒ notΒ doneΒ thisΒ Β before).
Β
- Open yourΒ terminalΒ andΒ enterΒ theΒ followingΒ command:
sshΒ [email protected]Β where
βyour_usernameβΒ isΒ theΒ Feide-usernameΒ and βxxβ Β Β Β Β Β Β Β Β Β Β Β isΒ yourΒ groupΒ numberΒ forΒ thisΒ Β projectΒ (01,Β 02,Β 03β¦Β etc).
(IfΒ thisΒ messageΒ popsΒ up,Β enter yes Β Β Β Β Β Β Β Β Β Β :
TheΒ authenticityΒ ofΒ hostΒ βtdt4225-xx.idi.ntnu.noΒ (xxx.xxx.xxx.xxx)βΒ canβtΒ beΒ established.
ECDSAΒ keyΒ fingerprintΒ isΒ β¦
AreΒ youΒ sureΒ youΒ wantΒ toΒ continueΒ connectingΒ (yes/no)?Β yes)
YouΒ willΒ thenΒ beΒ askedΒ toΒ enterΒ yourΒ password,Β useΒ theΒ Feide-passwordΒ here.
IfΒ theΒ passwordΒ isΒ correct,Β youΒ haveΒ nowΒ successfullyΒ loggedΒ in!
- Now weΒ wantΒ toΒ createΒ aΒ MySQL-userΒ thatΒ theΒ groupΒ willΒ useΒ duringΒ thisΒ Β Β First,Β enter sudo Β Β mysql Β Β inΒ yourΒ terminalΒ windowsΒ (whichΒ isΒ nowΒ Β insideΒ theΒ virtualΒ machine)Β andΒ typeΒ inΒ yourΒ FeideΒ password.Β IfΒ successful,Β youΒ Β areΒ nowΒ onΒ theΒ MySQLΒ serverΒ andΒ canΒ writeΒ MySQL-queriesΒ directlyΒ inΒ thisΒ Β window: mysql> Β Β βsomeΒ queryβ.
Β
- Run theΒ followingΒ commandsΒ inΒ MySQLΒ toΒ createΒ aΒ newΒ userΒ andΒ giveΒ theΒ userΒ Β admin
mysql>Β CREATEΒ USERΒ βYOUR_USERNAME_HEREβ@β%βΒ IDENTIFIEDΒ BY
βYOUR_PASSWORD_IN_PLAIN_TEXT_HEREβ;
mysql>Β GRANTΒ ALLΒ PRIVILEGESΒ ONΒ *.*Β TOΒ βYOUR_USERNAME_HEREβ@β%βWITHΒ GRANTΒ OPTION;
Β
Yes,Β writeΒ theΒ usernameΒ usingΒ quotasΒ ββ.Β HereΒ isΒ anΒ exampleΒ forΒ Β username=testuserΒ andΒ password=test123:
CREATEΒ USERΒ βtestuserβ@β%βΒ IDENTIFIEDΒ BYΒ βtest123β;
GRANTΒ ALLΒ PRIVILEGESΒ ONΒ *.*Β TOΒ βtestuserβ@β%βΒ WITHΒ GRANT
OPTION;
Β
NowΒ yourΒ newΒ userΒ hasΒ beenΒ grantedΒ adminΒ rights.Β TheΒ %Β signΒ signifiesΒ aΒ Β wildcard,Β meaningΒ thatΒ yourΒ newΒ userΒ willΒ haveΒ accessΒ toΒ allΒ schemasΒ inΒ MySQL.Β Β YouΒ willΒ wantΒ toΒ flushΒ theΒ privilegesΒ soΒ thatΒ MySQLΒ willΒ loadΒ yourΒ userΒ withΒ its
newΒ rights.Β Type mysql> Β Β FLUSHΒ PRIVILEGES;
Β
ToΒ checkΒ ifΒ theΒ userΒ isΒ created,Β type mysql> Β Β Β Β Β SELECTΒ UserΒ FROMΒ mysql.user;. Β Β IfΒ youΒ seeΒ yourΒ newlyΒ createdΒ userΒ inΒ theΒ list,Β youΒ haveΒ createdΒ Β theΒ userΒ successfully.
- Letβs create aΒ database.Β Type mysql> Β Β Β Β Β Β Β SHOWΒ DATABASES;, Β Β thereΒ shouldΒ beΒ Β fourΒ defaultΒ databases,Β ignoreΒ these.
CreateΒ aΒ newΒ databaseΒ byΒ typing mysql> Β Β Β Β Β Β Β Β Β Β Β CREATEΒ DATABASEΒ db_name;, Β Β Β where βdb_nameβ Β Β Β Β Β isΒ yourΒ chosenΒ nameΒ ofΒ theΒ database,Β example mysql>
CREATEΒ DATABASEΒ test_db;. Β Β TryΒ typingΒ mysql> Β Β SHOWΒ DATABASES;Β againΒ andΒ checkΒ ifΒ theΒ database Β isΒ there.Β IfΒ itΒ isΒ there,Β congratulations!
Β
ToΒ exitΒ theΒ MySQLΒ program,Β simplyΒ type exit Β Β Β andΒ youΒ areΒ backΒ atΒ theΒ virtualΒ machine.
NowΒ youΒ haveΒ toΒ type sudo Β Β Β Β Β Β Β Β Β Β serviceΒ mysqlΒ restartΒ in Β Β theΒ terminal.Β OnceΒ thisΒ isΒ Β complete,Β youΒ shouldΒ beΒ ableΒ toΒ useΒ theΒ databaseΒ fromΒ yourΒ PythonΒ programΒ (orΒ theΒ Β MySQLΒ terminal).
Β
SetupΒ Python
WeΒ willΒ beΒ usingΒ a MySQL Β Β Β connectorΒ forΒ PythonΒ inΒ thisΒ assignment.Β IfΒ youΒ forΒ someΒ Β reasonΒ wouldΒ likeΒ toΒ completeΒ theΒ assignmentΒ inΒ anotherΒ language,Β youΒ areΒ allowedΒ toΒ Β doΒ so. ( NB!Β WeΒ will not Β Β provideΒ supportΒ orΒ documentationΒ forΒ otherΒ languages,Β soΒ weΒ Β stronglyΒ encourageΒ youΒ toΒ useΒ Python.)Β Β
Β
WithΒ theΒ filesΒ providedΒ inΒ thisΒ assignment,Β youΒ willΒ findΒ a requirements.txt,
DbConnector.pyΒ and Β example.py Β ,Β amongΒ others.
Β
- To setΒ upΒ theΒ requiredΒ pip-packagesΒ forΒ thisΒ assignment,Β simplyΒ runΒ Β Β Β pipΒ installΒ -rΒ txtΒ while Β Β youΒ areΒ inΒ theΒ directoryΒ withΒ Β theΒ requirements-file.Β ThisΒ willΒ installΒ theΒ connector,Β alongΒ with tabulate Β Β (usedΒ Β toΒ printΒ prettyΒ tablesΒ inΒ python)Β and haversine Β Β (usedΒ toΒ calculateΒ distanceΒ Β betweenΒ twoΒ coordinates).
- Now, open py Β Β Β Β Β Β and Β Β lookΒ atΒ theΒ code.Β ThereΒ willΒ beΒ aΒ TODOΒ thereΒ Β toΒ setΒ upΒ theΒ databaseΒ correctlyΒ withΒ theΒ settingsΒ fromΒ theΒ databaseΒ setup.Β Β UpdateΒ theΒ valuesΒ accordingly:
Host = Β Β tdt4225-xx.idi.ntnu.no,Β whereΒ xxΒ isΒ yourΒ groupΒ number
Database = Β Β theΒ nameΒ youΒ providedΒ forΒ yourΒ databaseΒ inΒ stepΒ 5Β ofΒ theΒ databaseΒ Β setupΒ (test_dbΒ fromΒ theΒ example)
User = Β Β theΒ usernameΒ providedΒ inΒ stepΒ 4,Β (testuserΒ fromΒ theΒ example)
Password = Β Β theΒ passwordΒ providedΒ inΒ stepΒ 4Β (test123Β fromΒ theΒ example)
Port = Β Β thisΒ isΒ optional,Β andΒ defaultΒ isΒ 3306
- Passwords shouldΒ beΒ storedΒ asΒ anΒ environmentalΒ variableΒ ifΒ yourΒ codeΒ isΒ Β public.Β See here Β Β Β , Β Β orΒ searchΒ GoogleΒ toΒ findΒ outΒ howΒ toΒ doΒ it.
- Open py Β Β Β Β Β Β Β Β Β Β ,Β theΒ fileΒ hasΒ aΒ mainΒ methodΒ thatΒ createsΒ aΒ connectionΒ toΒ theΒ Β databaseΒ fromΒ DbConnector,Β createsΒ aΒ table Β namedΒ βPersonβ,Β insertsΒ someΒ Β namesΒ inΒ theΒ database,Β displaysΒ theΒ data,Β andΒ thenΒ dropsΒ theΒ table.Β Β TryΒ toΒ runΒ theΒ code.Β IfΒ everythingΒ isΒ setΒ upΒ correctly,Β youΒ willΒ establishΒ aΒ Β connectionΒ toΒ theΒ databaseΒ andΒ insert/deleteΒ data.Β YouΒ canΒ useΒ thisΒ fileΒ forΒ Β inspirationΒ whenΒ solvingΒ theΒ tasksΒ inΒ thisΒ assignment.Β Β Β Β
Β
Tasks
TheΒ tasksΒ areΒ dividedΒ intoΒ threeΒ parts:Β PartΒ 1Β willΒ focusΒ onΒ cleaningΒ andΒ insertingΒ theΒ Β dataΒ intoΒ definedΒ tables.Β PartΒ 2Β willΒ focusΒ onΒ writingΒ queriesΒ toΒ theΒ databaseΒ toΒ gainΒ Β knowledgeΒ ofΒ theΒ dataset.Β PartΒ 3Β willΒ focusΒ onΒ writingΒ aΒ reportΒ whereΒ youΒ discussΒ yourΒ Β answers.
WeΒ recommendΒ lookingΒ atΒ the documentation Β Β Β Β Β forΒ theΒ MySQL-PythonΒ connectorΒ Β beforeΒ youΒ startΒ coding.
Β
PartΒ 1
InΒ thisΒ taskΒ youβllΒ cleanΒ andΒ insertΒ theΒ GeolifeΒ datasetΒ intoΒ yourΒ ownΒ MySQLΒ database,Β Β toΒ Β beΒ ableΒ toΒ solveΒ theΒ questionsΒ inΒ PartΒ 2.Β InΒ theΒ sectionΒ βGeolifeΒ GPSΒ TrajectoryΒ Β datasetβΒ above,Β youβllΒ findΒ allΒ theΒ informationΒ youΒ needΒ aboutΒ theΒ datasetΒ youΒ areΒ Β handed,Β andΒ inΒ theΒ sectionΒ βTablesβΒ youβllΒ find our Β Β Β Β Β Β Β Β Β Β Β suggestionΒ forΒ howΒ toΒ structureΒ theΒ Β datasetΒ inΒ yourΒ MySQLΒ database.Β Β YouΒ areΒ freeΒ toΒ doΒ itΒ inΒ anotherΒ wayΒ asΒ well,Β butΒ Β please discuss Β Β Β Β Β Β Β inΒ yourΒ reportΒ whyΒ youΒ doΒ thingsΒ differently.
Β
WriteΒ aΒ PythonΒ programΒ thatΒ doesΒ theΒ following:
- Connects toΒ theΒ MySQLΒ serverΒ onΒ yourΒ UbuntuΒ virtual
- Creates andΒ definesΒ theΒ tablesΒ User,Β ActivityΒ andΒ TrackPoint
- Inserts theΒ dataΒ fromΒ theΒ GeolifeΒ datasetΒ intoΒ yourΒ MySQLΒ database
βΒ Β Β Here,Β weΒ requireΒ aΒ bitΒ ofΒ dataΒ cleaningΒ andΒ integration.Β StudyΒ theΒ datasetΒ Β andΒ theΒ tables closely Β Β toΒ understandΒ whichΒ dataΒ goesΒ where.
βΒ InsertΒ dataΒ intoΒ User,Β ActivityΒ andΒ TrackPoint,Β inΒ thatΒ order.
β Β Β Β E.g.Β youΒ cannotΒ createΒ anΒ ActivityΒ (withΒ theΒ filedΒ user_id)Β withoutΒ Β havingΒ aΒ UserΒ withΒ thatΒ id.
βΒ Β Β IteratingΒ throughΒ theΒ datasetΒ inΒ theΒ directoriesΒ canΒ beΒ doneΒ using os.walk Β Β Β Β Β method,Β butΒ youΒ areΒ freeΒ toΒ useΒ otherΒ methodsΒ ifΒ youΒ findΒ themΒ betterΒ forΒ Β yourΒ solution.
βΒ Β Β WhenΒ matchingΒ transportation_modeΒ fromΒ the labels.txt Β Β files Β Β toΒ theΒ Β activities,Β weΒ onlyΒ considerΒ exactΒ matchesΒ onΒ starttimeΒ Β andΒ endΒ time,Β i.e.Β Β ifΒ youΒ findΒ aΒ matchΒ inΒ startΒ time,Β butΒ notΒ inΒ endΒ time,Β orΒ viceΒ versa,Β itΒ Β shouldΒ notΒ beΒ included.Β Additionally,Β thereΒ areΒ someΒ labeledΒ activitiesΒ inΒ Β labels.txtΒ that Β Β willΒ notΒ haveΒ aΒ matchΒ amongstΒ theΒ activities.
β Important! Sometimes Β Β itΒ isΒ wiseΒ toΒ limitΒ theΒ datasetΒ weβreΒ workingΒ with.Β Β 24Β millionΒ TrackPointsΒ isΒ aΒ lot.Β ThereforeΒ weΒ onlyΒ wantΒ youΒ toΒ insertΒ Β activitiesΒ thatΒ have fewer Β Β thanΒ orΒ exactlyΒ 2500Β trackpointsΒ inΒ them,Β i.e.Β Β whenΒ insertingΒ activitiesΒ intoΒ theΒ database,Β checkΒ thatΒ theΒ sizeΒ ofΒ theΒ Β .plt-filesΒ doΒ notΒ exceedΒ 2500Β linesΒ (excludingΒ theΒ headers,Β ofΒ course).Β Β Β Β WhenΒ youΒ insertΒ TrackPoints,Β theΒ sameΒ ruleΒ appliesΒ (youΒ cannotΒ linkΒ aΒ Β trackpointΒ toΒ anΒ activityΒ thatΒ isΒ dropped,Β anyway).
βΒ Β Β InsertingΒ theΒ trackpointsΒ mayΒ takeΒ aΒ whileΒ (couldΒ potentiallyΒ beΒ 10-15Β Β mins)!Β InsteadΒ ofΒ insertingΒ oneΒ rowΒ ofΒ trackpointsΒ atΒ aΒ time,Β findΒ outΒ ifΒ Β thereΒ isΒ aΒ wayΒ toΒ insertΒ batchesΒ ofΒ dataΒ instead.
Β
PartΒ 2
SomeΒ ofΒ theΒ tasksΒ canΒ beΒ answeredΒ usingΒ MySQL-queriesΒ only,Β whileΒ someΒ mightΒ Β requireΒ bothΒ queriesΒ andΒ PythonΒ codeΒ toΒ manipulateΒ theΒ dataΒ correctly.
AnswerΒ theΒ followingΒ questionsΒ byΒ writingΒ aΒ PythonΒ programΒ usingΒ MySQL-queries
(likeΒ in example.py Β Β Β Β Β Β Β Β ):
- How manyΒ users,Β activitiesΒ andΒ trackpointsΒ areΒ thereΒ inΒ theΒ datasetΒ (afterΒ itΒ isΒ Β insertedΒ intoΒ theΒ database).
- Find theΒ average,Β minimumΒ andΒ maximumΒ numberΒ ofΒ activitiesΒ per
- Find theΒ topΒ 10Β usersΒ withΒ theΒ highestΒ numberΒ of
- Find theΒ numberΒ ofΒ usersΒ thatΒ haveΒ startedΒ theΒ activityΒ inΒ oneΒ dayΒ andΒ endedΒ theΒ activityΒ theΒ next
- Find activitiesΒ thatΒ areΒ registeredΒ multipleΒ Β YouΒ shouldΒ findΒ theΒ queryΒ evenΒ ifΒ youΒ getΒ zeroΒ results.
- Find theΒ numberΒ ofΒ usersΒ whichΒ haveΒ beenΒ closeΒ toΒ eachΒ otherΒ inΒ timeΒ andΒ spaceΒ (Covid-19Β tracking).Β CloseΒ isΒ definedΒ asΒ theΒ sameΒ minuteΒ (60Β seconds)Β andΒ spaceΒ (100Β meters).
- Find allΒ usersΒ thatΒ haveΒ neverΒ takenΒ a
- Find allΒ typesΒ ofΒ transportationΒ modesΒ andΒ countΒ howΒ manyΒ distinctΒ usersΒ thatΒ Β haveΒ usedΒ theΒ differentΒ transportationΒ Β DoΒ notΒ countΒ theΒ rowsΒ whereΒ theΒ Β transportationΒ modeΒ isΒ null .
Β
- a) FindΒ theΒ yearΒ andΒ monthΒ withΒ theΒ most
- b) Which userΒ hadΒ theΒ mostΒ activitiesΒ thisΒ yearΒ andΒ month,Β andΒ howΒ manyΒ Β recordedΒ hoursΒ doΒ theyΒ have?Β DoΒ theyΒ haveΒ moreΒ hoursΒ recordedΒ thanΒ theΒ userΒ Β withΒ the Β secondΒ mostΒ activities?
10.FindΒ theΒ totalΒ distanceΒ (inΒ km) walked Β Β inΒ 2008,Β byΒ userΒ withΒ id=112.
11.FindΒ theΒ topΒ 20Β usersΒ whoΒ haveΒ gainedΒ theΒ mostΒ altitude meters Β Β Β Β Β Β Β .
β OutputΒ shouldΒ beΒ aΒ tableΒ withΒ (id,Β totalΒ metersΒ gainedΒ perΒ user).
βΒ Β Β RememberΒ thatΒ someΒ altitude-valuesΒ areΒ invalid
Β
βΒ Β Β Β Tip:Β Β β(tp n.altitude β tp nβ1.altitude),Β tp n.altitude > tp nβ1.altitude
Β
12.FindΒ allΒ usersΒ whoΒ haveΒ invalidΒ activities,Β andΒ theΒ numberΒ ofΒ invalidΒ activitiesΒ perΒ Β user
βΒ Β Β AnΒ invalidΒ activityΒ isΒ definedΒ asΒ anΒ activityΒ withΒ consecutiveΒ trackpointsΒ Β whereΒ theΒ timestampsΒ deviateΒ withΒ atΒ leastΒ 5Β minutes.
Β
PartΒ 3
WriteΒ aΒ shortΒ reportΒ (see report-template.docx Β Β in Β Β the
tdt4225-assignment2.zip )Β whereΒ youΒ willΒ displayΒ andΒ discussΒ yourΒ resultsΒ fromΒ Β theΒ tasks.Β IncludeΒ screenshotsΒ fromΒ bothΒ partΒ 1Β (showingΒ topΒ 10Β rowsΒ fromΒ allΒ ofΒ yourΒ Β tablesΒ isΒ sufficient)Β andΒ partΒ 2Β (allΒ theΒ resultsΒ toΒ eachΒ task).
Β
HandΒ inΒ bothΒ theΒ reportΒ (asΒ PDF)Β andΒ yourΒ codeΒ toΒ BlackBoardΒ within Oct Β Β 9,Β atΒ 16:00 . Β
Β
Tips
- Using theΒ MySQLΒ terminalΒ onΒ yourΒ UbuntuΒ machineΒ couldΒ beΒ usefulΒ forΒ Β checkingΒ simpleΒ queriesΒ withoutΒ havingΒ toΒ useΒ PythonΒ andΒ the
βΒ E.g.Β justΒ checkingΒ ifΒ theΒ dataΒ isΒ correctΒ βSELECTΒ *Β FROMΒ UserΒ LIMITΒ 5β
βΒ Β Β OpenΒ theΒ MySQLΒ terminalΒ byΒ typing sudo Β Β Β Β Β Β Β Β Β Β Β mysqlΒ , Β Β logΒ inΒ withΒ Β Feide-passwordΒ andΒ typeΒ use Β Β name_of_dbΒ to Β Β doΒ this.
- Using dictionaries/hashmapsΒ areΒ fasterΒ forΒ lookupsΒ thanΒ lists/arraysΒ inΒ yourΒ Β PythonΒ code
- When extractingΒ transportation_modeΒ fromΒ the txt Β Β Β files,Β rememberΒ Β thatΒ youΒ onlyΒ haveΒ toΒ considerΒ theΒ user-idsΒ foundΒ inΒ the labeled_ids.txt Β Β Β Β Β Β ,Β asΒ Β theyΒ areΒ theΒ onlyΒ usersΒ whereΒ transportation_modeΒ may not Β Β Β Β Β Β Β Β Β Β Β beΒ null.
- Start_time andΒ end_timeΒ forΒ activitiesΒ canΒ beΒ foundΒ byΒ lookingΒ atΒ theΒ dateΒ andΒ Β timeΒ forΒ theΒ firstΒ andΒ lastΒ trackpointΒ inΒ eachΒ .plt-file
- Remember toΒ keepΒ trackΒ ofΒ activity-idsΒ whenΒ insertingΒ trackpoints,Β soΒ thatΒ eachΒ Β trackpointΒ hasΒ theΒ correctΒ foreignΒ keyΒ toΒ theΒ ActivityΒ table
- Remember foreignΒ keyΒ cascadeΒ rules
8
- How toΒ useΒ andΒ takeΒ advantageΒ ofΒ theΒ datetimeΒ dataΒ typeΒ inΒ yourΒ queriesΒ canΒ beΒ Β found here Β Β Β Β Β Β Β Β Β Β Β .
- Using variables Β Β inΒ MySQL Β mayΒ comeΒ inΒ handy
- As stated,Β using Haversine Β Β Β Β forΒ calculatingΒ distanceΒ isΒ recommended
- As stated,Β using Tabulate Β Β Β Β Β Β forΒ printingΒ tablesΒ inΒ yourΒ PythonΒ programΒ isΒ Β recommended
- Optional βΒ ifΒ youΒ wantΒ toΒ visualizeΒ theΒ dataΒ inΒ theΒ dataset,Β you Β canΒ getΒ inspiredΒ Β here.
Β
