Bad intersections - Highways - Railways - Work in progress

[PA-Dore]

This work is in progress. With help of Shades, I begin to understand how to remove all bad intersections (Highways and Railways). Bad intersections are not only on WP#2:
1. If a route has n WP, we have bad intersections when:
WP-intersections on WP#2, 3, 4.... (n-2), (n-1)
So we have to find all bad records where: 1 < WP-Intersections < n
2. A road has 2 intersection-WP (1 and MAXWP) or only 1 (ending-road) or none (isolated road).

A) Useful scripts (written by 4Shades):

1. Highways:
- To rebuild Highway_Intersections:

Code: Select all

# 
# This query is used to rebuild the Highway_Intersections table from scratch. 
# Run this query whenever new waypoints have been added to the Highways table. 
# Query written by IV/JG7_4Shades  6 September 2006 
# 
drop table if exists Highway_Intersections; 
CREATE TABLE `Highway_Intersections` ( 
`Map` varchar(50) default NULL, 
`Intersection_X_Axis` int(11) default NULL, 
`Intersection_Y_Axis` int(11) default NULL, 
`Route` int(11) default NULL, 
`Waypoint_Number` int(11) default NULL 
) TYPE=InnoDB; 
INSERT INTO Highway_Intersections SELECT DISTINCT * FROM (SELECT Map, X_Axis as Intersection_X_Axis, Y_Axis AS Intersection_Y_Axis, Route, Waypoint_Number FROM Highways, (SELECT Map AS Junction_Map, X_Axis AS Junction_X_Axis, Y_Axis AS Junction_Y_Axis, COUNT(Route) AS Exits FROM Highways GROUP BY CONCAT(Map,'^',X_Axis,'^',Y_Axis) HAVING COUNT(Route)>1) AS Junctions WHERE Map=Junctions.Junction_Map AND X_Axis=Junctions.Junction_X_Axis AND Y_Axis=Junction_Y_Axis ORDER BY X_Axis, Y_Axis, Route) AS Table_With_Dups; 
# 
# End of query. 
#

- To list all highways routes with Max(Waypoint_Number)<6:

Code: Select all

SELECT A.Map, A.Route, A.MaxWP FROM (SELECT Map, Route, Max(Waypoint_Number) AS MaxWP FROM Highways GROUP BY Route ORDER BY Route) AS A WHERE A.MaxWP<6 ORDER BY A.Map;

- To list all bad road intersection routes (1<WP<5):

Code: Select all

SELECT * FROM Highway_Intersections WHERE Waypoint_Number>1 AND Waypoint_Number<5 ORDER BY Map;

- To list all bad intersections when WPMAX=n and 1< WP-intersection <n:

Code: Select all

SELECT HMMap AS Map, HMRoute AS Route, HM.MaxWP AS RouteMaxWP, HI.Waypoint_Number AS IntersectionWP FROM (SELECT Map AS HIMap, Route as HIRoute, Waypoint_Number FROM Highway_Intersections WHERE Waypoint_Number>1 ORDER BY Map,Route) AS HI, (SELECT Map AS HMMap, Route AS HMRoute, Max(Waypoint_Number) AS MaxWP FROM Highways GROUP BY Map, Route ORDER BY Map,Route) AS HM WHERE HI.HIMap=HM.HMMap AND HI.HIRoute = HM.HMRoute AND HI.Waypoint_Number<>HM.MaxWP;

2. Railways:
- To rebuild Railway_Intersections:

Code: Select all

# This query is used to rebuild the Railway_Intersections table from scratch. 
# Run this query whenever new waypoints have been added to the Railway_Waypoints table. 
# Query written by IV/JG7_4Shades  26 September 2008 
# 
drop table if exists Railway_Intersections; 
CREATE TABLE `Railway_Intersections` ( 
`Map` varchar(50) default NULL, 
`Intersection_X_Axis` int(11) default NULL, 
`Intersection_Y_Axis` int(11) default NULL, 
`Route` int(11) default NULL, 
`Waypoint_Number` int(11) default NULL 
) TYPE=InnoDB; 
INSERT INTO Railway_Intersections SELECT DISTINCT * FROM (SELECT Map, X_Axis as Intersection_X_Axis, Y_Axis AS Intersection_Y_Axis, Route, Waypoint_Number FROM Railway_Waypoints, (SELECT Map AS Junction_Map, X_Axis AS Junction_X_Axis, Y_Axis AS Junction_Y_Axis, COUNT(Route) AS Exits FROM Railway_Waypoints GROUP BY CONCAT(Map,'^',X_Axis,'^',Y_Axis) HAVING COUNT(Route)>1) AS Junctions WHERE Map=Junctions.Junction_Map AND X_Axis=Junctions.Junction_X_Axis AND Y_Axis=Junction_Y_Axis ORDER BY X_Axis, Y_Axis, Route) AS Table_With_Dups; 
# 
# End of query. 
#

