Importing OSM into MySQL

RouteXL · 2010-11-05T07:57:16.000Z

I did not find all the information I needed to import an OSM file into a MySQL database, so I’ll share what I used.

Here is the database setup:


CREATE TABLE changeset_tags (
    id bigint NOT NULL, -- references changesets(id)
    k VARCHAR(255) DEFAULT '',
    v VARCHAR(255) DEFAULT ''
);
 
CREATE TABLE changesets (
    id bigint NOT NULL, -- autoincrement primary key
    user_id bigint NOT NULL, -- references users(id)
    created_at timestamp ,
    min_lat integer,
    max_lat integer,
    min_lon integer,
    max_lon integer,
    closed_at timestamp ,
    num_changes integer DEFAULT 0 NOT NULL
);

## Nodes

CREATE TABLE current_node_tags (
    id bigint NOT NULL, -- primary key part 1/2; references current_nodes(id)
    k VARCHAR(255) DEFAULT '', -- primary key part 2/2
    v VARCHAR(255) DEFAULT ''
);
 
CREATE TABLE current_nodes (
    id bigint NOT NULL, -- autoincrement primary key
    latitude integer NOT NULL,
    longitude integer NOT NULL,
    changeset_id bigint NOT NULL, -- references changesets(id)
    visible BOOLEAN NOT NULL,
    `timestamp` timestamp ,
    tile bigint NOT NULL,
    version bigint NOT NULL
);
 
CREATE TABLE node_tags (
    id bigint NOT NULL, -- primary key part 1/3; references nodes(id,version) part 1/2
    version bigint NOT NULL, -- primary key part 2/3; references nodes(id,version) part 2/2
    k VARCHAR(255) DEFAULT '', -- primary key part 3/3
    v VARCHAR(255) DEFAULT ''
);
 
CREATE TABLE nodes (
    id bigint NOT NULL, -- primary key part 1/2
    latitude integer NOT NULL,
    longitude integer NOT NULL,
    changeset_id bigint NOT NULL, -- references changesets(id)
    visible BOOLEAN NOT NULL,
    `timestamp` timestamp ,
    tile bigint NOT NULL,
    version bigint NOT NULL -- primary key part 2/2
);

## Ways

CREATE TABLE current_way_nodes (
    id bigint NOT NULL, -- primary key part 1/2; references current_ways(id)
    node_id bigint NOT NULL, -- references current_nodes(id)
    sequence_id bigint NOT NULL -- primary key part 2/2
);
 
CREATE TABLE current_way_tags (
    id bigint NOT NULL, -- primary key part 1/2; references current_ways(id)
    k VARCHAR(255) DEFAULT '', -- primary key part 2/2
    v VARCHAR(255) DEFAULT ''
);
 
CREATE TABLE current_ways (
    id bigint NOT NULL, -- autoincrement primary key
    changeset_id bigint NOT NULL, -- references changesets(id)
    `timestamp` timestamp ,
    visible BOOLEAN NOT NULL,
    version bigint NOT NULL
);
 
CREATE TABLE way_nodes (
    id bigint NOT NULL, -- primary key part 1/3; references ways(id, version) part 1/2
    node_id bigint NOT NULL,
    version bigint NOT NULL, -- primary key part 2/3; references ways(id, version) part 2/2
    sequence_id bigint NOT NULL -- primary key part 3/3
);
 
CREATE TABLE way_tags (
    id bigint DEFAULT 0 NOT NULL, -- primary key part 1/3; references ways(id, version) part 1/2
    k VARCHAR(255) NOT NULL, -- primary key part 3/3
    v VARCHAR(255) NOT NULL,
    version bigint NOT NULL -- primary key part 2/3 -- references ways(id, version) part 2/2
);
 
CREATE TABLE ways (
    id bigint DEFAULT 0 NOT NULL, -- primary key part 1/2
    changeset_id bigint NOT NULL, -- references changesets(id)
    `timestamp` timestamp ,
    version bigint NOT NULL, -- primary key part 2/2
    visible BOOLEAN DEFAULT true NOT NULL
);

## Relations

CREATE TABLE current_relation_members (
    id bigint NOT NULL, -- primary key part 1/5; references current_relations(id)
    member_type INT NOT NULL, -- primary key part 2/5
    member_id bigint NOT NULL, -- primary key part 3/5
    member_role VARCHAR(255) NOT NULL, -- primary key part 4/5
    sequence_id integer DEFAULT 0 NOT NULL -- primary key part 5/5
);
 
