Page 1 of 1
How To Refresh the Highway_Intersections Table
Posted: Sat 24 May 2008 11:14 am
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
Posted: Tue 27 May 2008 10:38 am
by II/JG77Hawk_5
Thanks M8, that is getting saved here for sure!!
Cheers,
Hawk5
Posted: Fri 29 Aug 2008 8:01 pm
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
Posted: Fri 29 Aug 2008 9:54 pm
by IV/JG7_4Shades
Hi Chap,
You could just try the following statement beforehand:
USE yourDBname
That might assist.
Cheers,
4Shades
Posted: Sat 21 Jul 2012 9:25 am
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.
#
Posted: Thu 21 Nov 2013 11:11 pm
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.
#