Benchmarking 2011/DB Schema

Imposm is used for importing the OSM data extracts into the PostGIS database. Imposm has a configurable database schema.

Tables
Here is a list of tables that are included by default. The imposm mapping file is here:

The geometries are stored in spherical (web)mercator projection (SRID/EPSG:3857).

Point tables

 * amenities
 * places
 * transport_points

Polygon tables

 * admin
 * buildings
 * landusages
 * aeroways
 * waterareas

Linestring tables

 * minorroads
 * mainroads
 * motorways
 * railways
 * waterways

Generalized tables
Tables like above but with simplified geometries (tolerance 200m):


 * motorways_gen0
 * mainroads_gen0
 * railways_gen0
 * landusages_gen0
 * waterareas_gen0

Tables like above but with simplified geometries (tolerance 50m):
 * motorways_gen1
 * mainroads_gen1
 * railways_gen1
 * landusages_gen1
 * waterareas_gen1

Views
These views combine minorroads, mainroads, motorway and railways:


 * roads
 * roads_gen0
 * roads_gen1

Usage Tips
For rendering roads with the right ordering (highways above minorroads, bridges above tunnels, etc.) you should use the roads views and order it by z_order.

For example:

(select * from osm_roads where tunnel = 1 order by z_order) as roads (select * from osm_roads where tunnel = 0 and bridge = 0 order by z_order) as roads (select * from osm_roads where bridge = 1 order by z_order) as roads

For lower resolutions, the roads_genX views can be used. The geometries have lower spatial resolution and the views do not contain minor roads.

Schema Dump
-- -- Name: osm_admin; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_admin (   osm_id integer NOT NULL,    name character varying(255),    type character varying(255),    admin_level smallint,    geometry geometry,    CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),    CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857)) );

-- -- Name: osm_aeroways; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_aeroways (   osm_id integer NOT NULL,    name character varying(255),    type character varying(255),    geometry geometry,    CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),    CONSTRAINT enforce_geotype_geometry CHECK (((geometrytype(geometry) = 'LINESTRING'::text) OR (geometry IS NULL))),    CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857)) );

-- -- Name: osm_amenities; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_amenities (   osm_id integer NOT NULL,    name character varying(255),    type character varying(255),    geometry geometry,    CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),    CONSTRAINT enforce_geotype_geometry CHECK (((geometrytype(geometry) = 'POINT'::text) OR (geometry IS NULL))),    CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857)) );

-- -- Name: osm_buildings; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_buildings (   osm_id integer NOT NULL,    name character varying(255),    type character varying(255),    geometry geometry,    CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),    CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857)) );

-- -- Name: osm_landusages; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_landusages (   osm_id integer NOT NULL,    name character varying(255),    type character varying(255),    area real,    z_order smallint,    geometry geometry,    CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),    CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857)) );

-- -- Name: osm_mainroads; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_mainroads (   osm_id integer NOT NULL,    name character varying(255),    type character varying(255),    tunnel smallint,    bridge smallint,    oneway smallint,    ref character varying(255),    z_order smallint,    geometry geometry,    CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),    CONSTRAINT enforce_geotype_geometry CHECK (((geometrytype(geometry) = 'LINESTRING'::text) OR (geometry IS NULL))),    CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857)) );

-- -- Name: osm_mainroads_gen0; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_mainroads_gen0 (   osm_id integer,    name character varying(255),    type character varying(255),    tunnel smallint,    bridge smallint,    oneway smallint,    ref character varying(255),    z_order smallint,    geometry geometry );

-- -- Name: osm_mainroads_gen1; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_mainroads_gen1 (   osm_id integer,    name character varying(255),    type character varying(255),    tunnel smallint,    bridge smallint,    oneway smallint,    ref character varying(255),    z_order smallint,    geometry geometry );

