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 #16 Railways vs Local Councils
2011 January 28

Previous Slide

Table of Contents

Next Slide

The problem

This time we'll use for the first time the railways dataset.
Please remember: this one is a really small dataset simply representing two railway lines:
this dataset is in the 23032 SRID [ED50 UTM zone 32]. The problem is:

Identify any Local Council crossed by a railway line.

Important notice: you must accomplish a preliminary step.
You are required downloading railways.zip (a very simple shapefile opportunely derived from OSM).
And then you have to load such shapefile into the railways table.

Railway	LocalCouncil	County	Region
...	...	...	...
Ferrovia Adriatica	SILVI	TERAMO	ABRUZZO
Ferrovia Adriatica	TORTORETO	TERAMO	ABRUZZO
Ferrovia Roma-Napoli	AVERSA	CASERTA	CAMPANIA
Ferrovia Roma-Napoli	CANCELLO ED ARNONE	CASERTA	CAMPANIA
...	...	...	...

SELECT rw.name AS Railway,
  lc.lc_name AS LocalCouncil,
  c.county_name AS County,
  r.region_name AS Region
FROM railways AS rw
JOIN local_councils AS lc ON (
  ST_Intersects(rw.geometry, lc.geometry)
    AND lc.ROWID IN (
      SELECT pkid
      FROM idx_local_councils_geometry
      WHERE pkid MATCH RTreeIntersects(
        MbrMinX(rw.geometry),
        MbrMinY(rw.geometry),
        MbrMaxX(rw.geometry),
        MbrMaxY(rw.geometry))))
JOIN counties AS c
  ON (c.county_id = lc.county_id)
JOIN regions AS r
  ON (r.region_id = c.region_id)
ORDER BY r.region_name,
  c.county_name,
  lc.lc_name;

We'll simply examine few interesting key points:

a JOIN clause is used so to retrieve the corresponding County and Region for each Local Council.
You already know how this works, because you had already used this in some previous example.
the most interesting point in this query is in the first JOIN clause:
the ST_Intersects() function is used to evaluate a Spatial relationships between the local_councils and the railways tables.
Anyway all this isn't at all surprising, because you've already seen something like this in previous examples.
and once again the appropriate R*Tree Spatial Index is used in order to speed up the query.

More or less, this is quite the same thing of the previous example, when we examined Spatial relationships existing between Local Councils and Populated Places.
Anyway, this confirms that using any possible kind of Spatial relationship is a reasonably easy task, and that you can successfully use Spatial relationships to resolve lots of different real-world problems.

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.