- To list all railway routes with Max(Waypoint_Number)<5:

Code: Select all

SELECT A.Map, A.Route, A.MaxWP FROM (SELECT Map, Route, Max(Waypoint_Number) AS MaxWP FROM Railway_Waypoints GROUP BY Route ORDER BY Route) AS A WHERE A.MaxWP<5 ORDER BY A.Map;

- To list the number of short routes in each map:

Code: Select all

SELECT A.Map, Count(*) AS NShort FROM (SELECT Map, Route, Max(Waypoint_Number) AS MaxWP FROM Railway_Waypoints GROUP BY Route ORDER BY Route) AS A WHERE A.MaxWP<5 GROUP BY A.Map ORDER BY A.Map;

- To list all bad rail intersection routes (1<WP<5):

Code: Select all

SELECT * FROM Railway_Intersections WHERE Waypoint_Number>1 AND Waypoint_Number<5 ORDER BY Map

- To list all bad intersections when WPMAX=n and 1< WP-intersection <n:

Code: Select all

SELECT HMMap AS Map, HMRoute AS Route, HM.MaxWP AS RouteMaxWP, HI.Waypoint_Number AS IntersectionWP FROM (SELECT Map AS HIMap, Route as HIRoute, Waypoint_Number FROM Railway_Intersections WHERE Waypoint_Number>1 ORDER BY Map,Route) AS HI, (SELECT Map AS HMMap, Route AS HMRoute, Max(Waypoint_Number) AS MaxWP FROM Railway_Waypoints GROUP BY Map, Route ORDER BY Map,Route) AS HM WHERE HI.HIMap=HM.HMMap AND HI.HIRoute = HM.HMRoute AND HI.Waypoint_Number<>HM.MaxWP;

B) Method:
1. For all short routes (MAXWP<5) we increase the WP's number to 5 by editing the tables and adding new WP: It gives very good results with trains.
2. For all overlaping routes (mainly Highways), we have to locate in FMB, find the overlaping road and rebuild tables. I'm able to do that only for maps where I have the highways.mis file.
--> Channel has the greatest density of railways: Results were perfect in London (SEE)

