Finding intersection density for weighted lines connecting points across the continental USA

Question

I'm working on an one-time problem that I'm hoping to solve with a computer program using free GIS libraries (preferably for VB.Net, C#, or Python... Java or C/C++ could work too). Alternatively, I wouldn't mind using a tool like the free MapWindow GIS.

The problem:

• Given knowledge about the ultimate origin/destination for passengers flying, draw lines weighted by passenger volume connecting origin and destination airports. (i.e. passengers flying from San Francisco to New York can travel via many routes, but we only care about the total volume of passengers flying between the two cities).
• Find intersections for all the lines. Each intersection will have a 'weight' - the sum of the passenger volume on the two intersecting lines.
• Given 3-digit ZIP codes, calculate a density for each ZIP code by summing the weights of each intersection within X miles of the centroid.

The idea here, being to find good natural locations for hub airport given current passenger volume.

Tools/Accuracy:

I found Shapely for Python that looks useful. It includes a lot of spatial analysis tools (like centroid, buffer, intersection, etc.) that would make solving this problem fairly easy. However, it works in the Cartesian coordinate system. It seems like that would be rather inaccurate on a continental scale. It seems like, from an accuracy standpoint, it would be best to do spatial analysis in the geographic coordinate system (lat/lon on a datum) itself rather than re-projecting the data.

So my questions:

1. Since we only want a general picture, is the Cartesian coordinate systems accurate enough?
2. Can I do spatial analysis directly in a geographic coordinate system/datum?
3. What tools exist that would allow me to convert the data (if necessary), and do the required spatial analysis on it (with minimal setup effort since this is an one-time thing).

(Feel free to re-tag as appropriate... thanks!)

Answer 1

Without delving too much into the nitty gritty, I'm pretty sure you can do what you want within a Spatialite database. A few comments/questions come to mind.

If I understand, you have a table of passenger data which has origin/destination/volume. First you'll want a point layer of city locations. Now flight routes are usually along a "great circle", so before creating the lines-routes between cities, the coordinates should be in Lon/Lat, geographic CRS. Creating a line between two points is a simple spatialite query. And if the CRS is geographic in Lon/Lat angles then the line will be a along a great circle. And this line layer of routes will have a "passenger volume" attrib column which will get its values from the table of origin/destination data.

Next you'll want to intersect the lines and find all points of intersection, then sum up the passenger volume from both of the intersecting lines into that point layer's table. Also an SQL aggregate query.

Now we get to the zipcodes and totalling all passenger volume for each of the intersection points within each zip code. Here I'd reproject to suitable projected CRS, since area calculations, or buffering circles around each zipcode centroid will be all wrong in an unprojected geographic projection. You might choose Albers Equal Area - often used for maps of N. America. You'll have to obtain the (5 digit) zipcode regions, available here for 2010. Then another SQL spatial query will aggregate all the passenger volumes from all the intersections within each zip region. If you prefer to calculate a centroid for each region and create a circular buffer around, that could also be done within spatialite.

(Actually, why use the zipcode regions? Another approach - with other tools - could be to create a raster of flight volume by interpolating the volume values between all the intersection points...)

HTH Micha