How to: Semi-automatic real world scenario generation

[TinyMusic]

There'll be another update to the tutorial tomorrow.
I compiled a single spreadsheet with all the cities in the world (about 200000 of them) that will make the whole task even easier, because you don't need to collect data for multiple countries if you make a scenario that spans a big area. ... Steps 8 to 11 will basically be unnecessary and some others become less complicated. Stay tuned

Thank you!

[Zanman777]

Man, it's really nice to see someone so motivated massively contributing for the Openttd community. Terrific job, moki. I also feel like digging in and helping construct the new scenario format, but I've been building up the guts to take the bull by its horns You on the other hand are on full throttle!

[moki]

Update in the first post!
I think, it doesn't get much easier anymore... at least until I get that fully automatic csv parser ready.

Motivation is easy when you got a goal that's useful for yourself or at least potentially fun. Rearranging spreadsheets is not fun, but being able to make any real world region into a playable OpenTTD scenario relatively fast is. Or maybe it's just me who's genuinely happy about learning new stuff every day

The next steps will be:
1. Learn to write in proper wiki style and make the tutorial available there. Shouldn't take more than a few days.
2. Write an automatic tool to do all the collecting, compiling and sorting of city and map data. May take a few weeks to months, because my last programming experience was in QBasic and more than 10 years ago
2. Get involved in the new scenario format, get it working and into OpenTTD trunk. I don't even want to think of a time frame, but it will happen some day.

[SwissFan91]

This sounds awesome - I will give it a proper run out later.

Regarding your proposed timescales, don't be afraid to ask for help. Chances are someone around here will be able to assist you.

[moki]

Sure, the people around here are very helpful. My problem is more about some real-life obligations that tend to take up a good bit of time that could be used for openTTD

Anyway... there's a tiny update for people who use my windows build. Please download it again.

I just replaced the windows build in the first post with one that is patched onto 1.3.2 stable. The patch works the same, but now you'll be able to actually play the maps with versions other than the latest nightlies. Creating a scenario in a newer version than the one you actually play with simply doesn't work. Forward compatibility never was OpenTTD's strength. With the current download, you can play your scenarios on any version newer than 1.3.2.
If you patch and compile yourself, I also recommend using 1.3.2 as the base. The patch needs no changes, because the console_cmds.cpp wasn't changed in a long time.

[R2dical]

I keep an eye out for opportunities such as this to practise my vba skillz and large database + file i/o is a nice workout

Anyway I made an excel spread sheet + macro which will create and export a txt file (exactly) as moki describes with steps 8 to 18 with the push of a button.
Its got a page with the parameters as described in the steps, and some additional info. There is not much in the way of error-handling though so be careful...exporting a full database by mistake with this script...will not be pretty. There is a check (500 max) but still. Not really tested so let me know if there are any bugs.

Also, it is an Excel spreadsheet only, unfortunately the OO scripting is not up to scratch yet...

EDIT: You may get an error (some thing like "compile error"...), this means that you need to activate a reference .dll. There is info in the window that shows when you crash (the VBA tool) if you scroll up.

linky EDIT: Removed link due to new version.

[moki]

Woah, nice work, R2dical. Scripting and macros in excel/calc are a complete mystery to me. Didn't know, such things were even possible.
I can't test it right now, because I don't have Microsoft Excel on this computer, but I'll try it on a friend's machine soon. I'd prefer a version for OpenOffice, because it's, well, open... I'll look around if it's possible to convert these things somehow. Maybe this is even better than a standalone program for this task.

[R2dical]

Yeah VBA for Excel is a powerful tool, because you can use and manipulate excel objects (like worksheets and cells) and functions (like autofilter, sort, formulas, etc) in tandem with normal programming language features as well as windows libraries all together right off the bat. I have worked on a number of projects similar to this and always end up using excel for its powerful speadsheet abilities. This macro was 300 lines of code, I estimate a C++ program would be 5 - 10x that for similar or less features. Never mind the less flexibility and error handling.

On the other hand this does tie you to Windows and Office somewhat. Open Office as I said has macro scripting features but are not nearly as advanced as MSOffice, I doubt even this little script could be duplicated (due to the somersalts needed for Unicode without BOM). That said OO does have a VBA compatibility project which may get this to work without too much effort. My script does use VBA7 code to deal with 64bit MSOffice but this can be easily removed, OO VBA project doesn't support VBA7 yet.

I found a few bugs after a proper test, so here is a new version. I included a pic of the GUI with the Jamaica example and some extra comments to hopefully explain better. Also in there is the source code for the macro if anyone want to try port to OO or replicate the functions in another language.

linky - The CityDatabase spreadsheet v1.1

Edit: Upped wrong version, fixed.

[Zuu]

I'd prefer a version for OpenOffice, because it's, well, open...

If you want something open, why not write a Python script? It is fairly portable and if you don't yet know Python you will probably not regret learning it. It may not be the "best" programming language out there, but it is very wide spread and a useful language to know.

[moki]

Ok, I installed a trial version of Office 2013 to try this. Powerful it may be, but doesn't work for me.

I just tried it with the standard coordinates for Jamaica and min. population 10000 and always get "more than 500 towns, nothing gets exported", though there are only 88 towns listed for Jamaica of which 16 have over 10000 inhabitants. I have no idea what exactly happens, but obviously the script tries to include towns that it shouldn't.

@Zuu:
Already got that suggestion and I'm looking into it. c++ seems a bit too powerful for such a simple task as sorting some data. It's like building a high-powered laser to kill cockroaches - works well enough, but it's not necessarily a good idea Learning c++, python, polish (ok, that one has nothing to do with OpenTTD) and a whole lot of facts about geography and data collection at the same time while still attending the so called real life is quite time consuming.

