pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to FindNearest
6+ messages / 3 participants
[nested] [flat]
* How to FindNearest
@ 2006-09-30 08:43 Alexander Ilyin <a_ilyin@ukr.net>
0 siblings, 1 reply; 6+ messages in thread
From: Alexander Ilyin @ 2006-09-30 08:43 UTC (permalink / raw)
To: pgsql-sql
How to implement the functionality similar to the BDE/Paradox TTable.FindNearest?
Suppose we have huge table. On the screen we need to show contents of that table in the Grid and under that Grid user can input in TextBox some letters. OnChange of this TextBox we need to make current row selection in the Grid on the row with value of some column nearest to the user input in the TextBox.
How this can be implemented in PostgreSQL?
At my first sight:
I found there is CURSOR functionality which can move that cursor through the ResultSet. But we can move it just relatively phisical row number. Is there any chance to make cursor move to the row by some rule based on the row values?
All possible approaches and ideas highly appreciated. There is just one requirement - the performance, it should work as fast as possible.
Regards,
Alexander Ilyin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to FindNearest
@ 2006-10-03 00:20 Bruno Wolff III <bruno@wolff.to>
parent: Alexander Ilyin <a_ilyin@ukr.net>
0 siblings, 1 reply; 6+ messages in thread
From: Bruno Wolff III @ 2006-10-03 00:20 UTC (permalink / raw)
To: Alexander Ilyin <a_ilyin@ukr.net>; +Cc: pgsql-sql
On Sat, Sep 30, 2006 at 11:43:40 +0300,
Alexander Ilyin <a_ilyin@ukr.net> wrote:
>
> Suppose we have huge table. On the screen we need to show contents of that table in the Grid and under that Grid user can input in TextBox some letters. OnChange of this TextBox we need to make current row selection in the Grid on the row with value of some column nearest to the user input in the TextBox.
>
> How this can be implemented in PostgreSQL?
You can search for the row with the closest above and the row with the
closest below and then return whichever these is closer. (Remember to handle
the case where the target is larger or smaller than all values in the
database.) You can use ORDER BY and LIMIT 1 and an appropiiate WHERE
clause (something like columnname >= targetvalue) to do this.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to FindNearest
@ 2006-10-03 11:43 Alexander Ilyin <a_ilyin@ukr.net>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 6+ messages in thread
From: Alexander Ilyin @ 2006-10-03 11:43 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
-----Original Message-----
From: Bruno Wolff III <bruno@wolff.to>
To: Alexander Ilyin <a_ilyin@ukr.net>
Date: Mon, 2 Oct 2006 19:20:49 -0500
Subject: Re: How to FindNearest
> On Sat, Sep 30, 2006 at 11:43:40 +0300,
> Alexander Ilyin <a_ilyin@ukr.net> wrote:
> >
> > Suppose we have huge table. On the screen we need to show contents of that table in the Grid and under that Grid user can input in TextBox some letters. OnChange of this TextBox we need to make current row selection in the Grid on the row with value of some column nearest to the user input in the TextBox.
> >
> > How this can be implemented in PostgreSQL?
>
> You can search for the row with the closest above and the row with the
> closest below and then return whichever these is closer. (Remember to handle
> the case where the target is larger or smaller than all values in the
> database.) You can use ORDER BY and LIMIT 1 and an appropiiate WHERE
> clause (something like columnname >= targetvalue) to do this.
>
>
Thank you for your great idea. But how it can be used for positioning the cursor in the already existed ResultSet? Using your idea I can found the closest to targetvalue row but not its position in my ResultSet.
Anyway thank you for your idea it is very useful by itself. Also I can solve my problem using your idea and emulating the movement in my existed ResultSet. Even better - no need to store the large RS between cursor movements. Just each time I need to fetch the all visible rows.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to FindNearest
@ 2006-10-03 17:11 Bruno Wolff III <bruno@wolff.to>
parent: Alexander Ilyin <a_ilyin@ukr.net>
0 siblings, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2006-10-03 17:11 UTC (permalink / raw)
To: Alexander Ilyin <a_ilyin@ukr.net>; +Cc: pgsql-sql
On Tue, Oct 03, 2006 at 14:43:40 +0300,
Alexander Ilyin <a_ilyin@ukr.net> wrote:
>
> Thank you for your great idea. But how it can be used for positioning the cursor in the already existed ResultSet? Using your idea I can found the closest to targetvalue row but not its position in my ResultSet.
You wouldn't be able to use it to position a cursor. But if you aren't
retrieving a lot of records at once, this may still be a workable strategy
for you.
> Anyway thank you for your idea it is very useful by itself. Also I can solve my problem using your idea and emulating the movement in my existed ResultSet. Even better - no need to store the large RS between cursor movements. Just each time I need to fetch the all visible rows.
That sounds pretty reasonable.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Fw: How to FindNearest
@ 2006-10-03 17:35 paallen@attglobal.net
0 siblings, 1 reply; 6+ messages in thread
From: paallen@attglobal.net @ 2006-10-03 17:35 UTC (permalink / raw)
To: pgsql-sql
I guess I explained that very poorly. Sorry. It is actually much simplier.
I have 3 tables
Hole
Hole_id
X
Y
Z
Down_hole_survey
Hole_id
Depth_meters
Azimuth
Vertical_inclination
X
Y
Z
Sample
Hole_id
Depth_meters
X
Y
Z
Hole_id is a primary key in the hole table while it is a foriegn key in down_hole_survey and sample tables.
My function will take all the above info about the hole and down_hole_survey tables, and sample.hole_id and sample.depth_meters and will calculate the coordinates. I need to put these coordinates into sample.x , sample.y and sample.z .
What I used to do was have one function that would return 3 values (x,y,z). Then create 3 more functions that would call call the main function and pull out the 3 values seperately to update the 3 seperate columns. It works fine but I have to call the main function 3 times which produces a slow performance.
I hope that I explained it better this time.
Phil
Sent via BlackBerry from Cingular Wireless
-----Original Message-----
From: Bruno Wolff III <bruno@wolff.to>
Date: Tue, 3 Oct 2006 12:11:25
To:Alexander Ilyin <a_ilyin@ukr.net>
Cc:pgsql-sql@postgresql.org
Subject: Re: [SQL] How to FindNearest
On Tue, Oct 03, 2006 at 14:43:40 +0300,
Alexander Ilyin <a_ilyin@ukr.net> wrote:
>
> Thank you for your great idea. But how it can be used for positioning the cursor in the already existed ResultSet? Using your idea I can found the closest to targetvalue row but not its position in my ResultSet.
You wouldn't be able to use it to position a cursor. But if you aren't
retrieving a lot of records at once, this may still be a workable strategy
for you.
> Anyway thank you for your idea it is very useful by itself. Also I can solve my problem using your idea and emulating the movement in my existed ResultSet. Even better - no need to store the large RS between cursor movements. Just each time I need to fetch the all visible rows.
That sounds pretty reasonable.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Fw: How to FindNearest
@ 2006-10-04 04:11 Bruno Wolff III <bruno@wolff.to>
parent: paallen@attglobal.net
0 siblings, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2006-10-04 04:11 UTC (permalink / raw)
To: paallen@attglobal.net; +Cc: pgsql-sql
On Tue, Oct 03, 2006 at 17:35:55 +0000,
paallen@attglobal.net wrote:
>
> My function will take all the above info about the hole and down_hole_survey tables, and sample.hole_id and sample.depth_meters and will calculate the coordinates. I need to put these coordinates into sample.x , sample.y and sample.z .
>
> What I used to do was have one function that would return 3 values (x,y,z). Then create 3 more functions that would call call the main function and pull out the 3 values seperately to update the 3 seperate columns. It works fine but I have to call the main function 3 times which produces a slow performance.
>
> I hope that I explained it better this time.
If they are in the same table you can update all there in the same
update statement using syntax something like the following:
UPDATE sample SET x=(some expression), y=(some expression), z=(some expression)
WHERE some condition;
Probably the simplest place to start is having your function do the update
rather than return the three values.
I think in 8.2 there will be an easier way to use the result of a function that
returns a row to update multiple columns in an update statement. (That
doesn't resu;t in calling the function three times.)
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-10-04 04:11 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-30 08:43 How to FindNearest Alexander Ilyin <a_ilyin@ukr.net>
2006-10-03 00:20 ` Bruno Wolff III <bruno@wolff.to>
2006-10-03 11:43 ` Alexander Ilyin <a_ilyin@ukr.net>
2006-10-03 17:11 ` Bruno Wolff III <bruno@wolff.to>
2006-10-03 17:35 Fw: How to FindNearest paallen@attglobal.net
2006-10-04 04:11 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox