IP2Location™ LITE IP-ASN Database

Database

IP2Location™ LITE IP-ASN Database

IP2Location™ LITE IP-ASN Database provides a reference to determine the autonomous system and number (ASN). An autonomous system (AS) is a collection of connected Internet Protocol (IP) routing prefixes under the control of one or more network operators as a single administrative entity in routing policy. A unique ASN is allocated to each AS for use in BGP routing. The data is being collected through routers in monthly basis. It might not be complete, up-to-date and suitable for real-time routing purpose. Please refer to RFC 1771 and RFC 1930 for more information about AS.

Free Download

IPv4 CSV IPv6 CSV

Product Features

Features	Translates IP address to autonomous system (AS) and autonomous system number (ASN).
Current Version	March 2020
Database Size	14.69 MB (956,531 Rows)
Database Format	ASCII Text File (Comma Delimited)

Database Fields

Name	Type	Description
ip_from	INT (10)^† / DECIMAL (39,0)^††	First IP address in netblock.
ip_to	INT (10)^† / DECIMAL (39,0)^††	Last IP address in netblock.
cidr	VARCHAR(43)	IP address range in CIDR.
asn	VARCHAR(10)	Autonomous system number (ASN)
as	VARCHAR(256)	Autonomous system (AS) name

^† IPv4
^†† IPv6

Database Setup

Below are the steps to set up the database for both IPv4 and IPv6 data.

IPv4
IPv6

Create table

CREATE DATABASE ip2location
GO

USE ip2location
GO

CREATE TABLE [ip2location].[dbo].[ip2location_asn](
	[ip_from] float NOT NULL,
	[ip_to] float NOT NULL,
	[cidr] nvarchar(18) NOT NULL,
	[asn] nvarchar(10) NOT NULL,
	[as] nvarchar(256) NOT NULL
) ON [PRIMARY]
GO

CREATE INDEX [ip_from] ON [ip2location].[dbo].[ip2location_asn]([ip_from]) ON [PRIMARY]
GO

CREATE INDEX [ip_to] ON [ip2location].[dbo].[ip2location_asn]([ip_to]) ON [PRIMARY]
GO

Import Database

BULK INSERT [ip2location].[dbo].[ip2location_asn]
	FROM 'C:\[path to your CSV file]\IP2LOCATION-LITE-ASN.CSV'
	WITH
	(
		FORMATFILE = 'C:\[path to your ASN.FMT file]\ASN.FMT'
	)
GO

ASN.FMT

NOTE: You will need to copy the FMT code below and save it as a file named ASN.FMT on your computer. The first line of the FMT code indicates the version of bcp. Please change the version as according to your MS-SQL installed.

SQL Server 2016	12.0
SQL Server 2014	12.0
SQL Server 2012	11.0
Azure SQL	10.0
SQL Server 2008/2008R2	10.0
SQL Server 2005	9.0
SQL Server 2000	8.0
SQL Server 7.0	7.0
SQL Server 6.5	6.5

10.0
6
1 SQLCHAR 0 1 "\"" 0 first_double_quote  Latin1_General_CI_AI
2 SQLCHAR 0 20 "\",\"" 1 ip_from ""
3 SQLCHAR 0 20 "\",\"" 2 ip_to ""
4 SQLCHAR 0 18 "\",\"" 3 cidr Latin1_General_CI_AI
5 SQLCHAR 0 20 "\",\"" 4 asn Latin1_General_CI_AI
6 SQLCHAR 0 256 "\"\r\n" 5 as Latin1_General_CI_AI

Create table

