Add geo columns to xf_ip

digitalpoint

digitalpoint

Well-known member
It would be nice if there was 2 new columns in the xf_ip table.

country could be 2 digit country code and region could be a varchar for region.

So I know not everyone uses Cloudflare, but by default Cloudflare is already sending the country to servers with every request. And you can enable more granular info even for Free plans (like region/city), so it's a terrible easy way to do geotargeting without any APIs or dependencies since the info is already there.

It could be as simple as adding something to the existing XF\Entity\Ip->verifyIp() method... and now we have geotargetted info being stored. And if someone doesn't use Cloudflare or they just want to use some other method to geotarget IPs, they can set the country and region in the entity as they see fit.

PHP: 
    protected function verifyIp(&$ip)
    {
        if (!empty($_SERVER['REMOTE_ADDR']) && $ip === $_SERVER['REMOTE_ADDR'])
        {
            if (!empty($_SERVER['HTTP_CF_IPCOUNTRY']) && strlen($_SERVER['HTTP_CF_IPCOUNTRY']) === 2)
            {
                $this->country = $_SERVER['HTTP_CF_IPCOUNTRY'];
            }
            $region = [];
            if (!empty($_SERVER['HTTP_CF_IPCITY']))
            {
                $region[] = $_SERVER['HTTP_CF_IPCITY'];
            }
            if (!empty($_SERVER['HTTP_CF_REGION']))
            {
                $region[] = $_SERVER['HTTP_CF_REGION'];
            }
            if ($region)
            {
                $this->region = substr(implode(', ', $region), 0, $this->getMaxLength('region'));
            }
        }
       
        $ip = \XF\Util\Ip::convertIpStringToBinary($ip);
        if ($ip === false)
        {
            // this will fail later
            $ip = '';
        }
       
        return true;
    }

Could of course cram it into a single column, but having the country code as its own allows you to do stuff with it (like show the country flag via device native emojis).

Then if country/region are populated, we can display it (or just show nothing if the record doesn't have the geo info).

1699734643230.png
 
Upvote 26
If someone wanted to get a little fancier, it could be done in a one-to-many relationship to a new table. Although the downside is then the geo location might get stale over time if the IP address "moves". So over time doing it that way would save database storage space, but potentially less accurate.
 
Last edited:
A big plus for the one-to-many table would be that you can have an arbitrary number of meta data items for each IP. Country and region like was mentioned but also zip code, long/lat or other geospatial data one may have access to for a user's IP.
 
thedude said:
A big plus for the one-to-many table would be that you can have an arbitrary number of meta data items for each IP. Country and region like was mentioned but also zip code, long/lat or other geospatial data one may have access to for a user's IP.
Click to expand...
Geo info for IP addresses changes so rarely (and when they do it's usually just making it a little more accurate in whatever locale it was already "in"). So as long as the geo record was being updated with the latest info and not just left as the first info seen, it would be okay I think.
 
I vote yes only if there's a simple checkbox in ACP to turn this off. Some admins might not want to associate this analytical data directly with identifiable users and keep a minimal data collection policy.
 
⭐ Alex ⭐ said:
I vote yes only if there's a simple checkbox in ACP to turn this off. Some admins might not want to associate this analytical data directly with identifiable users and keep a minimal data collection policy.
Click to expand...
Ya same... I started mucking around with rolling it into an addon today. Went the one-to-many route, with it logging country, region, longitude and latitude (and updating that one record per IP address with the newest info). It has global option to enable/disable the location info being stored for IPs.

We'll see if I get around to doing more with it than just the logging... 🤷🏻‍♂️
 
Top Bottom