<?php 

$debugstep=1185;
$debugoutput=1;

function teilweg($db,$p1,$p2,$swtyp,$costfactor){
//
// Bestimmt den naechstgelegenen Weg zu P1/P2 mit sidewalk=swtyp (oder <=103 falls swtyp=0)
// Teilt diesen Weg an p1,p2 auf, sofern das nicht sowieso schon der Anfangs- oder Endpunkt ist
// gibt den Punkt zurueck, an dem die Teilung stattfand, also entweder den schon
// vorhandenen source/target oder den neu entstandenen.
//
// Wurde nicht geteilt, weil da eh schon ein Start/Endpunkt war, wird dieser zurueckgegeben
// costfactor ist der _minimale_ costfactor der beiden Wegstücke
//
 $debugoutput=1;
 $ret=-1;
 if($swtyp==0){
  $query="select gid,source,target,st_distance(the_geom,st_geometryfromtext('POINT($p1 $p2)',4326),true) as dist,st_astext(the_geom) as weg from routing_ways where the_geom && expand(st_geometryfromtext('POINT($p1 $p2)',4326),0.00001) and (sidewalk is null or sidewalk<107) order by dist limit 1;";
 }else{
  $query="select gid,source,target,st_distance(the_geom,st_geometryfromtext('POINT($p1 $p2)',4326),true) as dist,st_astext(the_geom) as weg from routing_ways where the_geom && expand(st_geometryfromtext('POINT($p1 $p2)',4326),0.00001) and sidewalk='$swtyp' order by dist limit 1;";
 }
 $result=pg_query($db,$query);
 $r=pg_fetch_array($result);
 $dist=100;
 if($r){
  $gid=$r['gid'];
  $dist=$r['dist'];
  if($debugoutput){echo "   Naechster Weg $gid dist=$dist\n";}
 }
 if($dist<0.1){
  $query="select ST_Line_Locate_Point((select the_geom from routing_ways where gid=$gid),st_geometryfromtext('POINT($p1 $p2)',4326)) as ll;";
  $result=pg_query($db,$query);
  $e=pg_fetch_array($result);
  $ll=$startll=$e["ll"];
  if($ll<=0.005)$ret=$r['source'];
  if($ll>=0.995)$ret=$r['target'];
  if(($ll>0.005)&&($ll<0.995)){
   if($debugoutput){echo "      Teile Weg $gid bei $ll am Punkt $p1 $p2\n";}
   pg_query($db, "BEGIN WORK");  
   $query="select max(source) as m from routing_ways;";
   $result=pg_query($db,$query);
   $e=pg_fetch_array($result);
   $po=$e['m'];
   $query="select max(target) as m from routing_ways;";
   $result=pg_query($db,$query);
   $e=pg_fetch_array($result);
   if($e['m']>$po){$po=$e['m'];}
   $po++;
   $query="select max(gid) as m from routing_ways;";
   $result=pg_query($db,$query);
   $e=pg_fetch_array($result); 
   $gi=$e['m']+1;
   $query="select gid,class_id,osm_id,source,target,waysource,the_geom,length,costfactor,hdiff,sidewalk from routing_ways where gid=$gid;";
   $result=pg_query($db,$query);
   $e=pg_fetch_array($result);
   $gid=$e['gid'];
   $sidewalk=$e['sidewalk']+0;
   $osm_id=$e['osm_id'];
   if($e['costfactor']>$costfactor){$costfactor=$e['costfactor'];}
   $q="insert into routing_ways (gid,class_id,osm_id,source,target,waysource,the_geom,length,hdiff,costfactor,sidewalk)  values ($gi,".$e['class_id'].",".$e['osm_id'].",".$e['source'].",".$e['target'].",'".$e['waysource']."',(select the_geom from routing_ways where gid=$gid),".$e['length'].",".$e['hdiff'].",".$costfactor.",".$sidewalk.");";
   $r=pg_query($db,$q);
   $q="update routing_ways set costfactor=$costfactor where gid=$gi;";
   $r=pg_query($db,$q);
   $q="update routing_ways set sidewalk=$sidewalk where gid=$gi;";
   $r=pg_query($db,$q);
   $q="update routing_ways set target=$po where gid=$gid;";
   $r=pg_query($db,$q);   
   $q="update routing_ways set source=$po where gid=$gi;";
   $r=pg_query($db,$q);   
   $q="update routing_ways set hdiff=hdiff*$ll  where gid=$gid;";
   $r=pg_query($db,$q);   
   $q="update routing_ways set hdiff=hdiff*(1-$ll)  where gid=$gi;"; 
   $r=pg_query($db,$q);   
   $q="update routing_ways set the_geom=st_line_substring(the_geom,$ll,1) where gid=$gi;";
   $r=pg_query($db,$q);   
   $q="update routing_ways set the_geom=st_line_substring(the_geom,0,$ll) where gid=$gid;";
   $r=pg_query($db,$q);
   $q="update routing_ways set length=st_length(st_transform(the_geom,900913))*0.66  where gid=$gid;";
   $r=pg_query($db,$q);
   $q="update routing_ways set length=st_length(st_transform(the_geom,900913))*0.66  where gid=$gi;";
   $r=pg_query($db,$q);
   $q="update routing_ways set x1=st_x(st_startpoint(the_geom))  where gid=$gi;";
   $r=pg_query($db,$q);   
   $q="update routing_ways set y1=st_y(st_startpoint(the_geom))  where gid=$gi;";       
   $r=pg_query($db,$q);   
   $q="update routing_ways set x2=st_x(st_endpoint(the_geom))  where gid=$gi;";       
   $r=pg_query($db,$q);   
   $q="update routing_ways set y2=st_y(st_endpoint(the_geom))  where gid=$gi;";         
   $r=pg_query($db,$q);   
   $q="update routing_ways set x1=st_x(st_startpoint(the_geom))  where gid=$gid;";
   $r=pg_query($db,$q);   
   $q="update routing_ways set y1=st_y(st_startpoint(the_geom))  where gid=$gid;";       
   $r=pg_query($db,$q);   
   $q="update routing_ways set x2=st_x(st_endpoint(the_geom))  where gid=$gid;";       
   $r=pg_query($db,$q);   
   $q="update routing_ways set y2=st_y(st_endpoint(the_geom))  where gid=$gid;";         
   $r=pg_query($db,$q);   
   pg_query($db, "COMMIT"); 
   $ret=$po;
  }
 }
 if($debugoutput){echo "        Teile liefert den neuen Punk $ret\n";}
 return $ret;
}