-- -- Name: osm_minorroads; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_minorroads (   osm_id integer NOT NULL,    name character varying(255),    type character varying(255),    tunnel smallint,    bridge smallint,    oneway smallint,    ref character varying(255),    z_order smallint,    geometry geometry,    CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),    CONSTRAINT enforce_geotype_geometry CHECK (((geometrytype(geometry) = 'LINESTRING'::text) OR (geometry IS NULL))),    CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857)) );

-- -- Name: osm_motorways; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_motorways (   osm_id integer NOT NULL,    name character varying(255),    type character varying(255),    tunnel smallint,    bridge smallint,    oneway smallint,    ref character varying(255),    z_order smallint,    geometry geometry,    CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),    CONSTRAINT enforce_geotype_geometry CHECK (((geometrytype(geometry) = 'LINESTRING'::text) OR (geometry IS NULL))),    CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857)) );

-- -- Name: osm_motorways_gen0; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_motorways_gen0 (   osm_id integer,    name character varying(255),    type character varying(255),    tunnel smallint,    bridge smallint,    oneway smallint,    ref character varying(255),    z_order smallint,    geometry geometry );

-- -- Name: osm_motorways_gen1; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_motorways_gen1 (   osm_id integer,    name character varying(255),    type character varying(255),    tunnel smallint,    bridge smallint,    oneway smallint,    ref character varying(255),    z_order smallint,    geometry geometry );

-- -- Name: osm_places; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_places (   osm_id integer NOT NULL,    name character varying(255),    type character varying(255),    z_order smallint,    population integer,    geometry geometry,    CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),    CONSTRAINT enforce_geotype_geometry CHECK (((geometrytype(geometry) = 'POINT'::text) OR (geometry IS NULL))),    CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857)) );

-- -- Name: osm_railways; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_railways (   osm_id integer NOT NULL,    name character varying(255),    type character varying(255),    tunnel smallint,    bridge smallint,    z_order smallint,    geometry geometry,    CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),    CONSTRAINT enforce_geotype_geometry CHECK (((geometrytype(geometry) = 'LINESTRING'::text) OR (geometry IS NULL))),    CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857)) );

-- -- Name: osm_railways_gen0; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_railways_gen0 (   osm_id integer,    name character varying(255),    type character varying(255),    tunnel smallint,    bridge smallint,    z_order smallint,    geometry geometry );

-- -- Name: osm_railways_gen1; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_railways_gen1 (   osm_id integer,    name character varying(255),    type character varying(255),    tunnel smallint,    bridge smallint,    z_order smallint,    geometry geometry );

-- -- Name: osm_roads; Type: VIEW; Schema: public; Owner: osm --

CREATE VIEW osm_roads AS   ((SELECT osm_motorways.osm_id, osm_motorways.name, osm_motorways.type, osm_motorways.geometry, osm_motorways.bridge, osm_motorways.ref, osm_motorways.tunnel, osm_motorways.oneway, osm_motorways.z_order, 'motorways' AS class FROM osm_motorways UNION ALL SELECT osm_mainroads.osm_id, osm_mainroads.name, osm_mainroads.type, osm_mainroads.geometry, osm_mainroads.bridge, osm_mainroads.ref, osm_mainroads.tunnel, osm_mainroads.oneway, osm_mainroads.z_order, 'mainroads' AS class FROM osm_mainroads) UNION ALL SELECT osm_minorroads.osm_id, osm_minorroads.name, osm_minorroads.type, osm_minorroads.geometry, osm_minorroads.bridge, osm_minorroads.ref, osm_minorroads.tunnel, osm_minorroads.oneway, osm_minorroads.z_order, 'minorroads' AS class FROM osm_minorroads) UNION ALL SELECT osm_railways.osm_id, osm_railways.name, osm_railways.type, osm_railways.geometry, osm_railways.bridge, NULL::unknown AS ref, osm_railways.tunnel, 0 AS oneway, osm_railways.z_order, 'railways' AS class FROM osm_railways;