C) Final work: All intersections are on WP#1 or WP#MAX AND WP#MAX >2
Here are all maps that contain records in Highways or Railways_Waypoints tables[/u]:
Pay attention: the sql files do not contain the "drop table if exists tablename". Empty tables first!
Ardennes Highways New capture
Ardennes Railways: checked no change
Balaton Highways
Balaton Railways
Bay of Biscay Highways
Bay of Biscay Railways: checked no change
Berlin Highways New capture
Berlin Railways
Cartagena Highways: checked no change
Cartagena Railways: checked no change
Central Med Highways New capture
Central Med Railways New capture
Channel 1940 Highways
Channel 1940 Railways
Channel 1942 Highways
Channel 1942 Railways
Crete Highways: checked no change
Crimea Highways: checked no change
Crimea Railways
Cyrenaica Highways New capture
Cyrenaica Railways: checked no change
Darwin Highways: checked no change
Darwin Railways: checked no change
El Alamein Highways: checked no change
El Alamein Railways: checked no change
English Channel Highways: checked no change
EnglishChannel Railways
Fortress Malta Highways
Fortress Malta Railways: checked no change
France Highways
France Railways
Gothic Line Highways
Gothic_Line Railways
Gulf of Finland Highways New capture
Gulf_of_Finland Railways
Hawaii Highways New capture by Hawk5
Iasi Highways: checked no change
Iasi Railways: checked no change
Imphal Highways: checked no change
Italy Highways
Italy Railways: checked no change
Italy_Greece_Highways (European part of Mediterranean Map)New capture
Khalkhin Gol Highways: checked no change
Kiev Highways
Kiev Railways: checked no change
Kiev_AP Highways
Kiev_AP Railways
Korea Highways: checked no change
Korea Railways: checked no change
Kuban Highways
Kuban Railways
Kurland Highways New capture
Kurland Railways: checked no change
Kursk Highways: checked no change
Kursk Railways: checked no change
Kyushu Highways: checked no change
Kyushu Railways: checked no change
Lvov Highways
Lvov Railways
Libya Highways: checked no change
Libya Railways: checked no change
Malta Highways: checked no change
Malta Railways
Manchuria Highways (Hawk5) (WP#MAX > 1)
Manchuria Railways
Marianas Highways New capture
Mediterranean Highways New capture
Moscow Highways (Hawk_5)
Moscow Railways
Murmansk Highways: checked no change
Murmansk Railways: checked no change
New Guinea Highways: checked no change
Normandy Highways
Normandy Railways
NorthSea Highways
Odessa Highways: checked no change
Odessa Railways: checked no change
Okinawa Highways
Oslo Highways: checked no change
Ostfriesland Highways: checked no change
Ostfriesland Railways
Philippines Highways: checked no change
Philippines Railways: checked no change
Prokhorovka Highways: checked no change
Prokhorovka Railways: checked no change
Sardegna Highways: checked no change
Sinai Highways: checked no change
Sinai Railways: checked no change
Singapore Highways New capture
Singapore Railways: checked no change
Slot all maps Highways: checked no change
Slovakia Highways
Slovakia Railways
Slovenia Highways
Slovenia Railways
Smolensk Highways
Smolensk Railways
Stalingrad Highways: checked no change
Stalingrad Railways: checked no change
Tarawa Highways: checked no change
Tubruq Highways: checked no change

[22GCT_Gross]

Thank you JeanPierre, fantastic job.

Pay attention: the sql files do not contain the "drop table if exists tablename".
So, if you executed the sql file as it is, you are going to append the new data to the old ones. Delete the old table before restoring.

[II/JG77Hawk_5]

Hi JP,
here is updated Manchuria Highways sql.

http://60.242.222.214/ftp/SEOW%20Files/ ... ghways.zip

I confirmed and corrected overlaps on 4 of the routes both in the DB and in original mission file noted in your post. Routes I corrected were 7307, 7699, 8212 and 7690. The bad sections of Highway that had missed an Intersection were converted into new routes. I could not confirm the same problem with the remaining routes that you have noted.
Once these 4 were corrected, Highway_Intersections table was cleared and rebuilt then the table was checked for overlap problems. None were found.
I did not however add extra waypoints to routes with less than 5 waypoints.

Can you please confirm that a Highways route must now have a minimum of 5 waypoints?
Did your testing also find problems with road units using routes where MaxWP<5?

I hope to just have this point clarified for future data capture and before reworking Highway routes on such a large map.

I will also try to get some road testing done to confirm this issue.

Thanks for all your hard work and investigations. You have taken on a big job and go above and beyond in your efforts!

We will all appreciate our units going where we plan them to go and getting there (mostly) on time!

Cheers,
5

[PA-Dore]

Fine Hawk, your Highways table is OK (Intersection = only WP#1 or WP#MAX)

"Can you please confirm that a Highways route must now have a minimum of 5 waypoints?
Did your testing also find problems with road units using routes where MaxWP<5? "

I can't confirm, I tested this only for railways. For railways it's perfect. So, I guess that it's the same algorithm for Highways (Shades?).
I updated all the Highways tables from the maps I captured, increasing all short roads (2, 3, 4 WP) to 5 x WP.
But note that routes and specially short routes can be also "ending-roads" with only 1 intersection (WP#1 OR WPMAX) that's correct.

e.g Manchuria:
2 x WP = 1 --> no intersection at WP#2 (==> 1 ending-road)
3 x WP = 135 with 86 intersections at WP#3 (==> 49 ending-roads)
4 x WP = 103 with 64 intersections at WP#4 (==> 39 ending-roads)


I propose for future to have at least 5WP for Highways and Railways using this method:
From FMB:

Code: Select all

8240	51700	46500	120	1	Manchuria
8240	51300	46900	120	2	Manchuria

modified to:

Code: Select all

8240	51700	46500	120	1	Manchuria
8240	51600	46600	120	2	Manchuria
8240	51500	46700	120	3	Manchuria
8240	51400	46800	120	4	Manchuria
8240	51300	46900	120	5	Manchuria

[IV/JG7_4Shades]

Hi guys,

I cannot confirm that Highway routes require 5 waypoints yet. Remember, IL-2 treats trains and vehicles completely differently, so something that works well for trains may not be necessary for vehicles.

One thing I remember from the early days of IL-2 was that road waypoints that were closer than 200 m apart would cause a mission crash!!! This seems to have been fixed in one of the many IL-2 patches since, but I still am wary of putting Highway waypoints too close together.

Anyway, I will try to do some testing on all this stuff this weekend. As always, Dore's and Hawk_5's work is greatly appreciated here.

Cheers,
4S

[PA-Dore]

Work is in progress. See above. Thanks to Gross, Hawk5, Paddington for sending their highways/railways.mis. It was very useful. I would ask again if these following files exist? (only highways):
Highways.mis files needed:
Ardennes
Kurland
Marianas
Normandy
Okinawa
Singapore

Shades: Could you confirm that the final tables (above) are correct?
- No intersection WP between 1 to MAXWP
- No very short roads: MAXWP > 4 (except some "ending-roads" MAXWP = 3)
Question to all commanders: In Berlin City, I removed many unuseful roads, is it too much? I can now easily add some secondary roads if needed. SEE

[IV/JG7_4Shades]

Hi JP,

I have incorporated the latest Railways and Highways data listed in the previous messages. Things are looking much better, but there are still problems.

In Gothic Line railways, for example, there are cases where one route (A) terminates on another route (B) but in between successive nodes of B!!! This is very hard to detect with SQL. I think Gothic Line railways need recapture.

Anyway, Central Med still has railway overlaps indicated by SQL (where one route touches another route at an internal node), as opposed to the Gothic Line railway problem.

I have found a couple of roads.mis files for other sectors, I will send them on if I think they could be useful.

Summary: Railway Overlaps (via SQL)

Central Med: Routes 101006, 101007, 101010, 101011, 101012, 101013

Summary: Highway Overlaps (via SQL)

Ardennes: 4 routes
Central Med: 16 routes
Hawaii: 16 routes
Kurland: 4 routes
Marianas: 8 routes
Normandy: 1 route
Okinawa: 1 route
Singapore: 25 routes
Slovakia: 4 routes

Cheers,
4S

[II/JG77Hawk_5]

I have no more original files so I'll recapture Hawaii to start as it shouldn't take long.

With so much practice lately hopefully data set should be good.

Edit: Completed and all checked ok, no overlaps.

http://60.242.222.214/ftp/SEOW%20Files/ ... ghways.zip

[22GCT_Gross]

JP,
I have something about Slovakia.
http://www.22gct.it/Data/Slovakia_Highways.rar
http://www.22gct.it/Data/Slovakia_Errors.rar

You find some screemshots from the built missions.
Hopig this can helo you.

[PA-Dore]

Very nice

Shades --> I'm OK with you. Gothic Line & Central Med Railways captured.
Hawk--> Thanks for your work. I included Hawaii Highways in my post.
Diego --> Thanks this will help me.
Only part D) FINAL WORK is done
I will edit my post each time a new map is OK. (I posted today Central Med Highways) Work will be soon done.

I'm working now on another problem: In some crossroads we have sometimes this case:



I'm working on it...

[PA-Findo]

wowww mazette ( in french lol ) great great job my JiPi , you must sleep sometimes JiPi , thanx again to all guys specialy Jipoune

[PA-Dore]

Work is done:

All Railways and Highways from all maps were checked/modified.
I hope there are no more overlaps or bad intersections.
- Removed short roads Min number of WP = usually 4 or 5, in a few cases =3
- Removed all bad intersections: All intersections are at WP#1 or MAXWP.

You can download all new files from my first post above.
Pay attention: the sql files do not contain the "drop table if exists tablename". Empty tables first!

Note that zip files contain sql and csv files and sometimes new Route_Blockages files. I added also Highways/railways.mis file when I recaptured the map.

Many thanks to Shades, Gross, Hawk5, Paddington, and all who sent me their mis files.

[PA-Dore]

Updated:

Mediterranean_Highways
Italy_Greece_Highways

My first post has been updated, all others maps have no change.

[=gRiJ=Petr]

Hi JP,

We're going to start a Normandy campaign shortly and I'm wondering if the Normandy map also has a new waypoints capture?

Thanks!
Cheers,
Petr

[PA-Dore]

bonjour Petr;-)
Yes, you can download the new tables from my first post above.
Only minor changes: 1 road overlap and railways too.