From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: origins/destinations |
Date: | 2009-05-19 21:39:15 |
Message-ID: | 4A132703.70007@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Andy Colson wrote:
> Carson Farmer wrote:
>> Hi list,
>>
>> I have (what I thought was) a relatively simple problem, but my
>> knowledge of sql is just not good enough to get this done:
>>
>> I have a table which is basically a number of individuals with both
>> their origin and destination as columns (see Table 1). In this case,
>> origins and destinations are the census area in which they and work.
>> What I would like to do is generate an nxn matrix (preferably output
>> to csv but I'll take what I can get), where origins are on the y
>> axis, and destinations on the x axis (see Table 3).
>>
> <snip>
>
> Would it have to be sql only? I think this would be pretty easy in perl.
indeed, this would better be done outside the database. you're
generating a sparse table of N x N dimensions and likely only relatively
few elements populated, unless your population count greatly exceeds the
number of locations. I think I'd do a SQL query for
distinct(source,dest),count(population) and then use this to fill your
matrix on the client side.
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2009-05-19 21:40:42 | Re: Direct I/O and postgresql version |
Previous Message | Andy Colson | 2009-05-19 21:34:24 | Re: Help with join syntax sought |