geoip 지역까지 정보를 csv, 국가 도시별 IP대역

4 minute read

http://www.maxmind.com/download/geoip/database/GeoLiteCity_CSV/

geoip 지역까지 정보를 보여주는것.

설명페이지 http://www.maxmind.com/app/city —=—– DB구조 Database Fields

<tbody>&lt;tr bgcolor="#EADCBF"&gt;&amp;lt;td align="middle" nowrap="" style="font-size: small; "&amp;gt;**Field**&amp;lt;/td&amp;gt;&amp;lt;td align="middle" nowrap="" style="font-size: small; "&amp;gt;**Data Type**&amp;lt;/td&amp;gt;&amp;lt;td align="middle" nowrap="" style="font-size: small; "&amp;gt;**Field Description**&amp;lt;/td&amp;gt;&lt;/tr&gt;&lt;tr&gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;Start IP Number&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;unsigned int&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;First IP in netblock, [numeric representation](http://www.maxmind.com/app/csv). (Only in CSV format)&amp;lt;/td&amp;gt;&lt;/tr&gt;&lt;tr&gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;End IP Number&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;unsigned int&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;Last IP in netblock, [numeric representation](http://www.maxmind.com/app/csv). (Only in CSV format)&amp;lt;/td&amp;gt;&lt;/tr&gt;&lt;tr&gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;Location ID&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;unsigned int&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;Location ID used to join netblock and location tables (CSV Only).&amp;lt;/td&amp;gt;&lt;/tr&gt;&lt;tr&gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;Country Code&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;char(2)&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;[ISO 3166](http://www.maxmind.com/app/iso3166) Country Code, with the addition of- A1 for [Anonymous Proxies](http://www.maxmind.com/app/faq#anonproxy). More comprehensive [Open Proxy](http://www.maxmind.com/app/ipauthentication#open) detection is available through the [minFraud](http://www.maxmind.com/app/ccv_overview) or [proxy detection](http://www.maxmind.com/app/proxy) web service.&amp;amp;#xD; - A2 for [Satellite Providers](http://www.maxmind.com/app/faq#satellite)&amp;amp;#xD; - EU for [Europe](http://www.maxmind.com/app/faq#EUAPcodes)&amp;amp;#xD; - AP for Asia/Pacific Region&amp;amp;#xD; - In addition, we map overseas military bases to US&amp;amp;#xD; &amp;amp;#xD; &amp;lt;/td&amp;gt;&lt;/tr&gt;&lt;tr&gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;State/Region&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;char(2)&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;For US/Canada, [ISO-3166-2](http://www.maxmind.com/app/iso3166_2) code for the state/province name, with the addition of AA, AE, and AP for Armed Forces America, Europe and Pacific. Outside of the US and Canada, [FIPS 10-4](http://www.maxmind.com/app/fips10_4)code. Region name lookups are available in [selected APIs](http://www.maxmind.com/app/faq#fips)&amp;lt;/td&amp;gt;&lt;/tr&gt;&lt;tr&gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;City Name&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;varchar(255)&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;Name of city or town in ISO-8859-1 encoding. A [list of cities](http://www.maxmind.com/GeoIPCity-534-Location.csv) contained in GeoIP City is available. A localized [CSV file](http://www.maxmind.com/app/faq#localization) containing city names in various languages is available.&amp;lt;/td&amp;gt;&lt;/tr&gt;&lt;tr&gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;Postal Code&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;varchar(6)&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;For US, Zipcodes, for Canada, postal codes. [More info.](http://www.maxmind.com/app/faq#postalcode)&amp;lt;/td&amp;gt;&lt;/tr&gt;&lt;tr&gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;Latitude&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;numeric (float)&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;Latitude of city where IP is located&amp;lt;/td&amp;gt;&lt;/tr&gt;&lt;tr&gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;Longitude&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;numeric (float)&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;Longitude of city where IP is located&amp;lt;/td&amp;gt;&lt;/tr&gt;&lt;tr&gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;Metro Code&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;unsigned int&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" id="" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;[Metropolitan Area](http://www.maxmind.com/app/metro_code) (US Only).&amp;lt;/td&amp;gt;&lt;/tr&gt;&lt;tr&gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;Area Code&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;unsigned int&amp;lt;/td&amp;gt;&amp;lt;td class="Fields" id="" style="font-size: small; border-bottom-style: solid; border-bottom-width: 1px; border-bottom-color: rgb(234, 220, 191); text-align: justify; " valign="top"&amp;gt;Three digit telephone prefix (US Only).&amp;lt;/td&amp;gt;&lt;/tr&gt;</tbody>


CREATE TABLE geo_block (   startIpNum INT(10) UNSIGNED DEFAULT NULL COMMENT ‘시작IP숫자’,   endIpNum INT(10) UNSIGNED DEFAULT NULL COMMENT ‘종료IP숫자’,   locId INT(10) UNSIGNED DEFAULT NULL COMMENT ‘지역ID’,   KEY idx_startIpNum (startIpNum) ) ENGINE=MYISAM DEFAULT CHARSET=utf8

CREATE TABLE geo_location (   locId INT(11) NOT NULL COMMENT ‘지역ID’,   country CHAR(2) DEFAULT NULL COMMENT ‘국가코드’,   region CHAR(2) DEFAULT NULL COMMENT ‘지역코드’,   city VARCHAR(255) DEFAULT NULL COMMENT ‘도시명’,   postalCode VARBINARY(6) DEFAULT NULL COMMENT ‘For US, Zipcodes, for Canada, postal codes.’,   latitude FLOAT DEFAULT NULL COMMENT ‘위도 : Latitude of city where IP is located’,   longitude FLOAT DEFAULT NULL COMMENT ‘경도 : Longitude of city where IP is located’,   metroCode INT(10) UNSIGNED DEFAULT NULL COMMENT ‘Metropolitan Area (US Only).’,   areaCode INT(10) UNSIGNED DEFAULT NULL COMMENT ‘Three digit telephone prefix (US Only).’,   PRIMARY KEY  (locId) ) ENGINE=MYISAM DEFAULT CHARSET=utf8

LOAD DATA LOCAL INFILE ‘D:/work/homepage/html/web_work/geolitecity/file/data/GeoLiteCity-Location.csv’ INTO TABLE geo_location  FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”’ LINES TERMINATED BY ‘\n’  IGNORE 2 LINES

LOAD DATA LOCAL INFILE ‘D:/work/homepage/html/web_work/geolitecity/file/data/GeoLiteCity-Blocks.csv’ INTO TABLE geo_block FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”’ LINES TERMINATED BY ‘\n’  IGNORE 2 LINES


참고 키사에서 제공하는 국내 IP주소 대역.(XML) http://ip.kisa.or.kr/ipas/jsp/api/ipv4list.jsp


🔗original-link

Updated: