RootsLabs

Comment géolocaliser une adresse IP ?
Comment savoir de quel pays provient tel utilisateur ?

Voici un code qui vous permettra de créer une base de données SQLite, et vous permettra ainsi de répondre aux questions précédentes.

Procédure :
– Récupérer le zip suivant : http://www.blogama.org/ipinfodb_csv.zip
– Le décompresser dans un dossier et mettre les CSV dans un même dossier
– Utiliser le code suivant pour créer la base de données SQLite (Attention : le processus du code peut durer une bonne heure et demi)

ProcedureDLL IPInfo_CreateDB()
  hDB_IPInfo = OpenDatabase(#PB_Any, "ipinfo.sqlite","","",#PB_Database_SQLite)
  sql_clear_0.s = "DROP TABLE IF EXISTS 'cities';"
  sql_clear_1.s = "DROP TABLE IF EXISTS 'fips_countries';"
  sql_clear_2.s = "DROP TABLE IF EXISTS 'fips_regions';"
  sql_clear_3.s = "DROP TABLE IF EXISTS 'ip_group_city';"
  sql_clear_4.s = "DROP TABLE IF EXISTS 'ip_group_country';"
  DatabaseUpdate(hDB_IPInfo, sql_clear_0)
  DatabaseUpdate(hDB_IPInfo, sql_clear_1)
  DatabaseUpdate(hDB_IPInfo, sql_clear_2)
  DatabaseUpdate(hDB_IPInfo, sql_clear_3)
  DatabaseUpdate(hDB_IPInfo, sql_clear_4)
  
  sql_0.s = "CREATE TABLE 'cities' ('id' integer Not NULL primary key autoincrement,'country_code' varchar(2) Not NULL,'region_code' varchar(2) Not NULL, 'city' varchar(64) Not NULL,'latitude' float Not NULL,'longitude' float Not NULL,  'nbip' integer Not NULL)"
  sql_1.s = "CREATE TABLE 'fips_countries' ('code' varchar(2) Not NULL, 'name' char(64) Not NULL)";
  sql_2.s = "CREATE TABLE 'fips_regions' ('id' integer Not NULL primary key autoincrement, 'country_code' varchar(2) Not NULL, 'code' varchar(2) Not NULL,'name' varchar(64) Not NULL)";
  sql_3.s = "CREATE TABLE 'ip_group_city' ('ip_start' integer Not NULL, 'country_code' varchar(2) Not NULL, 'region_code' varchar(2) Not NULL,'city' varchar(64) Not NULL,'zipcode' varchar(6) Not NULL,'latitude' float Not NULL,  'longitude' float Not NULL)";
  sql_4.s = "CREATE TABLE 'ip_group_country' ('ip_start' integer Not NULL,'ip_end' integer Not NULL,'ip_cidr' varchar(20) Not NULL,'country_code' varchar(2) Not NULL)";
  result = DatabaseUpdate(hDB_IPInfo, sql_0) : If result = 0 : Debug DatabaseError() : EndIf
  result = DatabaseUpdate(hDB_IPInfo, sql_1) : If result = 0 : Debug DatabaseError() : EndIf
  result = DatabaseUpdate(hDB_IPInfo, sql_2) : If result = 0 : Debug DatabaseError() : EndIf
  result = DatabaseUpdate(hDB_IPInfo, sql_3) : If result = 0 : Debug DatabaseError() : EndIf
  result = DatabaseUpdate(hDB_IPInfo, sql_4) : If result = 0 : Debug DatabaseError() : EndIf
  ProcedureReturn hDB_IPInfo
EndProcedure
ProcedureDLL IPInfo_CloseDB(SQLId.l)
  CloseDatabase(SQLId)
EndProcedure

ProcedureDLL IPInfo_ImportCSV_Cities(SQLId.l, FileName.s)
  Protected hFile.l
  Protected bFirstLineDone.b = #False
  Protected sRequestSQL.s, sLine.s
  hFile = ReadFile(#PB_Any, FileName)
  If hFile
    While Eof(hFile) = 0
      If bFirstLineDone = #False
        ReadString(hFile, #PB_UTF8)
        bFirstLineDone = #True
      Else
        sLine = ReadString(hFile, #PB_UTF8)
        sLine = RemoveString(sLine, Chr(34))
        sLine = ReplaceString(sLine, "'", "''")
        sRequestSQL = "INSERT INTO `cities` (`country_code`,`region_code`,`city`,`latitude`,`longitude`,`nbip`) VALUES "
        sRequestSQL + "('"+StringField(sLine, 2, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 3, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 4, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 5, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 6, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 7, ";")+"');"
        result = DatabaseUpdate(SQLId, sRequestSQL)
        If result = 0
          Debug sRequestSQL
          Debug DatabaseError()
          End
        EndIf
      EndIf
    Wend
    CloseFile(hFile)
  Else
    MessageRequester("Information","Impossible d'ouvrir le fichier <"+FileName+"> !")
  EndIf
EndProcedure
ProcedureDLL IPInfo_ImportCSV_FIPs_Countries(SQLId.l, FileName.s)
  Protected hFile.l
  Protected bFirstLineDone.b = #False
  Protected sRequestSQL.s, sLine.s
  hFile = ReadFile(#PB_Any, FileName)
  If hFile
    While Eof(hFile) = 0
      If bFirstLineDone = #False
        ReadString(hFile, #PB_UTF8)
        bFirstLineDone = #True
      Else
        sLine = ReadString(hFile, #PB_UTF8)
        sLine = RemoveString(sLine, Chr(34))
        sLine = ReplaceString(sLine, "'", "''")
        sRequestSQL = "INSERT INTO `fips_countries` (`code`,`name`) VALUES "
        sRequestSQL + "('"+StringField(sLine, 1, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 2, ";")+"')"
        result = DatabaseUpdate(SQLId, sRequestSQL)
        If result = 0
          Debug sRequestSQL
          Debug DatabaseError()
          End
        EndIf
      EndIf
    Wend
    CloseFile(hFile)
  Else
    MessageRequester("Information","Impossible d'ouvrir le fichier <"+FileName+"> !")
  EndIf
EndProcedure
ProcedureDLL IPInfo_ImportCSV_FIPs_Regions(SQLId.l, FileName.s)
  Protected hFile.l
  Protected bFirstLineDone.b = #False
  Protected sRequestSQL.s, sLine.s
  hFile = ReadFile(#PB_Any, FileName)
  If hFile
    While Eof(hFile) = 0
      If bFirstLineDone = #False
        ReadString(hFile, #PB_UTF8)
        bFirstLineDone = #True
      Else
        sLine = ReadString(hFile, #PB_UTF8)
        sLine = RemoveString(sLine, Chr(34))
        sLine = ReplaceString(sLine, "'", "''")
        sRequestSQL = "INSERT INTO `fips_regions` (`country_code`,`code`,`name`) VALUES "
        sRequestSQL + "('"+StringField(sLine, 2, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 3, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 4, ";")+"')"
        result = DatabaseUpdate(SQLId, sRequestSQL)
        If result = 0
          Debug sRequestSQL
          Debug DatabaseError()
          End
        EndIf
      EndIf
    Wend
    CloseFile(hFile)
  Else
    MessageRequester("Information","Impossible d'ouvrir le fichier <"+FileName+"> !")
  EndIf
EndProcedure
ProcedureDLL IPInfo_ImportCSV_IP_Group_City(SQLId.l, FileName.s)
  Protected hFile.l
  Protected bFirstLineDone.b = #False
  Protected sRequestSQL.s, sLine.s
  hFile = ReadFile(#PB_Any, FileName)
  If hFile
    While Eof(hFile) = 0
      If bFirstLineDone = #False
        ReadString(hFile, #PB_UTF8)
        bFirstLineDone = #True
      Else
        sLine = ReadString(hFile, #PB_UTF8)
        sLine = RemoveString(sLine, Chr(34))
        sLine = ReplaceString(sLine, "'", "''")
        sRequestSQL = "INSERT INTO `ip_group_city` (`ip_start`,`country_code`,`region_code`,`city`,`zipcode`,`latitude`,`longitude`) VALUES "
        sRequestSQL + "('"+StringField(sLine, 1, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 2, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 3, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 4, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 5, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 6, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 7, ";")+"')"
        result = DatabaseUpdate(SQLId, sRequestSQL)
        If result = 0
          Debug sRequestSQL
          Debug DatabaseError()
          End
        EndIf
      EndIf
    Wend
    CloseFile(hFile)
  Else
    MessageRequester("Information","Impossible d'ouvrir le fichier <"+FileName+"> !")
  EndIf
EndProcedure
ProcedureDLL IPInfo_ImportCSV_IP_Group_Country(SQLId.l, FileName.s)
  Protected hFile.l
  Protected bFirstLineDone.b = #False
  Protected sRequestSQL.s, sLine.s
  hFile = ReadFile(#PB_Any, FileName)
  If hFile
    While Eof(hFile) = 0
      If bFirstLineDone = #False
        ReadString(hFile, #PB_UTF8)
        bFirstLineDone = #True
      Else
        sLine = ReadString(hFile, #PB_UTF8)
        sLine = RemoveString(sLine, Chr(34))
        sLine = ReplaceString(sLine, "'", "''")
        sRequestSQL = "INSERT INTO `ip_group_country` (`ip_start`,`ip_end`,`ip_cidr`,`country_code`) VALUES "
        sRequestSQL + "('"+StringField(sLine, 1, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 2, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 3, ";")+"'"
        sRequestSQL + ",'"+StringField(sLine, 4, ";")+"')"
        result = DatabaseUpdate(SQLId, sRequestSQL)
        If result = 0
          Debug sRequestSQL
          Debug DatabaseError()
          End
        EndIf
      EndIf
    Wend
    CloseFile(hFile)
  Else
    MessageRequester("Information","Impossible d'ouvrir le fichier <"+FileName+"> !")
  EndIf
EndProcedure

UseSQLiteDatabase()
Global hDB_IPInfo.l

Debug FormatDate("%dd/%mm/%yyyy %hh:%ii:%ss", Date())
Debug "IPInfo_CreateDB > "+FormatDate("%dd/%mm/%yyyy %hh:%ii:%ss", Date())
hDB_IPInfo = IPInfo_CreateDB()
Debug "IPInfo_ImportCSV_Cities >"+FormatDate("%dd/%mm/%yyyy %hh:%ii:%ss", Date()) 
IPInfo_ImportCSV_Cities(hDB_IPInfo, "cities.csv")
Debug "IPInfo_ImportCSV_FIPs_Countries > "+FormatDate("%dd/%mm/%yyyy %hh:%ii:%ss", Date())
IPInfo_ImportCSV_FIPs_Countries(hDB_IPInfo, "fips_countries.csv")
Debug "IPInfo_ImportCSV_FIPs_Regions > "+FormatDate("%dd/%mm/%yyyy %hh:%ii:%ss", Date())
IPInfo_ImportCSV_FIPs_Regions(hDB_IPInfo, "fips_regions.csv")
Debug "IPInfo_ImportCSV_IP_Group_City >" +FormatDate("%dd/%mm/%yyyy %hh:%ii:%ss", Date())
IPInfo_ImportCSV_IP_Group_City(hDB_IPInfo, "ip_group_city.csv")
Debug "IPInfo_ImportCSV_IP_Group_Country > "+FormatDate("%dd/%mm/%yyyy %hh:%ii:%ss", Date())
IPInfo_ImportCSV_IP_Group_Country(hDB_IPInfo, "ip_group_country.csv")
Debug "IPInfo_CloseDB > "+FormatDate("%dd/%mm/%yyyy %hh:%ii:%ss", Date())
IPInfo_CloseDB(hDB_IPInfo)
Debug FormatDate("%dd/%mm/%yyyy %hh:%ii:%ss", Date())

Et voici quelques codes d’utilisation :

UseSQLiteDatabase()

IPAdress.s = "74.125.45.100" ; google.com


hDB_IPInfo = OpenDatabase(#PB_Any, "ipinfo.sqlite","","",#PB_Database_SQLite)

;- Geolocation of an IP address
;{
  Debug "Geolocation of an IP address"
  IPItem_1.s = StringField(IPAdress, 1, ".")
  IPItem_2.s = StringField(IPAdress, 2, ".")
  IPItem_3.s = StringField(IPAdress, 3, ".")
  IP.l = (Val(IPItem_1)*256+Val(IPItem_2))*256+Val(IPItem_3)
  If DatabaseQuery(hDB_IPInfo, "SELECT * FROM `ip_group_city` where `ip_start` <= "+Str(ip)+" order by ip_start desc limit 1;")
    While NextDatabaseRow(hDB_IPInfo)
      Debug "ip_start > " + Str(GetDatabaseLong(hDB_IPInfo, 0))
      Debug "country_code > "+ GetDatabaseString(hDB_IPInfo, 1)
      Debug "region_code > "+GetDatabaseString(hDB_IPInfo, 2)
      Debug "city > "+GetDatabaseString(hDB_IPInfo, 3)
      Debug "zipcode > "+GetDatabaseString(hDB_IPInfo, 4)
      Debug "latitude > "+StrF(GetDatabaseFloat(hDB_IPInfo, 5))
      Debug "longitude > "+StrF(GetDatabaseFloat(hDB_IPInfo, 6))
      
      ; position GPS
      Debug RunProgram("firefox","http://maps.google.fr/maps?q="+StrF(GetDatabaseFloat(hDB_IPInfo, 5))+",+"+StrF(GetDatabaseFloat(hDB_IPInfo, 6)), "")
    Wend
    FinishDatabaseQuery(hDB_IPInfo)
  EndIf
;}
;- List Country from an IP Adress
;{
  Debug "List Country from an IP Adress"
  If DatabaseQuery(hDB_IPInfo, "Select * FROM `ip_group_country` where `ip_start` <= "+Str(IP)+" order by ip_start desc limit 1;")
    While NextDatabaseRow(hDB_IPInfo)
      Debug "ip_start > "+ Str(GetDatabaseLong(hDB_IPInfo, 0))
      Debug "ip_end > "+ Str(GetDatabaseLong(hDB_IPInfo, 1))
      Debug "ip_cidr > "+ GetDatabaseString(hDB_IPInfo, 2)
      Debug "country_code > "+ GetDatabaseString(hDB_IPInfo, 3)
    Wend
    FinishDatabaseQuery(hDB_IPInfo)
  EndIf
;}
;- List IP Adress from a country
;{
  Debug "List IP Adress from a country"
  If DatabaseQuery(hDB_IPInfo, "SELECT `ip_cidr` FROM `ip_group_country` WHERE `country_code` = 'FR' order by ip_start;")
    While NextDatabaseRow(hDB_IPInfo)
      Debug "ip_cidr > "+ GetDatabaseString(hDB_IPInfo, 0)
    Wend
    FinishDatabaseQuery(hDB_IPInfo)
  EndIf
;}

CloseDatabase(hDB_IPInfo)