Rietveld Code Review Tool
Help | Bug tracker | Discussion group | Source code | Sign in
(683)

Unified Diff: Task1_3.sql

Issue 324650043: Dijkstras algorithm
Patch Set: add more SQL scrip for creating tables that wil hold data from MaxMind task 1.3 Created 6 years, 5 months ago
Use n/p to move between diff chunks; N/P to move between comments. Please Sign in to add in-line comments.
Jump to:
View side-by-side diff with in-line comments
Download patch
« no previous file with comments | « no previous file | task1.sql » ('j') | no next file with comments »
Expand Comments ('e') | Collapse Comments ('c') | Show Comments Hide Comments ('s')
Index: Task1_3.sql
diff --git a/Task1_3.sql b/Task1_3.sql
new file mode 100644
index 0000000000000000000000000000000000000000..b7a0fb027f01a931599b6b76c8b35638b4c56742
--- /dev/null
+++ b/Task1_3.sql
@@ -0,0 +1,34 @@
+
+#Creating the table that will hold values from tables that contains geographic information:
+
+CREATE TABLE geoHops AS SELECT geoLocation.* ,GeoIp.startIpNum,GeoIp.endIpNum FROM geoLocation iNNER JOIN GeoIp ON geoLocation.locId = GeoIp.locId;
+
+
+
+#Filtering the database to get geographic information for every source ip address found in table Hops. This requires to translate every ip address to their integers representation, this can be done with the help of the function INET_ATON.
+
+SELECT Hops.idHop, Hops.ipSrc, geoHops.* FROM Hops, geoHops WHERE INET_ATON(ipSrc) Between startIpNum and endIpNum AND idHop BETWEEN 1 AND 53020 AND ipSrc != '*' ORDER BY idHop ASC INTO OUTFIlE '/var/lib/mysql-files/geoHopsFinal.csv';
+
+#This " INTO OUTFIlE '/var/lib/mysql-files/geoHopsFinal.csv'; " will only save the results of the output as csv file. One thing to mention is that when trying to filter from idHop 1 to 53020 takes hours to complete. I will advise to run this script for fewer idHops EX: between 1 and 5000.
+
+
+# We used the following SQL query to create the tables that will hold the data from MaxMind GeoIp services
+
+mysql> CREATE TABLE GeoIp (
+ -> startIpNum int(10) unsigned NOT NULL,
+ -> endIpNum int(10) unsigned NOT NULL,
+ -> locId int(10) unsigned NOT NULL,
+ -> PRIMARY KEY (startIpNum, endIpNum),
+ -> );
+
+
+mysql> CREATE TABLE geoLocation (
+ -> locId int(10) unsigned NOT NULL,
+ -> country char(2) NOT NULL,
+ -> region char(2) NOT NULL,
+ -> city varchar(64) NOT NULL,
+ -> postalCode varchar(5) NOT NULL,
+ -> latitude float,
+ -> logitude float,
+ -> PRIMARY KEY (locId));
+
« no previous file with comments | « no previous file | task1.sql » ('j') | no next file with comments »

Powered by Google App Engine
RSS Feeds Recent Issues | This issue
This is Rietveld f62528b