secondo/bin/Scripts/OrderedRelationGraphFromFullOSMImport.Railways.SEC

###########################################################################
#
# Construction of a Railway 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
#
# This script can be used to create a railway network in the same database
# as used for the road network. Can be run after the script for creating the road
# network.
#
###########################################################################

# Database hombruch

# query fullosmimport('arnsberg-regbez.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


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

# 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


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

# 10 secs



# 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 Roads = Ways feed filter[.WayInfo afeed filter[.WayTagKey = "highway"] count > 0] consume

# 113 secs

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

# 3.56 secs




let Railways = Ways feed filter[.WayInfo afeed filter[.WayTagKey = "railway"] filter[.WayTagValue = "rail"] count > 0] consume


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

let NodesR = 
  CityWays feed 
  CityWays feed {h2}
  itHashJoin[NodeRef, NodeRef_h2]
  filter[.WayId # .WayId_h2]
  CityNodesNew feed  
  itHashJoin[NodeRef, NodeId]
  Railways feed {r1} itHashJoin[WayId, WayId_r1]
  Railways feed {r2} itHashJoin[WayId_h2, WayId_r2]
  project[WayId, NodeCounter, NodeIdNew, Pos]
Railways feed 
  projectextend[WayId; Node: .NodeList afeed filter[.NodeCounter = 0] aconsume]
  unnest[Node]
  project[WayId, NodeCounter, NodeIdNew, Pos]
  concat
Railways 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



let EdgesUpR =
  NodesR 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]
  Railways 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],
      MaxSpeed: attr(t, WayInfo_r) afeed filter[.WayTagKey_r = "maxspeed"] extract 
       [WayTagValue_r]  
    ]
    aconsume ]
  unnest[Sections]
  consume

# 337 secs



let EdgesDownR =
  NodesR 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]
  Railways 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],
      MaxSpeed: attr(t, WayInfo_r) afeed filter[.WayTagKey_r = "maxspeed"] extract 
       [WayTagValue_r]  
    ]
    aconsume ]
  unnest[Sections]
  consume

# 372 secs



let EdgesR = EdgesUpR feed EdgesDownR feed concat 
  projectextend[Source, Target, SourcePos, TargetPos, SourceNodeCounter, TargetNodeCounter, Curve, RoadName, 
    RoadType, MaxSpeed; WayId: .WayId] 
  oconsume[Source, Target]

# 169 secs



let EdgeIndexR  = EdgesR 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


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




derive EdgeIndexR_Box_rtree = EdgeIndexR feed addid bulkloadrtree[Box]

# 9 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