this ungodly mess returns:

city,city_ascii,lat,lng,country_id,region_id 

It's a one-off query, I need to generate csv file for 2.5 million records so I can import into the db with foreign keys.

Any ideas? I think the loop for the sql queries is killing it.

<% @cities = City.find_by_sql "SELECT DISTINCT on (lat, lng) country, region, city, city_ascii, lat, lng FROM cities"  %>
<% @cities.each do |city| %>
  <%=city.city%>,<%=city.city_ascii%>,<%=city.lat%>,<%=city.lng%>,

<%@region = Region.find_by_sql "SELECT * FROM regions WHERE COUNTRY ~*'^#{city.country}$'
AND region ~*'^#{city.region}$' 
LIMIT 1" %>

<% @region.each do |region| %>

<%=region.id%>,
<% end %>

<% @country = Country.find_by_sql "SELECT * FROM countries WHERE code ~*'^#{city.country}$' 
LIMIT 1" %>
<% @country.each do |country| %>
<%=country.id%>
<% end %>
  
<% end %>


current db tables (these have auto-incrementing primary keys):

countries

:code,:name
A1,Anonymous Proxy,
A2,Satellite Provider,
AD,Andorra,
AE,United Arab Emirates,
AF,Afghanistan,
AG,Antigua and Barbuda,
AI,Anguilla,
AL,Albania,
AM,Armenia,

etc

regions

:country,:region,:name
CA,AB,Alberta
CA,BC,British Columbia
CA,MB,Manitoba
CA,NB,New Brunswick
CA,NL,Newfoundland
CA,NS,Nova Scotia
CA,NU,Nunavut
CA,ON,Ontario
CA,PE,Prince Edward Island

cities:

:country,:city,:city_ascii,:lat,:lng
ad,aixas,Aixàs,06,42.4833333,1.4666667
ad,aixirivali,Aixirivali,06,42.4666667,1.5
ad,aixirivall,Aixirivall,06,42.4666667,1.5
ad,aixirvall,Aixirvall,06,42.4666667,1.5
ad,aixovall,Aixovall,06,42.4666667,1.4833333

objective:

city,city_ascii,lat,lng,country_id,region_id 

above code accomplishes this when limited to one country, but the query is too large when done for all countries.