database design question

[sigil]

This is more a general design question than a specific code question.

I'm working on a route optimization program, basically a version of the traveling salesman problem. The problem is this:

Given an array of zip codes, e.g.,

dim $zArray[4] = [90210,10001,60290,33138]

and a proposed zip code to be added, e.g.,

$newzip=75201

The script's goal is to figure out where to add this new zip code into $zArray so that the sequential distance between the points in $zArray is the shortest.

I'm trying to figure out how best to create the data source. My current solution is to have a 43000 x 43000 array--there are about 43000 zip codes in the U.S.--and each cell is the distance between any two zip codes. Or do it in SQL with 43000 rows and 43000 columns. I'm just wondering if there's a better way to set this up. What do you guys think is the best way to set up the data source for fastest access?

[jchd]

Are you serious about a 1.3Giga-rows table?

No that's not the way to do it. The simplest setup I would recommend for that is Spatialite (SQLite with the rTree extension plus a myriad of geographic geodesic layer plus ...).

You'll find everything you need there, and so much more. And you're doubly lucky since AutoIt has an SQLite UDF as well.