[Gfoss] Spatialite cookbook ricetta #17

[a.furieri a lqt.it]

On Tue, 18 Feb 2014 09:14:38 -0800 (PST), Beppe wrote:
> Salve a tutti,
> sono finalmente giunto, anche grazie al vostro aiuto, alla ricetta 
> #17 dello
> spatialite cookbook, qualcuno potrebbe aiutarmi a riscrivere la 
> relativa
> query in modo che sia compatibile con la versione più recente
>

SELECT rw.name AS Railway,
   pp_e.name AS PopulatedPlace,
   (ST_Distance(rw.geometry,
     Transform(pp_a.geometry, 23032)) <= 1000.0)
       AS "A class [< 1Km]",
   (ST_Distance(rw.geometry,
     Transform(pp_b.geometry, 23032)) > 1000.0)
       AS "B class [< 2.5Km]",
   (ST_Distance(rw.geometry,
     Transform(pp_c.geometry, 23032)) > 2500.0)
       AS "C class [< 5Km]",
   (ST_Distance(rw.geometry,
     Transform(pp_d.geometry, 23032)) > 5000.0)
       AS "D class [< 10Km]",
   (ST_Distance(rw.geometry,
     Transform(pp_e.geometry, 23032)) > 10000.0)
       AS "E class [< 20Km]"
FROM railways AS rw
JOIN populated_places AS pp_e ON (
   ST_Distance(rw.geometry,
     Transform(pp_e.geometry, 23032)) <= 20000.0
   AND pp_e.id IN (
     SELECT rowid
     FROM SpatialIndex
     WHERE f_table_name = 'populated_places'
       AND search_frame = ST_Transform(
         ST_Envelope(rw.geometry), 4326)))
LEFT JOIN populated_places AS pp_d ON (
   pp_e.id = pp_d.id
   AND ST_Distance(rw.geometry,
     Transform(pp_d.geometry, 23032)) <= 10000.0
   AND pp_d.id IN (
     SELECT rowid
     FROM SpatialIndex
     WHERE f_table_name = 'populated_places'
       AND search_frame = ST_Transform(
         ST_Envelope(rw.geometry), 4326)))
LEFT JOIN populated_places AS pp_c ON (
   pp_d.id = pp_c.id
   AND ST_Distance(rw.geometry,
     Transform(pp_c.geometry, 23032)) <= 5000.0
   AND pp_c.id IN (
     SELECT rowid
     FROM SpatialIndex
     WHERE f_table_name = 'populated_places'
       AND search_frame = ST_Transform(
         ST_Envelope(rw.geometry), 4326)))
LEFT JOIN populated_places AS pp_b ON (
   pp_c.id = pp_b.id
   AND ST_Distance(rw.geometry,
     Transform(pp_b.geometry, 23032)) <= 2500.0
   AND pp_b.id IN (
     SELECT rowid
     FROM SpatialIndex
     WHERE f_table_name = 'populated_places'
       AND search_frame = ST_Transform(
         ST_Envelope(rw.geometry), 4326)))
LEFT JOIN populated_places AS pp_a ON (
   pp_b.id = pp_a.id
   AND ST_Distance(rw.geometry,
     Transform(pp_a.geometry, 23032)) <= 1000.0
   AND pp_a.id IN (
     SELECT rowid
     FROM SpatialIndex
     WHERE f_table_name = 'populated_places'
       AND search_frame = ST_Transform(
         ST_Envelope(rw.geometry), 4326)));

ciao Sandro