andriell/osm2sql

Library to import OSM (open street map) XML file into SQL database

Convert OSM XML file to SQL for MySQL

Quick start

Download OSM XML here https://download.bbbike.org/, _(*1)

Example 1

Convert OSM to SQL file 1. Create database tables from file resources/mysql.sql, _(*2)

1. Convert OSM to SQL inserts, _(*3)

   ini_set('memory_limit', '10M');
   // This class read big osm file
   $largeXmlReader = new LargeXmlReader();
   $largeXmlReader->setFilePath('/path/to/very/big/osm/xml/file.osm');
   
   // This class listens reader class and write data into file
   $listener = new FileReaderListener('/path/to/new/sql/file.sql');
   
   // Insert 500 rows in one operation
   $listener->setInsertSize(500);
   // Use INSERT IGNORE ...
   $listener->setInsertIgnore(true);
   $largeXmlReader->setListener($listener);
   $largeXmlReader->setProgressListener(function($readSize, $totalSize) use ($progress) {
       echo 'Completed ' . $readSize . ' Mb of ' . $totalSize . " Mb\n";
   });
   // Start parsing
   $largeXmlReader->parse();

2. Import sql file in to database, _(*4)

   mysql -u username -p database_name < /path/to/new/sql/file.sql

Example 2

You can create your ReaderListener and write data directly to the database, _(*5)

class DbReaderListener extends AbstractReaderListener
{
    // ...
    protected function write($table, $sql)
    {
        $this->myDbConnection->query($sql);
    }
}

// ...
$largeXmlReader->setListener(new DbReaderListener());
// ...

Example 3

Write data directly to the database and calculate building, highway, place tables, _(*6)

ini_set('memory_limit', '100M');

include 'vendor/autoload.php';

$file = '/path/to/very/big/osm/xml/file.osm';

// This class read big osm file
$largeXmlReader = new \osm2sql\LargeXmlReader();

// This class listens reader class and write data into database
$listener = new \osm2sql\mysql\PdoDbBuilder('mysql:host=localhost;dbname=osm;port=3306', 'user', 'password');
// Insert 100 rows in one operation
$listener->setInsertSize(100);
// Use INSERT IGNORE ...
$listener->setInsertIgnore(true);
$listener->setProgressListener(function ($size, $total) {
    echo 'Update DB ' . $size . ' row of ' . $total . " rows\n";
});
$listener->setExceptionListener(function($e, $sqlStr) {
    echo $sqlStr . "\n";
    echo $e . "\n";
});
// Delete all relations, way, node in database
$listener->deleteRelation();
$listener->deleteWay();
$listener->deleteNode();

$largeXmlReader->setFilePath($file);
$largeXmlReader->setListener($listener);
$largeXmlReader->setProgressListener(function($readSize, $totalSize) {
    echo  'Read file ' . $readSize . ' Mb of ' . $totalSize . " Mb\n";
});
// Insert new relations, way, node from osm file to database
$largeXmlReader->parse();

// Clear and calculate highway, building, place
$listener->deleteHighway();
$listener->calculateHighway();
$listener->deleteBuilding();
$listener->calculateBuilding();
$listener->deletePlace();
$listener->calculatePlace();

echo 'Done';

You can create your DbBuilder class and use database connection in your framework, _(*7)

class MyDbBuilder extends \osm2sql\mysql\AbstractDbBuilder
{
    // ...
    protected function querySelect($sqlStr)
    {
        $rows = $this->myDbConnection->query($sqlStr);
        $r = [];
        foreach ($rows as $row) {
            $r[] = (array) $row;
        }
        return $r;
    }

    protected function queryUpdate($sqlStr)
    {
        return (int) $this->myDbConnection->exec($sqlStr);
    }
}