Benchmarking 2011/DB Schema - Revision history

Jmckenna at 13:32, 6 June 2012

2012-06-06T13:32:49Z

Wiki-Olt: /* Tables */ add SRID

2011-08-01T15:43:05Z

Tables: add SRID

Wiki-Olt: /* Schema Dump */ 900913 -> 3857

2011-08-01T15:41:47Z

Schema Dump: 900913 -> 3857

Wiki-Olt: /* Schema Dump */

2011-07-06T14:28:52Z

Schema Dump

Wiki-Olt: /* Generalized tables */

2011-07-06T14:27:21Z

Generalized tables

Wiki-Olt: formatting

2011-05-17T07:43:23Z

formatting

Wiki-Olt: usage tips

2011-05-17T07:42:16Z

usage tips

Wiki-Olt: add link title

2011-05-17T07:30:25Z

add link title

Wiki-Olt: Created page with '[http://imposm.org] 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…'

2011-05-17T07:28:50Z

Created page with '[http://imposm.org] 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…'

New page

[http://imposm.org] 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: [https://bitbucket.org/olt/imposm/src/tip/imposm/defaultmapping.py]

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

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

=== Views ===

These views combine minorroads, mainroads, motorway and railways:

* roads
* roads_gen0
* roads_gen1

== Schema Dump ==
<pre>
--
-- 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) = 900913))
);

--
-- 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) = 900913))
);

--
-- 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) = 900913))
);

--
-- 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) = 900913))
);

--
-- 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),
z_order smallint,
geometry geometry,
CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),
CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 900913))
);

--
-- 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) = 900913))
);

--
-- 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) = 900913))
);

--
-- 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) = 900913))
);

--
-- 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) = 900913))
);

--
-- 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) = 900913))
);

--
-- 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) = 900913))
);

--
-- 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) = 900913))
);

--
-- 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) = 900913))
);

--
-- 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) = 900913))
);
</pre>

← Older revision		Revision as of 13:32, 6 June 2012
Line 414:		Line 414:
	);		);
	</pre>		</pre>
		+
		+	----
		+
		+	[[Category:Benchmarking]]

← Older revision		Revision as of 15:43, 1 August 2011
Line 4:		Line 4:

	Here is a list of tables that are included by default. The imposm mapping file is here: [https://bitbucket.org/olt/imposm/src/tip/imposm/defaultmapping.py]		Here is a list of tables that are included by default. The imposm mapping file is here: [https://bitbucket.org/olt/imposm/src/tip/imposm/defaultmapping.py]
		+
		+	The geometries are stored in spherical (web)mercator projection (SRID/EPSG:3857).

	=== Point tables ===		=== Point tables ===

@@ Line 46: / Line 46: @@
 == 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 rendering roads with the right ordering (highways above minorroads, bridges above tunnels, etc.) you should use the <tt>roads</tt> views and order it by <tt>z_order</tt>.
 For example:
@@ Line 54: / Line 54: @@
   (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.
+For lower resolutions, the <tt>roads_genX</tt> views can be used. The geometries have lower spatial resolution and the views do not contain minor roads.
 == Schema Dump ==

← Older revision		Revision as of 07:30, 17 May 2011
Line 1:		Line 1:
−	[http://imposm.org] is used for importing the OSM data extracts into the PostGIS database. Imposm has a configurable database schema.	+	[http://imposm.org Imposm] is used for importing the OSM data extracts into the PostGIS database. Imposm has a configurable database schema.

	== Tables ==		== Tables ==

@@ Line 73: / Line 73: @@
      geometry geometry,
      CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),
-     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 900913))
+     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857))
 );
@@ Line 88: / Line 88: @@
      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) = 900913))
+     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857))
 );
@@ Line 103: / Line 103: @@
      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) = 900913))
+     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857))
 );
@@ Line 117: / Line 117: @@
      geometry geometry,
      CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),
-     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 900913))
+     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857))
 );
@@ Line 133: / Line 133: @@
      geometry geometry,
      CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),
-     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 900913))
+     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857))
 );
@@ Line 153: / Line 153: @@
      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) = 900913))
+     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857))
 );
@@ Line 207: / Line 207: @@
      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) = 900913))
+     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857))
 );
@@ Line 227: / Line 227: @@
      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) = 900913))
+     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857))
 );
@@ Line 278: / Line 278: @@
      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) = 900913))
+     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857))
 );
@@ Line 296: / Line 296: @@
      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) = 900913))
+     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857))
 );
@@ Line 364: / Line 364: @@
      geometry geometry,
      CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),
-     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 900913))
+     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857))
 );
@@ Line 380: / Line 380: @@
      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) = 900913))
+     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857))
 );
@@ Line 394: / Line 394: @@
      geometry geometry,
      CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),
-     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 900913))
+     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857))
 );
@@ Line 409: / Line 409: @@
      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) = 900913))
+     CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 3857))
 );
 </pre>

@@ Line 129: / Line 129: @@
      name character varying(255),
      type character varying(255),
      z_order smallint,
      geometry geometry,

@@ Line 30: / Line 30: @@
 * mainroads_gen0
 * railways_gen0
 Tables like above but with simplified geometries (tolerance 50m):
@@ Line 35: / Line 37: @@
 * mainroads_gen1
 * railways_gen1
 === Views ===

@@ Line 43: / Line 43: @@
 * roads_gen0
 * roads_gen1
 == Schema Dump ==