################################################################################ ## File: Network Queries for Trip Based Approach ############################### ################################################################################ ## This file is part of SECONDO. ## ## ## ## Copyright (C) 2010, 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 ## ################################################################################ # JNetwork queries for the trip based approach of the BerlinMOD Benchmark. # # The script assumes that there is a database berlinmod with a jnetwork data # model representation of the BerlinMOD Benchmark data. # # This database can be generated by the script 'BerlinMOD_DataGenerator.SEC'. # The network data model representation and accroding indexes can be generated # with the script 'JNetwork_CreateObjects.SEC' # # Start Script Opening the Database# open database berlinmod; # Query 1: What are the models of the vehicles with license plate numbers from # QueryLicence? let TBAJNres001 = QueryLicences feed {l} loopjoin [dataMcar_Licence_btree dataMcar exactmatch[.Licence_l]] project [Licence, Model] consume; # Query 2: How many vehicles exit that are passenger cars? let TBAJNres002 = dataMcar feed filter [.Type = "passenger"] count; # Query 3: Where have the vehicles with licenses from QueryLicence1 been at # each instant from QueryInstant1? let TBAJNres003 = QueryLicences1 feed {l} loopsel[dataMcar_Licence_btree dataMcar exactmatch[.Licence_l] {ll}] loopjoin[dataMJtrip_Moid_btree dataMJtrip exactmatch[.Moid_ll]] QueryInstant1 feed {i} symmjoin [.Trip present ..Instant_i] projectextend[Instant_i, Licence_ll; Pos: val(.Trip atinstant .Instant_i)] sortby[Instant_i, Licence_ll] consume; # Query 4: Which license plate numbers belong to vehicles that have passed the # points from QueryPoints? let TBAJNres004 = 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[dataMJtrip_TrajBoxNet windowintersectsS[.NBox] sort rdup dataMJtrip gettuples] project [Moid, Id] loopsel[fun(t:TUPLE) dataMcar_Moid_btree dataMcar exactmatch[attr(t, Moid)] projectextend[Licence; Id: attr(t,Id)]] 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 TBAJNres005tmp1 = QueryLicences1 feed project[Licence] {LL1} loopsel[fun(t:TUPLE) dataMcar_Licence_btree dataMcar exactmatch[attr(t,Licence_LL1)] {CAR} loopsel[dataMJtrip_Moid_btree dataMJtrip exactmatch[.Moid_CAR]] projectextend[;Traj: trajectory(.Trip)] aggregateB[Traj; fun(L1: jline, L2: jline) L1 union L2; [const jline value ("JBNet" ())]] feed namedtransformstream[Traxj] extend[Licence: attr(t,Licence_LL1)]] projectextend[Licence; Trax: fromnetwork(.Traxj)] consume; let TBAJNres005 = QueryLicences2 feed project[Licence] {LL2} loopsel[fun(s:TUPLE) dataMcar_Licence_btree dataMcar exactmatch[attr(s,Licence_LL2)] {CAR} loopsel[dataMJtrip_Moid_btree dataMJtrip exactmatch[.Moid_CAR]] projectextend[;Traj: trajectory(.Trip)] aggregateB[Traj; fun(L3: jline, L4: jline) L3 union L4; [const jline value ("JBNet"())]] feed namedtransformstream[Traxj] extend[Licence: attr(s,Licence_LL2)]] projectextend[Licence; Trax: fromnetwork(.Traxj)]{c2} TBAJNres005tmp1 feed {c1} product projectextend [Licence_c1, Licence_c2; Distance: round(distance(.Trax_c1, .Trax_c2),3)] sortby[Licence_c1, Licence_c2] consume; # delete temporary object delete TBAJNres005tmp1; # 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 TBAJNres006tmp1 = dataMcar feed filter [.Type = "truck"] project [Licence, Moid] {c} loopjoin[dataMJtrip_Moid_btree dataMJtrip exactmatch[.Moid_c]] projectextend [;Licence: .Licence_c, Ptrip: fromnetwork(.Trip)] extend[BBox: bbox(.Ptrip)] projectextend[Licence, Ptrip; Box: rectangle3((minD(.BBox,1) - 5.0), (maxD(.BBox,1) + 5.0), (minD(.BBox,2) - 5.0), (maxD(.BBox,2) + 5.0), minD(.BBox,3), maxD(.BBox,3))] consume; let TBAJNres006 = TBAJNres006tmp1 feed {c1} TBAJNres006tmp1 feed {c2} symmjoin[((.Box_c1 intersects ..Box_c2) and (.Licence_c1 < ..Licence_c2)) and (everNearerThan(.Ptrip_c1, ..Ptrip_c2, 10.0)] project [Licence_c1, Licence_c2] sortby [Licence_c1 asc, Licence_c2 asc] krdup [Licence_c1, Licence_c2] consume; # delete intermediate result delete TBAJNres006tmp1; # 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 TBAJNres007tmp1 = 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) dataMJtrip_TrajBoxNet windowintersectsS[attr(t,NBox)] sort rdup dataMJtrip gettuples loopjoin[dataMcar_Moid_btree dataMcar exactmatch [.Moid] filter[.Type = "passenger"] project[Licence] {X}] projectextend[ Licence_X; TimeAtPos: inst(initial(.Trip at attr(t,Pos))), Id: attr(t, Id)]] filter[not(isempty(.TimeAtPos))] sortby [Id asc, TimeAtPos asc] consume; let TBAJNres007 = TBAJNres007tmp1 feed groupby [Id; FirstTime: group feed min[TimeAtPos]]{b} TBAJNres007tmp1 feed {a} symmjoin[(..Id_a = .Id_b) and (..TimeAtPos_a <= .FirstTime_b)] project [Id_a, Licence_X_a] sortby [Id_a asc, Licence_X_a asc] krdup [Id_a, Licence_X_a] consume; # delete intermediate result delete TBAJNres007tmp1; # Query 8: What are the overall traveled distances of the vehicles with # license plate numbers from QueryLicences1 during the periods from # QueryPeriods1? let TBAJNres008 = QueryLicences1 feed {l} loopjoin[dataMcar_Licence_btree dataMcar exactmatch[.Licence_l]] project[Licence, Moid] loopsel[fun(t:TUPLE) dataMJtrip_Moid_btree dataMJtrip exactmatch[attr(t, Moid)] projectextend[Trip; Licence: attr(t,Licence)]] QueryPeriods1 feed symmjoin [.Trip present ..Period] projectextend [Licence, Period, Id; Distance: length(.Trip atperiods .Period)] sortby [Id asc, Licence asc, Distance desc] groupby[Id, Period, Licence; Dist: round(group feed sum[Distance],3)] project[Licence, Period, Dist] sortby[Licence, Period, Dist] consume; # Query 9: What is the longest distance that was traveled by a vehicle during # each of the periods from QueryPeriods? let TBAJNres009 = dataMJtrip feed {c} QueryPeriods feed filter[not(isempty(.Period))]{p} symmjoin[.Trip_c present ..Period_p] projectextend [Moid_c, Period_p, Id_p; Distance: length(.Trip_c atperiods .Period_p)] sortby [Id_p asc, Period_p asc, Moid_c asc, Distance desc] groupby[Id_p, Period_p, Moid_c; Dist: group feed sum[Distance]] groupby[Id_p, Period_p; Dista: round(group feed max[Dist],3)] filter[.Dista > 0.0] project[Period_p, Dista] sortby[Period_p, Dista] 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 TBAJNres010 = QueryLicences1 feed project[Licence] {V1} loopsel[fun(t:TUPLE) dataMcar_Licence_btree dataMcar exactmatch[attr(t,Licence_V1)] project[Moid] loopjoin[dataMJtrip_Moid_btree dataMJtrip exactmatch[.Moid] remove[Moid]] {V3} extend[T3bbx: bbox(.Trip_V3)] extend[PtripA: fromnetwork(.Trip_V3)] loopjoin[fun(u:TUPLE) dataMJtrip_SpatioTemp 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 dataMJtrip gettuples filter[.Moid # attr(u, Moid_V3)] projectextend[Moid; PtripB: fromnetwork(.Trip)] filter[everNearerThan(attr(u, PtripA), .PtripB, 3.0)] projectextend[Moid; Times: deftime((distance(attr(u, PtripA), .PtripB) < 3.0) at TRUE)] filter[not(isempty(.Times))] loopjoin[dataMcar_Moid_btree dataMcar exactmatch[.Moid] project[Licence]]] projectextend[; QueryLicence: attr(t, Licence_V1), OtherLicence: .Licence, Pos: .Trip_V3 atperiods .Times] filter[not(isempty(.Pos))]] sortby[QueryLicence asc, OtherLicence asc] groupby [QueryLicence, OtherLicence; AllPos: group feed aggregateB[Pos; fun(M1:mjpoint, M2:mjpoint) M1 union M2; [const mjpoint value("JBNet"())]]] project[QueryLicence, OtherLicence, AllPos] sortby[QueryLicence, OtherLicence, AllPos] consume; # Query 11: Which vehicles passed a point from QueryPoints1 at one of the # instants from QueryInstant1? let TBAJNres011 = 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 loopsel[fun(t:TUPLE) dataMJtrip_BoxNet_timespace windowintersectsS[box3d(attr(t,NBox_p), attr(t,Instant_i))] sort rdup dataMJtrip gettuples projectextend [Moid; Id: attr(t,Id_p), Instant: attr(t,Instant_i)]]{a} loopjoin[dataMcar_Moid_btree dataMcar exactmatch[.Moid_a]] project[Id_a, Instant_a, Licence] sortby [Id_a asc, Instant_a asc, Licence asc] krdup [Id_a, Instant_a, Licence] consume; # Query 12: Which vehicles met at a point from QueryPoints1 at an instant from # QueryInstant1? let TBAJNres012tmp1 = 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} QueryInstant1 feed {i} product projectextend [Id_p, Pos_p, Instant_i; Box: box3d(.NBox_p, .Instant_i)] loopsel[fun(t:TUPLE) dataMJtrip_BoxNet_timespace windowintersectsS[attr(t,Box)] sort rdup dataMJtrip gettuples projectextend [Moid; Id: attr(t,Id_p), Instant: attr(t,Instant_i)]]{a} loopjoin[dataMcar_Moid_btree dataMcar exactmatch[.Moid_a]] projectextend[Moid, Licence; Id: .Id_a, Instant: .Instant_a] consume; let TBAJNres012 = TBAJNres012tmp1 feed {A} TBAJNres012tmp1 feed {B} symmjoin [(.Id_A = ..Id_B) and (.Instant_A = ..Instant_B) and (.Moid_A < ..Moid_B)] project [Id_A, Instant_A, Licence_A, Licence_B] sortby [Id_A asc, Instant_A asc, Licence_B asc] consume; # delete intermediate result delete TBAJNres012tmp1; # Query 13: Which vehicles traveled within one of the regions from # QueryRegions1 during the periods from QueryPeriods1? let TBAJNres013 = dataMJtrip feed {c} QueryRegions1JNet feed filter[not(isempty(.Region))] {r} symmjoin[.Trip_c passes ..Region_r] projectextend[Moid_c, Id_r; Trip: .Trip_c at .Region_r] QueryPeriods1 feed filter[not(isempty(.Period))]{p} symmjoin [.Trip present ..Period_p] projectextend[Moid_c, Id_r, Period_p; TripA: .Trip atperiods .Period_p] filter[not(isempty(.TripA))] loopjoin [dataMcar_Moid_btree dataMcar exactmatch[.Moid_c]] project[Licence, Id_r, Period_p] sortby [Licence asc, Id_r asc, Period_p asc] krdup [Licence, Id_r, Period_p] consume; # Query 14: Which vehicles traveled within one of the regions from # QueryRegions1 at one of the instants from QueryInstant1? let TBAJNres014 = QueryRegions1JNet feed filter[not(isempty(.Region))] projectextendstream[Id, Region; UReg: units(.Region)] extend[Brect: netbox(.UReg)]{r} QueryInstant1 feed {i} product loopsel[fun(t:TUPLE) dataMJtrip_BoxNet_timespace windowintersectsS[box3d(attr(t,Brect_r), attr(t,Instant_i))] sort rdup dataMJtrip gettuples filter[(val(.Trip atinstant (attr(t,Instant_i)))) inside (attr(t,Region_r))] projectextend [Moid;Instant: attr(t,Instant_i), Id: attr(t,Id_r)]]{a} loopjoin[dataMcar_Moid_btree dataMcar exactmatch [.Moid_a]] projectextend[Licence; Id: .Id_a, Instant: .Instant_a] sortby [Id asc, Instant asc, Licence asc] krdup[Id, Instant, Licence] consume; # Query 15: Which vehicles passed a point from QueryPoints1 during a period # from QueryPeriods1? let TBAJNres015 = 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) dataMJtrip_BoxNet_timespace windowintersectsS[box3d(attr(t,NBox_p), attr(t,Period_t))] sort rdup dataMJtrip gettuples filter[(.Trip atperiods (attr(t,Period_t))) passes attr(t,Pos_p)] projectextend [Moid;Period: attr(t,Period_t), Id: attr(t,Id_p)]]{a} loopjoin[dataMcar_Moid_btree dataMcar exactmatch [.Moid_a]] projectextend[Licence; Id: .Id_a, Period: .Period_a] sortby [Id asc, Period asc, Licence asc] krdup[Id, Period, Licence] project[Licence, Id, Period] 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 TBAJNres016 = QueryLicences1 feed {l} loopjoin [dataMcar_Licence_btree dataMcar exactmatch[.Licence_l]] {a} loopjoin[dataMJtrip_Moid_btree dataMJtrip exactmatch[.Moid_a]] QueryPeriods1 feed filter[not(isempty(.Period))]{p} symmjoin [.Trip present ..Period_p] projectextend[Id_p, Period_p; Licence: .Licence_a, Trip: .Trip atperiods .Period_p] filter[not(isempty(.Trip))] QueryRegions1JNet feed filter[not(isempty(.Region))]{r} symmjoin [.Trip passes ..Region_r] projectextend[Licence, Id_p, Period_p, Id_r; Trip: .Trip at .Region_r] filter [not(isempty(.Trip))]{a} QueryLicences2 feed {l} loopjoin [dataMcar_Licence_btree dataMcar exactmatch[.Licence_l]]{a} loopjoin[dataMJtrip_Moid_btree dataMJtrip exactmatch[.Moid_a]] QueryPeriods1 feed filter[not(isempty(.Period))]{p} symmjoin [.Trip present ..Period_p] projectextend[Id_p, Period_p; Licence: .Licence_a, Trip: .Trip atperiods .Period_p] filter[not(isempty(.Trip))] QueryRegions1JNet feed filter[not(isempty(.Region))]{r} symmjoin [.Trip passes ..Region_r] projectextend[Licence, Id_p, Id_r; Trip: .Trip at .Region_r] filter [not(isempty(.Trip))]{b} symmjoin[(.Id_r_a = ..Id_r_b) and (.Id_p_a = ..Id_p_b)] filter[.Licence_a # .Licence_b] filter [not(.Trip_a intersects .Trip_b)] project [Id_r_a, Id_p_a, Licence_a, Licence_b] sortby [Id_r_a asc, Id_p_a asc, Licence_a asc, Licence_b asc] krdup [Id_r_a, Id_p_a, Licence_a, Licence_b] consume; # Query 17: Which points from QueryPoints have been visited by a maximum # number of different vehicles? let TBAJNres017tmp1 = 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) dataMJtrip_TrajBoxNet windowintersectsS[attr(t,NBox)] sort rdup dataMJtrip gettuples projectextend [Moid; Id_p: attr(t,Id)]] sortby [Id_p asc, Moid asc] krdup[Id_p, Moid] groupby[Id_p; Hits: group feed count] consume; let TBAJNres017 = TBAJNres017tmp1 feed filter [.Hits = (TBAJNres017tmp1 feed max[Hits])] project [Id_p, Hits] consume; # delete temporary object delete TBAJNres017tmp1; # save query runtimes let QRT_JNET_TBA = SEC2COMMANDS feed consume; # Uncomment the next line if you want to save run time information on disk # save QRT_JNET_TBA to 'JNetworkTBARunTimes.DAT'; # Finish Script and Close Database close database;