Woher weiß Google meinen Standort? Geotargeting macht's möglich!

Erstellt am 27.10.2016 | Zuletzt geändert am 20.11.2016

Was ist Geotargeting?

Hast du dich schon einmal gefragt, woher Google oder andere Webseiten deinen Standort wissen? Zugegeben, oftmals ist der tatsächliche Standort doch relativ weit weg vom angezeigten, aber ganz grob stimmt die Angabe meistens schon. Was ist nun das Geheimnis dahinter?

Das Geheimnis nennt sich Geotargeting. Geotargeting ordnet IP-Adressen ihrer geografischen Herkunft zu. Im Prinzip ist die Sache ganz einfach: Nehmen wir einmal an, jeder Internetnutzer bzw. jedes Gerät, das an das Internet angebunden ist, hätte dauerhaft die selbe IP-Adresse. Dann bräuchte man nur ein einziges Mal von allen IP-Adressen den Standort zuordnen, und schon könnte man dauerhaft den Internetnutzer lokalisieren (sofern er seinen Standort nicht ändert), indem man sich die Zuordnungstabelle ansieht.

In der Realität sieht es jedoch ein wenig anders aus: ein normaler privater Internetnutzer bekommt in der Regel spätestens nach 24 Stunden eine neue IP-Adresse von seinem Provider zugewiesen. Jedem Internetprovider steht ein bestimmter Adressraum zur Verfügung, aus dem er seinen Kunden einzelne Adressen zuweisen kann. Diese Zuweisung der Adressräume ist notwendig, damit es nicht passieren kann, dass mehrere Provider zur gleichen Zeit die gleiche IP-Adresse vergeben.

Die Adressräume der einzelnen Provider werden wiederrum meistens geografisch unterteilt. Im Endeffekt bedeutet dies, dass man als Internetnutzer stets eine IP-Adresse aus einem bestimmten geografisch zugeordneten Adressraum erhält. Ein Webseitenbetreiber benötigt im Prinzip nur eine Tabelle, in der den IP-Adressen bestimmte Orte zugeordnet sind. Und das ist des Rätsels Lösung.

Möchtest du deine Nutzer orten?

Wie wir bereits im vorherigen Abschnitt erfahren haben, benötigen wir zum Orten der Nutzer nur eine IP-Tabelle. Und genau solch eine Tabelle gibt es beispielsweise hier. Dort gibt es sowohl kostenlose, als auch kostenpflichtige Tabellen. Die kostenpflichtigen Tabellen besitzen eine bessere Auflösung, aber im Prinzip sollte die kostenlose Tabelle IP to city vollkommen ausreichen. Natürlich sollte einem klar sein, dass die Lokalisierung nur sehr ungenau ist und manchmal überhaupt nicht stimmt; spätestens dann, wenn der Internetnutzer einen Proxyserver verwendet.

Datenbank IP to City verwenden

Die Datenbank IP to City enthält fünf Spalten: IP Anfang, IP Ende, Land, Region und Stadt. Die Spalte IP Anfang benötigt man nicht wirklich, da es absolut ausreichend ist, das Ende des Adressraumes zu kennen.

Möchtest du diese Tabelle in deine MySQL-Datenbank importieren? Dann stehst du zunächst vor einem Problem, da die Tabelle im CSV-Format vorliegt und sehr groß ist. Außerdem ist es nicht sinnvoll die Daten so zu speichern, wie sie in dieser CSV-Datei vorliegen. Wie bereits erwähnt benötigt man die Spalte mit den Anfangsadressen nicht und außerdem sollten die IP-Adressen im Binärformat in der Datenbank gespeichert werden.

PHP-Skript: CSV → SQL

Um die Tabelle vernünftig importieren zu können, habe ich ein PHP-Skript geschrieben, welches die *.sql.gz-Datei in zwei *.sql-Dateien (IPv4 und IPv6) umwandelt und die unnötige Spalte mit den Anfangsadressen entfernt. Diese zwei *.sql Dateien können dann ganz einfach importiert werden, am besten via Konsole. Wie man den Import großer Datenbanken bewerkstelligt, habe ich hier beschrieben.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
<?php

##########################################################
$Data['input_name'] 		= "dbip-city-2016-10.csv.gz";
$Data['name_ipv4'] 			= "location_ipv4";
$Data['name_ipv6'] 			= "location_ipv6";
$Data['insert_lines_max']	= 10000;
##########################################################

create_SQL_files($Data);

