SpatiaLite logo

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

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

GNU logo 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.