Note: these pages are no longer maintained

Never the less, much of the information is still relevant.
Beware, however, that some of the command syntax is from older versions, and thus may no longer work as expected.
Also: external links, from external sources, inside these pages may no longer function.

	Recipe #18 Railway Zones as Buffers
2011 January 28

Previous Slide

Table of Contents

Next Slide

The problem

This is like a kind of visual conclusion of the latest exercise. The problem now is:

create an appropriate Map Layer representing A, B, C, D and E-class zones as previously defined.

CREATE TABLE railway_zones (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  railway_name TEXT NOT NULL,
  zone_name TEXT NOT NULL);

SELECT AddGeometryColumn('railway_zones', 'geometry',
23032, 'MULTIPOLYGON', 'XY');

We'll start creating a new table:

as usual, we'll first create the table, omitting any Geometry column
and we'll then create the Geometry column in a second time, using AddGeometryColumn(); but you already known all this.
placing this new table into the 23032 SRID [ED50 UTM zone 32] is an absolutely obvious choice: after all, the original railways table is into the same identical SRID
declaring a MULTYPOLYGON Geometry type is less obvious: but we'll see later why this is required.

INSERT INTO railway_zones
  (id, railway_name, zone_name, geometry)
SELECT NULL, name, 'A class [< 1Km]',
  CastToMultiPolygon(
    ST_Buffer(geometry, 1000.0))
FROM railways;

There is very little interest in this INSERT INTO ... SELECT ... statement (again, you already known all this).
Except for the following topic:

we'll use ST_Buffer() to create a POLYGON corresponding to the 1Km A-class zone.
Please note: is not at all easy guessing the exact type of any Geometry created by ST_Buffer();
sometimes this function will create a POLYGON, but other times a MULTYPOLYGON may be created
(this depends on the exact shape of the input line, and obviously the given buffer radius has a strong influence as well).
so, in order to avoid any possible type inconsistency we defined a MULTIPOLYGON Geometry for this table.
and we are now forcing the type by calling the explicit type-casting function CastToMultiPolygon()

INSERT INTO railway_zones
  (id, railway_name, zone_name, geometry)
SELECT NULL, name, 'B class [< 2.5Km]',
  CastToMultiPolygon(
    ST_Difference(
      ST_Buffer(geometry, 2500.0),
      ST_Buffer(geometry, 1000.0)))
FROM railways;

INSERT INTO railway_zones
  (id, railway_name, zone_name, geometry)
SELECT NULL, name, 'C class [< 5Km]',
  CastToMultiPolygon(
    ST_Difference(
      ST_Buffer(geometry, 5000.0),
      ST_Buffer(geometry, 2500.0)))
FROM railways;

INSERT INTO railway_zones
  (id, railway_name, zone_name, geometry)
SELECT NULL, name, 'D class [< 10Km]',
  CastToMultiPolygon(
    ST_Difference(
      ST_Buffer(geometry, 10000.0),
      ST_Buffer(geometry, 5000.0)))
FROM railways;

INSERT INTO railway_zones
  (id, railway_name, zone_name, geometry)
SELECT NULL, name, 'E class [< 20Km]',
  CastToMultiPolygon(
    ST_Difference(
      ST_Buffer(geometry, 20000.0),
      ST_Buffer(geometry, 10000.0)))
FROM railways;

Creating any further zone isn't much more difficult.

we'll simply use ST_Difference() in order to get the appropriate representation:
in other words, we must create an interior hole for each zone, so to exclude any other other zone we've already created.

You can now perform a simple visual check using QGIS. And that's all.

Previous Slide

Table of Contents

Next Slide

	Author: Alessandro Furieri a.furieri@lqt.it
	This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

	Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.