256 lines
7.3 KiB
Plaintext
256 lines
7.3 KiB
Plaintext
|
# Test file for Distributed Algebra 2
|
||
|
|
|
||
|
|
# Database has been prepared by executing testDistAlgA.
|
||
|
|
|
||
|
|
##################################################################
|
||
|
|
# 7 Querying
|
||
|
|
#
|
||
|
|
# 7.1 Selection
|
||
|
|
#
|
||
|
|
# 7.1.1 By Scanning
|
||
|
|
|
||
|
|
let eichlinghofen = [const region value (
|
||
|
|
(
|
||
|
|
(
|
||
|
|
(7.419515247680575 51.47332155746125)
|
||
|
|
(7.394967670776298 51.47332155746125)
|
||
|
|
(7.394967670776298 51.48716614802665)
|
||
|
|
(7.419515247680575 51.48716614802665)))) ]
|
||
|
|
|
||
|
|
query BuildingsB1 dmap["", . feed filter[.Type = "school"] count]
|
||
|
|
getValue tie[. + ..]
|
||
|
|
|
||
|
|
query share("eichlinghofen", TRUE, WORKERS)
|
||
|
|
|
||
|
|
query RoadsB1 dmap["", . feed filter[.GeoData intersects eichlinghofen]]
|
||
|
|
dsummarize consume count
|
||
|
|
|
||
|
|
|
||
|
|
##################################################################
|
||
|
|
# 7.1.2 Creating a Standard Index
|
||
|
|
|
||
|
|
let RoadsB2_Name = RoadsB2 dloop["RoadsB2_Name", . createbtree[Name] ]
|
||
|
|
|
||
|
|
|
||
|
|
##################################################################
|
||
|
|
# 7.1.3 Using a Standard Index
|
||
|
|
|
||
|
|
query RoadsB2_Name RoadsB2
|
||
|
|
dloop2["", . .. exactmatch['Universitätsstraße'] count]
|
||
|
|
getValue tie[. + ..]
|
||
|
|
|
||
|
|
|
||
|
|
##################################################################
|
||
|
|
# 7.1.4 Creating a Spatial Index
|
||
|
|
|
||
|
|
let BuildingsB4_GeoData = BuildingsB4 dloop["",
|
||
|
|
. feed addid extend[Box: scalerect(.EnlargedBox, 1000000.0, 1000000.0)]
|
||
|
|
sortby[Box] remove[Box] bulkloadrtree[EnlargedBox] ]
|
||
|
|
|
||
|
|
##################################################################
|
||
|
|
# 7.1.5 Using a Spatial Index
|
||
|
|
|
||
|
|
query BuildingsB4_GeoData BuildingsB4
|
||
|
|
dmap2["", . .. windowintersects[eichlinghofen]
|
||
|
|
filter[.Original]
|
||
|
|
filter[.GeoData intersects eichlinghofen], ftport
|
||
|
|
]
|
||
|
|
dsummarize consume count
|
||
|
|
|
||
|
|
|
||
|
|
##################################################################
|
||
|
|
# 7.2 Join
|
||
|
|
#
|
||
|
|
# 7.2.1 Equijoin
|
||
|
|
#
|
||
|
|
# (1) Distributed by Join Attribute
|
||
|
|
|
||
|
|
let NaturalB2 = Natural feed filter[isdefined(.Name)]
|
||
|
|
ddistribute4["NaturalB2", hashvalue(.Name, 999997), NSlots, WORKERS]
|
||
|
|
|
||
|
|
query NaturalB2 dmap["",
|
||
|
|
. feed {n1} . feed {n2} itHashJoin[Name_n1, Name_n2]
|
||
|
|
filter[.Osm_id_n1 < .Osm_id_n2]]
|
||
|
|
dsummarize consume count
|
||
|
|
|
||
|
|
##################################################################
|
||
|
|
# (2) Arbitrary Distribution
|
||
|
|
|
||
|
|
let NaturalB1 = Natural feed
|
||
|
|
dfdistribute3["NaturalB1", NSlots, TRUE, WORKERS]
|
||
|
|
|
||
|
|
query NaturalB1 partitionF["", . feed filter[isdefined(.Name)],
|
||
|
|
hashvalue(..Name, 999997), 0]
|
||
|
|
collect2["", ftport]
|
||
|
|
dmap["",
|
||
|
|
. feed {n1} . feed {n2} itHashJoin[Name_n1, Name_n2]
|
||
|
|
filter[.Osm_id_n1 < .Osm_id_n2]]
|
||
|
|
dsummarize consume count
|
||
|
|
|
||
|
|
|
||
|
|
##################################################################
|
||
|
|
# 7.2.2 Spatial Join
|
||
|
|
#
|
||
|
|
# (1) Both arguments are distributed by spatial attributes
|
||
|
|
|
||
|
|
let WaterwaysB4 = Waterways feed
|
||
|
|
extendstream[Cell: cellnumber(bbox(.GeoData), grid)]
|
||
|
|
dfdistribute2["WaterwaysB4", Cell, NSlots, WORKERS]
|
||
|
|
|
||
|
|
query RoadsB4 WaterwaysB4 dmap2["",
|
||
|
|
. feed {r} .. feed {w} itSpatialJoin[GeoData_r, GeoData_w]
|
||
|
|
filter[.Cell_r = .Cell_w]
|
||
|
|
filter[gridintersects(grid, bbox(.GeoData_r),
|
||
|
|
bbox(.GeoData_w), .Cell_r)]
|
||
|
|
filter[.GeoData_r intersects .GeoData_w] count, ftport ]
|
||
|
|
getValue tie[. + ..]
|
||
|
|
|
||
|
|
##################################################################
|
||
|
|
# (2) Not distributed by spatial attributes
|
||
|
|
|
||
|
|
let WaterwaysB1 = Waterways feed dfdistribute3["WaterwaysB1", NSlots, TRUE,
|
||
|
|
WORKERS]
|
||
|
|
|
||
|
|
query
|
||
|
|
RoadsB1 partitionF["",
|
||
|
|
. feed extendstream[Cell: cellnumber(bbox(.GeoData), grid)],
|
||
|
|
..Cell, 0]
|
||
|
|
WaterwaysB1 partitionF["",
|
||
|
|
. feed extendstream[Cell: cellnumber(bbox(.GeoData), grid)],
|
||
|
|
..Cell, 0]
|
||
|
|
areduce2["",
|
||
|
|
. feed {r} .. feed {w} itSpatialJoin[GeoData_r, GeoData_w]
|
||
|
|
filter[.Cell_r = .Cell_w]
|
||
|
|
filter[gridintersects(grid, bbox(.GeoData_r),
|
||
|
|
bbox(.GeoData_w), .Cell_r)]
|
||
|
|
filter[.GeoData_r intersects .GeoData_w] count, ftport ]
|
||
|
|
getValue tie[. + ..]
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
##################################################################
|
||
|
|
# Expressions in the Select-Clause
|
||
|
|
|
||
|
|
query RoadsB4 WaterwaysB4 dmap2["",
|
||
|
|
. feed {r} .. feed {w} itSpatialJoin[GeoData_r, GeoData_w]
|
||
|
|
filter[.Cell_r = .Cell_w]
|
||
|
|
filter[gridintersects(grid, bbox(.GeoData_r),
|
||
|
|
bbox(.GeoData_w), .Cell_r)]
|
||
|
|
filter[.GeoData_r intersects .GeoData_w], ftport ]
|
||
|
|
dsummarize
|
||
|
|
projectextend[Osm_id_r, Name_r, GeoData_r, Osm_id_w, Name_w, GeoData_w;
|
||
|
|
BridgePosition: crossings(.GeoData_r, .GeoData_w)]
|
||
|
|
consume count
|
||
|
|
|
||
|
|
query RoadsB4 WaterwaysB4 dmap2["",
|
||
|
|
. feed {r} .. feed {w} itSpatialJoin[GeoData_r, GeoData_w]
|
||
|
|
filter[.Cell_r = .Cell_w]
|
||
|
|
filter[gridintersects(grid, bbox(.GeoData_r),
|
||
|
|
bbox(.GeoData_w), .Cell_r)]
|
||
|
|
filter[.GeoData_r intersects .GeoData_w]
|
||
|
|
projectextend[Osm_id_r, Name_r, Osm_id_w, Name_w; BridgePosition:
|
||
|
|
crossings(.GeoData_r, .GeoData_w)],ftport
|
||
|
|
]
|
||
|
|
dsummarize
|
||
|
|
consume count
|
||
|
|
|
||
|
|
|
||
|
|
##################################################################
|
||
|
|
# 7.2.3 General Join
|
||
|
|
|
||
|
|
query share("Waterways", TRUE, WORKERS)
|
||
|
|
|
||
|
|
query RoadsB1 dmap["", . feed filter[isdefined(.Name)]
|
||
|
|
filter[.Fclass = "pedestrian"] {r}
|
||
|
|
Waterways feed filter[.Fclass = "river"] {w}
|
||
|
|
symmjoin[.Name_r contains ..Name_w] ]
|
||
|
|
dsummarize
|
||
|
|
consume count
|
||
|
|
|
||
|
|
query RoadsB1 WaterwaysB1 dproduct["", . feed
|
||
|
|
filter[isdefined(.Name)] filter[.Fclass = "pedestrian"] {r}
|
||
|
|
.. feed filter[.Fclass = "river"] {w}
|
||
|
|
symmjoin[.Name_r contains ..Name_w], ftport]
|
||
|
|
dsummarize
|
||
|
|
consume count
|
||
|
|
|
||
|
|
|
||
|
|
##################################################################
|
||
|
|
# 7.2.4 Index-Based Equijoin
|
||
|
|
|
||
|
|
let RoadsB3_Name = RoadsB3 dloop["RoadsB3_Name", . createbtree[Name]]
|
||
|
|
|
||
|
|
query RoadsB3 RoadsB3_Name RoadsB3 dmap3["", $1 feed
|
||
|
|
filter[.Fclass = "pedestrian"] {r1}
|
||
|
|
loopjoin[$2 $3 exactmatch[.Name_r1] filter[.Fclass = "pedestrian"]]
|
||
|
|
filter[.Osm_id_r1 < .Osm_id], ftport]
|
||
|
|
dsummarize
|
||
|
|
consume count
|
||
|
|
|
||
|
|
##################################################################
|
||
|
|
# 7.2.5 Index-Based Spatial Join
|
||
|
|
|
||
|
|
query RoadsB4 BuildingsB4_GeoData BuildingsB4 dmap3["",
|
||
|
|
$1 feed filter[.Fclass = "bridleway"] {r}
|
||
|
|
loopjoin[$2 $3 windowintersects[.GeoData_r] {b}]
|
||
|
|
filter[.Cell_r = .Cell_b]
|
||
|
|
filter[gridintersects(grid, bbox(.GeoData_r), .EnlargedBox_b,
|
||
|
|
.Cell_r)]
|
||
|
|
filter[distance(gk(.GeoData_r), gk(.GeoData_b)) < 500] count, ftport]
|
||
|
|
getValue tie[. + ..]
|
||
|
|
|
||
|
|
query RoadsB4 BuildingsB4_GeoData BuildingsB4 dmap3["",
|
||
|
|
$1 feed filter[.Fclass = "bridleway"] {r}
|
||
|
|
loopjoin[$2 $3 windowintersects[.GeoData_r] {b}]
|
||
|
|
filter[.Cell_r = .Cell_b]
|
||
|
|
filter[gridintersects(grid, bbox(.GeoData_r), .EnlargedBox_b,
|
||
|
|
.Cell_r)] count, ftport]
|
||
|
|
getValue tie[. + ..]
|
||
|
|
|
||
|
|
|
||
|
|
##################################################################
|
||
|
|
# 7.3 Aggregation
|
||
|
|
#
|
||
|
|
# 7.3.1 Counting
|
||
|
|
|
||
|
|
query RoadsB1 dmap["", . feed sortby[Fclass]
|
||
|
|
groupby[Fclass; Cnt: group count] ]
|
||
|
|
dsummarize sortby[Fclass] groupby[Fclass; Cnt: group feed sum[Cnt]]
|
||
|
|
consume
|
||
|
|
|
||
|
|
##################################################################
|
||
|
|
# 7.3 Aggregation
|
||
|
|
#
|
||
|
|
# 7.3.2 Sum, Average
|
||
|
|
|
||
|
|
query WaterwaysB1 dmap["", . feed filter[.Width between[0, 10000]]
|
||
|
|
sortby[Fclass]
|
||
|
|
groupby[Fclass; Cnt: group count, SWidth: group feed sum[Width]]
|
||
|
|
]
|
||
|
|
dsummarize sortby[Fclass]
|
||
|
|
groupby[Fclass; SumWidth: group feed sum[SWidth],
|
||
|
|
SumCnt: group feed sum[Cnt]]
|
||
|
|
extend[AvgWidth: .SumWidth / .SumCnt]
|
||
|
|
project[Fclass, AvgWidth]
|
||
|
|
consume
|
||
|
|
|
||
|
|
|
||
|
|
query WaterwaysB1 dmap["", . feed filter[.Width between[0, 10000]]
|
||
|
|
groupby2[Fclass; Cnt: fun(t: TUPLE, agg:int) agg + 1::0,
|
||
|
|
SWidth: fun(t2: TUPLE, agg2:int) agg2 + attr(t2, Width)::0]
|
||
|
|
]
|
||
|
|
dsummarize sortby[Fclass]
|
||
|
|
groupby[Fclass; AWidth: group feed sum[SWidth] / group feed sum[Cnt]]
|
||
|
|
consume
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|