Member Avatar for sankar113

i need a sql file for this Country ,state and city

  • 2 Contributors
  • 3 Replies
  • 141 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by showman13

Dani AI

Generated

asked for an SQL file to support Country / State / City dropdowns. pointed toward free dumps — a more complete, repeatable approach is to build a small normalized schema and populate it from a maintained place-name dump (GeoNames is a common source: GeoNames download). The schema below is compact, efficient for cascaded dropdowns, and keeps geography data reusable.

A minimal schema (example):

CREATE TABLE countries (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  iso2 CHAR(2) NOT NULL,
  iso3 CHAR(3),
  name VARCHAR(200) NOT NULL,
  geoname_id INT NULL,
  UNIQUE KEY (iso2)
);

CREATE TABLE states (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  country_id INT UNSIGNED NOT NULL,
  code VARCHAR(10),
  name VARCHAR(200) NOT NULL,
  geoname_id INT NULL,
  UNIQUE KEY (country_id, code),
  KEY (country_id)
);

CREATE TABLE cities (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  state_id INT UNSIGNED NULL,
  country_id INT UNSIGNED NOT NULL,
  name VARCHAR(200) NOT NULL,
  latitude DECIMAL(9,6),
  longitude DECIMAL(9,6),
  population INT DEFAULT 0,
  geoname_id INT NULL,
  KEY (country_id),
  KEY (state_id)
);

Import workflow notes: GeoNames provides countryInfo.txt, admin1CodesASCII.txt, and city files (tab-separated). Load countryInfo.txt into countries, parse admin1CodesASCII.txt to build states (admin1 codes are keyed by country.admin1), then import a cities file (cities15000 / cities500 / allCountries) mapping country_code + admin1 to countries.id and states.id. Use LOAD DATA INFILE (or mysqlimport / phpMyAdmin) for large files, and keep geoname_id as the stable external key.

Practical tips: use utf8mb4 charset, add indexes on columns used in WHERE/JOIN for fast dropdown queries (e.g., SELECT id,name FROM states WHERE country_id = ? ORDER BY name). Trim city data to needed size (population threshold) to save space. Cache state lists per country on the application side to avoid repeated DB hits. Verify dataset license terms on the provider site before redistribution.

Recommended Answers

All 3 Replies

Member Avatar for sankar113

some one please help it soon
thanks and warm regards,
sankar113

Member Avatar for showman13

Here is a site I found with a google search...

Hope it helps

Member Avatar for showman13

You could also check GeoBytes. They have a free downloadable database of the worlds cities with regions, countries, and coordinates (latitude/longitude). Just look for "GeoWorldMap" on the free services page.

Also MaxMind's free cities database, at maxmind.com
It's got cities, countries, states/provinces, latitude/longitude, and city populations.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.