Cities.txt

Raidergoo · 06-02-2006, 07:25 AM

Modding Cities.txt

Cities.txt is a massive 95,000+ line text file that lists most every city above 1000 people on the planet. It was compiled from census data.

Components

city, population, nation ID, state or province name, latitude, longitude

Example:

New York,8107916,206,New York,40.67,-73.94

New York = the city, in this case, the Big Apple
8107916 = the population, in this case, eight million people
206 = nation ID, in this case The United States
New York = the state, in this case New York
40.67 = 40.67 degrees North
-73.94 = 73.94 degrees West

Please note that latitude and longitude can be negative numbers. A negative number for latitude makes the latitude south of the equator. Think Australia. A negative number for longitude makes the longitude west of the Prime Meridian (in bloody England). Think USA.

Editing

Since this is a text file, you can easily edit a single line in Notepad, Microsoft Office, or in Open Office.

Make sure that you do NOT use a comma in a place name or in population, or put spaces between the records.

Good:
New York,8107916,206,New York,40.67,-73.94

Bad:
New York, 8,107,916, 206, New York, 40.67, -73.94

Intermediate Editing

Let's change New York City into the five constituent boroughs.

Locate the line for New York.

New York,8107916,206,New York,40.67,-73.94

Delete that line.

Add the following lines:

Brooklyn,2465326,206,New York,40.65,-73.95
Queens,2229379,206,New York40.74,-73.79
The Bronx,1332650,206,New York,40.85,-73.86
Staten Island,443728,206,New York,40.58,-74.15
Manhattan,1537195,206,New York,40.78,-73.96

Save your work, and the next universe you create will reflect the changes.

For other places, you'll have to do your own research on population, state or province name, longitude and latitude. The nation ID is located in nations.txt, which is in the same folder as cities.txt.

Advanced Editing

You will want to use Microsoft Excel for this. I found using Open Office very slow.

Microsoft Excel can't handle a file with as many lines as cities.txt. What I did was copy cities.txt, rename the copy, import the newly renamed file as a comma space delimited file, and get as many records into one sheet as possible. I located the break point, then wrote that down. I went back into the original file, located the break point, then copied the rest of the data into a new txt file. Next, I imported that text file as a new comma space delimited in a new Excel file. That gave me two Excel spreadsheets, one with 65,000 lines and one with 30,000 lines. I copied the data from the smaller file into a new workbook on the first Excel spreadsheet.

This should give you 1 Excel spreadsheet with two workbooks. Sheet one will be massive, topping 65,000 lines, and sheet two will be 30,000 lines.

You cant sort by columns and do some mass changes now.

Example

Building the Confederate States of America

This example depends on a change being made to nations.txt labeling the CSA as nation ID 227.

I sorted both spreadsheets by the state column. I located each state in the old Confederacy. I changed the nation ID for the first city in Arkansas to 227. I copied that line and pasted it to every city in every state in the old Confederacy on sheet one. I repeated this on sheet two.

I saved each sheet as a CSV under a new name. I opened the second CSV, copied all of the data, then pasted it into the first CSV.

enuttage · 06-03-2006, 03:38 PM

Quote:

Originally Posted by Raidergoo

Save your work, and the next universe you create will reflect the changes.

Just a quick question of clarification...

If we edit the file, it will NOT be reflected in currently operating universes. Is that correct?

McAllen, Texas went bye-bye in the game's files (tsk, tsk. That's a big city to miss

), so I gotta add it back in, but have already created the league.

This one's gonna be tough for folks who don't know their city doesn't exist until after they start the game and get a whole lot of other stuff set up.

If I'm misunderstanding, then awesome.

TIA,

-E

Raidergoo · 06-03-2006, 04:32 PM

Quote:

Originally Posted by enuttage

Just a quick question of clarification...

If we edit the file, it will NOT be reflected in currently operating universes. Is that correct?

McAllen, Texas went bye-bye in the game's files (tsk, tsk. That's a big city to miss

), so I gotta add it back in, but have already created the league.

This one's gonna be tough for folks who don't know their city doesn't exist until after they start the game and get a whole lot of other stuff set up.

-E

File edits of this file MUST take place before the universe starts.

McAllen,608,206,Texas,28.61,-97.8

http://www.allsimbaseball.com/index....d=18&Itemid=37

It was decided not to use an even bigger cities.txt file than the current one, so I turned the data I had into a mod. Follow the link and get the additional 14,000 US cities, if you wish.

enuttage · 06-03-2006, 06:45 PM

Quote:

Originally Posted by Raidergoo

File edits of this file MUST take place before the universe starts.

McAllen,608,206,Texas,28.61,-97.8

http://www.allsimbaseball.com/index....d=18&Itemid=37

It was decided not to use an even bigger cities.txt file than the current one, so I turned the data I had into a mod. Follow the link and get the additional 14,000 US cities, if you wish.

What's odd is that McAllen has well over 100,000 people in it per the 2000 census. Lord only knows how many that's ballooned to at this point. It's a very quickly growing area.

Thanks for the response. That's a bummer.