function teilgid($db,$p1,$p2,$gid,$costfactor){
//
// Bestimmt den naechstgelegenen Weg zu P1/P2 mit gid=gid;
// Teilt diesen Weg an p1,p2 auf, sofern das nicht sowieso schon der Anfangs- oder Endpunkt ist
// gibt den Punkt zurueck, an dem die Teilung stattfand, also entweder den schon
// vorhandenen source/target oder den neu entstandenen.
//
// Wurde nicht geteilt, weil da eh schon ein Start/Endpunkt war, wird dieser zurueckgegeben
// costfactor ist der _minimale_ costfactor der beiden Wegstücke
//
 $debugoutput=1;
 $ret=-1;
 $query="select gid,source,target,st_distance(the_geom,st_geometryfromtext('POINT($p1 $p2)',4326),true) as dist,st_astext(the_geom) as weg from routing_ways where gid=$gid order by dist limit 1;";
 $result=pg_query($db,$query);
 $r=pg_fetch_array($result);
 $dist=100;
 if($r){
  $gid=$r['gid'];
  $dist=$r['dist'];
  if($debugoutput){echo "   Naechster Weg $gid dist=$dist\n";}
 }
 if($dist<0.1){
  $query="select ST_Line_Locate_Point((select the_geom from routing_ways where gid=$gid),st_geometryfromtext('POINT($p1 $p2)',4326)) as ll;";
  $result=pg_query($db,$query);
  $e=pg_fetch_array($result);
  $ll=$startll=$e["ll"];
  if($ll<=0.005)$ret=$r['source'];
  if($ll>=0.995)$ret=$r['target'];
  if(($ll>0.005)&&($ll<0.995)){
   if($debugoutput){echo "      Teile Weg $gid bei $ll am Punkt $p1 $p2\n";}
   pg_query($db, "BEGIN WORK");  
   $query="select max(source) as m from routing_ways;";
   $result=pg_query($db,$query);
   $e=pg_fetch_array($result);
   $po=$e['m'];
   $query="select max(target) as m from routing_ways;";
   $result=pg_query($db,$query);
   $e=pg_fetch_array($result);
   if($e['m']>$po){$po=$e['m'];}
   $po++;
   $query="select max(gid) as m from routing_ways;";
   $result=pg_query($db,$query);
   $e=pg_fetch_array($result); 
   $gi=$e['m']+1;
   $query="select gid,class_id,osm_id,source,target,waysource,the_geom,length,costfactor,hdiff,sidewalk from routing_ways where gid=$gid;";
   $result=pg_query($db,$query);
   $e=pg_fetch_array($result);
   $gid=$e['gid'];
   $sidewalk=$e['sidewalk']+0;
   $osm_id=$e['osm_id'];
   if($e['costfactor']>$costfactor){$costfactor=$e['costfactor'];}
   $q="insert into routing_ways (gid,class_id,osm_id,source,target,waysource,the_geom,length,hdiff,costfactor,sidewalk)  values ($gi,".$e['class_id'].",".$e['osm_id'].",".$e['source'].",".$e['target'].",'".$e['waysource']."',(select the_geom from routing_ways where gid=$gid),".$e['length'].",".$e['hdiff'].",".$costfactor.",".$sidewalk.");";
   $r=pg_query($db,$q);
   $q="update routing_ways set costfactor=$costfactor where gid=$gi;";
   $r=pg_query($db,$q);
   $q="update routing_ways set sidewalk=$sidewalk where gid=$gi;";
   $r=pg_query($db,$q);
   $q="update routing_ways set target=$po where gid=$gid;";
   $r=pg_query($db,$q);   
   $q="update routing_ways set source=$po where gid=$gi;";
   $r=pg_query($db,$q);   
   $q="update routing_ways set hdiff=hdiff*$ll  where gid=$gid;";
   $r=pg_query($db,$q);   
   $q="update routing_ways set hdiff=hdiff*(1-$ll)  where gid=$gi;"; 
   $r=pg_query($db,$q);   
   $q="update routing_ways set the_geom=st_line_substring(the_geom,$ll,1) where gid=$gi;";
   $r=pg_query($db,$q);   
   $q="update routing_ways set the_geom=st_line_substring(the_geom,0,$ll) where gid=$gid;";
   $r=pg_query($db,$q);
   $q="update routing_ways set length=st_length(st_transform(the_geom,900913))*0.66  where gid=$gid;";
   $r=pg_query($db,$q);
   $q="update routing_ways set length=st_length(st_transform(the_geom,900913))*0.66  where gid=$gi;";
   $r=pg_query($db,$q);
   $q="update routing_ways set x1=st_x(st_startpoint(the_geom))  where gid=$gi;";
   $r=pg_query($db,$q);   
   $q="update routing_ways set y1=st_y(st_startpoint(the_geom))  where gid=$gi;";       
   $r=pg_query($db,$q);   
   $q="update routing_ways set x2=st_x(st_endpoint(the_geom))  where gid=$gi;";       
   $r=pg_query($db,$q);   
   $q="update routing_ways set y2=st_y(st_endpoint(the_geom))  where gid=$gi;";         
   $r=pg_query($db,$q);   
   $q="update routing_ways set x1=st_x(st_startpoint(the_geom))  where gid=$gid;";
   $r=pg_query($db,$q);   
   $q="update routing_ways set y1=st_y(st_startpoint(the_geom))  where gid=$gid;";       
   $r=pg_query($db,$q);   
   $q="update routing_ways set x2=st_x(st_endpoint(the_geom))  where gid=$gid;";       
   $r=pg_query($db,$q);   
   $q="update routing_ways set y2=st_y(st_endpoint(the_geom))  where gid=$gid;";         
   $r=pg_query($db,$q);   
   pg_query($db, "COMMIT"); 
   $ret=$po;
  }
 }
 if($debugoutput){echo "        Teile liefert den neuen Punk $ret\n";}
 return $ret;
}


