MaxMind IP Adress GeoIP2 and GeoLite2

curl -I 'https://download.maxmind.com/app/geoip_download?edition_id=GeoIP2-City-CSV&license_key=YOUR_LICENSE_KEY&suffix=zip'

-- official version
create table geoip2_network (
  network cidr not null,
  geoname_id int,
  registered_country_geoname_id int,
  represented_country_geoname_id int,
  is_anonymous_proxy bool,
  is_satellite_provider bool,
  postal_code text,
  latitude numeric,
  longitude numeric,
  accuracy_radius int
);

-- or https://colinwilson.uk/2019/07/22/how-to-import-maxminds-geolite2-database-to-postgresql/

CREATE TABLE geoip2_network (
    network cidr,
    geoname_id bigint,
    registered_country_geoname_id bigint,
    represented_country_geoname_id bigint,
    is_anonymous_proxy bool,
    is_satellite_provider bool,
    postal_code varchar(8),
    latitude decimal(9,6),
    longitude decimal(9,6),
    accuracy_radius smallint
);

create index on geoip2_network using gist (network inet_ops);

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('geoip2_network', function (Blueprint $table) {
            $table->ipNetwork('network');
            $table->bigInteger('geoname_id')->nullable();
            $table->bigInteger('registered_country_geoname_id')->nullable();
            $table->bigInteger('represented_country_geoname_id')->nullable();
            $table->boolean('is_anonymous_proxy')->nullable();
            $table->boolean('is_satellite_provider')->nullable();
            $table->text('postal_code')->nullable();
            $table->decimal('latitude', 9, 6)->nullable();
            $table->decimal('longitude', 9, 6)->nullable();
            $table->integer('accuracy_radius')->nullable();

            $table->dateTimeTz('created_at')->useCurrent();
            $table->dateTimeTz('updated_at')->useCurrent();
        });

        DB::statement('CREATE INDEX gn_network ON geoip2_network USING gist (network inet_ops)');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('geoip2_network');
    }
};

-- Official version
\copy geoip2_network(
  network, geoname_id, registered_country_geoname_id, represented_country_geoname_id,
  is_anonymous_proxy, is_satellite_provider, postal_code, latitude, longitude, accuracy_radius
) from 'GeoIP2-City-Blocks-IPv4.csv' with (format csv, header);

\copy geoip2_network(
  network, geoname_id, registered_country_geoname_id, represented_country_geoname_id,
  is_anonymous_proxy, is_satellite_provider, postal_code, latitude, longitude, accuracy_radius
) from 'GeoIP2-City-Blocks-IPv6.csv' with (format csv, header);


/* If user is not a superuser or member of the pg_read_server_files role use
psql's \copy meta command to import CSV files to database */
copy geoip2_network from '/tmp/GeoLite2-City-Blocks-IPv4.csv' delimiter ',' csv header;
copy geoip2_network from '/tmp/GeoLite2-City-Blocks-IPv6.csv' delimiter ',' csv header;

select * from geoip2_network where network >> '146.243.121.22';

-- official version
create table geoip2_location (
  geoname_id int not null,
  locale_code text not null,
  continent_code text not null,
  continent_name text not null,
  country_iso_code text,
  country_name text,
  subdivision_1_iso_code text,
  subdivision_1_name text,
  subdivision_2_iso_code text,
  subdivision_2_name text,
  city_name text,
  metro_code int,
  time_zone text,
  is_in_european_union bool not null,
  primary key (geoname_id, locale_code)
);

-- or https://colinwilson.uk/2019/07/22/how-to-import-maxminds-geolite2-database-to-postgresql/

