whether to get all records before calculation or fewer as determined by the calc?

[bazz]

I can imagine your answer to this but I need to know for sure.

here is the basic code which should bring me in everything I might need.


SELECT BD.$value
FROM tbl_businessDetails


Putting that into an array, i should have all the values from that field and be able to loop through them to do a calculation on each.

QUESTION:
is it better to get all the values and to then perform the calculation to differentiate between/select specifically, a few or,
is it possible to do a conditional select where the condition involves a mathematical calculation.

I want to get the values from the records where pythagoras triangulates them to be within a certain distance of a base reference.

bazz

[Fumigator]

So the two choices are perform a gazillion queries and calculate for each query, or perform one query and calculate for each row returned? I pick the 2nd one, as long as you don't select a bunch of rows you don't need.

[oesxyl]

It's not clear for me, can give some detail?

What I think this moment:
- you fetch from the database an array of, let's say, floats
- want to filter the values of this array, exclude some of them, based on
a distance criteria between each of this value and a given one
- do you want to know how is better to filter, one of this:
1. mysql function, used in query
2. filter after fetching values using perl

- using mysql you can compute different things but are some limitation
- using perl you have other limitation

best regards

[bazz]

Ok,

I think the choices are: -

1. to bring in 'a gazillion' values to an array and to process each, in a loop. Having done so, the number of relevant ones are identified where a second query can bring in the other data for each of the relevant records.

2. to query the db and to bring in only the needed records as determined by the mathematical conditions.

so the question, I suppose is whether to try to put the mathematical calculation into the query or to do it with perl, in between to queries.

I plump for #2 so that it would only bring in what I need. Is this query anywhere near close?


my $first_ref = 0001;
my $second_ref = 2345;
my $limit = 25;

SELECT BD.business_id, BD.value1, BD.value2
FROM tbl_businessDetails BD
WHERE ((($first_ref - value1)/1000)**2) + ((($second_ref - value2)/1000)**2) < $limit


bazz

[oesxyl]

so the question, I suppose is whether to try to put the mathematical calculation into the query or to do it with perl, in between to queries.

yes, exactly, my questions was lost between thinking an writing, :)


I plump for #2 so that it would only bring in what I need. Is this query anywhere near close?


my $first_ref = 0001;
my $second_ref = 2345;
my $limit = 25;

SELECT BD.business_id, BD.value1, BD.value2
FROM tbl_businessDetails BD
WHERE ((($first_ref - value1)/1000)**2) + ((($second_ref - value2)/1000)**2) < $limit


bazz

agree, is the best solution. Anyway even the mathematical expression is more complicated than that and as far as can be done using mysql function and operators is faster and use less resources then first.

best regards

[bazz]

here is my query now.


my $first_ref = 349730;
my $second_ref = 381576;
my $limit = 25;
my $dbh;
my $dbhconnect = connect_to_db($dbh);

my $sth = $dbhconnect->prepare ("SELECT ADDR.address_id, ADDR.northings, ADDR.eastings
FROM tbl_address ADDR
WHERE POW((($first_ref - ADDR.northings)/1000),2)+POW((($second_ref - ADDR.eastings)/1000),2)> 1 AND < $limit
") or die "prepare statement failed: $DBO::errstr\n";


Can you help me with the part shown in red? If I don't narrow it down to be greater than 1, it brings in the record which is my base point, causing a duplicate. I need also to limit it to <25 to avoid bringing in data I won't need.

bazz

[oesxyl]

try this:


my $first_ref = 349730;
my $second_ref = 381576;
my $limit = 25;
my $dbh;
my $dbhconnect = connect_to_db($dbh);

my $sth = $dbhconnect->prepare ("SELECT ADDR.address_id, ADDR.northings, ADDR.eastings
FROM tbl_address ADDR
WHERE POW((($first_ref - ADDR.northings)/1000),2)+POW((($second_ref - ADDR.eastings)/1000),2)
BETWEEN 1 AND $limit") or die "prepare statement failed: $DBO::errstr\n";



Can you help me with the part shown in red? If I don't narrow it down to be greater than 1, it brings in the record which is my base point, causing a duplicate. I need also to limit it to <25 to avoid bringing in data I won't need.

bazz

I can't test it but I don't see anything that can be wrong.

best regards

[bazz]

thats great. and working now. :thumbsup:

It is much shorter a code than my current perl process which worked with every possible ref in my system and applied pythagoras in a loop. However can you tell me if there is a function I should be looking for so that i can get from the query to show how far each one is from the base point. And presumably I want to use ORDER BY to output them in distance order - nearest first.

I guess I am really asking, what value does MySQL have in this query which might represents the returned results. Something like a default variable. I could do it in perl after returning the array but, it may as well do it in MySQL if possible. :)

bazz

[oesxyl]

thats great< and working now.

however i would like to know if there is a function or soemthing i should look for so that i can get from the query how far each one is from the base.

bazz

something like this:


SELECT ADDR.address_id, ADDR.northings, ADDR.eastings
FROM tbl_address ADDR,
sqrt(POW((($first_ref - ADDR.northings)/1000),2)+POW((($second_ref - ADDR.eastings)/1000),2) as distance
WHERE POW((($first_ref - ADDR.northings)/1000),2)+POW((($second_ref - ADDR.eastings)/1000),2)
BETWEEN 1 AND $limit


adding a new column with the distance?.
I'm pretty sure that could be used a mysql variable and avoid to
compute twice the distance, but not sure how.


using something like:


select @dist := ... where @dist between 1 and $limit


don't work because @dist is not the value of the curent row before output


best regards

[bazz]

Thank you oesxyl for your help.

Is the result set, of a query, put into a special Var? Can anyone point me to its location, in the documentation, please

bazz

[oesxyl]

Thank you oesxyl for your help.

Is the result set, of a query, put into a special Var or array, or something? Maybe this would enable me to find a way to get the distance.

bazz

in the way I write in my post will be a normal column named distance in output of the query. Unfortunataley have some limitation, for example as far I know you can't create a view.

best regards

[bazz]

OK, I'll process the returned refs in perl then.

thanks again.