function verbinde($db,$source,$target,$x1,$y1,$x2,$y2,$osm_id,$sidewalk,$nextsidewalk,$costs){
//
// Fuegt den Wege von source/x1/x2 zu target/x2/y2 way mit der osm_id vom Gehweg in den Graphen ein
// liefert den gid des neuen Weges zurueck
//
  $query="select max(gid) as m from routing_ways;";
  $result=pg_query($db,$query);
  $e=pg_fetch_array($result);
  $gid=$e['m']+1;
  $q="insert into routing_ways (gid,class_id,osm_id,source,target,sidewalk,nextsidewalk,costfactor,hdiff,waysource) values ($gid,0,$osm_id,$source,$target,$sidewalk,$nextsidewalk,$costs,0,'virtualcrossing');";
  $r=pg_query($db,$q);
  $q="update routing_ways set the_geom=ST_MakeLine(ST_GeomFromText('POINT($x1 $y1)',4326),ST_GeomFromText('POINT($x2 $y2)',4326)) where gid=$gid;";
  $r=pg_query($db,$q);   
  $q="update routing_ways set length=st_length(st_transform(the_geom,900913))*0.66  where gid=$gid;";
  $r=pg_query($db,$q);
  $q="update routing_ways set x1=st_x(st_startpoint(the_geom))  where gid=$gid;";
  $r=pg_query($db,$q);   
  $q="update routing_ways set y1=st_y(st_startpoint(the_geom))  where gid=$gid;";       
  $r=pg_query($db,$q);   
  $q="update routing_ways set x2=st_x(st_endpoint(the_geom))  where gid=$gid;";       
  $r=pg_query($db,$q);   
  $q="update routing_ways set y2=st_y(st_endpoint(the_geom))  where gid=$gid;";         
  $r=pg_query($db,$q);   
  return $gid;
}


