secondo/bin/Scripts/OrderedRelationGraphFromFullOSMImport.SEC

###########################################################################
#
# Construction of a Road Network from OpenStreetMap Data in OrderedRelation
# Graph Representation
#
# Uses NestedRelationAlgebra
#
# -> adapt the filename of the osm-file to be imported.
#
# running times and result sizes refer to arnsberg-regbez.osm, using 
# GlobalMemory = 2 GB
# GlobalMemory = 6 GB (Version von arnsberg 14.09.2015, aktuelles Secondo)
#
###########################################################################

# Database arnsberg

query fullosmimport('Raetsel/arnsberg.osm', "City")

# 155 secs

# relation CityNodes with 5008465 tuples stored
# relation CityNodeTags with 389898 tuples stored
# relation CityWays with 6419349 tuples stored
# relation CityWayTags with 2193352 tuples stored
# relation CityRelations with 355347 tuples stored
# relation CityRelationTags with 52448 tuples stored

# 482 secs

# file arnsberg-regbez-latest.osm opened successfully
# relation CityNodes with 12088760 tuples stored
# relation CityNodeTags with 817775 tuples stored
# relation CityWays with 15922663 tuples stored
# relation CityWayTags with 5419221 tuples stored
# relation CityRelations with 788797 tuples stored
# relation CityRelationTags with 112188 tuples stored



let CityNodesNew = CityNodes feed
  extend[Easting: .Lon * 1000000, Northing: .Lat * 1000000]
  extend[Box: rectangle2(.Easting, .Easting, .Northing, .Northing)]
  sortby[Box]
  projectextend[NodeId;  Pos: makepoint(.Lon, .Lat)] 
  addcounter[NodeIdNew, 1] 
  consume

# 213 secs
# 576 secs

# query nodes(CityNodesNew feed addid bulkloadrtree[Pos]) consume


let Ways =
  CityNodesNew feed
  CityWays feed itHashJoin[NodeId, NodeRef] sortby[WayId, NodeCounter] nest[WayId; NodeList]
    extend[Curve  : .NodeList afeed projecttransformstream[Pos] collect_line[TRUE]] 
  CityWayTags feed nest[WayIdInTag; WayInfo] itHashJoin[WayId, WayIdInTag]
  extend[Box: bbox(.Curve) scale[1000000.0]]
  sortby[Box]
  remove[Box]
  consume

# 3339 secs
# 4414 secs


derive Ways_Curve_rtree = Ways feed addid projectextend[TID; Box: bbox(.Curve)] bulkloadrtree[Box]

# 10 secs
# 25 secs



# query Ways_Curve_rtree windowintersectsS[flughafendortmund] Ways gettuples count


# query Ways feed filter[.WayInfo afeed filter[(.WayTagKey = "aeroway") and (.WayTagValue = "runway")] count > 0] consume

let Ways_WayTag = Ways feed addid project[TID, WayInfo] unnest[WayInfo] extend[WayTag: tostring(.WayTagKey + "#" + .WayTagValue)] sortby[WayTag] createbtree[WayTag]

# - secs
# 210 secs

# query Ways_WayTag exactmatchS["aeroway#runway"] Ways gettuples consume

# query Ways_WayTag exactmatchS["aeroway#terminal"] Ways gettuples consume

# query Ways_WayTag rangeS["building", "building"++] Ways gettuples project[WayInfo] consume



# query Ways feed addid projectextend[TID; Box: bbox(.Curve)] bulkloadrtree[Box]

# query nodes(Ways feed addid projectextend[TID; Box: bbox(.Curve)] bulkloadrtree[Box]) consume



let RoadsB = Ways feed filter[.WayInfo afeed filter[.WayTagKey = "highway"] count > 0] consume

# 113 secs
# 182 secs

derive RoadsB_Curve_rtree = RoadsB feed addid projectextend[TID; Box: bbox(.Curve)] bulkloadrtree[Box]

# 3.56 secs
# 4.67 secs

# compute Nodes as the union of start points, end points and intersections of RoadsB