// Creates two SQL files (IPv4, IPv6) out of the CSV file
function create_SQL_files($Data) {

	// Open files
	$file_gz 	= gzopen(__DIR__."/".$Data['input_name'], "r");
	$file_ipv4 	= fopen(__DIR__."/".$Data['name_ipv4'].".sql", "wb");
	$file_ipv6 	= fopen(__DIR__."/".$Data['name_ipv6'].".sql", "wb");

	$ipv4 = header_info('ipv4', $Data['name_ipv4']);
	$ipv6 = header_info('ipv6', $Data['name_ipv6']);

	$number_ipv4 	= 0;
	$number_ipv6 	= 0;
	$prev_line 		= "";
	$gz_blocks		= 1024*1024;								// Process file in 1 MB blocks (uncompressed)

	// For the whole file
	while(!gzeof($file_gz)) {
	
		$string 			= gzread($file_gz, $gz_blocks);		// Read the file in blocks
		$lines 				= explode("\n", $string);			// Extract the lines
		$number_of_lines 	= count($lines)-1;					// Determine number of lines
		$lines[0] 			= $prev_line.$lines[0];				// Add the last line of the previous iteration in front of the first line

		// For all lines
		for($i = 0; $i < $number_of_lines; $i++) {

			$values 		= explode("\"", $lines[$i]);		// Extract the values from the line
			$ip_bin 		= inet_pton($values[3]);			// Convert IP into binary value
			$ip_hex_string 	= unpack('H*', $ip_bin);			// Convert binary IP into hex string
			$ip_hex_string 	= "0x".$ip_hex_string[1];

			// If this is a IPv4 address
			if(strlen($ip_bin) == 4) {
				
				// If we need a new INSERT
				if($number_ipv4 % $Data['insert_lines_max'] == 0) $ipv4 .= ";\nINSERT INTO `".$Data['name_ipv4']."` VALUES ";
				// If we don't need a new INSERT
				else $ipv4 .= ",";
				
				$ipv4 .= "(".$ip_hex_string.",\"".htmlspecialchars($values[5])."\",\"".htmlspecialchars($values[7])."\",\"".htmlspecialchars($values[9])."\")";
				$number_ipv4++;			
				
			}
			
			// If this is a IPv6 address
			else {
				
				// If we need a new INSERT
				if($number_ipv6 % $Data['insert_lines_max'] == 0) $ipv6 .= ";\nINSERT INTO `".$Data['name_ipv6']."` VALUES ";
				// If we don't need a new INSERT
				else $ipv6 .= ",";
				
				$ipv6 .= "(".$ip_hex_string.",\"".htmlspecialchars($values[5])."\",\"".htmlspecialchars($values[7])."\",\"".htmlspecialchars($values[9])."\")";
				$number_ipv6++;

			}		
		
		}
		$prev_line = $lines[$i];
		
		// Write data into file
		fwrite($file_ipv4, $ipv4, strlen($ipv4));
		fwrite($file_ipv6, $ipv6, strlen($ipv6));
		$ipv4 = "";
		$ipv6 = "";

	}

	$ipv4 = footer_info($Data['name_ipv4']);
	$ipv6 = footer_info($Data['name_ipv6']);

	// Write data into files
	fwrite($file_ipv4, $ipv4, strlen($ipv4));
	fwrite($file_ipv6, $ipv6, strlen($ipv6));

	// Close files
	fclose($file_gz);
	gzclose($file_ipv4);
	gzclose($file_ipv6);

	print("Done!");
	print("<br>Number of entries: ".($number_ipv4+$number_ipv6));
	print("<br>Number of IPv4 entries: ".$number_ipv4);
	print("<br>Number of IPv6 entries: ". $number_ipv6);

}

// Print the header information of the SQL file
function header_info($type, $name) {
	
	$type = $type == "ipv4";
	
	return 
"/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

DROP TABLE IF EXISTS `".$name."`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `".$name."` (
  `ip_end` binary(".($type ? 4 : 16).") NOT NULL,
  `country` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `stateprov` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `city` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ip_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
LOCK TABLES `".$name."` WRITE;
/*!40000 ALTER TABLE `".$name."` DISABLE KEYS */";
	
}

// Print the footer information of the SQL file
function footer_info($name) {
	
	return ";
/*!40000 ALTER TABLE `".$name."` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;";
	
}

?>

Die Laufzeit des Skriptes beträgt in etwa 50 Sekunden. Via Konsole dauert der Import der IPv4-Tabelle ca. 2 Minuten 15 Sekunden und der Import der IPv6-Tabelle ca. 30 Sekunden. Diese Zeiten können natürlich, abhängig vom verwendeten System, stark variieren.

Das PHP-Skript kannst du hier herunterladen: IP table CSV2SQL
Teile diesen Beitrag