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))
);
----
Category:Benchmarking