[R2dical]

Ok, I installed a trial version of Office 2013 to try this. Powerful it may be, but doesn't work for me.

Powerful it is, bug free my script isn't...

Though I did catch the one you speak of in my v1.1 post above Zuu's. I also increased the max town list to 5000 in this so if you got a msg saying 500 and not 5000 you need to get the updated version. Also if you used the "Min Pop (X)" under "City" to try filter for min population of 10000 then what this is doing is instead making each town with pop > 10000 a "city = 1" for OpenTTD. The one you are looking for is "Smallest" under "Population" and "Filter".

Using your variables in v1.1 I get a file of:

Code: Select all

18.7,-75.95,17.45,-78.58
Kingston,L,1,17.99702,-76.79358
New Kingston,L,1,18.00747,-76.78319
Spanish Town,L,1,17.99107,-76.95742
Portmore,L,1,17.97024,-76.86722
Montego Bay,L,1,18.47116,-77.91883
Mandeville,L,1,18.04168,-77.50714
May Pen,L,1,17.96454,-77.24515
Old Harbour,L,0,17.94144,-77.10898
Linstead,L,0,18.13683,-77.03171
Half Way Tree,M,0,18.01248,-76.79928
Savanna-la-Mar,M,0,18.21895,-78.13320
Saint Ann’s Bay,M,0,18.43581,-77.20098
Bog Walk,M,0,18.10205,-77.00541
Constant Spring,M,0,18.05078,-76.79372
Port Antonio,M,0,18.17998,-76.46121
Ewarton,M,0,18.18084,-77.07664

[moki]

Ok, I did accidentally use the old version.
Downloaded the new one and still get the same (though with > 5000 instead of > 500 now). For clarification, I attached a screenshot of the exact settings I used. Don't worry about the commas - that's just my local style setting and should not influence the calculations. Am I a bit dumb today or is there just no friendship between me and Excel?

[zytok]

Inspired by Moki, I've done a England and Wales map which allows more space to put towns and cities in the correct place without too much overlap. This includes every town with a population of over 25000 and all towns are scaled correctly so 25000 real world population = 250 in-game population. I've only not been able to fit in the borough of Islington as there are just too many to fit in. Any suggestions/comments welcome.

[Yisc]

@moki: I haven't looked into this in details, but using comma's where dots are supposed, could get you into trouble.
Did you tried to use dots as seperator?
In the end this should be solved by the creator of the macro, as it is common for people to use both comma's and dots as seperators (based on where they live)

[Eddi]

commas and dots should automatically be handled by your windows locale settings.

[moki]

What Eddi said is exactly what I meant. Because my standard locale is German, everything gets changed to comma instead of point - the whole database, formulas and everything else. Excel knows that the content is the same, only the style is different on different systems.

[R2dical]

Ahh, yes. Locale. A tricky thing this and tbh I did not consider it.

The problem is that there are "layers" of stuff between what is entered in the cell and the actual autofilter applied in the macro. Windows and MS Excel "should" be able to sort it out but I did not give it the help it needs to do so reliably.

It should be a fairly simple fix, if you don't mind testing it your side. My locale settings don't allow commas to be recognised as number delims.
- For all the white cells on the main page using numbers as values (i.e every one except the file name box).
- Format->Format cells and select "number" on the left. Choose an appropriate number of decimal places. Probably 2 for lat/long, 0 for the rest.
- In the "Database" tab, select entire rows D,E and G and format to "number" also. For Latitude and Longitude you might want to set a longer decimal count as how the result looks is what is written in the file. Probably 6 for lat/long and 0 for population.

This should help Excel recognise numbers instead of text and do the right filter. It sounds like your filters are not registering, so thats why you are getting the error msg. Good thing I put that check in I guess, as trying to export all 198716 towns will lock up your pc for at least an hour

On my side, using periods (.) where you have commas but otherwise all settings the same I get a correct file. If the above does not work with commas for you, but does with periods let me know and I will try fix.

EDIT: It will be fine to save after the formatting, but before running the macro.

[BoyInGreen]

Looks impressive. Can't wait to try it, but for some reason it won't work for me. Here is the crash log:

[girth]

Superb work in this thread!!


Just taking a look at some other resources, it might not be too hard to automate this process (of course, easier said than done ).

Geonames has a JSON webservice which you can easily get a dump of the cities using the points you pass in: http://www.geonames.org/export/JSON-webservices.html
Once you've loaded this data into an array, you can massage it how you like.

Just taking a look at some sites out there that provide elevation data, and export in png format.
If you can source from both places, you could have a scenario that sucks out data and cities instantly from the net. At the very least, the data could be locally downloaded, and you just source it from there.

This site here looks interesting: http://www.naturalearthdata.com/ I wonder if you can get an image of the terrain, then the water, etc... could tie in with that format that Terkhen and co. have gone about defining...?

[girth]

Not sure if we should merge the two threads, as they are rather similar, but this guy has found away get images fairly quickly: http://www.tt-forums.net/viewtopic.php?f=60&t=66489


Use their ArcGIS Map Viewer thingy, pan around, and then click Measure on the toolbar, then Location, and click anywhere on the map, and it gives you the long and lat of that point.

Get the bottom-left point and the top-right point of the section you need, drop in into the exportImage thingy as the bounding box (should look something like "121.975,23.690,149.221,46.653").

Using GIMP, I used the Curves tool and increased the strength of the lower-end greys to make the ground a little less extreme in gradient.

Then, when getting the list of names for a country, you can use those points: http://api.geonames.org/cities?north=46.653&south=23.690&east=149.221&west=121.975&username=demo