[SQL] Flight numbers data
Hi, i am in the process of writing an application about tickets, flights, etc, and i am thinking of getting the primitive data ready at the begining and doing it the right way, (e.g. the user will just select a flight number and doesnt have to enter the airports, airport coordinates, countries, airline name, airline code, departure/arrival times, etc...) I found some sources on the web about airlines (codes, names, countries, etc...), also i found about airports, but nothing about flight numbers! This application will be for enterprise (internal) company use, covering the flights of emploees around the world. I was just wondering how internet ticket agents manage to do it. Has anyone from the postgresql camp ever solved such a problem? One point for me, is that my company is doing shipping, and we are not in the travel agents business (IATA, standards etc..) at all, so its hard for me to find the path to follow, and in addition, going the full Travel Agent path would be an overkill, since what we need is just an application for the tickets. It is just that i wanna do it right, maybe play a little bit with AJAX also who knows :) Thanx for any hints. -- -Achilleus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Ugly group by problem
Hello, I have a table filled from third party that basically has the following structure: link_id | feat_id | other | columns... +-+---+--- 1 | 2 | ... 2 | 5 | ... 2 | 23 | ... 3 | 5 | ... 3 | 23 | some | data 3 | 23 | other | data 5 | 23 | ... 9 | 23 | ... This structure is fixed, and we can't change it, but we can create We have about 37 million different link_ids, and 35 million feat_ids. There are feat_ids that appear at several thousand link_ids, but a link_id does not have more than a douzen feat_ids. Now I need to group together all link_ids that have the same set of feat_ids. In the example above, the sets would be (1), (2,3) and (5,9), and the other columns would be run through some aggregate functions. Currently, this is done via an external JAVA application, but I'm looking for a way to express this via sql / plpgsql to ease deployment. I could imagine some ugly code using ARRAY (not tried yet), but how would you pack this problem? It seems that I'm just stuck in my thoughts and miss the beauty way to solve it. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Ugly group by problem
O Markus Schaber έγραψε στις Mar 29, 2006 :
> Hello,
>
> I have a table filled from third party that basically has the following
> structure:
>
> link_id | feat_id | other | columns...
> +-+---+---
> 1 | 2 | ...
> 2 | 5 | ...
> 2 | 23 | ...
> 3 | 5 | ...
> 3 | 23 | some | data
> 3 | 23 | other | data
> 5 | 23 | ...
> 9 | 23 | ...
>
> This structure is fixed, and we can't change it, but we can create
>
> We have about 37 million different link_ids, and 35 million feat_ids.
> There are feat_ids that appear at several thousand link_ids, but a
> link_id does not have more than a douzen feat_ids.
>
> Now I need to group together all link_ids that have the same set of
> feat_ids. In the example above, the sets would be (1), (2,3) and (5,9),
> and the other columns would be run through some aggregate functions.
>
> Currently, this is done via an external JAVA application, but I'm
> looking for a way to express this via sql / plpgsql to ease deployment.
>
> I could imagine some ugly code using ARRAY (not tried yet), but how
> would you pack this problem? It seems that I'm just stuck in my thoughts
> and miss the beauty way to solve it.
Well i did it with arrays, i dont know the performance
implications tho:
foodb=# SELECT * from markustest ;
link_id | feat_id | other
-+-+---
1 | 2 | 1
2 | 5 | 2
2 | 23 | 2
3 | 5 | 3
3 | 23 | 3
3 | 23 | 3
5 | 23 | 5
9 | 23 | 9
(8 rows)
foodb=# SELECT qoo.foo2,sum(qoo.foo3) from (SELECT mt.link_id as
foo,_int_union(array(select mt2.feat_id from markustest mt2 where
mt2.link_id=mt.link_id order by mt2.feat_id),'{}') as foo2,other::int4 as
foo3 from markustest mt) as qoo GROUP BY qoo.foo2;
foo2 | sum
+-
{2}| 1
{5,23} | 13
{23} | 14
(3 rows)
foodb=#
The _int_union trick is to force the arrays to have unique values.
The order by has the meaning that '{5,23}' and '{23,5}' should be treated
the same way.
>
> Thanks,
> Markus
>
--
-Achilleus
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Flight numbers data
On Wed, 2006-03-29 at 02:17, Achilleus Mantzios wrote: > Hi, i am in the process of writing an application about > tickets, flights, etc, and i am thinking of getting the primitive > data ready at the begining and doing it the right way, > (e.g. the user will just select a flight number and doesnt > have to enter the airports, airport coordinates, countries, > airline name, airline code, departure/arrival times, etc...) > > I found some sources on the web about airlines (codes, names, countries, > etc...), also i found about airports, but nothing about flight numbers! That's cause companies that keep track of those things charge a LOT of money for their datasets. > This application will be for enterprise (internal) company use, > covering the flights of emploees around the world. > > I was just wondering how internet ticket agents manage to do it. They subscribe to some service that has this information in the back end. The data in that back end comes from the one or two sources of that data who charge yearly subscriptions in the hundreds of thousands of dollars. > Has anyone from the postgresql camp ever solved such a problem? Where I work, we're building a middle level system (look up the website that goes with my domain). And if we weren't in the airline reservation industry, we couldn't afford the data sets. > It is just that i wanna do it right, maybe play a little bit with AJAX > also who knows :) But "doing it right" goes against almost every tenet of the airline reservation industry :) haha. only serious. Seriously though, you might be able to get your travel agent or whoever you do reservations through to provide you with this information in some kind of reliable format for the tickets you've bought. If you can get some kind of automated feed from them, that would be the best way, if not, maybe they can email you the info each time, and you can cut and paste it in. There's much in the reservation industry that is basically a computer implementation of a 3x5 note card system. And none of those systems were originally built to talk to each other, so it's often impossible for a single user to get any kind of automatically created data from such an industry. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Getting more information about errorcodes such as when these error1 happen
Good morning, In my plpgsql functions I use "exception when..." to catch possible exceptions of my data. I found postgresql error code track functions are very helpful. In my functions, I will not catch all error codes, but only the ones applying to my data operation . I found errorcodes info here: http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html But I am afraid that I could not imagine when and under what possible circumstances some errorcodes may happen just by their names such as : locator_exception, invalid_grantor, active_sql_transaction , and so on. I tried to search the online docs in order to get more info such as when will errorcode X happens. But I could not find it. Could someone tell me some links that I can find more information about these errorcodes please? Thanks alot, Ying ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Ugly group by problem
On 29 mar 2006, at 17.42, Achilleus Mantzios wrote:
The _int_union trick is to force the arrays to have unique values.
The order by has the meaning that '{5,23}' and '{23,5}' should be
treated
the same way.
I didn't have the _int_union function. Is it internal? What PG-
version did you use? I solved it using DISTINCT instead (perhaps
making it a little simpler as well):
SELECT
ARRAY(SELECT DISTINCT feat_id FROM linktest WHERE
link_id=t1.link_id ORDER BY feat_id) AS feat_group,
SUM(other)
FROM linktext t1
GROUP BY 1;
feat_group | sum
---+-
{2}| 1
{5,23} | 13
{23} | 14
(3 rows)
Of course I haven't tested the performance on a table containing a
lot of data... I'm not sure how the planner will treat the ARRAY-
construct. You might be able to speed it up by turning it into a
function marked STABLE, something like this:
CREATE OR REPLACE FUNCTION feat_group(INTEGER) RETURNS INTEGER[] AS $$
SELECT ARRAY(SELECT DISTINCT feat_id FROM linktest WHERE link_id=$1
ORDER BY feat_id);
$$ LANGUAGE sql STABLE;
SELECT
feat_group(link_id),
SUM(other)
FROM linktext t1
GROUP BY 1;
Sincerely,
Niklas Johansson
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] results being col wise rather than row wise
HI All, I have 5 tables which have different columns as shown below, each table has a different case column, i want to retrieve sample related information as follows: pid | xindex | yindex | height Index | flag | case1 | case 2.etc But i get this queer result: pid | xindex | yindex | Height Index | flag | case-+++-+-+-102234 | 411 | 271 | 9 | 1 | 80102234 | 411 | 271 | 9 | 1 | 83102234 | 411 | 271 | 9 | 1 | 87102234 | 411 | 271 | 9 | 1 | 101102234 | 411 | 271 | 9 | 1 | 129102234 | 411 | 271 | 9 | 1 | 91102234 | 411 | 271 | 9 | 1 | 120.3 Why is the result in a column wise rather than row wise??? Could somebody explain why the redundancy with respect to pi, xindex, yindex, height index, flag but the CORRECT values for the cases but not in the order expected!!! Thanks, Hrishi
Re: [SQL] results being col wise rather than row wise
On Wed, 29 Mar 2006, Hrishikesh Deshmukh wrote: > HI All, > > I have 5 tables which have different columns as shown below, each table has > a different case column, > i want to retrieve sample related information as follows: > > pid | xindex | yindex | height Index | flag | case1 | case 2.etc > > But i get this queer result: > pid| xindex | yindex | Height Index | flag | case > -+++-+-+- > 102234 |411 |271 | 9 | 1 | 80 > 102234 |411 |271 | 9 | 1 | 83 > 102234 |411 |271 | 9 | 1 | 87 > 102234 |411 |271 | 9 | 1 | 101 > 102234 |411 |271 | 9 | 1 | 129 > 102234 |411 |271 | 9 | 1 | 91 > 102234 |411 |271 | 9 | 1 | 120.3 > > Why is the result in a column wise rather than row wise??? > Could somebody explain why the redundancy with respect to pi, xindex, > yindex, height index, flag but the CORRECT values for the cases but not in > the order expected!!! Can you give us the specific schema and query you used? We're only going to be guessing otherwise. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Flight numbers data
O Scott Marlowe έγραψε στις Mar 29, 2006 : > On Wed, 2006-03-29 at 02:17, Achilleus Mantzios wrote: > > Hi, i am in the process of writing an application about > > tickets, flights, etc, and i am thinking of getting the primitive > > data ready at the begining and doing it the right way, > > (e.g. the user will just select a flight number and doesnt > > have to enter the airports, airport coordinates, countries, > > airline name, airline code, departure/arrival times, etc...) > > > > I found some sources on the web about airlines (codes, names, countries, > > etc...), also i found about airports, but nothing about flight numbers! > > That's cause companies that keep track of those things charge a LOT of > money for their datasets. > > > This application will be for enterprise (internal) company use, > > covering the flights of emploees around the world. > > > > I was just wondering how internet ticket agents manage to do it. > > They subscribe to some service that has this information in the back > end. The data in that back end comes from the one or two sources of > that data who charge yearly subscriptions in the hundreds of thousands > of dollars. Just to examine a little closer (bassically show my boss this!), can you give me some pointers of some kind of web services providers (in the broad sense) that sell such yearly subscriptions, for internet travel agents? > > > Has anyone from the postgresql camp ever solved such a problem? > > Where I work, we're building a middle level system (look up the website > that goes with my domain). And if we weren't in the airline reservation > industry, we couldn't afford the data sets. > > > It is just that i wanna do it right, maybe play a little bit with AJAX > > also who knows :) > > But "doing it right" goes against almost every tenet of the airline > reservation industry :) haha. only serious. > > Seriously though, you might be able to get your travel agent or whoever > you do reservations through to provide you with this information in some > kind of reliable format for the tickets you've bought. If you can get > some kind of automated feed from them, that would be the best way, if > not, maybe they can email you the info each time, and you can cut and > paste it in. > > There's much in the reservation industry that is basically a computer > implementation of a 3x5 note card system. And none of those systems > were originally built to talk to each other, so it's often impossible > for a single user to get any kind of automatically created data from > such an industry. That was pretty much what i suspected, i have seen some 3270 type terminal emulators in some travel agents that reminded me VSE/MVS/AS400/CICS and other beauties like that!! Well, Scott your response was really helpful. What i will do is just build the tables with airlines info, airports info (IATA codes,etc..) with sources for the web. Really are there any good sources out there for such info? For airports i came across http://www.partow.net/miscellaneous/airportdatabase/ it seems very complete but it misses new airports (it doesnt have the new Greek one (IATA code ATH)which is some 5 years old). Other sources i found is just HTML pages which is not so handy. Then i would let the in-company tickets person build the flight numbers table incrementally as she works. What do you think? > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > -- -Achilleus ---(end of broadcast)--- TIP 6: explain analyze is your friend