CREATE DATABASE ip2location;
USE ip2location;
CREATE TABLE `ip2location_asn`(
	`ip_from` INT(10) UNSIGNED,
	`ip_to` INT(10) UNSIGNED,
	`cidr` VARCHAR(18),
	`asn` VARCHAR(10),
	`as` VARCHAR(256),
	INDEX `idx_ip_from` (`ip_from`),
	INDEX `idx_ip_to` (`ip_to`),
	INDEX `idx_ip_from_to` (`ip_from`, `ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Import Database

LOAD DATA LOCAL
	INFILE 'IP2LOCATION-LITE-ASN.CSV'
INTO TABLE
	`ip2location_asn`
CHARACTER SET latin1
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 0 LINES;

Create table

CREATE DATABASE ip2location WITH ENCODING 'UTF8';
\c ip2location
CREATE TABLE ip2location_asn(
	ip_from bigint NOT NULL,
	ip_to bigint NOT NULL,
	cidr character varying(18) NOT NULL,
	asn character varying(10) NOT NULL,
	"as" character varying(256) NOT NULL,
	CONSTRAINT ip2location_asn_pkey PRIMARY KEY (ip_from, ip_to)
);

Import Database

SET CLIENT_ENCODING TO 'latin1';
COPY ip2location_asn FROM 'IP2LOCATION-LITE-ASN.CSV' WITH CSV QUOTE AS '"';

Create table

CREATE DATABASE ip2location
GO

USE ip2location
GO

CREATE TABLE [ip2location].[dbo].[ip2location_asn_ipv6](
	[ip_from] char(39) NOT NULL,
	[ip_to] char(39) NOT NULL,
	[cidr] nvarchar(43) NOT NULL,
	[asn] nvarchar(10) NOT NULL,
	[as] nvarchar(256) NOT NULL
) ON [PRIMARY]
GO

CREATE INDEX [ip_from] ON [ip2location].[dbo].[ip2location_asn_ipv6]([ip_from]) ON [PRIMARY]
GO

CREATE INDEX [ip_to] ON [ip2location].[dbo].[ip2location_asn_ipv6]([ip_to]) ON [PRIMARY]
GO

Import Database

BULK INSERT [ip2location].[dbo].[ip2location_asn]
	FROM 'C:\[path to your CSV file]\IP2LOCATION-LITE-ASN.IPV6.CSV'
	WITH
	(
		FORMATFILE = 'C:\[path to your ASN.FMT file]\ASN.IPV6.FMT'
	)
GO

ASN.FMT

SQL Server 2016	12.0
SQL Server 2014	12.0
SQL Server 2012	11.0
Azure SQL	10.0
SQL Server 2008/2008R2	10.0
SQL Server 2005	9.0
SQL Server 2000	8.0
SQL Server 7.0	7.0
SQL Server 6.5	6.5

10.0
6
1 SQLCHAR 0 1 "\"" 0 first_double_quote  Latin1_General_CI_AI
2 SQLCHAR 0 39 "\",\"" 1 ip_from ""
3 SQLCHAR 0 39 "\",\"" 2 ip_to ""
4 SQLCHAR 0 43 "\",\"" 3 cidr Latin1_General_CI_AI
5 SQLCHAR 0 20 "\",\"" 4 asn Latin1_General_CI_AI
6 SQLCHAR 0 256 "\"\r\n" 5 as Latin1_General_CI_AI

Create table

CREATE DATABASE ip2location;
USE ip2location;
CREATE TABLE `ip2location_asn_ipv6`(
	`ip_from` DECIMAL(39,0) UNSIGNED NULL DEFAULT NULL,
	`ip_to` DECIMAL(39,0) UNSIGNED NULL DEFAULT NULL,
	`cidr` VARCHAR(43),
	`asn` VARCHAR(10),
	`as` VARCHAR(256),
	INDEX `idx_ip_from` (`ip_from`),
	INDEX `idx_ip_to` (`ip_to`),
	INDEX `idx_ip_from_to` (`ip_from`, `ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Import Database

LOAD DATA LOCAL
	INFILE 'IP2LOCATION-LITE-ASN.IPV6.CSV'
INTO TABLE
	`ip2location_asn_ipv6`
CHARACTER SET latin1
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 0 LINES;

Create table

CREATE DATABASE ip2location WITH ENCODING 'UTF8';
\c ip2location
CREATE TABLE ip2location_asn_ipv6(
	ip_from decimal(39,0) NOT NULL,
	ip_to decimal(39,0) NOT NULL,
	cidr character varying(43) NOT NULL,
	asn character varying(10) NOT NULL,
	"as" character varying(256) NOT NULL,
	CONSTRAINT ip2location_asn_pkey PRIMARY KEY (ip_from, ip_to)
);

Import Database

SET CLIENT_ENCODING TO 'latin1';
COPY ip2location_asn_ipv6 FROM 'IP2LOCATION-LITE-ASN.IPV6.CSV' WITH CSV QUOTE AS '"';

AMI & Images

IP2Location provides a free, pre-configured & ready-to-run AMI and images to easily set up the database.

IP2Location MySQL Server
IP2Location PostgreSQL Server

Support

Please visit Stack Overflow to view the technical questions/answers about our LITE products.

Database