function parallele($db,$sgid,$osm_id,$way,$costfactor,$hdiff){
//
// Fuegt den parallelen Weg $way in den Graphen ein.
// sidewalk=104, nextsidewalk=sgid (die von der Strasse)
//
  $debugoutput=1;
  $query="select st_length(st_geometryfromtext('$way'),true) as l;";
  $result=pg_query($db,$query);
  $e=pg_fetch_array($result);
  $length=$e['l'];
  if($length>7){
   $start=3/$length;
   $ende=($length-3)/$length;
  }else{
   if($length>3){
    $start=1/$length;
    $ende=($length-1)/$length;
   }else{
    if($length>1){
     $start=0.3/$length;
     $ende=($length-0.3)/$length;
    }else{
     $start=0;$ende=1;
    }
   }
  }
  $query="select st_astext(ST_Line_Substring(st_geometryfromtext('$way'),$start,$ende)) as w;";
  $result=pg_query($db,$query);
  $e=pg_fetch_array($result);
  $sway=$e['w'];
  $query="select max(gid) as m,max(source) as s,max(target) as t from routing_ways;";
  $result=pg_query($db,$query);
  $e=pg_fetch_array($result);
  $gid=$e['m']+1;
  $source=$e['s'];
  if($e['t']>$source){$source=$e['t'];}
  $source++;$target=$source+1;
  $q="insert into routing_ways (gid,class_id,osm_id,source,target,sidewalk,nextsidewalk,costfactor,hdiff,waysource) values ($gid,0,$osm_id,$source,$target,104,$sgid,$costfactor,$hdiff,'virtualparallel');";
  $r=pg_query($db,$q);
  $q="update routing_ways set the_geom=st_geometryfromtext('$sway',4326) where gid=$gid;";
  $r=pg_query($db,$q);   
  $q="update routing_ways set length=st_length(st_transform(the_geom,900913))*0.66  where gid=$gid;";
  $r=pg_query($db,$q);
  $q="update routing_ways set x1=st_x(st_startpoint(the_geom))  where gid=$gid;";
  $r=pg_query($db,$q);   
  $q="update routing_ways set y1=st_y(st_startpoint(the_geom))  where gid=$gid;";       
  $r=pg_query($db,$q);   
  $q="update routing_ways set x2=st_x(st_endpoint(the_geom))  where gid=$gid;";       
  $r=pg_query($db,$q);   
  $q="update routing_ways set y2=st_y(st_endpoint(the_geom))  where gid=$gid;";         
  $r=pg_query($db,$q);   
}


// ------------------------------------------------------------------------------------------------
// ------------------------- hier gehts los -------------------------------------------------------
// ------------------------------------------------------------------------------------------------

//
// Alles auf Anfang 
// hier wird allerdings nicht sidewalk=X gesetzt, das muss man ggf vorher machen
// mit sidewalk=101 fuer 'left' 102 fuer 'right' und 103 fuer 'both'
// Ausserdem muss man noch die Tabelle mit den Kreuzungen anlegen
// create table routing_crossings (osm_id bigint,the_geom geometry,highway text,crossing text,done integer);
// create index routing_crossings_done_idx on routing_crossings(done);
// create index routing_crossings_id_idx on routing_crossings(osm_id);
// create index routing_crossings_geom_idx on routing_crossings using gist(the_geom);
// GRANT SELECT, INSERT, UPDATE, DELETE on routing_crossings TO osm;
// GRANT SELECT on routing_crossings TO root;
//

 $db=pg_pconnect('host=localhost port=5432 dbname=osm user=osm password=osm') or die("Keine DB, sonst alles in Ordnung");
 
 
// Alle Sidewalks holen und zu jeder Strasse die Parallelen einzeichnern
 
 if($debugstep>=100){
 echo date("H:i:s")." putzen\n"; 
 $query="delete from routing_ways where sidewalk=104 or sidewalk=106;";
 $result=pg_query($db,$query);
 $query="delete from routing_crossings;";
 $result=pg_query($db,$query);
 echo date("H:i:s")." kreuzen\n";
 $query="insert into  routing_crossings (select osm_id,st_transform(way,4326),highway,tags->'crossing' as crossing,0 as done from osm_point as P where (highway='crossing' or (tags->'crossing'!='' and tags->'crossing'!='no')));";
 $result=pg_query($db,$query);
//  $query="select * from routing_ways where (sidewalk=101 or sidewalk=102 or sidewalk=103) and the_geom && Box2D(ST_GeomFromText('LINESTRING(11.68559 48.15787,11.7334 48.18289)'));"; 
  $query="select * from routing_ways where (sidewalk=101 or sidewalk=102 or sidewalk=103);"; 
  $result=pg_query($db,$query);
  $n=0;
  echo date("H:i:s")." Parallelen anlegen\n";

// Eine Schleife fuer alle Strassen mit sidewalk=101..103, Parallelen bilden
// und mit sidewalk=104 eintragen

  while($r=pg_fetch_array($result)) {
   $n++;
   $gid=$r['gid'];
   $osm_id=$r['osm_id'];
   $length=$r['length'];
   $sw=$r['sidewalk'];
   $he=$r['hdiff'];
   if($debugoutput){echo "$n Strasse $gid ($osm_id) Len: $length Sidewalk: $sw\n";}
   $start=3/$length;
   $ende=($length-3)/$length;
   $query1="select st_astext(st_transform(st_parallel(st_transform(the_geom,900913),-3.5,1,0),4326)) as lw from routing_ways where gid=$gid;";
   $result1=pg_query($db,$query1);
   $r1=pg_fetch_array($result1);
   $leftway=$r1['lw'];
   $query1="select st_astext(st_transform(st_parallel(st_transform(the_geom,900913),3.5,1,0),4326)) as rw from routing_ways where gid=$gid;";
   $result1=pg_query($db,$query1);
   $r1=pg_fetch_array($result1);
   $rightway=$r1['rw'];
   if($sw==101){parallele($db,$gid,$osm_id,$leftway,2000000,$he);}
   if($sw==102){parallele($db,$gid,$osm_id,$rightway,2000000,$he);}
   if($sw==103){parallele($db,$gid,$osm_id,$leftway,2000000,$he);parallele($db,$gid,$osm_id,$rightway,2000000,$he);}
  }
 }