CREATE TABLE current_relation_tags (
    id bigint NOT NULL, -- primary key part 1/2; references current_relations(id)
    k VARCHAR(255) DEFAULT '', -- primary key part 2/2
    v VARCHAR(255) DEFAULT ''
);
 
CREATE TABLE current_relations (
    id bigint NOT NULL, -- autoincrement primary key
    changeset_id bigint NOT NULL, -- references changesets(id)
    `timestamp` timestamp ,
    visible BOOLEAN NOT NULL,
    version bigint NOT NULL
);
 
CREATE TABLE relation_members (
    id bigint DEFAULT 0 NOT NULL, -- primary key part 1/5; references relations(id, version) part 1/2
    member_type INT NOT NULL, -- primary key part 3/6
    member_id bigint NOT NULL, -- primary key part 4/6
    member_role VARCHAR(255) NOT NULL, -- primary key part 5/6
    version bigint DEFAULT 0 NOT NULL, -- primary key part 2/6; references relations(id, version) part 2/2
    sequence_id integer DEFAULT 0 NOT NULL -- primary key part 6/6
);
 
CREATE TABLE relation_tags (
    id bigint DEFAULT 0 NOT NULL, -- primary key part 1/3; references relations(id, version) part 1/2
    k VARCHAR(255) DEFAULT '', -- primary key part 3/3
    v VARCHAR(255) DEFAULT '',
    version bigint NOT NULL -- primary key part 2/3; references relations(id, version) part 2/2
);
 
CREATE TABLE relations (
    id bigint DEFAULT 0 NOT NULL, -- primary key part 1/2
    changeset_id bigint NOT NULL, -- references changesets(id)
    `timestamp` timestamp ,
    version bigint NOT NULL, -- primary key part 2/2
    visible BOOLEAN DEFAULT true NOT NULL
);

##  Other geodata tables

CREATE TABLE countries (
    id integer NOT NULL, -- autoincrement primary key
    code VARCHAR(2) NOT NULL,
    min_lat double precision NOT NULL,
    max_lat double precision NOT NULL,
    min_lon double precision NOT NULL,
    max_lon double precision NOT NULL
);

## GPX tables

CREATE TABLE gps_points (
    altitude double precision,
    trackid integer NOT NULL,
    latitude integer NOT NULL,
    longitude integer NOT NULL,
    gpx_id bigint NOT NULL, -- references gpx_files(id)
    `timestamp` timestamp ,
    tile bigint
);
 
CREATE TABLE gpx_file_tags (
    gpx_id bigint DEFAULT 0 NOT NULL, -- references gpx_files(id)
    tag VARCHAR(255) NOT NULL,
    id bigint NOT NULL -- autoincrement primary key
);
 
CREATE TABLE gpx_files (
    id bigint NOT NULL, -- autoincrement primary key
    user_id bigint NOT NULL, -- references users(id)
    visible BOOLEAN DEFAULT true NOT NULL,
    name VARCHAR(255) DEFAULT '',
    size bigint,
    latitude double precision,
    longitude double precision,
    `timestamp` timestamp ,
    description VARCHAR(255) DEFAULT '',
    inserted BOOLEAN NOT NULL,
    visibility INT DEFAULT 0 NOT NULL
);

## Administrative tables

CREATE TABLE acls (
    id integer NOT NULL, -- autoincrement primary key
    address VARCHAR(255) NOT NULL,
    netmask VARCHAR(255) NOT NULL,
    k VARCHAR(255) NOT NULL,
    v VARCHAR(255)
);
 
CREATE TABLE client_applications (
    id integer NOT NULL, -- autoincrement primary key
    name VARCHAR(255),
    url VARCHAR(255),
    support_url VARCHAR(255),
    callback_url VARCHAR(255),
    `key` VARCHAR(50),
    secret VARCHAR(50),
    user_id integer, -- references users(id)
    created_at timestamp ,
    updated_at timestamp ,
    allow_read_prefs BOOLEAN DEFAULT false NOT NULL,
    allow_write_prefs BOOLEAN DEFAULT false NOT NULL,
    allow_write_diary BOOLEAN DEFAULT false NOT NULL,
    allow_write_api BOOLEAN DEFAULT false NOT NULL,
    allow_read_gpx BOOLEAN DEFAULT false NOT NULL,
    allow_write_gpx BOOLEAN DEFAULT false NOT NULL
);
 
