519 lines
21 KiB
Plaintext
519 lines
21 KiB
Plaintext
######################################################################
|
|
## File: OBA-JNetwork-Queries ################################
|
|
######################################################################
|
|
## This file is part of SECONDO.
|
|
##
|
|
## Copyright (C) 2007, University in Hagen, Faculty of Mathematics and
|
|
## Computer Science, Database Systems for New Applications.
|
|
##
|
|
## SECONDO is free software; you can redistribute it and/or modify
|
|
## it under the terms of the GNU General Public License as published by
|
|
## the Free Software Foundation; either version 2 of the License, or
|
|
## (at your option) any later version.
|
|
##
|
|
## SECONDO is distributed in the hope that it will be useful,
|
|
## but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
## GNU General Public License for more details.
|
|
##
|
|
## You should have received a copy of the GNU General Public License
|
|
## along with SECONDO; if not, write to the Free Software
|
|
## Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
|
|
################################################################################
|
|
## This file performs the OBA-Queries of the BerlinMOD benchmark #
|
|
## in the Compact Representation on the JNetwork representation Secondo DBMS. #
|
|
################################################################################
|
|
|
|
open database berlinmod;
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 1: What are the models of the vehicles with license plate numbers from #
|
|
# QueryLicence? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres001 =
|
|
QueryLicences feed {l}
|
|
loopjoin[ dataSJcar_Licence_btree dataSJcar exactmatch[.Licence_l]]
|
|
project[Licence, Model]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 2: How many vehicles exist that are passenger cars? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres002 =
|
|
dataSJcar feed
|
|
filter [.Type = "passenger"]
|
|
count;
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 3: Where have the vehicles with licenses from QueryLicence1 been at #
|
|
# each instant from QueryInstant1? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres003 =
|
|
QueryLicences1 feed {l}
|
|
loopjoin[dataSJcar_Licence_btree dataSJcar exactmatch[.Licence_l]]
|
|
QueryInstants feed {i} head[10]
|
|
product
|
|
projectextend[; Licence: .Licence_l,
|
|
Instant: .Instant_i,
|
|
Pos: val(.Trip atinstant .Instant_i)]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 4: Which license plate numbers belong to vehicles that have passed the #
|
|
# points from QueryPoints? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres004 =
|
|
QueryPointsJNetAll feed
|
|
extend[Prect: netbox(.Pos)]
|
|
projectextend[Id, Pos; NBox: rectangle2(minD(.Prect,1), maxD(.Prect, 1),
|
|
minD(.Prect,2) - 0.00001,
|
|
maxD(.Prect,2) + 0.00001)]
|
|
loopjoin[
|
|
dataSJcar_TrajBoxNet windowintersectsS[.NBox]
|
|
sort rdup dataSJcar gettuples]
|
|
filter [.Trip passes .Pos]
|
|
project[Id, Licence]
|
|
sortby[Id asc, Licence asc]
|
|
krdup[Id, Licence]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 5: What is the minimum distance between places, where a vehicle with a #
|
|
# license from QueryLicences1 and a vehicle with licenses from #
|
|
# from QueryLicence2 have been? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres005 =
|
|
QueryLicences2 feed
|
|
loopsel[ dataSJcar_Licence_btree dataSJcar exactmatch[.Licence] ]
|
|
projectextend[Licence; Traj: fromnetwork(trajectory(.Trip))]{t2}
|
|
QueryLicences1 feed
|
|
loopsel[dataSJcar_Licence_btree dataSJcar exactmatch[.Licence] ]
|
|
projectextend[Licence; Traj: fromnetwork(trajectory(.Trip))]{t1}
|
|
product
|
|
projectextend[ ; Licence1: .Licence_t1,
|
|
Licence2: .Licence_t2,
|
|
Dist: round(distance(.Traj_t1, .Traj_t2),3)]
|
|
sortby [Licence1, Licence2]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 6: What are the pairs of license plate numbers of "trucks", that have #
|
|
# been as close as 10m or less to each other? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres006tmp1 =
|
|
dataSJcar feed
|
|
filter [.Type = "truck"]
|
|
projectextend [Licence; Ptrip: fromnetwork(.Trip), BBox: bbox(.Trip)]
|
|
extend [Box: rectangle2(minD(.BBox,1) - 5.0, maxD(.BBox,1) + 5.0,
|
|
minD(.BBox,2) - 5.0, maxD(.BBox,2) + 5.0)]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
let OBAJNres006 =
|
|
OBAJNres006tmp1 feed {a}
|
|
OBAJNres006tmp1 feed {b}
|
|
symmjoin[(.Box_a intersects ..Box_b) and (.Licence_a < ..Licence_b) and (everNearerThan(.Ptrip_a, ..Ptrip_b, 10.0))]
|
|
project [Licence_a, Licence_b]
|
|
sortby [Licence_a asc, Licence_b asc]
|
|
krdup [Licence_a, Licence_b]
|
|
consume;
|
|
|
|
delete OBAJNres006tmp1;
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 7: What are the license plate numbers of the "passenger" cars that #
|
|
# have reached points from QueryPoints first of all "passenger" cars #
|
|
# during the complete observation period? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres007tmp1 =
|
|
QueryPointsJNetAll feed
|
|
extend[Prect: netbox(.Pos)]
|
|
projectextend[Id, Pos; NBox: rectangle2(minD(.Prect,1), maxD(.Prect, 1),
|
|
minD(.Prect,2) - 0.00001,
|
|
maxD(.Prect,2) + 0.00001)]
|
|
loopsel [fun(t:TUPLE)
|
|
dataSJcar_TrajBoxNet windowintersectsS[attr(t,NBox)]
|
|
sort rdup dataSJcar gettuples
|
|
filter [.Type = "passenger"]
|
|
projectextend[Licence; Id: attr(t,Id) ,
|
|
Instant: inst(initial(.Trip at attr(t,Pos)))]]
|
|
filter[not(isempty(.Instant))]
|
|
sortby[Id asc, Instant asc]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
let OBAJNres007 =
|
|
OBAJNres007tmp1 feed
|
|
groupby[Id; FirstTime: group feed min[Instant]]{b}
|
|
OBAJNres007tmp1 feed {a}
|
|
hashjoin[Id_b,Id_a]
|
|
filter [(.Id_a = .Id_b) and (.Instant_a <= .FirstTime_b)]
|
|
project[Id_a, Licence_a]
|
|
sortby[Id_a, Licence_a]
|
|
rdup
|
|
consume;
|
|
|
|
delete OBAJNres007tmp1;
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 8: What are the overall traveled distances of the vehicles with #
|
|
# license plate numbers from QueryLicences1 during the periods from #
|
|
# QueryPeriods1? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres008 =
|
|
QueryLicences1 feed {l}
|
|
loopsel[ dataSJcar_Licence_btree dataSJcar exactmatch[.Licence_l]]
|
|
QueryPeriods1 feed
|
|
filter [not(isempty(.Period))]{p}
|
|
product
|
|
projectextend[Licence; Period: .Period_p,
|
|
Dist: round(length(.Trip atperiods .Period_p),3)]
|
|
project[Licence, Period, Dist]
|
|
sortby [Licence asc, Period asc]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 9: What is the longest distance that was traveled by a vehicle during #
|
|
# each of the periods from QueryPeriods? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres009 =
|
|
dataSJcar feed {c}
|
|
QueryPeriods feed
|
|
filter[not(isempty(.Period))]{p}
|
|
product
|
|
projectextend [Id_p, Period_p, Licence_c; Dist: round(length(.Trip_c atperiods .Period_p),3)]
|
|
sortby [Id_p asc, Period_p asc, Dist desc]
|
|
groupby [Id_p, Period_p; Distance: group feed max[Dist]]
|
|
project [Period_p, Distance]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 10: When and where did the vehicles with license plate numbers from #
|
|
# QueryLicences1 meet other vehicles (distance < 3m) and what are #
|
|
# the latter licenses? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres010tmp1 =
|
|
QueryLicences1 feed
|
|
loopsel[dataSJcar_Licence_btree dataSJcar exactmatch[.Licence]]
|
|
projectextend[Licence, Trip; TripA: fromnetwork(.Trip), BBox: bbox(.Trip)]
|
|
projectextend[Licence, Trip, TripA; Box: rectangle2((minD(.BBox,1) - 1.5),
|
|
(maxD(.BBox,1) + 1.5),
|
|
(minD(.BBox,2) - 1.5),
|
|
(maxD(.BBox,2) + 1.5))]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
let OBAJNres010 =
|
|
dataSJcar feed
|
|
projectextend[Licence; TripA: fromnetwork(.Trip), BBox: bbox(.Trip)]
|
|
projectextend[Licence, TripA ;Box: rectangle2((minD(.BBox,1) - 1.5),
|
|
(maxD(.BBox,1) + 1.5),
|
|
(minD(.BBox,2) - 1.5),
|
|
(maxD(.BBox,2) + 1.5))]{c1}
|
|
OBAJNres010tmp1 feed {c2}
|
|
symmjoin[.Box_c1 intersects ..Box_c2]
|
|
filter[.Licence_c1 # .Licence_c2]
|
|
filter[everNearerThan(.TripA_c1, .TripA_c2, 3.0)]
|
|
projectextend [Licence_c1, Licence_c2; Pos: .Trip_c2 atperiods deftime((distance(.TripA_c1, .TripA_c2) < 3.0) at TRUE)]
|
|
filter [not(isempty(.Pos))]
|
|
project [Licence_c2, Licence_c1, Pos]
|
|
sortby [Licence_c2 asc, Licence_c1 asc]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
delete OBAJNres010tmp1;
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 11: Which vehicles passed a point from QueryPoints1 at one of the #
|
|
# instants from QueryInstant1? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres011 =
|
|
QueryPoints1JNetAll feed
|
|
extend[Prect: netbox(.Pos)]
|
|
projectextend[Id, Pos; NBox: rectangle2(minD(.Prect,1), maxD(.Prect, 1),
|
|
minD(.Prect,2) - 0.00001,
|
|
maxD(.Prect,2) + 0.00001)]
|
|
loopsel[fun(t:TUPLE)
|
|
dataSJcar_TrajBoxNet windowintersectsS[attr(t,NBox)]
|
|
sort rdup dataSJcar gettuples
|
|
filter [.Trip passes attr(t,Pos)]
|
|
projectextend[Licence; Id: (attr(t,Id)), TripN: .Trip at attr(t,Pos)]]
|
|
QueryInstant1 feed {i}
|
|
symmjoin[.TripN present ..Instant_i]
|
|
project[Licence, Id, Instant_i]
|
|
sortby[Id, Licence, Instant_i]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 12: Which vehicles met at a point from QueryPoints1 at an instant from #
|
|
# QueryInstant1? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres012tmp1 =
|
|
QueryInstant1 feed {i}
|
|
QueryPoints1JNetAll feed
|
|
extend[Prect: netbox(.Pos)]
|
|
projectextend[Id, Pos; NBox: rectangle2(minD(.Prect,1), maxD(.Prect,1),
|
|
minD(.Prect,2) - 0.00001,
|
|
maxD(.Prect,2) + 0.00001)]{p}
|
|
product
|
|
projectextend[Instant_i, Id_p, Pos_p; Box: box3d(.NBox_p, .Instant_i)]
|
|
loopsel[fun(t:TUPLE)
|
|
dataSJcar_BoxNet_timespace windowintersectsS[attr(t,Box)]
|
|
sort rdup dataSJcar gettuples
|
|
projectextend [Licence; Id: attr(t,Id_p), Instant: attr(t,Instant_i), Pos: attr(t,Pos_p)]]
|
|
sortby [Id asc, Instant asc, Licence asc, Pos asc]
|
|
rdup
|
|
consume;
|
|
|
|
query now();
|
|
|
|
let OBAJNres012 =
|
|
OBAJNres012tmp1 feed {c1}
|
|
OBAJNres012tmp1 feed {c2}
|
|
symmjoin[((.Licence_c1 < ..Licence_c2) and
|
|
(.Id_c1 = ..Id_c2)) and
|
|
(.Instant_c1 = ..Instant_c2)]
|
|
project[Id_c1, Pos_c1, Instant_c1, Licence_c1, Licence_c2]
|
|
sortby[Id_c1 asc, Instant_c1 asc, Licence_c2 asc]
|
|
rdup
|
|
consume;
|
|
|
|
query now();
|
|
|
|
delete OBAJNres012tmp1;
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 13: Which vehicles traveled within one of the regions from #
|
|
# QueryRegions1 during the periods from QueryPeriods1? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres013 =
|
|
dataSJcar feed {c}
|
|
QueryRegions1JNet feed
|
|
filter[not(isempty(.Region))] {r}
|
|
symmjoin[.Trip_c passes ..Region_r]
|
|
projectextend[Licence_c, Id_r, Region_r; Trip: .Trip_c at .Region_r]
|
|
filter[not(isempty(.Trip))]
|
|
QueryPeriods1 feed
|
|
filter[not(isempty(.Period))]{p}
|
|
symmjoin [.Trip present ..Period_p]
|
|
projectextend[Id_r, Period_p; Licence: .Licence_c, Trip: .Trip atperiods .Period_p]
|
|
filter [not(isempty(.Trip))]
|
|
project[Id_r, Period_p, Licence]
|
|
sortby[Id_r asc, Period_p asc, Licence asc]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 14: Which vehicles traveled within one of the regions from #
|
|
# QueryRegions1 at one of the instants from QueryInstant1? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres014 =
|
|
QueryRegions1JNet feed
|
|
filter [not(isempty(.Region))]
|
|
projectextendstream[Id, Region; Box: units(.Region)]
|
|
projectextend[Id, Region; BBox: netbox(.Box)]{r}
|
|
QueryInstant1 feed {i}
|
|
product
|
|
loopsel [fun (t:TUPLE)
|
|
dataSJcar_BoxNet_timespace windowintersectsS[box3d(attr(t,BBox_r), attr(t,Instant_i))]
|
|
sort rdup dataSJcar gettuples
|
|
filter[val(.Trip atinstant attr(t,Instant_i)) inside attr(t,Region_r)]
|
|
projectextend[Licence; Instant: attr(t,Instant_i), Id_r: attr(t,Id_r)]]
|
|
sortby[Id_r, Instant, Licence]
|
|
krdup[Id_r, Instant, Licence]
|
|
project[Id_r, Instant, Licence]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 15: Which vehicles passed a point from QueryPoints1 during a period #
|
|
# from QueryPeriods1? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres015 =
|
|
QueryPoints1JNetAll feed
|
|
extend[Prect: netbox(.Pos)]
|
|
projectextend[Id, Pos; NBox: rectangle2(minD(.Prect,1), maxD(.Prect, 1),
|
|
minD(.Prect,2) - 0.00001,
|
|
maxD(.Prect,2) + 0.00001)] {p}
|
|
QueryPeriods1 feed filter [not(isempty(.Period))] {t}
|
|
product
|
|
loopsel[fun(t:TUPLE)
|
|
dataSJcar_BoxNet_timespace windowintersectsS[box3d(attr(t,NBox_p), attr(t,Period_t))]
|
|
sort rdup dataSJcar gettuples
|
|
filter [.Trip passes attr(t,Pos_p)]
|
|
filter [(.Trip at attr(t,Pos_p)) present attr(t,Period_t)]
|
|
projectextend[Licence; Id_pos: attr(t,Id_p), Times: attr(t,Period_t)]]
|
|
project[Id_pos, Times, Licence]
|
|
sortby[Id_pos asc, Times asc, Licence asc]
|
|
krdup[Id_pos, Times, Licence]
|
|
consume;
|
|
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 16: List the pairs of licenses for vehicles the first from #
|
|
# QueryLicences1, the second from QueryLicences2, where the #
|
|
# corresponding vehicles are both present within a region from #
|
|
# QueryRegions1 during a period from QueryPeriod1, but do not meet #
|
|
# each other there and then. #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres016 =
|
|
QueryLicences1 feed {l}
|
|
loopjoin [dataSJcar_Licence_btree dataSJcar exactmatch [.Licence_l]] {c}
|
|
QueryPeriods1 feed
|
|
filter[not(isempty(.Period))]{p}
|
|
symmjoin [.Trip_c present ..Period_p]
|
|
projectextend[Id_p, Period_p; Licence: .Licence_c, Trip: .Trip_c atperiods .Period_p]
|
|
filter [not(isempty(.Trip))]
|
|
QueryRegions1JNet feed
|
|
filter[not(isempty(.Region))] {r}
|
|
symmjoin[.Trip passes ..Region_r]
|
|
projectextend[Licence, Id_r, Region_r, Id_p, Period_p; Trip: .Trip at .Region_r]
|
|
filter [not(isempty(.Trip))]{a}
|
|
QueryLicences2 feed {l}
|
|
loopjoin [dataSJcar_Licence_btree dataSJcar exactmatch [.Licence_l]] {c}
|
|
QueryPeriods1 feed
|
|
filter[not(isempty(.Period))]{p}
|
|
symmjoin [.Trip_c present ..Period_p]
|
|
projectextend[Id_p, Period_p; Licence: .Licence_c, Trip: .Trip_c atperiods .Period_p]
|
|
filter [not(isempty(.Trip))]
|
|
QueryRegions1JNet feed
|
|
filter[not(isempty(.Region))] {r}
|
|
symmjoin[.Trip passes ..Region_r]
|
|
projectextend[Licence, Id_r, Region_r, Id_p, Period_p; Trip: .Trip at .Region_r]
|
|
filter [not(isempty(.Trip))]{b}
|
|
symmjoin[(((.Id_r_a = ..Id_r_b) and
|
|
(.Id_p_a = ..Id_p_b)) and
|
|
(.Licence_a # ..Licence_b)) and
|
|
(not(.Trip_a intersects ..Trip_b))]
|
|
project [Id_r_a, Period_p_a, Licence_a, Licence_b]
|
|
sortby[Id_r_a, Period_p_a, Licence_a, Licence_b]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
################################################################################
|
|
# #
|
|
# Query 17: Which points from QueryPoints have been visited by a maximum #
|
|
# number of different vehicles? #
|
|
# #
|
|
################################################################################
|
|
|
|
let OBAJNres017tmp1 =
|
|
QueryPointsJNetAll feed
|
|
extend[Prect: netbox(.Pos)]
|
|
projectextend[Id, Pos; NBox: rectangle2(minD(.Prect,1), maxD(.Prect, 1),
|
|
minD(.Prect,2) - 0.00001,
|
|
maxD(.Prect,2) + 0.00001)]
|
|
loopjoin[
|
|
dataSJcar_TrajBoxNet windowintersectsS[.NBox]
|
|
sort rdup dataSJcar gettuples]
|
|
project [Id, Licence]
|
|
sortby [Id, Licence]
|
|
krdup [Id, Licence]
|
|
groupby[Id; Hits: group feed count]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
let OBAJNres017 =
|
|
OBAJNres017tmp1 feed
|
|
filter [.Hits = (OBAJNres017tmp1 feed max[Hits])]
|
|
project [Id, Hits]
|
|
consume;
|
|
|
|
query now();
|
|
|
|
delete OBAJNres017tmp1;
|
|
|
|
################################################################################
|
|
# #
|
|
# Store Query times #
|
|
# #
|
|
################################################################################
|
|
|
|
let QRT_JNET_OBA = SEC2COMMANDS feed consume;
|
|
|
|
#save EVAL_SEC2COMMANDS_BERLINMOD_OBA_JNET to 'BerlinMODOBAJNetRunTimesRunX.DAT';
|
|
|
|
close database;
|