Entering 2D array w/ headers into SQLite

[sigil]

I have a 2D array, 400 x 400. The first row is headers for the data (I pulled it from an Excel sheet). I want to create a SQLite table with the header row for column names, then insert the rest of the rows into the table as values. Has anyone built a UDF that handles this job? I'm pretty sure I know how to write it: going through the array with a For..Next loop and concatenating a lot of single-quote marks and commas to create the SQLite statements. Just wondering if anyone has already built a function that handles the problem of passing arrays into SQLite.

Edited March 8, 2010 by sigil

[PsaltyDS]

It's likely too situation-specific for a UDF, but not hard to code. One recommendation would be to code the loop to execute "BEGIN", loop through all the "ADD" lines, then execute "GO" to commit them. That makes SQLite can run that sequence internally MUCH faster than fully committing one "ADD" line at a time.

[jchd]

I've not much to add to what our venerable pinguin already said, except this: 400 columns stinks like a doubtful design.

Anyway, try something and come back if you run into problems.

[sigil]

I've not much to add to what our venerable pinguin already said, except this: 400 columns stinks like a doubtful design.

Anyway, try something and come back if you run into problems.

It's working fine so far.

What better design would you recommend? My goal is to have a table of zip code distances, so that given any two zip codes, I can look up the distance between them.

[PsaltyDS]

It's working fine so far.

What better design would you recommend? My goal is to have a table of zip code distances, so that given any two zip codes, I can look up the distance between them.

Egad! Where did you get the data table source for that? Is that air miles or trucking kilometers? Does it take into account major air corridors or highway routes?

[jchd]

If your target is inside US, I believe that factual federal data has to be public domain. The NGIS has lots of stuff online but also look Geonames.org. Now if you need bird distances, you should use the Rtree extension and even possibly Spaliatlite (builds on SQLite) for such computations and/or representations. As always, Google is ...

BTW is you 400x400 table your version of inter-cities distances? If so, I hope it's a precomputed road distance table. If it's bird distance, that doesn't make sense to store such a large table. Accessing random columns within large rows is likely not to be faster than computing actual (bird) distance knowing coordinates of both ends.

In any case, such cartesian product is not the right choice, as it won't scale and there's a limit in the number of column supported, in SQLite and in many other DBMS.

The right way to model a many-to-many relationship is having a table of cities (in your case) with Id as IPK (integer primary key) and a table of distances city_1_Id, city_2_Id, distance.

Such a scheme scales correctly.