0

I have an Intel i3-2350M CPU @ 2.30GHz with Windows 7, 64 bit, 4GByte Ram and enough free HD space.

The openstreetmap-file planet-latest.osm.bz2 is very big and compressed. (The version I have is 36 GByte compressed, and I believe it's like 100 GByte uncompressed. I have a mysql-database running, that I installed with XAMPP. (So I have apache and php available, too.)

My questions are:

  1. The OSM-project seems to have switched to postgresql and postgis. Is this really better (===faster) than MySql? (I strongly doubt that.)
  2. Is there a set of commands to get the compressed file into the database? Or would it reduce the risk of failure of the commands if I first decompress it? 2b. Could I discard of all XML-nodes that are not neccessary for the graphical output in the process of importing the data into the database? (With one stroke. :P )
  3. It's possible for me to convert the data to a .csv-file (out of the XML): Would that make it more complacent to get the data into the database?
  4. How long will the machine take? I can't let it run for hours without supervision. (I can break up the big file in smaller files, though I deem this to be error prone and not advisable.)

Greetings John

P.S. I have most if not all standard programs at hand that are linked ont he OSM-wiki. (Though the OSM-wiki is outright deficient and a bit outdated about the currently available tools.)

John
  • 605
  • 9
  • 28
  • 2
    MySQL cannot compete against PostGIS in terms of features. If you're doing a lot of GIS type work, use PostGIS. Is it faster? I haven't seen any benchmarks, the two are very hard to compare. Keep in mind if you need to work with large maps, on-demand cloud-servers, paid by the hour, help immeasurably. – tadman Mar 06 '15 at 22:41
  • [so] is not general technical support. It exists to answer specific questions in software development. You have too many questions here, and some of those are either opinion-based or just unanswerable (_How long will it take?_ How would we know?). If you have __specific__ question within the site scope we'll be happy to help. –  Mar 06 '15 at 22:47
  • @Hobo Sapiens: (Ty for the formatting.) It's really too many questions, but it's one workflow, and overall inseperable to have this questions all solved. Without positive answers for all 4 questions, the approach with mysql is dead. As tadman (Ty, too.) pointed out in his last sentence: It's all a questions of money in the end, and for that it must work. I have no time (literally) that I could give away on testing with 100+ GByte data set. --- About the runtime: That's why I put my system specs in. And any REAL WORLD reference run time would help. – John Mar 06 '15 at 22:58
  • Postgres is in general the [more modern DBMS](http://use-the-index-luke.com/de/blog/2015-02/modern-sql) than MySQL (or [here](http://stackoverflow.com/a/8182996/330315)) and especially when it comes to GIS features. –  Mar 07 '15 at 07:12
  • Are you sure you want to import the whole planet instead of a smaller [country or area extract](http://wiki.openstreetmap.org/wiki/Extract#Country_and_area_extracts)? – scai Mar 07 '15 at 09:45
  • at a_horse: The links seem interesting! Ty! - at scai: That's basically part of my question: Does it work with such a big dataset? :) I will try today or tomorrow with Mysql, then with postgres, and if that doesn't work well (and I assume it won't work too well), I will split it up. (In the end I will split the data anyway, but it would be very helpful and make things easier to be able to have fast access to the whole data set.) – John Mar 07 '15 at 15:22
  • I can't tell you if this works with MySQL. Almost nobody is using MySQL for OSM so you will have a hard time debugging possible problems, especially since most import tools are designed to work with PostgreSQL. – scai Mar 08 '15 at 09:49

1 Answers1

3

Open Street Map uses geographic information system features in postgreSQL that aren't available in MySQL. In particular, some of the operations use GIS map-projection features.

Here's a description of the various schemas and their requirements for database server make and model. http://wiki.openstreetmap.org/wiki/Databases#Database_Schemas

Both postgreSQL and MySQL are decently fast technologies on 64 bit platforms. For a database the size of global OSM, your performance will depend on fast storage as well as processing power.

It's hard to give advice about what technology might be faster without knowing a lot more about what you're trying to do. But I can say this: by using the technology created and tested by the open street map team, your nodes-per-month performance will be higher for at least a couple of months -- the months you would spend adapting the data.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • I have read through a couple of webpages now, and they all say things like that. But I have never come across a single run time comparison. I don'T need special features, I'm looking for a fast and power saving system. – John Mar 06 '15 at 23:06
  • It's a pity my question was deleted.(Is it??) Because I found one answer that alleges that OSM only changed because MySQL is now part of Oracle and that they want to use a fully OpenSource solution. (Whatever that means.) The data itself has a very easy structure. There should be not much to be optimized, regardless of the database used. Personally I heard that neither Mysql nor postgresql are the best databases for large data sets. And about the OSM-wiki: It's really maintained badly, and therefore unreliable on many things. The project seems to stall a little bit. – John Mar 07 '15 at 00:36
  • I forgot: Thank you for the reasonable feedback! Having gone through many webpages already without any page giving an answer about OSM-data performance in different databases, I'm going to give it one try with MySql, and if this doesn't work, I will try postgresql and I will look for a database especially for large datasets. – John Mar 07 '15 at 00:45