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.
_______________________________________________
Gfoss@lists.gfoss.it
http://lists.gfoss.it/cgi-bin/mailman/listinfo/gfoss
Questa e' una lista di discussione pubblica aperta a tutti.
I messaggi di questa lista non hanno relazione diretta con le posizioni
dell'Associazione GFOSS.it.
666 iscritti al 22.7.2013
