comp.databases.mysql comp.databases.mysql

#11

On Oct 12, 6:33*am, Axel Schwenke <axel.schwe... (AT) gmx (DOT) de> wrote:

Quote:

Charles <ccha... (AT) gmail (DOT) com> wrote:
On Oct 12, 12:09=A0am, Axel Schwenke <axel.schwe... (AT) gmx (DOT) de> wrote:

http://dev.mysql.com/doc/refman/5.1/...-routines.html

Neither way you can execute any Java code in the MySQL server.
But how complicated can that "formula" be, that you can't
express it in SQL?

Quite.

http://www.movable-type.co.uk/script...-vincenty.html

1. this is not complicated
2. this can be expressed in PL/SQL
3. this is rather senseless IMHO

What I'm looking for is *coding so that I can:

SELECT * FROM table1 WHERE column9 = function(COL5, COL6, COL7, COL8)

4. comparing floating point numbers for equality is foolish

Looks like a perfect fit for a stored function. RTFM!

XL

Using 4 floats to compute an integer is probably OK, though.

#12

Charles <cchamb2 (AT) gmail (DOT) com> writes:

Quote:

On Oct 12, 7:37Â*am, Sherm Pendley <sherm.pend... (AT) gmail (DOT) com> wrote:
Charles <ccha... (AT) gmail (DOT) com> writes:
On Oct 12, 12:09Â*am, Axel Schwenke <axel.schwe... (AT) gmx (DOT) de> wrote:

But how complicated can that "formula" be, that you can't
express it in SQL?

Quite.

http://www.movable-type.co.uk/script...-vincenty.html

You're kidding, right? That's just a loop and a few calls to built-in
trig functions. It's well within the capabilities of an SQL procedure.

Which was the original purpose of the question - to make sure it can
be done and maybe a hint or suggestion as to direction in which to
find it.

Here's a starting point:

<http://dev.mysql.com/doc/refman/5.0/en/stored-programs-defining.html>

sherm--

--
Sherm Pendley
<http://camelbones.sourceforge.net>
Cocoa Developer

#13

Quote:

The function I want to implement takes the GPS latitude/longtitude and
computes the distance between them.

What I'm looking for is coding so that I can:

SELECT * FROM table1 WHERE column9 = function(COL5, COL6, COL7, COL8)

and possible pass two constants as parameters so that I don't have
this problem again.

As others have pointed out, an equality is hardly possible with
floating-point numbers. But I assume you are looking for points within a
range or something.

It may sound like premature optimization, but if your table is of any
serious size, this query can be very slow. It needs a table scan over
the entire table. If there is any less computationally expensive way of
estimating the result, it could help to store intermediate results or to
add an extra constraint in the WHERE clause if it can use an index to
reduce the number of rows scanned by the heavy function.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

#14

On Oct 14, 2:25*am, Willem Bogaerts <w.bogae... (AT) kratz (DOT) nl> wrote:

Quote:

The function I want to implement takes the GPS latitude/longtitude and
computes the distance between them.

What I'm looking for is *coding so that I can:

SELECT * FROM table1 WHERE column9 = function(COL5, COL6, COL7, COL8)

and possible pass two constants as parameters so that I don't have
this problem again.

As others have pointed out, an equality is hardly possible with
floating-point numbers. But I assume you are looking for points within a
range or something.

It may sound like premature optimization, but if your table is of any
serious size, this query can be very slow. It needs a table scan over
the entire table. If there is any less computationally expensive way of
estimating the result, it could help to store intermediate results or to
add an extra constraint in the WHERE clause if it can use an index to
reduce the number of rows scanned by the heavy function.

I'm already at that point. I strip down the fields in the SELECT
statement creating the table this procedure runs on, and the resulting
table is 3 fields smaller.

#15

On Oct 14, 2:25*am, Willem Bogaerts <w.bogae... (AT) kratz (DOT) nl> wrote:

Quote:

The function I want to implement takes the GPS latitude/longtitude and
computes the distance between them.

What I'm looking for is *coding so that I can:

SELECT * FROM table1 WHERE column9 = function(COL5, COL6, COL7, COL8)

and possible pass two constants as parameters so that I don't have
this problem again.

As others have pointed out, an equality is hardly possible with
floating-point numbers. But I assume you are looking for points within a
range or something.

It may sound like premature optimization, but if your table is of any
serious size, this query can be very slow. It needs a table scan over
the entire table. If there is any less computationally expensive way of
estimating the result, it could help to store intermediate results or to
add an extra constraint in the WHERE clause if it can use an index to
reduce the number of rows scanned by the heavy function.

Computing a distance to one decimal point with four floats (two GPS
coordinages) is just fine for accuracy.

As far as optimization, I've already looked that far ahead. The
formula takes an 8 column table and reduces it to 5, on an average of
less than 200 records, and it will run this query about 30 times an
hour (assuming it can run in that kind of time).

The development predecessor to MySQL was Paradox, and it took 2 1/2
minutes without any data just to run the formula.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SQL

comp.databases.mysql comp.databases.mysql

Re: SQL - 10-12-2010 , 09:59 AM

Re: SQL - 10-12-2010 , 10:19 AM

Re: SQL - 10-14-2010 , 04:25 AM

Re: SQL - 10-15-2010 , 05:17 AM

Re: SQL - 10-18-2010 , 10:22 AM