###################################################################### ## 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 on the # JNetwork Representation Secondo DBMS. Created by 'BerlinMOD_DataGenerator.SEC' # and 'JNetwork_CreateBMODObjects.SEC' open database berlinmod; # 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 2: How many vehicles exist that are passenger cars? let OBAJNres002 = dataSJcar feed filter [.Type = "passenger"] count; # 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 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 5: What is the minimum distance between places, where a vehicle with a # license from QueryLicences1 and a vehicle with licenses from # QueryLicence2 have been? let OBAJNres005tmp1 = QueryLicences1 feed loopsel[dataSJcar_Licence_btree dataSJcar exactmatch[.Licence]] projectextend[Licence; Traj: fromnetwork(trajectory(.Trip))] consume; let OBAJNres005 = QueryLicences2 feed loopsel[ dataSJcar_Licence_btree dataSJcar exactmatch[.Licence] ] projectextend[Licence; Traj: fromnetwork(trajectory(.Trip))]{t2} OBAJNres005tmp1 feed {t1} product projectextend[ ; Licence1: .Licence_t1, Licence2: .Licence_t2, Dist: round(distance(.Traj_t1, .Traj_t2),3)] sortby [Licence1, Licence2] consume; #delete temporary object delete OBAJNres005tmp1; # 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; 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 temporary object delete OBAJNres006tmp1; # 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; 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 temporary object delete OBAJNres007tmp1; # 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 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 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; 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) and (.Licence_c1 # ..Licence_c2)) and (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; # delete temporary object 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 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; 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; # delete temporary object delete OBAJNres012tmp1; # 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 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 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 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 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; let OBAJNres017 = OBAJNres017tmp1 feed filter [.Hits = (OBAJNres017tmp1 feed max[Hits])] project [Id, Hits] consume; # delete temporary object delete OBAJNres017tmp1; # Store Query Run Times let QRT_JNET_OBA = SEC2COMMANDS feed consume; # Uncomment next line to save runtimes on hard disk #save QRT_JNET_OBA to 'JNetworkOBARunTimes.DAT'; # finsihed close database close database;