Thank you, Carsten, for your response. As a matter of fact, I DID try the
arrangement from lowest to highest and that didn't work either.
I ran some sample counts and came up with a solution. Unfortunately, I still
can't make use of the BETWEEN function for the longitudes (negative values).
Here's what I found. (Sorry if it's long winded, but it explains the sequence
of events pretty clearly).
I tried the numerically larger of the negative values first as was suggested,
but that didn't work.
mysql> select count(*) from test_table
-> where latitude between 40.589506 and 40.589992
-> and longitude between -73.922420 and -73.922080;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.04 sec)
So I tested how many records are in the table.
mysql> select count(*) from test_table;
+----------+
| count(*) |
+----------+
| 23661 |
+----------+
1 row in set (0.00 sec)
And how many are in the LATITUDE I'm testing for.
mysql> select count(*) from test_table
-> where latitude between 40.589506 and 40.589992;
+----------+
| count(*) |
+----------+
| 131 |
+----------+
1 row in set (0.05 sec)
I then tested for just the longitude portion of the query, but got ZERO.
mysql> select count(*) from test_table
-> where longitude between -73.922420 and -73.922080;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
So I try an OTHERWISE view and got a reasonable number
mysql> select count(*) from test_table
-> where longitude not between -73.922420 and -73.922080;
+----------+
| count(*) |
+----------+
| 23614 |
+----------+
1 row in set (1.74 sec)
I then combined the reasonable queries and got good results.
The results point to the 10 I'm missing! (131 minus the 121 is appropriate.)
mysql> select count(*) from test_table
-> where latitude between 40.589506 and 40.589992
-> and longitude not between -73.922420 and -73.922080;
+----------+
| count(*) |
+----------+
| 121 |
+----------+
1 row in set (0.67 sec)
So I tried this as an alternative to the BETWEEN functionality:
mysql> select count(*) from test_table
-> where latitude between 40.589506 and 40.589992
-> and longitude >= -73.922420
-> and not longitude > -73.922080;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.04 sec)
There's the 10!
And try it with the actual rows
mysql> select situs_line2, latitude, longitude from test_table
-> where latitude between 40.589506 and 40.589992
-> and not longitude > -73.922080
-> and longitude >= -73.922420
-> order by longitude;
+-------------+-----------+------------+
| situs_line2 | latitude | longitude |
+-------------+-----------+------------+
| BROOKLYN NY | 40.589836 | -73.922420 |
| BROOKLYN NY | 40.589773 | -73.922352 |
| BROOKLYN NY | 40.589742 | -73.922318 |
| BROOKLYN NY | 40.589710 | -73.922284 |
| BROOKLYN NY | 40.589679 | -73.922250 |
| BROOKLYN NY | 40.589648 | -73.922216 |
| BROOKLYN NY | 40.589616 | -73.922182 |
| BROOKLYN NY | 40.589585 | -73.922148 |
| BROOKLYN NY | 40.589554 | -73.922114 |
| BROOKLYN NY | 40.589523 | -73.922080 |
+-------------+-----------+------------+
10 rows in set (0.06 sec)
The "BETWEEN min AND max" makes sense and I use that on a Sybase db I work
with at the office, but it doesn't seem to work here.
I'm using WIN32 on NT 3.23.21-beta at home and 3.23.19-beta at work.
Kieran
"Carsten H. Pedersen" wrote:
> > I can code to select latitudes BETWEEN certain values. But it won't work
> > with the LONGITUDES.
> > eg: I'd like to say
> > select count(*) from test_table where latitude between 40.589506 and
> > 40.589992
> > and longitude between -73.922080 and -73.922420.
> >
> > I should only get 10 out of the above rows. I get none.
> > Both Latitude and Longitude are defined as decimal 9 6
> > Could anyone offer any guidance?
> > TIA
> > Kieran Ames
> >
>
> Reading The Fine Manual:
> --
> expr BETWEEN min AND max
> If expr is greater than or equal to min and expr
> is less than or equal to max, BETWEEN returns 1,
> otherwise it returns 0. This is equivalent to the
> expression (min <= expr AND expr <= max) if all the
> arguments are of the same type.
> --
> I.e. you must put the smallest of the two numbers
> (the numerically larger of the negative values)
> before 'and' like so :
>
> select count(*) from test_table
> where latitude between 40.589506 and 40.589992 and
> longitude between -73.922420 and -73.922080.
>
> / Carsten
> --
> Carsten H. Pedersen
> keeper and maintainer of the bitbybit.dk MySQL FAQ
> http://www.bitbybit.dk/mysqlfaq