Raidergoo · 06-03-2006, 06:54 PM

http://factfinder.census.gov/servlet...g=en&pctxt=fph

. . .hmmm. . . .they SI file the game uses is supposed to handle everythin on Earth bigger than 1000 people. Looks like I will have to report that as an issue.

McAllen ,106414,206,TX,26.22,-98.24

http://www.allsimbaseball.com/index....d=18&Itemid=37

An alternate US only cities listing is at that site, and it looks like I got it right in that file.

I bet the listing for McAllen,608,206,Texas,28.61,-97.8 in my small cities of America file is a fragment of McAllen in a different county. Cities in multiple counties were a major pain to edit for the work I've done.

enuttage · 06-03-2006, 07:01 PM

The fragment must've somehow overrided the larger counterpart and made it filter out McAllen from the default file.

And, of course, since I'm an OCD-type, now I'm strongly considering re-starting my whole universe for just this one city.

lol.

I've definitely got some issues.

Raidergoo · 06-03-2006, 07:03 PM

Quote:

Originally Posted by enuttage

The fragment must've somehow overrided the larger counterpart and made it filter out McAllen from the default file.

And, of course, since I'm an OCD-type, now I'm strongly considering re-starting my whole universe for just this one city.

lol.

I've definitely got some issues.

Wait til you get to Brooklyn.

enuttage · 06-03-2006, 07:07 PM

Quote:

Originally Posted by Raidergoo

Wait til you get to Brooklyn.

Why's that?

This particular one is a Texas League, otherwise there's no way in hell I'd ever peg McAllen for a franchise.

But I'm out of other cities in the Valley! I'm using Edinburg as McAllen's home city right now, but I gotta fix this [pulls out hair].

Raidergoo · 06-03-2006, 07:09 PM

Quote:

Originally Posted by enuttage

Why's that?

There is no Brooklyn in OOTP 2006!

MrHaroldG · 06-06-2006, 02:59 AM

I noticed that in cases where there should be an "ó", there is a "z" instead (case in point: Córdoba/Czrdoba, Argentina).

johnph77 · 06-06-2006, 05:10 AM

For files with as many records as yours I'd suggest Corel's QuattroPro, WordPerfect's office suite spreadsheet. In versions 12 and X3 you can have over 1,000,000 records - if you have the RAM. The spreadsheet commands are very similar to Excel.

I don't think there's a better text editor on the market than UltraEdit. If the columns in the data are true columns, creating "csv" files are a snap with the columnar editing feature.

Raidergoo · 06-06-2006, 07:09 AM

I am downloading both apps suggested. Quattro is 230+ megs, so it will take up my little at home this AM. I will report back with results after June 7th.

Raidergoo · 06-06-2006, 10:22 PM

After testing Quattro, I will follow my mother's advice. If you can't say something nice, don't say it at all.

After scanning Ultra Edit's zip, for the first time in my life, my antivirus scanner went off. I have never seen Grisoft AVG in action before, and I am to never see it again.

UE was disposed of untested.

johnph77 · 06-07-2006, 04:17 AM

Re QuattroPro - I only use it in applications where I have more than 65,336 records I need displayed in columnar form.

Re UltraEdit - I have the CD version; I've never used the download. I apologize for any inconvenience on your part but IDM (supposedly) is a reputable company to deal with. I'm going to report your experience to them and will keep you posted of any response from them.

Raidergoo · 06-07-2006, 07:25 AM

I am curious how well Ultra Edit does with english.xml, so I appreciate what you are doing.

06-02-2006, 07:25 AM	#1
Raidergoo Hall Of Famer Join Date: Mar 2003 Posts: 9,004	Cities.txt Modding Cities.txt Cities.txt is a massive 95,000+ line text file that lists most every city above 1000 people on the planet. It was compiled from census data. Components city, population, nation ID, state or province name, latitude, longitude Example: New York,8107916,206,New York,40.67,-73.94 New York = the city, in this case, the Big Apple 8107916 = the population, in this case, eight million people 206 = nation ID, in this case The United States New York = the state, in this case New York 40.67 = 40.67 degrees North -73.94 = 73.94 degrees West Please note that latitude and longitude can be negative numbers. A negative number for latitude makes the latitude south of the equator. Think Australia. A negative number for longitude makes the longitude west of the Prime Meridian (in bloody England). Think USA. Editing Since this is a text file, you can easily edit a single line in Notepad, Microsoft Office, or in Open Office. Make sure that you do NOT use a comma in a place name or in population, or put spaces between the records. Good: New York,8107916,206,New York,40.67,-73.94 Bad: New York, 8,107,916, 206, New York, 40.67, -73.94 Intermediate Editing Let's change New York City into the five constituent boroughs. Locate the line for New York. New York,8107916,206,New York,40.67,-73.94 Delete that line. Add the following lines: Brooklyn,2465326,206,New York,40.65,-73.95 Queens,2229379,206,New York40.74,-73.79 The Bronx,1332650,206,New York,40.85,-73.86 Staten Island,443728,206,New York,40.58,-74.15 Manhattan,1537195,206,New York,40.78,-73.96 Save your work, and the next universe you create will reflect the changes. For other places, you'll have to do your own research on population, state or province name, longitude and latitude. The nation ID is located in nations.txt, which is in the same folder as cities.txt. Advanced Editing You will want to use Microsoft Excel for this. I found using Open Office very slow. Microsoft Excel can't handle a file with as many lines as cities.txt. What I did was copy cities.txt, rename the copy, import the newly renamed file as a comma space delimited file, and get as many records into one sheet as possible. I located the break point, then wrote that down. I went back into the original file, located the break point, then copied the rest of the data into a new txt file. Next, I imported that text file as a new comma space delimited in a new Excel file. That gave me two Excel spreadsheets, one with 65,000 lines and one with 30,000 lines. I copied the data from the smaller file into a new workbook on the first Excel spreadsheet. This should give you 1 Excel spreadsheet with two workbooks. Sheet one will be massive, topping 65,000 lines, and sheet two will be 30,000 lines. You cant sort by columns and do some mass changes now. Example Building the Confederate States of America This example depends on a change being made to nations.txt labeling the CSA as nation ID 227. I sorted both spreadsheets by the state column. I located each state in the old Confederacy. I changed the nation ID for the first city in Arkansas to 227. I copied that line and pasted it to every city in every state in the old Confederacy on sheet one. I repeated this on sheet two. I saved each sheet as a CSV under a new name. I opened the second CSV, copied all of the data, then pasted it into the first CSV.