CREATE TABLE diary_comments (
    id bigint NOT NULL, -- autoincrement primary key
    diary_entry_id bigint NOT NULL, -- references diary_entries(id)
    user_id bigint NOT NULL,
    body text NOT NULL,
    created_at timestamp ,
    updated_at timestamp 
);
 
CREATE TABLE diary_entries (
    id bigint NOT NULL, -- autoincrement primary key
    user_id bigint NOT NULL, -- references users(id)
    title VARCHAR(255) NOT NULL,
    body text NOT NULL,
    created_at timestamp ,
    updated_at timestamp ,
    latitude double precision,
    longitude double precision,
    language_code VARCHAR(255) DEFAULT 'en' -- references languages(code)
);
 
CREATE TABLE friends (
    id bigint NOT NULL, -- autoincrement primary key
    user_id bigint NOT NULL, -- references users(id)
    friend_user_id bigint NOT NULL -- references users(id)
);
 
CREATE TABLE languages (
    code VARCHAR(255) NOT NULL, -- primary key
    english_name VARCHAR(255) NOT NULL,
    native_name VARCHAR(255)
);
 
CREATE TABLE messages (
    id bigint NOT NULL, -- autoincrement primary key
    from_user_id bigint NOT NULL, -- references users(id)
    title VARCHAR(255) NOT NULL,
    body text NOT NULL,
    sent_on timestamp ,
    message_read BOOLEAN DEFAULT false NOT NULL,
    to_user_id bigint NOT NULL, -- references users(id)
    to_user_visible BOOLEAN DEFAULT true NOT NULL,
    from_user_visible BOOLEAN DEFAULT true NOT NULL
);
 
CREATE TABLE oauth_nonces (
    id integer NOT NULL, -- autoincrement primary key
    nonce VARCHAR(255),
    `timestamp` integer,
    created_at timestamp ,
    updated_at timestamp 
);
 
CREATE TABLE oauth_tokens (
    id integer NOT NULL, -- autoincrement primary key
    user_id integer, -- references users(id)
    type VARCHAR(20),
    client_application_id integer, -- references client_applications(id)
    token VARCHAR(50),
    secret VARCHAR(50),
    authorized_at timestamp ,
    invalidated_at timestamp ,
    created_at timestamp ,
    updated_at timestamp ,
    allow_read_prefs BOOLEAN DEFAULT false NOT NULL,
    allow_write_prefs BOOLEAN DEFAULT false NOT NULL,
    allow_write_diary BOOLEAN DEFAULT false NOT NULL,
    allow_write_api BOOLEAN DEFAULT false NOT NULL,
    allow_read_gpx BOOLEAN DEFAULT false NOT NULL,
    allow_write_gpx BOOLEAN DEFAULT false NOT NULL
);
 
CREATE TABLE schema_migrations (
    version VARCHAR(255) NOT NULL
);
 
CREATE TABLE sessions (
    id integer NOT NULL, -- autoincrement primary key
    session_id VARCHAR(255),
    DATA text,
    created_at timestamp ,
    updated_at timestamp 
);
 
CREATE TABLE user_blocks (
    id integer NOT NULL, -- autoincrement primary key
    user_id bigint NOT NULL, -- references users(id)
    creator_id bigint NOT NULL, -- references users(id)
    reason text NOT NULL,
    ends_at timestamp ,
    needs_view BOOLEAN DEFAULT false NOT NULL,
    revoker_id bigint, -- references users(id)
    created_at timestamp ,
    updated_at timestamp 
);
 
CREATE TABLE user_preferences (
    user_id bigint NOT NULL, -- primary key part 1/2; references users(id)
    k VARCHAR(255) NOT NULL, -- primary key part 2/2
    v VARCHAR(255) NOT NULL
);
 
CREATE TABLE user_roles (
    id integer NOT NULL, -- autoincrement primary key
    user_id bigint NOT NULL, -- references users(id)
    created_at timestamp ,
    updated_at timestamp ,
    role INT NOT NULL,
    granter_id bigint NOT NULL -- references users(id)
);
 
