How To Refresh the Highway_Intersections Table

For bug reports and fixes, installation issues, and new ideas for technical features.

Moderator: SEOW Developers

Post Reply
IV/JG7_4Shades
Posts: 2201
Joined: Mon 08 Jan 2007 11:10 pm
Location: Perth, Western Australia

How To Refresh the Highway_Intersections Table

Post by IV/JG7_4Shades »

Hi Everyone,

Some people have asked how to refresh the contents of the Highway_Intersections table after some edits have been made to the Highways table. Well, this is done by a query, not by hand.

Here is the query written for MySQL:

Code: Select all

#
# This query is used to rebuild the Highway_Intersections table from scratch.
# Run this query whenever edits have been made 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.
#
For the full Highways table, running this takes about 5-10 minutes on a LOCAL MySQL DB. For a split DB, much shorter.

Cheers,
4Shades
IV/JG7_4Shades
SEOW Developer

Image
II/JG77Hawk_5
Posts: 162
Joined: Wed 10 Jan 2007 1:13 am
Location: Sydney, Australia

Post by II/JG77Hawk_5 »

Thanks M8, that is getting saved here for sure!!

Cheers,
Hawk5
242Sqn_Chap
Posts: 30
Joined: Tue 01 Jan 2008 9:49 am

Post by 242Sqn_Chap »

Is there a line required that points to relevant DB ?

I paste this this query into the query section of my SQL and just get an error :-(
IV/JG7_4Shades
Posts: 2201
Joined: Mon 08 Jan 2007 11:10 pm
Location: Perth, Western Australia

Post by IV/JG7_4Shades »

Hi Chap,

You could just try the following statement beforehand:

USE yourDBname


That might assist.

Cheers,
4Shades
IV/JG7_4Shades
SEOW Developer

Image
II/JG77Hawk_5
Posts: 162
Joined: Wed 10 Jan 2007 1:13 am
Location: Sydney, Australia

Post by II/JG77Hawk_5 »

I found that with my MySQL 5.5 system that the above query didn't work and after a bit of investigating the TYPE command isn't supported in newer MySQL versions (v5.0+) and is deprecated.

If you get a syntax error rebuilding Highway_Intersections try deleting 'TYPE=InnoDB' and all should be ok.
You could also try substituting TYPE for ENGINE.

Example:


#
# This query is used to rebuild the Highway_Intersections table from scratch.
# Run this query whenever edits have been made 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
);
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.
#



OR


#
# This query is used to rebuild the Highway_Intersections table from scratch.
# Run this query whenever edits have been made 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
) ENGINE=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.
#
IV/JG7_4Shades
Posts: 2201
Joined: Mon 08 Jan 2007 11:10 pm
Location: Perth, Western Australia

Post by IV/JG7_4Shades »

And for Railway_Intersections:

Code: Select all

#
# This query is used to rebuild the Railway_Intersections table from scratch.
# Run this query whenever edits have been made to the Railway_Waypoints table.
# Query written by IV/JG7_4Shades  6 September 2006
#

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
) ENGINE=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.
# 
IV/JG7_4Shades
SEOW Developer

Image
Post Reply