On Aug 26, 1:11 am, a...@desiboy.com wrote:
assume we have 10,000 cities. imagine those distance calculating
tables, where city A (row) to city Z (col) would equal a float value.
city Z (row) to city Z(col) would be 0. The only problem is our table
is not A-Z with a 26x26 table. It's 10,000 x 10,000 .
How can you store this data in a sql table efficiently ?
Thanks in advance.
1) If you really want the distance as a column in the table, then:
CREATE TABLE OneWayTrips
(first_city_code INTEGER NOT NULL,
second_city_code INTEGER NOT NULL,
distance FLOAT NOT NULL,
CHECK (first_city_code < second_city_code) ,
PRIMARY KEY (first_city_code, second_city_code) );
Now do everything off of a VIEW with an INSTEAD OF TRIGGER for
insertions, and updates and deletes.
CREATE VIEW Trips (first_city_code, second_city_code, distance)
AS
SELECT first_city_code, second_city_code, distance
FROM OneWayTrips
UNION ALL
SELECT second_city_code, first_city_code, distance
FROM OneWayTrips
UNION ALL
SELECT first_city_code, first_city_code, 0.00
FROM OneWayTrips
UNION ALL
SELECT second_city_code, second_city_code, distance
FROM OneWayTrips;
2) Otherwise:
CREATE TABLE Cities
(city_code INTEGER NOT NULL PRIMARY KEY,
longitude FLOAT NOT NULL,
latitude FLOAT NOT NULL);
Compute the distance with the usual formulas.