CREATE TABLE geoip2_location (
    geoname_id bigint,
    locale_code varchar(2),
    continent_code varchar(2),
    continent_name varchar(255),
    country_iso_code varchar(2),
    country_name varchar(255),
    subdivision_1_iso_code varchar(3),
    subdivision_1_name varchar(255),
    subdivision_2_iso_code varchar(3),
    subdivision_2_name varchar(255),
    city_name varchar(255),
    metro_code varchar(3),
    time_zone varchar(255),
    is_in_european_union bool
);

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('geoip2_location', function (Blueprint $table) {
            $table->bigInteger('geoname_id');
            $table->string('locale_code', 3);
            $table->text('continent_code');
            $table->text('continent_name');
            $table->text('country_iso_code')->nullable();
            $table->text('country_name')->nullable();
            $table->text('subdivision_1_iso_code')->nullable();
            $table->text('subdivision_1_name')->nullable();
            $table->text('subdivision_2_iso_code')->nullable();
            $table->text('subdivision_2_name')->nullable();
            $table->text('city_name')->nullable();
            $table->integer('metro_code')->nullable();
            $table->text('time_zone')->nullable();
            $table->boolean('is_in_european_union');

            $table->dateTimeTz('created_at')->useCurrent();
            $table->dateTimeTz('updated_at')->useCurrent();

            $table->primary(['geoname_id', 'locale_code'], 'geoip2_location_pk');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('geoip2_location');
    }
};

\copy geoip2_location(
  geoname_id, locale_code, continent_code, continent_name, country_iso_code, country_name,
  subdivision_1_iso_code, subdivision_1_name, subdivision_2_iso_code, subdivision_2_name,
  city_name, metro_code, time_zone, is_in_european_union
) from 'GeoIP2-City-Locations-en.csv' with (format csv, header);


copy geoip2_location from '/tmp/GeoLite2-City-Locations-en.csv' delimiter ',' csv header encoding 'UTF8';

select latitude, longitude, accuracy_radius, continent_name, country_name, subdivision_1_name, city_name
from geoip2_network net
left join geoip2_location location on (
  net.geoname_id = location.geoname_id
  and location.locale_code = 'en'
)
where network >> '146.243.121.22';

CREATE TABLE geoip2_asn (
    network cidr,
    autonomous_system_number bigint,
	autonomous_system_organization varchar(255)
);

CREATE INDEX asn_blocks_network_idx ON asn_blocks USING gist (network inet_ops);

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('geoip2_asn', function (Blueprint $table) {
            $table->ipNetwork('network');
            $table->bigInteger('autonomous_system_number');
            $table->string('autonomous_system_organization', 255);

            $table->dateTimeTz('created_at')->useCurrent();
            $table->dateTimeTz('updated_at')->useCurrent();
        });

        DB::statement('CREATE INDEX ga_network ON geoip2_asn USING gist (network inet_ops)');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('geoip2_asn');
    }
};

copy geoip2_asn from '/tmp/GeoLite2-ASN-Blocks-IPv4.csv' delimiter ',' csv header;
copy geoip2_asn from '/tmp/GeoLite2-ASN-Blocks-IPv6.csv' delimiter ',' csv header;

select * from geoip2_asn where network >> '146.243.121.22';

This product includes GeoLite2 data created by MaxMind, available from
<a href="https://www.maxmind.com">https://www.maxmind.com</a>.

composer require torann/geoip

php artisan vendor:publish --provider="Torann\GeoIP\GeoIPServiceProvider" --tag=config

MAXMIND_USER_ID=
MAXMIND_LICENSE_KEY=

// config/geoip.php
<?php