//
// Eine Schleife über alle Endpunkte von Strassen mit Gehweg
//

 $nochverbg=array();$nochverbi=array();
 if($debugstep>=90){
  echo date("H:i:s")." Endpunkte suchen\n";
//  $query="select distinct p,x,y from (select source as p,gid,x1 as x,y1 as y from routing_ways where sidewalk in (101,102,103) and the_geom && Box2D(ST_GeomFromText('LINESTRING(11.68559 48.15787,11.7334 48.18289)')) union select target as p,gid,x2 as x,y2 as y from routing_ways where sidewalk in (101,102,103) and the_geom && Box2D(ST_GeomFromText('LINESTRING(11.68559 48.15787,11.7334 48.18289)')) ) as foo order by p;";
  $query="select distinct p,x,y from (select source as p,gid,x1 as x,y1 as y from routing_ways where sidewalk in (101,102,103) union select target as p,gid,x2 as x,y2 as y from routing_ways where sidewalk in (101,102,103) ) as foo order by p;";
  $result=pg_query($db,$query);
  while($r=pg_fetch_array($result)) {
   $p=$r['p'];
   $kx=$r['x'];
   $ky=$r['y'];
   if($debugoutput){echo "Punkt $p ($kx,$ky)\n";}
   $query1="select osm_id from routing_crossings where the_geom && st_transform(st_expand(st_transform(ST_GeomFromText('POINT($kx $ky)',4326),900913),0.1),4326);";
   $result1=pg_query($db,$query1);
   $r1=pg_fetch_array($result1);
   $iscrossing=$r1['osm_id'];
   if($iscrossing){
    if($debugoutput){echo "       ist eine Kreuzung\n";}
    $query1="update routing_crossings set done=1 where osm_id=$iscrossing;";
    $result1=pg_query($db,$query1);
   }

// Alle beteiligten Endpunkte der Gehwege suchen
   
   $query1="select distinct p,x,y,gid,nextsidewalk from (select target as p,x2 as x,y2 as y,gid,nextsidewalk from routing_ways where sidewalk=104 and cast(nextsidewalk as integer) in (select gid from routing_ways where target=$p and sidewalk in (101,102,103)) union select source as p,x1 as x,y1 as y,gid,nextsidewalk from routing_ways where sidewalk=104 and cast(nextsidewalk as integer) in (select gid from routing_ways where source=$p and sidewalk in (101,102,103))) as foo order by p;";            
   $result1=pg_query($db,$query1);
   $n=0;$ep=array();$x=array();$y=array();$nx=array();$gwgid=array();
   $erledigt=array();
   $schnittfund=0;
   while($r1=pg_fetch_array($result1)){
    $ep[]=$r1['p'];
    $x[]=$r1['x'];
    $y[]=$r1['y'];
    $nx[]=$r1['nextsidewalk'];
    $gwgid[]=$r1['gid'];
    if($debugoutput){echo "   Gehsteigpunkt dazu ".$ep[$n]."\n";}
    $n++;    
   }
   
   for($i=0;$i<$n;$i++){
    $erledigt[$i]=0;
   }


// Falls der Mittelpunkt ein Kreuzungspunkt ist, wird erstmal jedes Gehsteigende
// mit der Mitte verbunden

    if($iscrossing&&($n>0)){ 
     for($i=0;$i<$n;$i++){  
      verbinde($db,$p,$ep[$i],$kx,$ky,$x[$i],$y[$i],0,105,$p,2);     
      $erledigt[$i]=1;$schnittfund=1;
     }
    }


// Von jedem dieser Gehwegendpunkten zu jedem anderen einen Weg bilden
// und nachsehen, ob der einen bereits vorhandenen Weg schneidet

   if($n>1){
    for($i=0;$i<$n-1;$i++){
     for($j=$i+1;$j<$n;$j++){
      $x1=$x[$i];$y1=$y[$i];$x2=$x[$j];$y2=$y[$j];
      if($debugoutput){echo "      Teste ".$ep[$i]." zu ".$ep[$j]."\n";}
      $query3="select gid,osm_id from routing_ways where st_intersects(the_geom,ST_MakeLine(ST_GeomFromText('POINT($x1 $y1)',4326),ST_GeomFromText('POINT($x2 $y2)',4326))) and (sidewalk is null or (sidewalk!=104 and sidewalk!=105 and sidewalk!=106));";
      $result3=pg_query($db,$query3);
      $r3=pg_fetch_array($result3);
      $sgid=$r3['gid']+0;
      $osm_id=$r3['osm_id'];
      $r3=pg_fetch_array($result3);
      $sgid2=$r3['gid']+0;
      if($sgid2!=0){

// zwei Wege koennen wir nicht virtuell ueberspringen -> ignorieren

       if($debugoutput){echo "       ---> Schnittpunkte mit zwei Wegen\n";}
      }else{
       if($sgid==0){
        if($debugoutput){echo "       ---> Kein Schnittpunkt mit anderen Wegen\n";}

// Kein Schnittpunkt -> Die beiden Punkte verbinden

        verbinde($db,$ep[$i],$ep[$j],$x1,$y1,$x2,$y2,0,105,$p,1);
        $erledigt[$i]=1;$erledigt[$j]=1;
       }else{
        if(($sgid==$nx[$i])||($sgid==$nx[$j])){

// Schnittpunkt mit der Strasse, deren Buergersteig wir gerade bearbeiten -> ignorieren

          if($debugoutput){echo "       ---> Schnittpunkt mit eigenem Weg $sgid (nx_i=".$nx[$i]."  nx_j=".$nx[$j].")\n";}

        }else{

// Schnitt mit einer Strasse, die bisher nichts mit diesem Buergersteig zu tun hatte -> Punkte verbinden und diese
// Strasse in der Mitte anschliessen. Vorher noch den highway-Typ der Str abfragen, und entsprechend dieses Typs
// die Strafpunkte vergeben.

         $query4="select highway from osm_line where osm_id=$osm_id;";
         $result4=pg_query($db,$query4);
         $r4=pg_fetch_array($result4);
         $hwt=$r4['highway'];
         $cf=15;
         if(($hwt=='path')||($hwt=='path')||($hwt=='footway')||($hwt=='cycleway')||($hwt=='track')||($hwt=='living_street')){$cf=1;}
         if(($hwt=='service')){$cf=2;}
         if(($hwt=='residential')||($hwt=='unclassified')||($hwt=='tertiary')){$cf=8;}
         if($debugoutput){echo "       ---> Schnittpunkt mit fremdem Weg $sgid ($hwt)  (nx_i=".$nx[$i]."  nx_j=".$nx[$j].")\n";}
         $ngid=verbinde($db,$ep[$i],$ep[$j],$x1,$y1,$x2,$y2,0,106,$p,$cf);
         $erledigt[$i]=1;$erledigt[$j]=1;
         $schnittfund=1;
         $nochverbg[]=$ngid;
         $nochverbi[]=$osm_id;
        }
       }
      }
     }
    }
   }
   
// Am Ende gehn wir nochmal die Punkte durch und verbinden alle, die keinen
// Anschluss gefunden haben (das sind die Endstuecke von Sackgassen und Strassen,
// deren Buergersteige ploetzlich enden)
// Das gleiche machen wir, falls es zwar Anschluesse gab, aber keiner davon die
// Verbindung zu einer der Strassen gefunden hat

    if($n>0){ 
     for($i=0;$i<$n;$i++){  
      if((!$erledigt[$i])||(!$schnittfund)){
       verbinde($db,$p,$ep[$i],$kx,$ky,$x[$i],$y[$i],0,106,$p,8);     
       $erledigt[$i]=1;
      }
     }
    }
  } 

 }
 

 if($debugstep>=80){
  echo date("H:i:s")." Bordsteinspruenge\n";
  $query="delete from routing_ways where sidewalk=107;";
  $result=pg_query($db,$query);
//   $query="select * from routing_ways where (sidewalk=101 or sidewalk=102 or sidewalk=103) and the_geom && Box2D(ST_GeomFromText('LINESTRING(11.68559 48.15787,11.7334 48.18289)'));"; 
  $query="select * from routing_ways where (sidewalk=101 or sidewalk=102 or sidewalk=103);"; 
  $result=pg_query($db,$query);
  $n=0;

// Nochmal Schleife fuer alle Strassen mit sidewalk=101..103
// und von dieser Str aus alle (1 oder 2) dazugehoerigen Sidewalks suchen.
// Diese mit der Str verbinden, aber noch nicht m,it echten source/target und
// die Str an der Stelle auch noch nicht auftrennen.

  while($r=pg_fetch_array($result)) {
   $n++;
   $gid=$r['gid'];
   $osm_id=$r['osm_id'];
   $len=$r['length'];
   $sw=$r['sidewalk'];
   if($debugoutput){echo " Strasse: $gid\n";}
   $query1="select gid from routing_ways where sidewalk=104 and nextsidewalk='$gid';";
   $result1=pg_query($db,$query1);
   $r1=pg_fetch_array($result1);
   $nextsidewalk=$r1['gid'];
   $r1=pg_fetch_array($result1);
   if($r1['gid']+0>0){$nextsidewalk=$nextsidewalk.",".$r1['gid'];}
   $nexts=preg_split('/,/i',$nextsidewalk);
   if($debugoutput){echo "$n paralleler Weg $gid ($osm_id) Nachbarn: $nextsidewalk\n";}

// Jeden Weg aufstueckeln, den Punkt an der Grenze der Stuecke finden  

   $tt=5;
   while($tt<$len-5){
    $t=$tt/$len;   
    $query1="select st_x(ST_Line_Interpolate_Point((select the_geom from routing_ways where gid=$gid),$t)) as x,st_y(ST_Line_Interpolate_Point((select the_geom from routing_ways where gid=$gid),$t)) as y;";
    $result1=pg_query($db,$query1);
    $r1=pg_fetch_array($result1);
    $x=$r1['x'];$y=$r1['y'];
    if($debugoutput){echo "  Teilstueck $tt/$len ($x,$y)\n";}

// Von diesen Punkten ausgehend den jeweils naechstgelegenen Punkt am Buergersteig finden

    foreach ($nexts as $nx){
     $query3="select ST_Line_Locate_Point((select st_transform(the_geom,900913) from routing_ways where gid=$nx),st_transform(ST_GeomFromText('POINT($x $y)',4326),900913)) as ll;";
     $result3=pg_query($db,$query3);
     $r3=pg_fetch_array($result3);
     $ll=$r3['ll'];
     $query3="select st_x(st_transform((ST_Line_Interpolate_Point((select st_transform(the_geom,900913) from routing_ways where gid=$nx),$ll)),4326)) as x,st_y(st_transform((ST_Line_Interpolate_Point((select st_transform(the_geom,900913) from routing_ways where gid=$nx),$ll)),4326)) as y;";
     $result3=pg_query($db,$query3);
     $r3=pg_fetch_array($result3);
     $x1=$r3['x'];$y1=$r3['y'];
     if($debugoutput){echo "    Lege Verbindung zu $nx bei ($x1,$y1)\n";}
     verbinde($db,-1,-2,$x,$y,$x1,$y1,$nx,107,$gid,8);      
    }
    $tt+=30;
    if(($tt>($len-5))&&($tt<($len+15))){$tt=$len-5.01;}               
   }
  }      
 }

 if($debugstep>=70){
  echo date("H:i:s")." Bordsteine ohne Spruenge\n";
  $query="delete from routing_ways where sidewalk=108;";
  $result=pg_query($db,$query);
//   $query="select * from routing_ways where (sidewalk=101 or sidewalk=102 or sidewalk=103) and the_geom && Box2D(ST_GeomFromText('LINESTRING(11.68559 48.15787,11.7334 48.18289)'));"; 
  $query="select * from routing_ways where (sidewalk=101 or sidewalk=102 or sidewalk=103);"; 
  $result=pg_query($db,$query);
  $n=0;

// Nochmal Schleife fuer alle Strassen mit sidewalk=101..103
// Das gleiche wie oben, aber nicht mit willkuerlichen Wegen,
// sondern nur an echten Kreuzungen (sofern die nicht oben schon
// abgearbeitet wurden)

  while($r=pg_fetch_array($result)) {
   $n++;
   $gid=$r['gid'];
   $osm_id=$r['osm_id'];
   $len=$r['length'];
   $sw=$r['sidewalk'];
   if($debugoutput){echo " Strasse: $gid L=$len\n";}
   $query4="select osm_id,ST_Line_Locate_Point((select the_geom from routing_ways where gid=$gid),the_geom) as tt from routing_crossings where the_geom && (select the_geom from routing_ways where gid=$gid) and st_distance(the_geom,(select the_geom from routing_ways where gid=$gid),true)<0.1 and done=0;";
   $result4=pg_query($db,$query4);
   while($r4=pg_fetch_array($result4)){
    $tt=$r4['tt'];
    $tosmid=$r4['osm_id'];
    if($debugoutput){echo "     Crossing bei $tt (P: $tosmid)\n";}
    $query1="select gid from routing_ways where sidewalk=104 and nextsidewalk='$gid';";
    $result1=pg_query($db,$query1);
    $r1=pg_fetch_array($result1);
    $nextsidewalk=$r1['gid'];
    $r1=pg_fetch_array($result1);
    if($r1['gid']+0>0){$nextsidewalk=$nextsidewalk.",".$r1['gid'];}
    $nexts=preg_split('/,/i',$nextsidewalk);
    if($debugoutput){echo "$n paralleler Weg $gid ($osm_id) Nachbarn: $nextsidewalk\n";}
    $t=$tt;   
    $query1="select st_x(ST_Line_Interpolate_Point((select the_geom from routing_ways where gid=$gid),$t)) as x,st_y(ST_Line_Interpolate_Point((select the_geom from routing_ways where gid=$gid),$t)) as y;";
    $result1=pg_query($db,$query1);
    $r1=pg_fetch_array($result1);
    $x=$r1['x'];$y=$r1['y'];
    if($debugoutput){echo "  Teilstueck $tt/$len ($x,$y)\n";}

// Von diesen Punkten ausgehend den jeweils naechstgelegenen Punkt am Buergersteig finden

    foreach ($nexts as $nx){
     $query3="select ST_Line_Locate_Point((select st_transform(the_geom,900913) from routing_ways where gid=$nx),st_transform(ST_GeomFromText('POINT($x $y)',4326),900913)) as ll;";
     $result3=pg_query($db,$query3);
     $r3=pg_fetch_array($result3);
     $ll=$r3['ll'];
     $query3="select st_x(st_transform((ST_Line_Interpolate_Point((select st_transform(the_geom,900913) from routing_ways where gid=$nx),$ll)),4326)) as x,st_y(st_transform((ST_Line_Interpolate_Point((select st_transform(the_geom,900913) from routing_ways where gid=$nx),$ll)),4326)) as y;";
     $result3=pg_query($db,$query3);
     $r3=pg_fetch_array($result3);
     $x1=$r3['x'];$y1=$r3['y'];
     if($debugoutput){echo "    Lege Verbindung zu $nx bei ($x1,$y1)\n";}
     verbinde($db,-1,-2,$x,$y,$x1,$y1,$nx,108,$gid,2);      
    }
   }
  }      
 }

    
 if($debugstep>=60){     
  echo date("H:i:s")." Anschluesse verbinden\n";
  $query="select * from routing_ways where sidewalk=107 or sidewalk=108;";
  $result=pg_query($db,$query);
  $n=0;$pa=$pg=0;$knoten=array();
  while($r=pg_fetch_array($result)){
 
// Schleife ueber alle neu angelegten Verbindungen, Nachbarn an Start und Endpunkt auftrennen
// und Enden verbinden
// 
   $n++;
   $gid=$r['gid'];
   $sosmid=$r['nextsidewalk']; 
   $tosmid=$r['osm_id'];
   $sw=$r['sidewalk'];
   if($debugoutput){echo "$n mit sosm=$sosmid und tosm=$tosmid\n";}
   $ncf=10;
   if($sw==107){$ncf=10;}
   if($sw==108){$ncf=1;}
   $s=teilweg($db,$r['x1'],$r['y1'],0,$ncf);
   $t=teilweg($db,$r['x2'],$r['y2'],0,$ncf);  
   $query1="update routing_ways set source=$s,target=$t,costfactor=$ncf where gid=$gid;";
   $result1=pg_query($db,$query1);
  }   
 }