CREATE TABLE user_tokens (
    id bigint NOT NULL, -- autoincrement primary key
    user_id bigint NOT NULL, -- references users(id)
    token VARCHAR(255) NOT NULL,
    expiry timestamp ,
    referer text
);
 
CREATE TABLE users (
    email VARCHAR(255) NOT NULL,
    id bigint NOT NULL, -- autoincrement primary key
    active integer DEFAULT 0 NOT NULL,
    pass_crypt VARCHAR(255) NOT NULL,
    creation_time timestamp ,
    display_name VARCHAR(255) DEFAULT '',
    data_public BOOLEAN DEFAULT false NOT NULL,
    description text DEFAULT '' NOT NULL,
    home_lat double precision,
    home_lon double precision,
    home_zoom smallint DEFAULT 3,
    nearby integer DEFAULT 50,
    pass_salt VARCHAR(255),
    image text,
    email_valid BOOLEAN DEFAULT false NOT NULL,
    new_email VARCHAR(255),
    visible BOOLEAN DEFAULT true NOT NULL,
    creation_ip VARCHAR(255),
    languages VARCHAR(255)
);

And the Osmosis command:


osmosis --read-xml enableDateParsing=no file="YOUR_OSM_IMPORT_FILE" --buffer --write-apidb dbType="mysql" host="HOSTNAME" database="DATABASE_NAME" user="USERNAME" password="PASSWORD" validateSchemaVersion=no

ThePromenader · 2011-03-25T12:24:34.000Z

Thank you so much! I was getting ready to make a php script to do all that, but you pointed me in the right way - thanks!