return [

    /*
    |--------------------------------------------------------------------------
    | Logging Configuration
    |--------------------------------------------------------------------------
    |
    | Here you may configure the log settings for when a location is not found
    | for the IP provided.
    |
    */

    'log_failures' => true,

    /*
    |--------------------------------------------------------------------------
    | Include Currency in Results
    |--------------------------------------------------------------------------
    |
    | When enabled the system will do it's best in deciding the user's currency
    | by matching their ISO code to a preset list of currencies.
    |
    */

    'include_currency' => true,

    /*
    |--------------------------------------------------------------------------
    | Default Service
    |--------------------------------------------------------------------------
    |
    | Here you may specify the default storage driver that should be used
    | by the framework.
    |
    | Supported: "maxmind_database", "maxmind_api", "ipapi"
    |
    */

    'service' => 'maxmind_database',

    /*
    |--------------------------------------------------------------------------
    | Storage Specific Configuration
    |--------------------------------------------------------------------------
    |
    | Here you may configure as many storage drivers as you wish.
    |
    */

    'services' => [

        'maxmind_database' => [
            'class' => \Torann\GeoIP\Services\MaxMindDatabase::class,
            'database_path' => storage_path('app/geoip.mmdb'),
            'update_url' => sprintf('https://download.maxmind.com/app/geoip_download?edition_id=GeoLite2-City&license_key=%s&suffix=tar.gz', env('MAXMIND_LICENSE_KEY')),
            'locales' => ['en'],
        ],

        'maxmind_api' => [
            'class' => \Torann\GeoIP\Services\MaxMindWebService::class,
            'user_id' => env('MAXMIND_USER_ID'),
            'license_key' => env('MAXMIND_LICENSE_KEY'),
            'locales' => ['en'],
        ],

        'ipapi' => [
            'class' => \Torann\GeoIP\Services\IPApi::class,
            'secure' => true,
            'key' => env('IPAPI_KEY'),
            'continent_path' => storage_path('app/continents.json'),
            'lang' => 'en',
        ],

        'ipgeolocation' => [
            'class' => \Torann\GeoIP\Services\IPGeoLocation::class,
            'secure' => true,
            'key' => env('IPGEOLOCATION_KEY'),
            'continent_path' => storage_path('app/continents.json'),
            'lang' => 'en',
        ],

        'ipdata' => [
            'class'  => \Torann\GeoIP\Services\IPData::class,
            'key'    => env('IPDATA_API_KEY'),
            'secure' => true,
        ],

        'ipfinder' => [
            'class'  => \Torann\GeoIP\Services\IPFinder::class,
            'key'    => env('IPFINDER_API_KEY'),
            'secure' => true,
            'locales' => ['en'],
        ],

    ],

    /*
    |--------------------------------------------------------------------------
    | Default Cache Driver
    |--------------------------------------------------------------------------
    |
    | Here you may specify the type of caching that should be used
    | by the package.
    |
    | Options:
    |
    |  all  - All location are cached
    |  some - Cache only the requesting user
    |  none - Disable cached
    |
    */

    'cache' => 'all',

    /*
    |--------------------------------------------------------------------------
    | Cache Tags
    |--------------------------------------------------------------------------
    |
    | Cache tags are not supported when using the file or database cache
    | drivers in Laravel. This is done so that only locations can be cleared.
    |
    */

    'cache_tags' => ['torann-geoip-location'],

    /*
    |--------------------------------------------------------------------------
    | Cache Expiration
    |--------------------------------------------------------------------------
    |
    | Define how long cached location are valid.
    |
    */

    'cache_expires' => 60,

    /*
    |--------------------------------------------------------------------------
    | Default Location
    |--------------------------------------------------------------------------
    |
    | Return when a location is not found.
    |
    */

    'default_location' => [
        'ip' => '127.0.0.0',
        'iso_code' => 'US',
        'country' => 'United States',
        'city' => 'New Haven',
        'state' => 'CT',
        'state_name' => 'Connecticut',
        'postal_code' => '06510',
        'lat' => 41.31,
        'lon' => -72.92,
        'timezone' => 'America/New_York',
        'continent' => 'NA',
        'default' => true,
        'currency' => 'USD',
    ],

];

php artisan geoip:update

'maxmind_database' => [
    'class' => \Torann\GeoIP\Services\MaxMindDatabase::class,
    'database_path' => storage_path('app/geoip.mmdb'),
    'update_url' => sprintf('https://download.maxmind.com/app/geoip_download?edition_id=GeoLite2-City&license_key=%s&suffix=tar.gz', env('MAXMIND_LICENSE_KEY')),
    'locales' => ['en'],
],

geoip()->getLocation('27.974.399.65');