// Nochmal eine Schleife ueber alle virtuellen Wege, die echte Wege kreuzen
// Schnittpunkt suchen, beide Wege dort auftrennen und verbinden

  echo date("H:i:s")." virtuelle und echte Wege verbinden\n";
  
  for($i=0;$i<count($nochverbg);$i++){
   $gid=$nochverbg[$i];
   $id=$nochverbi[$i];
   $query3="select gid from routing_ways where osm_id=$id and (sidewalk is null or sidewalk<104) and st_intersects(the_geom,(select the_geom from routing_ways where gid=$gid));";
   $result3=pg_query($db,$query3);
   $r3=pg_fetch_array($result3);   
   $wgid=$r3['gid'];
   $query3="select st_x(i) as x,st_y(i) as y from (select st_intersection((select the_geom from routing_ways where gid=$gid),(select the_geom from routing_ways where gid=$wgid)) as i) as foo;";
   $result3=pg_query($db,$query3);
   $r3=pg_fetch_array($result3);
   $x=$r3['x'];
   $y=$r3['y'];     
   if($debugoutput){echo " $i      Verbinde gid=$gid mit osm_id=$id ($wgid) bei $x $y\n";}
   $s=teilgid($db,$x,$y,$gid,1);
   $t=teilgid($db,$x,$y,$wgid,1);
   if($debugoutput){echo " $i      Ersetze S/T $t durch $s\n";}
   $query3="update routing_ways set target=$s where target=$t;";
   $result3=pg_query($db,$query3);
   $query3="update routing_ways set source=$s where source=$t;";
   $result3=pg_query($db,$query3);
  }
 
$query3="update routing_ways set costfactor=8  where (sidewalk=101 or sidewalk=102 or sidewalk=103) and costfactor<8;";
$result3=pg_query($db,$query3);
$query3="update routing_ways set costfactor=1 where sidewalk=104;";
$result3=pg_query($db,$query3);

    
echo date("H:i:s")." fertig\n";