You are not logged in.

#1 2010-11-05 08:57:16

pmars
Member
From: The Netherlands
Registered: 2010-10-17
Posts: 53
Website

Importing OSM into MySQL

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

Last edited by pmars (2010-11-05 08:58:59)

Offline

#2 2011-03-25 13:24:34

ThePromenader
Member
Registered: 2011-03-25
Posts: 5

Re: Importing OSM into MySQL

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!

Offline

#3 2011-03-29 09:33:58

wyo
Member
From: Thalwil
Registered: 2010-08-04
Posts: 667
Website

Re: Importing OSM into MySQL

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

Offline

#4 2011-08-19 19:08:11

pmars
Member
From: The Netherlands
Registered: 2010-10-17
Posts: 53
Website

Re: Importing OSM into MySQL

@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...

Offline

#5 2011-09-07 11:29:53

xpto_hel
Member
Registered: 2011-09-07
Posts: 3

Re: Importing OSM into MySQL

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

Offline

#6 2013-02-20 14:25:38

pietro_m
Member
Registered: 2013-02-20
Posts: 2

Re: Importing OSM into MySQL

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`
--

Offline

#7 2014-08-20 19:21:15

andri25
Member
Registered: 2014-08-20
Posts: 1

Re: Importing OSM into MySQL

why i'm import error
25X1t.png

Offline

#8 2014-08-25 16:24:19

stephan75
Member
Registered: 2008-05-28
Posts: 2,802

Re: Importing OSM into MySQL

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?

Offline

#9 2014-11-03 13:25:33

Garry
Member
Registered: 2008-03-17
Posts: 3

Re: Importing OSM into MySQL

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_* hmm

Last edited by Garry (2014-11-03 13:26:27)

Offline

Board footer

Powered by FluxBB