Why are you using mySql? Myself, I need it to a) generate KML for only certain types of data (namely buildings) and b) ensuring that the polygons generated match OSM’s data (there are few (or no) reliable OSM to KML translators out there - all I’ve tried dropped multipolygon info (polygons with holes, for example). Cheers!

wyo · 2011-03-29T08:33:58.000Z

Very interesting. Did you also try to update the database via the daily change files? It would be nice to have a PHP solution for updates.

Wyo

RouteXL · 2011-08-19T18:08:11.000Z

@ThePromenader: I needed a fast way to geocode addresses in my php/mysql environment, so I tried this hoping I could do some kind of string matching. But it didn’t work out.

@wyo: no, I did not…

xpto_hel · 2011-09-07T10:29:53.000Z

thanks a lot for the info!
Will give it a try today.

My goal is to find all routes name from a osm file, imagine i have a osm file from my city and i want to extract all routes name.

If someone can point me the right way i appreciate, if not i will scratch after the import is made.

Again,
THanks a lot

system · 2011-09-10T22:21:00.000Z

wyo:

Very interesting. Did you also try to update the database via the daily change files? It would be nice to have a PHP solution for updates.

Wyo

agreed on the PHP updates, I wonder how we make this happen? I have been studying android application development and different placement options via IT staffing and how I could potentially make my own version of an open street maps app if a business asked me to perform that task as part of my IT duties. What do you guys think of this idea? I have mad skills I tell ya, just need a place to start my career and start making some money working with computers.

pietro_m · 2013-02-20T13:25:38.000Z

The database setup proposed by pmars does’nt work for me (osmosis 0.46, .osm file generated in february 2013).
I needed to adjust the names of some fields in the database tables. Here is the database schema that worked for me


-- phpMyAdmin SQL Dump
-- version 2.11.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generato il: 20 Feb, 2013 at 02:12 PM
-- Versione MySQL: 5.0.45
-- Versione PHP: 5.2.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `osm`
--

-- --------------------------------------------------------

--
-- Struttura della tabella `acls`
--

CREATE TABLE IF NOT EXISTS `acls` (
  `id` int(11) NOT NULL,
  `address` varchar(255) NOT NULL,
  `netmask` varchar(255) NOT NULL,
  `k` varchar(255) NOT NULL,
  `v` varchar(255) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `acls`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `changesets`
--

CREATE TABLE IF NOT EXISTS `changesets` (
  `id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `min_lat` int(11) default NULL,
  `max_lat` int(11) default NULL,
  `min_lon` int(11) default NULL,
  `max_lon` int(11) default NULL,
  `closed_at` timestamp NOT NULL default '0000-00-00 00:00:00',
  `num_changes` int(11) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `changesets`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `changeset_tags`
--

CREATE TABLE IF NOT EXISTS `changeset_tags` (
  `changeset_id` bigint(20) NOT NULL,
  `k` varchar(255) default '',
  `v` varchar(255) default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `changeset_tags`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `client_applications`
--

CREATE TABLE IF NOT EXISTS `client_applications` (
  `id` int(11) NOT NULL,
  `name` varchar(255) default NULL,
  `url` varchar(255) default NULL,
  `support_url` varchar(255) default NULL,
  `callback_url` varchar(255) default NULL,
  `key` varchar(50) default NULL,
  `secret` varchar(50) default NULL,
  `user_id` int(11) default NULL,
  `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL default '0000-00-00 00:00:00',
  `allow_read_prefs` tinyint(1) NOT NULL default '0',
  `allow_write_prefs` tinyint(1) NOT NULL default '0',
  `allow_write_diary` tinyint(1) NOT NULL default '0',
  `allow_write_api` tinyint(1) NOT NULL default '0',
  `allow_read_gpx` tinyint(1) NOT NULL default '0',
  `allow_write_gpx` tinyint(1) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `client_applications`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `countries`
--

CREATE TABLE IF NOT EXISTS `countries` (
  `id` int(11) NOT NULL,
  `code` varchar(2) NOT NULL,
  `min_lat` double NOT NULL,
  `max_lat` double NOT NULL,
  `min_lon` double NOT NULL,
  `max_lon` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `countries`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `current_nodes`
--

CREATE TABLE IF NOT EXISTS `current_nodes` (
  `node_id` bigint(20) NOT NULL,
  `latitude` int(11) NOT NULL,
  `longitude` int(11) NOT NULL,
  `changeset_id` bigint(20) NOT NULL,
  `visible` tinyint(1) NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `tile` bigint(20) NOT NULL,
  `version` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `current_nodes`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `current_node_tags`
--

CREATE TABLE IF NOT EXISTS `current_node_tags` (
  `node_id` bigint(20) NOT NULL,
  `k` varchar(255) default '',
  `v` varchar(255) default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `current_node_tags`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `current_relations`
--

CREATE TABLE IF NOT EXISTS `current_relations` (
  `relation_id` bigint(20) NOT NULL,
  `changeset_id` bigint(20) NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `visible` tinyint(1) NOT NULL,
  `version` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `current_relations`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `current_relation_members`
--

CREATE TABLE IF NOT EXISTS `current_relation_members` (
  `relation_id` bigint(20) NOT NULL,
  `member_type` int(11) NOT NULL,
  `member_id` bigint(20) NOT NULL,
  `member_role` varchar(255) NOT NULL,
  `sequence_id` int(11) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `current_relation_members`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `current_relation_tags`
--

CREATE TABLE IF NOT EXISTS `current_relation_tags` (
  `relation_id` bigint(20) NOT NULL,
  `k` varchar(255) default '',
  `v` varchar(255) default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `current_relation_tags`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `current_ways`
--

CREATE TABLE IF NOT EXISTS `current_ways` (
  `way_id` bigint(20) NOT NULL,
  `changeset_id` bigint(20) NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `visible` tinyint(1) NOT NULL,
  `version` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `current_ways`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `current_way_nodes`
--

CREATE TABLE IF NOT EXISTS `current_way_nodes` (
  `way_id` bigint(20) NOT NULL,
  `node_id` bigint(20) NOT NULL,
  `sequence_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `current_way_nodes`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `current_way_tags`
--

CREATE TABLE IF NOT EXISTS `current_way_tags` (
  `way_id` bigint(20) NOT NULL,
  `k` varchar(255) default '',
  `v` varchar(255) default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `current_way_tags`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `diary_comments`
--

CREATE TABLE IF NOT EXISTS `diary_comments` (
  `id` bigint(20) NOT NULL,
  `diary_entry_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `body` text NOT NULL,
  `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL default '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `diary_comments`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `diary_entries`
--

CREATE TABLE IF NOT EXISTS `diary_entries` (
  `id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL default '0000-00-00 00:00:00',
  `latitude` double default NULL,
  `longitude` double default NULL,
  `language_code` varchar(255) default 'en'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `diary_entries`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `friends`
--

CREATE TABLE IF NOT EXISTS `friends` (
  `id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `friend_user_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `friends`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `gps_points`
--

CREATE TABLE IF NOT EXISTS `gps_points` (
  `altitude` double default NULL,
  `trackid` int(11) NOT NULL,
  `latitude` int(11) NOT NULL,
  `longitude` int(11) NOT NULL,
  `gpx_id` bigint(20) NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `tile` bigint(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `gps_points`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `gpx_files`
--

CREATE TABLE IF NOT EXISTS `gpx_files` (
  `id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `visible` tinyint(1) NOT NULL default '1',
  `name` varchar(255) default '',
  `size` bigint(20) default NULL,
  `latitude` double default NULL,
  `longitude` double default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `description` varchar(255) default '',
  `inserted` tinyint(1) NOT NULL,
  `visibility` int(11) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `gpx_files`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `gpx_file_tags`
--

CREATE TABLE IF NOT EXISTS `gpx_file_tags` (
  `gpx_id` bigint(20) NOT NULL default '0',
  `tag` varchar(255) NOT NULL,
  `id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `gpx_file_tags`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `languages`
--

CREATE TABLE IF NOT EXISTS `languages` (
  `code` varchar(255) NOT NULL,
  `english_name` varchar(255) NOT NULL,
  `native_name` varchar(255) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `languages`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `messages`
--

CREATE TABLE IF NOT EXISTS `messages` (
  `id` bigint(20) NOT NULL,
  `from_user_id` bigint(20) NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `sent_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `message_read` tinyint(1) NOT NULL default '0',
  `to_user_id` bigint(20) NOT NULL,
  `to_user_visible` tinyint(1) NOT NULL default '1',
  `from_user_visible` tinyint(1) NOT NULL default '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `messages`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `nodes`
--

CREATE TABLE IF NOT EXISTS `nodes` (
  `node_id` bigint(20) NOT NULL,
  `latitude` int(11) NOT NULL,
  `longitude` int(11) NOT NULL,
  `changeset_id` bigint(20) NOT NULL,
  `visible` tinyint(1) NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `tile` bigint(20) NOT NULL,
  `version` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `nodes`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `node_tags`
--

CREATE TABLE IF NOT EXISTS `node_tags` (
  `node_id` bigint(20) NOT NULL,
  `version` bigint(20) NOT NULL,
  `k` varchar(255) default '',
  `v` varchar(255) default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `node_tags`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `oauth_nonces`
--

CREATE TABLE IF NOT EXISTS `oauth_nonces` (
  `id` int(11) NOT NULL,
  `nonce` varchar(255) default NULL,
  `timestamp` int(11) default NULL,
  `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL default '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `oauth_nonces`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `oauth_tokens`
--

CREATE TABLE IF NOT EXISTS `oauth_tokens` (
  `id` int(11) NOT NULL,
  `user_id` int(11) default NULL,
  `type` varchar(20) default NULL,
  `client_application_id` int(11) default NULL,
  `token` varchar(50) default NULL,
  `secret` varchar(50) default NULL,
  `authorized_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `invalidated_at` timestamp NOT NULL default '0000-00-00 00:00:00',
  `created_at` timestamp NOT NULL default '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL default '0000-00-00 00:00:00',
  `allow_read_prefs` tinyint(1) NOT NULL default '0',
  `allow_write_prefs` tinyint(1) NOT NULL default '0',
  `allow_write_diary` tinyint(1) NOT NULL default '0',
  `allow_write_api` tinyint(1) NOT NULL default '0',
  `allow_read_gpx` tinyint(1) NOT NULL default '0',
  `allow_write_gpx` tinyint(1) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `oauth_tokens`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `relations`
--

CREATE TABLE IF NOT EXISTS `relations` (
  `relation_id` bigint(20) NOT NULL default '0',
  `changeset_id` bigint(20) NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `version` bigint(20) NOT NULL,
  `visible` tinyint(1) NOT NULL default '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `relations`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `relation_members`
--

CREATE TABLE IF NOT EXISTS `relation_members` (
  `relation_id` bigint(20) NOT NULL default '0',
  `member_type` varchar(20) NOT NULL,
  `member_id` bigint(20) NOT NULL,
  `member_role` varchar(255) NOT NULL,
  `version` bigint(20) NOT NULL default '0',
  `sequence_id` int(11) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `relation_members`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `relation_tags`
--

CREATE TABLE IF NOT EXISTS `relation_tags` (
  `relation_id` bigint(20) NOT NULL default '0',
  `k` varchar(255) default '',
  `v` varchar(255) default '',
  `version` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `relation_tags`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `schema_migrations`
--

CREATE TABLE IF NOT EXISTS `schema_migrations` (
  `version` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `schema_migrations`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `sessions`
--

CREATE TABLE IF NOT EXISTS `sessions` (
  `id` int(11) NOT NULL,
  `session_id` varchar(255) default NULL,
  `DATA` text,
  `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL default '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `sessions`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `email` varchar(255) NOT NULL,
  `id` bigint(20) NOT NULL,
  `active` int(11) NOT NULL default '0',
  `pass_crypt` varchar(255) NOT NULL,
  `creation_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `display_name` varchar(255) default '',
  `data_public` tinyint(1) NOT NULL default '0',
  `description` text NOT NULL,
  `home_lat` double default NULL,
  `home_lon` double default NULL,
  `home_zoom` smallint(6) default '3',
  `nearby` int(11) default '50',
  `pass_salt` varchar(255) default NULL,
  `image` text,
  `email_valid` tinyint(1) NOT NULL default '0',
  `new_email` varchar(255) default NULL,
  `visible` tinyint(1) NOT NULL default '1',
  `creation_ip` varchar(255) default NULL,
  `languages` varchar(255) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `users`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `user_blocks`
--

CREATE TABLE IF NOT EXISTS `user_blocks` (
  `id` int(11) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `creator_id` bigint(20) NOT NULL,
  `reason` text NOT NULL,
  `ends_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `needs_view` tinyint(1) NOT NULL default '0',
  `revoker_id` bigint(20) default NULL,
  `created_at` timestamp NOT NULL default '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL default '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `user_blocks`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `user_preferences`
--

CREATE TABLE IF NOT EXISTS `user_preferences` (
  `user_id` bigint(20) NOT NULL,
  `k` varchar(255) NOT NULL,
  `v` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `user_preferences`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `user_roles`
--

CREATE TABLE IF NOT EXISTS `user_roles` (
  `id` int(11) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL default '0000-00-00 00:00:00',
  `role` int(11) NOT NULL,
  `granter_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `user_roles`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `user_tokens`
--

CREATE TABLE IF NOT EXISTS `user_tokens` (
  `id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `token` varchar(255) NOT NULL,
  `expiry` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `referer` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `user_tokens`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `ways`
--

CREATE TABLE IF NOT EXISTS `ways` (
  `way_id` bigint(20) NOT NULL default '0',
  `changeset_id` bigint(20) NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `version` bigint(20) NOT NULL,
  `visible` tinyint(1) NOT NULL default '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `ways`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `way_nodes`
--

CREATE TABLE IF NOT EXISTS `way_nodes` (
  `way_id` bigint(20) NOT NULL,
  `node_id` bigint(20) NOT NULL,
  `version` bigint(20) NOT NULL,
  `sequence_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `way_nodes`
--


-- --------------------------------------------------------

--
-- Struttura della tabella `way_tags`
--

CREATE TABLE IF NOT EXISTS `way_tags` (
  `way_id` bigint(20) NOT NULL default '0',
  `k` varchar(255) NOT NULL,
  `v` varchar(255) NOT NULL,
  `version` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `way_tags`
--

andri25 · 2014-08-20T18:21:15.000Z

why i’m import error

stephan75 · 2014-08-25T15:24:19.000Z

So is your OSM source file correct?

Or XML structure defect? Can you open it with a proffesional text editor like notepad++ or PSPad?

You can also try to do your task with a smaller OSM XML file … and do you have enough free disk space?

Garry · 2014-11-03T12:25:33.000Z

Old thread but is importing .osm as slow as molasses for everyone? I can import small files ~100mb ok but a large file 30gb looks like it is going to take forever! I am watching my mysql data folder and nodes.ibd is flying, 3GB in 30 minutes but once it gets to current_ it grinds to a halt, 500kb every 10 seconds! so to get to the estimated size of 5GB for current_nodes it is going to take a few weeks, god knows how long for the rest of the current_*