State Wise Data of Country and City

Where can I get a list of Countries, States and Cities?

The UN maintains a list of countries and "states" / regions for economic trade. That DB is available here: http://www.unece.org/cefact/locode/welcome.html

What is the best option to save country wise state and city information? XML or database?

XML is a very useful technology for moving data between different databases or between databases and other programs. However, it is not itself a database. XML is a data interchange format. One can have XML parsing libraries that can query a DOM with XPath expressions but that is not the same thing as a DBMS.

I would prefer to use any DBMS over XML.

Here I have found another stackoverflow question regarding this which has good answer:

“XML is not a database. It was never meant to be a database. It is
never going to be a database. Relational databases are proven
technology with more than 20 years of implementation experience. They
are solid, stable, useful products. They are not going away. XML is a
very useful technology for moving data between different databases or
between databases and other programs. However, it is not itself a
database. Don't use it like one.“

https://stackoverflow.com/questions/201568/when-would-i-use-xml-instead-of-sql#:~:text=XML%20is%20a%20very%20useful,is%20not%20itself%20a%20database.&text=XML%20is%20a%20data%20interchange,same%20thing%20as%20a%20DBMS.

You can create three tables:

Country (ID int, Name varchar(200));
State (ID int, Name varchar(200), CountryID int);
City (ID int, Name varchar(200), StateID int);

ID field of all three tables can be auto incremented primary key column. CountryID in State table will be foreign key to ID column in Country table and StateID in city table will be foreign key to ID column of State table.

You can also add code column to all three tables to add country code, state code and city code to you data.

How to get count of State and city of country using SQL query from database?

Because countries can have multiple states and each state can have multiple cities when you join these 1 to many and 1 to many many your state count is inflated. So you need the distinct count of state. The city count is already unique to country and state, thus doesn't need the distinct. where as state is not unique to country city, thus distinct is needed. This of course assumes you want the count of unique states in each country.

SELECT c.name, count(distinct s.name) as statecount,  count(Ci.name) as CityCount
FROM countries c
INNER JOIN states s 
  on c.id = s.country_ID
INNER JOIN cities ci
  ON s.id = ci.state_id
GROUP BY C.name

Or keeping your old style join notation:

SELECT c.name, count(distinct s.name) as statecount,  count(ci.name) citycount 
FROM countries c,states s,cities ci
WHERE ci.state_id = s.id 
  and s.country_id = c.id 
GROUP BY s.name

Consider the following example: http://rextester.com/ZGYF56786

or pictorially below

See when the joins occur between country, state and city. state gets repeated because of the join to city, making state no longer unique in that column, by doing a distinct we only return a count of 2 states instead of 7, one for each record.

+-----+------------+-------------+
| USA | Illinois   | Chicago     |
| USA | Illinois   | Springfield |
| USA | Illinois   | Peoria      |
| USA | California | LosAngeles  |
| USA | California | Sacramento  |
| USA | California | SanDeigo    |
| USA | California | Hollywood   |
| USA | California | Oakland     |
|-----|------------|-------------|
|Name | statecount | Citycount   |
| USA | 2          | 7           | <-- Is this result correct? (i hope so)
| USA | 7          | 7           | <-- or this one? (then why bother just count(*) and only 1 count needed.
+-----+------------+-------------+

I would think you want the 1st result since there are only 2 states in USA table listed and 7 cities.

Get all countries states and cities in one query

You need these 3 SQL statements:

All Cities:

SELECT cit.id, 'cities', cont.name, st.name, cit.name 
FROM countries cont 
INNER JOIN states st ON  cont.id = st.country_id 
INNER JOIN  join cities cit ON st.id = cit.state_id

All states:

SELECT stat.id, 'states', cont.name, st.name, '' 
FROM  countries cont 
INNER JOIN states st ON cont.id = st.country_id

All countries;

SELECT cont.id, 'countries', cont.name, '', '' FROM countries cont 

Then you can combine them all like

SELECT cit.id, 'cities', cont.name, st.name, cit.name 
FROM countries cont 
INNER JOIN states st ON  cont.id = st.country_id 
INNER JOIN  join cities cit ON st.id = cit.state_id

  UNION ALL

SELECT stat.id, 'states', cont.name, st.name, '' 
FROM  countries cont 
INNER JOIN states st ON cont.id = st.country_id

  UNION ALL

SELECT cont.id, 'countries', cont.name, '', '' FROM countries cont 

Ruby on Rails - Load State and city when Country and State is selected

You can do that in rails without using any gem.

f.collection_select(:state_id, State.all, :id, :name, {},{:data => {  :remote => true,
                :url => url_for(:controller => "states", 
                                :action => "display_cities")
            }
    }
)

In display_cities action, filter cities based on the state_id passed. Use display_cities.js.erb to populate the div element with new html generated.

---

Related Topics