###################################################################### ## File: TBA-CR-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 TBA-Queries of the BerlinMOD benchmark ## in the Secondo DBMS. open database berlinmod; query now(); ################################################################################ # # # Query 1: What are the models of the vehicles with license plate numbers from # # QueryLicence? # # # ################################################################################ let TBACRres001 = QueryLicences feed {O} loopjoin[ dataMCcar_Licence_btree dataMCcar exactmatch[.Licence_O] ] project[Licence, Model] consume; query now(); ################################################################################ # # # Query 2: How many vehicles exist that are passenger cars? # # # ################################################################################ let TBACRres002 = dataMCcar feed filter [.Type = "passenger"] count; query now(); ################################################################################ # # # Query 3: Where have the vehicles with licenses from QueryLicence1 been at # # each instant from QueryInstant1? # # # ################################################################################ let TBACRres003 = QueryLicences feed head[10] {LL} loopsel[dataMCcar_Licence_btree dataMCcar exactmatch[.Licence_LL] project[Licence, Moid] {LL}] loopjoin[dataMCtrip_Moid_btree dataMCtrip exactmatch[.Moid_LL]] QueryInstants feed head[10] {II} symmjoin[.Trip present ..Instant_II] projectextend[; Licence: .Licence_LL, Instant: .Instant_II, Pos: val(.Trip atinstant .Instant_II)] sortby[Instant asc, Licence asc] project[Instant, Licence, Pos] consume; query now(); ################################################################################ # # # Query 4: Which license plate numbers belong to vehicles that have passed the # # points from QueryPoints? # # # ################################################################################ let TBACRres004 = QueryPoints feed loopjoin[ dataMCtrip_Trip_sptuni windowintersectsS[bbox(.Pos)] sort rdup dataMCtrip gettuples] filter[.Trip passes .Pos] project[Id, Moid] loopsel[fun(t1: TUPLE) dataMCcar_Moid_btree dataMCcar exactmatch[attr(t1,Moid)] projectextend[Moid; Id: attr(t1,Id), Licence: .Licence]] sortby[Id asc, Licence asc] krdup[Id, Licence] project[Licence, Id] 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 TBACRres005tmp1 = QueryLicences feed head[10] project[Licence] {LL1} loopsel[ fun(t:TUPLE) dataMCcar_Licence_btree dataMCcar exactmatch[attr(t,Licence_LL1)] {CAR} loopsel[dataMCtrip_Moid_btree dataMCtrip exactmatch[.Moid_CAR]] projectextend[;Traj: simplify(trajectory(.Trip),0.000001)] aggregateB[Traj; fun(L1: line, L2: line) union_new(L1,L2); [const line value ()]] feed namedtransformstream[Traj] extend[Licence: attr(t,Licence_LL1)] ] consume; query now(); let TBACRres005tmp2 = QueryLicences feed head[20] filter[.Id >10] {LL1} loopsel[ fun(t:TUPLE) dataMCcar_Licence_btree dataMCcar exactmatch[attr(t,Licence_LL1)] {CAR} loopsel[dataMCtrip_Moid_btree dataMCtrip exactmatch[.Moid_CAR]] projectextend[;Traj: simplify(trajectory(.Trip),0.000001)] aggregateB[Traj; fun(L1: line, L2: line) union_new(L1,L2); [const line value ()]] feed namedtransformstream[Traj] extend[Licence: attr(t,Licence_LL1)] ] consume; query now(); let TBACRres005 = TBACRres005tmp1 feed {LL1} TBACRres005tmp2 feed {LL2} product projectextend[ ; Licence1: .Licence_LL1, Licence2: .Licence_LL2, Dist: round(distance(.Traj_LL1, .Traj_LL2),3) ] sortby[Licence1, Licence2] consume; query now(); delete TBACRres005tmp1; delete TBACRres005tmp2; 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 TBACRres006tmp1 = dataMCcar feed filter[.Type = "truck"] project[Licence, Moid] loopsel[fun(t: TUPLE) dataMCtrip_Moid_btree dataMCtrip exactmatch[attr(t,Moid)] projectextend[Trip, Moid; BBox: bbox(.Trip), Licence: attr(t,Licence)] projectextend[Moid, Licence, Trip, BBox; 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 TBACRres006 = TBACRres006tmp1 feed {C1} TBACRres006tmp1 feed {C2} spatialjoin[Box_C1, Box_C2] filter[ .Licence_C1 < .Licence_C2 ] filter[ everNearerThan(.Trip_C1, .Trip_C2, 10.0) ] projectextend[ ; Licence1: .Licence_C1, Licence2: .Licence_C2 ] sort rdup consume; query now(); delete TBACRres006tmp1; 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 TBACRres007tmp1 = QueryPoints feed project[Id, Pos] loopjoin[ fun(t:TUPLE) dataMCtrip_Trip_sptuni windowintersectsS[bbox(attr(t,Pos))] sort rdup dataMCtrip gettuples filter[.Trip passes attr(t,Pos)] loopjoin[ dataMCcar_Moid_btree dataMCcar exactmatch[.Moid] filter[.Type = "passenger"] project[Licence] {X} ] projectextend[; TimeAtPos: inst(initial( .Trip at attr(t,Pos)))] min[TimeAtPos] feed namedtransformstream[FirstTime] filter[not(isempty(.FirstTime))] ] consume; query now(); let TBACRres007 = TBACRres007tmp1 feed loopjoin[ fun(t:TUPLE) dataMCtrip_Trip_sptmpuni windowintersectsS[box3d(bbox(attr(t,Pos)), attr(t,FirstTime))] sort rdup dataMCtrip gettuples filter[val(.Trip atinstant attr(t,FirstTime)) = attr(t,Pos)] loopsel[fun(t2: TUPLE) dataMCcar_Moid_btree dataMCcar exactmatch[attr(t2,Moid)] filter[.Type = "passenger"] project[Licence, Moid]] ] project[ Pos, Licence, Moid ] rdup project[Pos, Licence] consume; query now(); delete TBACRres007tmp1; 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 TBACRres008 = QueryPeriods feed head[10] QueryLicences feed head[10] project[Licence] product loopsel[ fun(t:TUPLE) dataMCcar_Licence_btree dataMCcar exactmatch[attr(t,Licence)] {CAR} extend[Dist: round( dataMCtrip_Moid_btree dataMCtrip exactmatch[.Moid_CAR] filter[.Trip present attr(t,Period)] projectextend[;L: round(length(.Trip atperiods attr(t,Period)),3)] sum[L], 3)] projectextend[; Licence: attr(t,Licence), Period: attr(t,Period), Dist: .Dist] ] sortby[Licence, Period, Dist] consume; query now(); ################################################################################ # # # Query 9: What is the longest distance that was traveled by a vehicle during # # each of the periods from QueryPeriods? # # # ################################################################################ let TBACRres009 = QueryPeriods feed extend[ PeriodBox: queryrect2d( minimum(.Period)) union queryrect2d(maximum(.Period))] loopjoin[ fun(t:TUPLE) dataMCtrip_Trip_tmpuni windowintersectsS[ attr(t,PeriodBox) ] sort rdup dataMCtrip gettuples projectextend[Moid; TripOdo: length(.Trip atperiods attr(t,Period))] filter[.TripOdo > 0] sortby[Moid asc] groupby[Moid; Length: round(group feed sum[TripOdo],3)] ] groupby[Id, Period; Dist: group feed max[Length]] project[Period, Dist] sortby [Period, Dist] 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 TBACRres010 = QueryLicences feed head[10] project[Licence] {V1} loopsel[ fun(t:TUPLE) dataMCcar_Licence_btree dataMCcar exactmatch[attr(t,Licence_V1)] project[Moid] loopjoin[dataMCtrip_Moid_btree dataMCtrip exactmatch[.Moid] remove[Moid]] {V3} extend[T3bbx: bbox(.Trip_V3)] loopjoin[ fun(u:TUPLE) dataMCtrip_Trip_sptmpuni windowintersectsS[rectangle3( minD(attr(u,T3bbx),1) - 3.0, maxD(attr(u,T3bbx),1) + 3.0, minD(attr(u,T3bbx),2) - 3.0, maxD(attr(u,T3bbx),2) + 3.0, minD(attr(u,T3bbx),3), maxD(attr(u,T3bbx),3)) ] sort rdup dataMCtrip gettuples filter[.Moid # attr(u, Moid_V3)] filter[everNearerThan(attr(u, Trip_V3), .Trip, 3.0)] projectextend[Moid; Times: deftime((distance(attr(u, Trip_V3), .Trip) < 3.0) at TRUE)] filter[not(isempty(.Times))] sortby[Moid] groupby[Moid; Times1: group feed aggregateB[Times; fun(P1:periods, P2:periods) P1 union P2; [const periods value ()] ] ] loopjoin[dataMCcar_Moid_btree dataMCcar exactmatch[.Moid] project[Licence]] ] projectextend[Moid_V3, Moid, Times1, Trip_V3; QueryLicence: attr(t, Licence_V1), OtherLicence: .Licence] sortby[Moid_V3, Moid, QueryLicence, OtherLicence] groupby[Moid_V3, Moid, QueryLicence, OtherLicence; AllTimes: group feed aggregateB[Times1;fun(P3:periods,P4:periods) P3 union P4; [const periods value ()] ], AllTrips: group feed projectextend[Trip_V3;Start: inst(initial(.Trip_V3))] sortby[Start] projecttransformstream[Trip_V3] concatS ] projectextend[QueryLicence,OtherLicence; Pos: .AllTrips atperiods .AllTimes] ] sortby[QueryLicence, OtherLicence, Pos] consume; query now(); ################################################################################ # # # Query 11: Which vehicles passed a point from QueryPoints1 at one of the # # instants from QueryInstant1? # # # ################################################################################ let TBACRres011 = QueryPoints feed head[10] project[Id, Pos] {PP} QueryInstants feed head[10] project[Instant] {II} product loopjoin[ fun(t:TUPLE) dataMCtrip_Trip_sptmpuni windowintersectsS[box3d(bbox(attr(t,Pos_PP)), attr(t,Instant_II))] sort rdup dataMCtrip gettuples filter[.Trip present attr(t,Instant_II)] projectextend[Moid; XPos: val(.Trip atinstant attr(t,Instant_II))] filter[not(isempty(.XPos))] filter[distance(.XPos,attr(t,Pos_PP)) < 0.5] project[Moid] sort rdup ] loopjoin[dataMCcar_Moid_btree dataMCcar exactmatch[.Moid] project[Licence]] projectextend[; Id: .Id_PP, Instant: .Instant_II, Licenc: .Licence] sortby[Id, Instant, Licenc] consume; query now(); ################################################################################ # # # Query 12: Which vehicles met at a point from QueryPoints1 at an instant from # # QueryInstant1? # # # ################################################################################ let TBACRres012 = QueryPoints feed head[10] project[Id, Pos] QueryInstants feed head[10] project[Instant] product loopsel[ fun(t: TUPLE) dataMCtrip_Trip_sptmpuni windowintersectsS[box3d( bbox(attr(t,Pos)), attr(t,Instant))] sort rdup {A} dataMCtrip_Trip_sptmpuni windowintersectsS[box3d( bbox(attr(t,Pos)), attr(t,Instant))] sort rdup {B} symmjoin[.Id_A # ..Id_B] dataMCtrip gettuples2[Id_A] {C} dataMCtrip gettuples2[Id_B_C] filter[.Moid < .Moid_C] filter[no_components(intersection(.Trip, .Trip_C)) > 0] project[Moid, Moid_C] sort rdup loopjoin[dataMCcar_Moid_btree dataMCcar exactmatch[.Moid] project[Licence]] loopjoin[dataMCcar_Moid_btree dataMCcar exactmatch[.Moid_C] project[Licence] {C}] projectextend[ ; Id: attr(t,Id), Instant: attr(t,Instant), Licence1: .Licence, Licence2: .Licence_C] ] sortby[Id, Instant, Licence1, Licence2] consume; query now(); ################################################################################ # # # Query 13: Which vehicles traveled within one of the regions from # # QueryRegions1 during the periods from QueryPeriods1? # # # ################################################################################ let TBACRres013 = QueryRegions feed head[10] filter[not(isempty(.Region))]{RR} QueryPeriods feed head[10] filter[not(isempty(.Period))]{PP} product loopsel [ fun(t:TUPLE) dataMCtrip_Trip_sptmpuni windowintersectsS[box3d( bbox(attr(t,Region_RR)),attr(t,Period_PP))] sort rdup dataMCtrip gettuples filter[(.Trip atperiods attr(t,Period_PP)) passes attr(t,Region_RR) ] project[Moid] sort rdup loopjoin[dataMCcar_Moid_btree dataMCcar exactmatch[.Moid] project[Licence]] projectextend[;Licence: .Licence, Id_r: attr(t,Id_RR), Period: attr(t,Period_PP)] ] sortby[Licence, Id_r, Period] consume; query now(); ################################################################################ # # # Query 14: Which vehicles traveled within one of the regions from # # QueryRegions1 at one of the instants from QueryInstant1? # # # ################################################################################ let TBACRres014 = QueryRegions feed head[10] {RR} QueryInstants feed head[10] {II} product loopsel [ fun(t:TUPLE) dataMCtrip_Trip_sptmpuni windowintersectsS[box3d( bbox(attr(t,Region_RR)),attr(t,Instant_II))] sort rdup dataMCtrip gettuples filter[.Trip present attr(t,Instant_II)] filter[val(.Trip atinstant attr(t,Instant_II)) inside attr(t,Region_RR) ] project[Moid] sort rdup loopjoin[dataMCcar_Moid_btree dataMCcar exactmatch[.Moid] project[Licence]] projectextend[; Licence: .Licence, Id: attr(t,Id_RR), Instant: attr(t,Instant_II)] ] sortby[Id, Instant, Licence] krdup[Id, Instant, Licence] consume; query now(); ################################################################################ # # # Query 15: Which vehicles passed a point from QueryPoints1 during a period # # from QueryPeriods1? # # # ################################################################################ let TBACRres015 = QueryPoints feed head[10] {PO} QueryPeriods feed head[10] {PR} product loopsel [ fun(t:TUPLE) dataMCtrip_Trip_sptmpuni windowintersectsS[ box3d(bbox(attr(t,Pos_PO)),attr(t,Period_PR)) ] sort rdup dataMCtrip gettuples filter[.Trip present attr(t,Period_PR)] filter[(.Trip atperiods attr(t,Period_PR)) passes attr(t,Pos_PO) ] project[Moid] sort rdup loopjoin[dataMCcar_Moid_btree dataMCcar exactmatch[.Moid] project[Licence]] projectextend[; Id: attr(t,Id_PO), Period: attr(t,Period_PR),Licence: .Licence] ] sortby[Id, Period, Licence] project[Licence, Id, Period] 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 TBACRres016tmp1 = QueryRegions feed head[10] {RR} QueryPeriods feed head[10] {PP} product extend[QBox: box3d(bbox(.Region_RR), .Period_PP)] QueryLicences feed head[10] {LL} product loopjoin[ fun(tt1:TUPLE) dataMCcar_Licence_btree dataMCcar exactmatch[attr(tt1,Licence_LL)] project[Moid] loopsel[dataMCtrip_Moid_btree exactmatchS[.Moid]] sort {L} dataMCtrip_Trip_sptmpuni windowintersectsS[attr(tt1,QBox)] sort rdup {W} mergejoin[Id_L, Id_W] dataMCtrip gettuples2[Id_L] filter[ .Trip present attr(tt1,Period_PP) ] filter[ .Trip passes attr(tt1,Region_RR) ] projectextend[Moid; Trip: (.Trip atperiods attr(tt1,Period_PP)) at attr(tt1,Region_RR)] filter[no_components(.Trip) > 0] ] projectextend[Id_RR, Id_PP, Region_RR, Period_PP, Trip, Moid; Licence: .Licence_LL] consume; query now(); let TBACRres016tmp2 = QueryRegions feed head[10] {RR} QueryPeriods feed head[10] {PP} product extend[QBox: box3d(bbox(.Region_RR), .Period_PP)] QueryLicences feed head[20] filter[.Id > 10] {LL} product loopjoin[ fun(tt1:TUPLE) dataMCcar_Licence_btree dataMCcar exactmatch[attr(tt1,Licence_LL)] project[Moid] loopsel[dataMCtrip_Moid_btree exactmatchS[.Moid]] sort {L} dataMCtrip_Trip_sptmpuni windowintersectsS[attr(tt1,QBox)] sort rdup {W} mergejoin[Id_L, Id_W] dataMCtrip gettuples2[Id_L] filter[ .Trip present attr(tt1,Period_PP) ] filter[ .Trip passes attr(tt1,Region_RR) ] projectextend[Moid; Trip: (.Trip atperiods attr(tt1,Period_PP)) at attr(tt1,Region_RR)] filter[no_components(.Trip) > 0] ] projectextend[Id_RR, Id_PP, Region_RR, Period_PP, Trip, Moid; Licence: .Licence_LL] consume; query now(); let TBACRres016 = TBACRres016tmp1 feed {C1} TBACRres016tmp2 feed {C2} symmjoin[ (.Moid_C1 # ..Moid_C2) and (.Id_RR_C1 = ..Id_RR_C2) and (.Id_PP_C1 = ..Id_PP_C2) ] filter[ not(everNearerThan(.Trip_C1, .Trip_C2, 0.1)) ] projectextend[Moid_C1, Moid_C2 ; Licence1: .Licence_C1, Licence2: .Licence_C2, Region: .Region_RR_C1, Period: .Period_PP_C1, Id_RR: .Id_RR_C1, Id_PP: .Id_PP_C1 ] sortby[Id_RR, Id_PP, Moid_C1, Moid_C2] krdup[Id_RR, Id_PP, Moid_C1, Moid_C2] project[Id_RR, Id_PP, Licence1, Licence2] consume; query now(); delete TBACRres016tmp1; delete TBACRres016tmp2; query now(); ################################################################################ # # # Query 17: Which points from QueryPoints have been visited by a maximum # # number of different vehicles? # # # ################################################################################ let TBACRres017tmp1 = QueryPoints feed project[Id, Pos] loopjoin[ fun(t:TUPLE) dataMCtrip_Trip_sptuni windowintersectsS[bbox(attr(t,Pos))] sort rdup dataMCtrip gettuples filter[.Trip passes attr(t,Pos)] project[Moid] sort rdup count feed namedtransformstream[Hits] ] sortby[Hits desc, Id asc] consume; query now(); let TBACRres017 = (TBACRres017tmp1 feed head[1] extract[Hits]) within [ fun(MaxHits: int) TBACRres017tmp1 feed filter[.Hits = MaxHits ] tconsume ] feed project [Id, Hits] consume; query now(); delete TBACRres017tmp1; query now(); ################################################################################ # # # Store Query times # # # ################################################################################ let QRT_BMOD_TBA = SEC2COMMANDS feed consume; #save EVAL_SEC2COMMANDS_BERLINMODTBA to 'BerlinMODNNNTBARunTimesRunX.DAT'; close database;