06-03-2006, 06:54 PM	#5
Raidergoo Hall Of Famer Join Date: Mar 2003 Posts: 9,004	http://factfinder.census.gov/servlet...g=en&pctxt=fph . . .hmmm. . . .they SI file the game uses is supposed to handle everythin on Earth bigger than 1000 people. Looks like I will have to report that as an issue. McAllen ,106414,206,TX,26.22,-98.24 http://www.allsimbaseball.com/index....d=18&Itemid=37 An alternate US only cities listing is at that site, and it looks like I got it right in that file. I bet the listing for McAllen,608,206,Texas,28.61,-97.8 in my small cities of America file is a fragment of McAllen in a different county. Cities in multiple counties were a major pain to edit for the work I've done. Last edited by Raidergoo; 06-03-2006 at 07:00 PM.

06-03-2006, 07:01 PM	#6
enuttage Hall Of Famer Join Date: Mar 2002 Location: Hill Country Posts: 2,409	The fragment must've somehow overrided the larger counterpart and made it filter out McAllen from the default file. And, of course, since I'm an OCD-type, now I'm strongly considering re-starting my whole universe for just this one city. lol. I've definitely got some issues. __________________ The former GM of the WHBL Managua Four Roses "When you meet someone better than yourself, turn your thoughts to becoming his equal. When you meet someone not as good as you are, look within and examine your own self."

06-06-2006, 02:59 AM	#10
MrHaroldG Major Leagues Join Date: Nov 2002 Location: Chicago, IL Posts: 381	I noticed that in cases where there should be an "ó", there is a "z" instead (case in point: Córdoba/Czrdoba, Argentina). __________________ I take the expression "If you're scoring at home" literally. I do score at home.

06-06-2006, 10:22 PM	#13
Raidergoo Hall Of Famer Join Date: Mar 2003 Posts: 9,004	After testing Quattro, I will follow my mother's advice. If you can't say something nice, don't say it at all. After scanning Ultra Edit's zip, for the first time in my life, my antivirus scanner went off. I have never seen Grisoft AVG in action before, and I am to never see it again. UE was disposed of untested. Last edited by Raidergoo; 06-07-2006 at 07:24 AM.

06-06-2006, 05:10 AM	#11
johnph77 Minors (Single A) Join Date: Mar 2006 Posts: 67	For files with as many records as yours I'd suggest Corel's QuattroPro, WordPerfect's office suite spreadsheet. In versions 12 and X3 you can have over 1,000,000 records - if you have the RAM. The spreadsheet commands are very similar to Excel. I don't think there's a better text editor on the market than UltraEdit. If the columns in the data are true columns, creating "csv" files are a snap with the columnar editing feature.

06-06-2006, 07:09 AM	#12
Raidergoo Hall Of Famer Join Date: Mar 2003 Posts: 9,004	I am downloading both apps suggested. Quattro is 230+ megs, so it will take up my little at home this AM. I will report back with results after June 7th.

06-07-2006, 04:17 AM	#14
johnph77 Minors (Single A) Join Date: Mar 2006 Posts: 67	Re QuattroPro - I only use it in applications where I have more than 65,336 records I need displayed in columnar form. Re UltraEdit - I have the CD version; I've never used the download. I apologize for any inconvenience on your part but IDM (supposedly) is a reputable company to deal with. I'm going to report your experience to them and will keep you posted of any response from them.

06-07-2006, 07:25 AM	#15
Raidergoo Hall Of Famer Join Date: Mar 2003 Posts: 9,004	I am curious how well Ultra Edit does with english.xml, so I appreciate what you are doing.