Benchmarking 2011/DB Schema

From OSGeo
< Benchmarking 2011
Revision as of 07:41, 1 August 2011 by Wiki-Olt (talk | contribs) (→‎Schema Dump: 900913 -> 3857)
Jump to navigation Jump to search

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: [1]

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))
);
Retrieved from "http://wiki.osgeo.org/w/index.php?title=Benchmarking_2011/DB_Schema&oldid=56233"