let Nodes = 
  CityWays feed
  CityWays feed {h2}
  itHashJoin[NodeRef, NodeRef_h2]
  filter[.WayId # .WayId_h2]
  CityNodesNew feed
  itHashJoin[NodeRef, NodeId]
  RoadsB feed {r1} itHashJoin[WayId, WayId_r1]
  RoadsB feed {r2} itHashJoin[WayId_h2, WayId_r2]
  project[WayId, NodeCounter, NodeIdNew, Pos]
RoadsB feed 
  projectextend[WayId; Node: .NodeList afeed filter[.NodeCounter = 0] aconsume]
  unnest[Node]
  project[WayId, NodeCounter, NodeIdNew, Pos]
  concat
RoadsB feed 
  extend[HighNodeNo: (.NodeList afeed count) - 1]
  projectextend[WayId; Node:  fun(t: TUPLE) 
    attr(t, NodeList) afeed filter[.NodeCounter = attr(t, HighNodeNo)] aconsume]
  unnest[Node]
  project[WayId, NodeCounter, NodeIdNew, Pos]
  concat
  sortby[WayId, NodeCounter]
  rdup
  consume

# 269 secs
# 655 secs



let EdgesUp =
  Nodes feed nest[WayId; SectionNodes]
    projectextend[WayId; Sections: .SectionNodes afeed
      extend_last[Source: ..NodeIdNew::0, Target: .NodeIdNew::0, 
        SourcePos: ..Pos::[const point value undef], 
        TargetPos: .Pos::[const point value undef],
        SourceNodeCounter: ..NodeCounter::0,
        TargetNodeCounter: .NodeCounter::0]
      filter[.Source # 0] 
      project[Source, Target, SourcePos, TargetPos, 
        SourceNodeCounter, TargetNodeCounter]
      aconsume]
  RoadsB feed {r}
  itHashJoin[WayId, WayId_r]
  projectextend[WayId; Sections: fun(t:TUPLE)
    attr(t, Sections) afeed 
    extend[
      Curve: fun(u: TUPLE)
      attr(t, NodeList_r) afeed 
        filter[.NodeCounter_r between[attr(u, SourceNodeCounter),
          attr(u, TargetNodeCounter)] ]
        projecttransformstream[Pos_r]
        collect_sline[TRUE],
      RoadName: attr(t, WayInfo_r) afeed filter[.WayTagKey_r = "name"] extract
       [WayTagValue_r],
      RoadType: attr(t, WayInfo_r) afeed filter[.WayTagKey_r = "highway"] extract
       [WayTagValue_r]
    ]
    aconsume ]
  unnest[Sections]
  consume

# 337 secs
# 560 secs



let EdgesDown =
  Nodes feed nest[WayId; SectionNodes]
    projectextend[WayId; Sections: .SectionNodes afeed sortby[NodeCounter desc]
      extend_last[Source: ..NodeIdNew::0, Target: .NodeIdNew::0, 
        SourcePos: ..Pos::[const point value undef], 
        TargetPos: .Pos::[const point value undef],
        SourceNodeCounter: ..NodeCounter::0,
        TargetNodeCounter: .NodeCounter::0]
      filter[.Source # 0] 
      project[Source, Target, SourcePos, TargetPos, 
        SourceNodeCounter, TargetNodeCounter]
      aconsume]
  RoadsB feed 
    filter[.WayInfo afeed filter[.WayTagKey = "oneway"] 
      filter[(.WayTagValue = "yes")] count = 0] {r}
  itHashJoin[WayId, WayId_r]
  projectextend[WayId; Sections: fun(t:TUPLE)
    attr(t, Sections) afeed extend[Curve: fun(u: TUPLE)
      attr(t, NodeList_r) afeed sortby[NodeCounter_r desc]
        filter[.NodeCounter_r between[attr(u, TargetNodeCounter),
          attr(u, SourceNodeCounter)] ]
        projecttransformstream[Pos_r]
        collect_sline[TRUE],
      RoadName: attr(t, WayInfo_r) afeed filter[.WayTagKey_r = "name"] extract
       [WayTagValue_r],
      RoadType: attr(t, WayInfo_r) afeed filter[.WayTagKey_r = "highway"] extract
       [WayTagValue_r]
    ]
    aconsume ]
  unnest[Sections]
  consume

# 372 secs
# 631 secs



let Edges = EdgesUp feed EdgesDown feed concat 
  projectextend[Source, Target, SourcePos, TargetPos, SourceNodeCounter, TargetNodeCounter, Curve, RoadName, 
    RoadType; WayId: .WayId] 
  oconsume[Source, Target]

# 169 secs
# 107 secs



let EdgeIndex = Edges feed projectextend[Source, Target, Curve, SourcePos; Box: bbox(.Curve)] 
  filter[isdefined(.Box)] 
  extend[Box2: bbox(.SourcePos) scale[1000000.0]]
  sortby[Box2]
  project[Source, Target, Curve, Box]
  consume

# 99 secs
# 118 secs


# query nodes(EdgeIndex feed addid bulkloadrtree[Box]) consume

derive EdgeIndex_Box_rtree = EdgeIndex feed addid bulkloadrtree[Box]

# 9 secs
# 11.4 secs


# Queries
#
# Get edges from a restricted area:

# let hombruch = [const rect value (7.419 7.457 51.462 51.484)]

# query EdgeIndex_Box_rtree EdgeIndex windowintersects[hombruch] remove[Box] 
# loopsel[Edges orange[.Source, .Target; .Source, .Target]] consume

# 2.17 secs, 3.83, 2.02

# query Ways_Curve_rtree windowintersectsS[hombruch] Ways gettuples remove[NodeList] consume

# 5.08 secs, 4.03, 5.66


# Runtime for Scripts/OrderedRelationGraphFromFullOSMImport.SEC: Times (elapsed / cpu): 
# 84:56min (5095.72sec) /2510.84sec = 2.02949

# Runtime for Scripts/OrderedRelationGraphFromFullOSMImport.SEC: Times (elapsed / cpu): 
# 133:07min (7986.96sec) /5679.17sec = 1.40636