[Gfoss] Spatialite cookbook ricetta #17

Beppe · February 18, 2014, 5:14pm

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
dell'interfaccia dello Spatialindex?
La query è la seguente:

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 pkid
    FROM idx_populated_places_geometry
    WHERE pkid MATCH RTreeIntersects(
      MbrMinX(
        Transform(
          ST_Envelope(rw.geometry), 4326)),
      MbrMinY(
        Transform(
          ST_Envelope(rw.geometry), 4326)),
      MbrMaxX(
        Transform(
          ST_Envelope(rw.geometry), 4236)),
      MbrMaxY(
        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 pkid
    FROM idx_populated_places_geometry
    WHERE pkid MATCH RTreeIntersects(
      MbrMinX(
        Transform(
          ST_Envelope(rw.geometry), 4326)),
      MbrMinY(
        Transform(
          ST_Envelope(rw.geometry), 4326)),
      MbrMaxX(
        Transform(
          ST_Envelope(rw.geometry), 4236)),
      MbrMaxY(
        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 pkid
    FROM idx_populated_places_geometry
    WHERE pkid MATCH RTreeIntersects(
      MbrMinX(
        Transform(
          ST_Envelope(rw.geometry), 4326)),
      MbrMinY(
        Transform(
          ST_Envelope(rw.geometry), 4326)),
      MbrMaxX(
        Transform(
          ST_Envelope(rw.geometry), 4236)),
      MbrMaxY(
        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 pkid
    FROM idx_populated_places_geometry
    WHERE pkid MATCH RTreeIntersects(
      MbrMinX(
        Transform(
          ST_Envelope(rw.geometry), 4326)),
      MbrMinY(
        Transform(
          ST_Envelope(rw.geometry), 4326)),
      MbrMaxX(
        Transform(
          ST_Envelope(rw.geometry), 4236)),
      MbrMaxY(
        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 pkid
    FROM idx_populated_places_geometry
    WHERE pkid MATCH RTreeIntersects(
      MbrMinX(
        Transform(
          ST_Envelope(rw.geometry), 4326)),
      MbrMinY(
        Transform(
          ST_Envelope(rw.geometry), 4326)),
      MbrMaxX(
        Transform(
          ST_Envelope(rw.geometry), 4236)),
      MbrMaxY(
        Transform(
          ST_Envelope(rw.geometry), 4326)))));

Grazie in anticipo,
Beppe

--
View this message in context: http://gfoss-geographic-free-and-open-source-software-italian-mailing.3056002.n2.nabble.com/Spatialite-cookbook-ricetta-17-tp7586780.html
Sent from the Gfoss -- Geographic Free and Open Source Software - Italian mailing list mailing list archive at Nabble.com.

a.furieri · February 18, 2014, 5:55pm

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

Beppe · February 18, 2014, 6:17pm

Grazie di nuovo Sandro per il pronto aiuto!

Colgo l'occasione per chiederti se potresti consigliarmi un testo,
possibilmente semplice e magari (anche se non necessariamente) anche sotto
licenza GNU, che mi consenta di approfondire in maniera "gently" quanto sto
apprendendo dallo studio dello "Spatialite cookbook"? Per comprendere il mio
livello, tieni presente che sono appena entrato nel mondo degli Spatial DB
grazie al cookbook.

Beppe

--
View this message in context: http://gfoss-geographic-free-and-open-source-software-italian-mailing.3056002.n2.nabble.com/Spatialite-cookbook-ricetta-17-tp7586780p7586783.html
Sent from the Gfoss -- Geographic Free and Open Source Software - Italian mailing list mailing list archive at Nabble.com.

a.furieri · February 18, 2014, 7:24pm

On Tue, 18 Feb 2014 10:17:46 -0800 (PST), Beppe wrote:

Colgo l'occasione per chiederti se potresti consigliarmi un testo,
possibilmente semplice e magari (anche se non necessariamente) anche sotto
licenza GNU, che mi consenta di approfondire in maniera "gently" quanto sto
apprendendo dallo studio dello "Spatialite cookbook"?

se cerchi un "libro di carta", puoi dare un'occhiata a questo:
http://www.darioflaccovio.it/libro.php/gis-open-source-df0149_C794

contiene un capitolo tutto dedicato a SpatiaLite scritto da un
autore che dovrebbe intendersene (forse ... comunque e' uno che
a me mi sta molto simpatico)

se invece preferisci scegliere a gusto tuo tra e-book e "mattone
di carta" direi che un serio candidato per approfondire il tema
Spatial DBMS e' sicuramente questo qua:
http://www.packtpub.com/postgis-to-store-organize-manipulate-analyze-spatial-data-cookbook/book

Per comprendere il mio livello, tieni presente che sono appena entrato
nel mondo degli Spatial DB grazie al cookbook.

confidenza per confidenza; quando ho iniziato a sviluppare SpatiaLite
la mia unica fonte di informazione sugli Spatial DBMS e' stata questa:
https://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

da li ho scoperto che sotto a tutta la baracca Spatial SQL c'era
uno standard internazionale formalmente definito, e da allora in
poi ho sempre tenuto la specifica ufficiale come stella polare.
non e' una lettura facilissima, ma e' decisamente autorevole
ed altamente istruttiva:
http://portal.opengeospatial.org/files/?artifact_id=25354

ciao Sandro

Beppe · February 18, 2014, 8:27pm

Grazie, farò tesoro delle tue indicazioni e le utilizzerò per accompagnarmi
in questo per me nuovo mondo che ho scoperto essere molto appassionante
oltre che essenziale per chi come me si occupa di cartografia e GIS.

Beppe

--
View this message in context: http://gfoss-geographic-free-and-open-source-software-italian-mailing.3056002.n2.nabble.com/Spatialite-cookbook-ricetta-17-tp7586780p7586788.html
Sent from the Gfoss -- Geographic Free and Open Source Software - Italian mailing list mailing list archive at Nabble.com.