18390View count
9m 23sLength in seconds

Travian Farming Tutorial: Quickly find nearby villages with inactive players and grab their resources. Import Travian map.sql files with OpenOffice Calc&Base in less than 10 mins to view nearby villages' populations, and their population growth. I'm assuming a basic understanding of OpenOffice, spreadsheets, databases and SQL for this tutorial. Up-to-date maps (maps of all villages on a server, Player and Nater) can be downloaded from Travian servers - for FREE! Properly sorted, these file contain a wealth of information. What villages are the nearest neighbours? How fast have their populations been growing? Find your server in this list (http://travian.wikia.com/wiki/List_of_servers) - hint: look at the address bar while you're playing the game. Add /map.sql to the server name and you have the link to download the map. For example: http://s7.travian.com/map.sql http://s2.travian.au/map.sql Maps usually update once a day, so downloading and storing maps every 2 or 3 days gives you a good timeline of village populations. Screencapped with: CamStudio http://sourceforge.net/projects/camstudio Edited with: Microsoft Movie Maker MAP.SQL import with: http://www.OpenOffice.org Played with: http://www.Travian.com Thanks for the comments, sorry for the late responses (5 months for a response -CAPITAL OOOPS). Notes: ------ (108, -62) = location of example village that's why you see ("X"-108) * ("X"-108) + ("Y"+62) * ("Y"+62) in the SQL. eg. If your village is at (64, 92) then use: ("X"-64) * ("X"-64) + ("Y"-92) * ("Y"-92) Some text strings you'll want to Copy&Paste as you follow the video: \\ \' INSERT INTO `x_world` VALUES ( ); Headings of the columns pasted at row 1 of Calc spreadsheet: FID, X, Y, TID, VID, Village, UID, Player, AID, Alliance, Population, ScanDate Make sure your tables are named Day1, Day2, Day3 - or the following SQL code will need to be modified. Without this code the whole thing falls apart. If you can't read the code in the vid, here it is (I can't show the less than signs (left arrows) because of YouTube description restrictions (!), so I'm substituting "LessThan", please fix this when you Copy&Paste). The SQL statement used to search the single map: SELECT SQRT(("X"-108) * ("X"-108) + ("Y"+62) * ("Y"+62)) AS "Dist", "Player", "Village", "Population", DATEDIFF('dd', "ScanDate", CURDATE()) AS "Days Ago", "X", "Y" FROM "Day3" WHERE "AID" = 0 AND "Dist" LessThan 20 AND ("Player" = 'Natars' OR "Player" IN (SELECT "Player" FROM "Day3" GROUP BY "Player" HAVING COUNT(*) = 1 )) ORDER BY "Dist" ASC, "Village", "Days Ago" ASC The SQL statement used to search the three maps together (make sure "Run SQL Command Directly" is enabled): SELECT SQRT(("X"-108) * ("X"-108) + ("Y"+62) * ("Y"+62)) AS "Dist", "Player", "Village", "Population", DATEDIFF('dd', "ScanDate", CURDATE()) AS "Days Ago", "X", "Y" FROM "Day3" WHERE "AID" = 0 AND "Dist" LessThan 20 AND ("Player" = 'Natars' OR "Player" IN (SELECT "Player" FROM "Day3" GROUP BY "Player" HAVING COUNT(*) = 1)) UNION SELECT SQRT(("X"-108) * ("X"-108) + ("Y"+62) * ("Y"+62)) AS "Dist", "Player", "Village", "Population", DATEDIFF('dd', "ScanDate", CURDATE()) AS "Days Ago", "X", "Y" FROM "Day2" WHERE "AID" = 0 AND "Dist" LessThan 20 AND ("Player" = 'Natars' OR "Player" IN (SELECT "Player" FROM "Day2" GROUP BY "Player" HAVING COUNT(*) = 1)) UNION SELECT SQRT(("X"-108) * ("X"-108) + ("Y"+62) * ("Y"+62)) AS "Dist", "Player", "Village", "Population", DATEDIFF('dd', "ScanDate", CURDATE()) AS "Days Ago", "X", "Y" FROM "Day1" WHERE "AID" = 0 AND "Dist" LessThan 20 AND ("Player" = 'Natars' OR "Player" IN (SELECT "Player" FROM "Day1" GROUP BY "Player" HAVING COUNT(*) = 1)) ORDER BY "Dist" ASC, "Village" ASC, "Days Ago" ASC