-- -- Name: osm_roads_gen0; Type: VIEW; Schema: public; Owner: osm --

CREATE VIEW osm_roads_gen0 AS   (SELECT osm_railways_gen0.osm_id, osm_railways_gen0.name, osm_railways_gen0.type, osm_railways_gen0.geometry, osm_railways_gen0.bridge, NULL::unknown AS ref, osm_railways_gen0.tunnel, 0 AS oneway, osm_railways_gen0.z_order, 'railways_gen1' AS class FROM osm_railways_gen0 UNION ALL SELECT osm_mainroads_gen0.osm_id, osm_mainroads_gen0.name, osm_mainroads_gen0.type, osm_mainroads_gen0.geometry, osm_mainroads_gen0.bridge, osm_mainroads_gen0.ref, osm_mainroads_gen0.tunnel, osm_mainroads_gen0.oneway, osm_mainroads_gen0.z_order, 'mainroads_gen1' AS class FROM osm_mainroads_gen0) UNION ALL SELECT osm_motorways_gen0.osm_id, osm_motorways_gen0.name, osm_motorways_gen0.type, osm_motorways_gen0.geometry, osm_motorways_gen0.bridge, osm_motorways_gen0.ref, osm_motorways_gen0.tunnel, osm_motorways_gen0.oneway, osm_motorways_gen0.z_order, 'motorways_gen1' AS class FROM osm_motorways_gen0;

-- -- Name: osm_roads_gen1; Type: VIEW; Schema: public; Owner: osm --

CREATE VIEW osm_roads_gen1 AS   (SELECT osm_railways_gen1.osm_id, osm_railways_gen1.name, osm_railways_gen1.type, osm_railways_gen1.geometry, osm_railways_gen1.bridge, NULL::unknown AS ref, osm_railways_gen1.tunnel, 0 AS oneway, osm_railways_gen1.z_order, 'railways' AS class FROM osm_railways_gen1 UNION ALL SELECT osm_mainroads_gen1.osm_id, osm_mainroads_gen1.name, osm_mainroads_gen1.type, osm_mainroads_gen1.geometry, osm_mainroads_gen1.bridge, osm_mainroads_gen1.ref, osm_mainroads_gen1.tunnel, osm_mainroads_gen1.oneway, osm_mainroads_gen1.z_order, 'mainroads' AS class FROM osm_mainroads_gen1) UNION ALL SELECT osm_motorways_gen1.osm_id, osm_motorways_gen1.name, osm_motorways_gen1.type, osm_motorways_gen1.geometry, osm_motorways_gen1.bridge, osm_motorways_gen1.ref, osm_motorways_gen1.tunnel, osm_motorways_gen1.oneway, osm_motorways_gen1.z_order, 'motorways' AS class FROM osm_motorways_gen1;

-- -- Name: osm_transport_areas; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_transport_areas (   osm_id integer NOT NULL,    name character varying(255),    type character varying(255),    geometry geometry,    CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),    CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857)) );

-- -- Name: osm_transport_points; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_transport_points (   osm_id integer NOT NULL,    name character varying(255),    type character varying(255),    ref character varying(255),    geometry geometry,    CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),    CONSTRAINT enforce_geotype_geometry CHECK (((geometrytype(geometry) = 'POINT'::text) OR (geometry IS NULL))),    CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857)) );

-- -- Name: osm_waterareas; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_waterareas (   osm_id integer NOT NULL,    name character varying(255),    type character varying(255),    geometry geometry,    CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),    CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857)) );

-- -- Name: osm_waterways; Type: TABLE; Schema: public; Owner: osm; Tablespace: --

CREATE TABLE osm_waterways (   osm_id integer NOT NULL,    name character varying(255),    type character varying(255),    geometry geometry,    CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),    CONSTRAINT enforce_geotype_geometry CHECK (((geometrytype(geometry) = 'LINESTRING'::text) OR (geometry IS NULL))),    CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857)) );