Weird "Map" stuff going on...

[youradds]

Hi,

I'm a bit baffled about a "bug" I've found with my map code. The following query is being run:

SELECT ID,Title, ( 6371 * acos( cos( radians('41.7282358') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('9.212059299999964') ) + sin( radians('41.7282358') ) * sin( radians( Latitude ) ) ) ) AS distance

        FROM glinks_Links link WHERE link.Latitude BETWEEN 41.0282358 AND 42.4282358 AND link.Longitude BETWEEN 8.11205929999996 AND 10.3120593 ORDER BY distance WHERE distance < 100 LIMIT 1000

..and a link isn't being included properly in the result

If I just run this code so that I can see that link + the distance, the "distance" value is blank!!!

SELECT ID,Title, ( 6371 * acos( cos( radians('41.7282358') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('9.212059299999964') ) + sin( radians('41.7282358') ) * sin( radians( Latitude ) ) ) ) AS distance

        FROM glinks_Links link WHERE link.Latitude BETWEEN 41.0282358 AND 42.4282358 AND link.Longitude BETWEEN 8.11205929999996 AND 10.3120593 ORDER BY distance WHERE distance < 100 LIMIT 1000

..shows:

| 41269 | Aux Bords du Temps                                   | 41.728235800000000000000000000000 | 9.212059300000000000000000000000 |

You can see this in the image in this post.

Anyone got any ideas? The values seem ok for the lat/lng, but it just doesn't seem to be getting the distance value ( which is why its not showing up in my results)

TIA!

Andy

 

[youradds]

Damn it - I posted this in the wrong forum Could a mod please remove it, and I'll report in the "MySQL" forum. Sorry about that!

 

[gmmastros]

Shouldn't the where clause appear before the order by clause? I know this is true for SQL Server and Access, don't really know about MySQL.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[youradds]

Hi,

Thanks for the reply. Doing that gives a SQL error, so I'm guessing not I think its:

WHERE xxx ORDER BY xxx LIMIT xxx

Cheers

Andy

 

[gmmastros]

The part that was throwing me off is that you have 2 where clauses.

SELECT ID,Title, ( 6371 * acos( cos( radians('41.7282358') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('9.212059299999964') ) + sin( radians('41.7282358') ) * sin( radians( Latitude ) ) ) ) AS distance

        FROM glinks_Links link [!]WHERE[/!] link.Latitude BETWEEN 41.0282358 AND 42.4282358 AND link.Longitude BETWEEN 8.11205929999996 AND 10.3120593 ORDER BY distance [!]WHERE[/!] distance < 100 LIMIT 1000

As a test, I would suggest you remove the where clause on the distance. Since you are using a bounding box, you shouldn't need the distance filter.

Does the glinks_Links table have a distance column in it. This could account for your problem. Again, going back to my experience with SQL Server (and acknowledging that things may be different with MySQL), I know you cannot use a column alias in a where clause. Instead, you would need to repeat the entire calculation in the Where clause, like this:

SELECT ID,
       Title, 
       ( 6371 * acos( cos( radians('41.7282358') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('9.212059299999964') ) + sin( radians('41.7282358') ) * sin( radians( Latitude ) ) ) ) AS distance

FROM   glinks_Links link 
WHERE  link.Latitude BETWEEN 41.0282358 AND 42.4282358 
       AND link.Longitude BETWEEN 8.11205929999996 AND 10.3120593
       [!]AND ( 6371 * acos( cos( radians('41.7282358') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('9.212059299999964') ) + sin( radians('41.7282358') ) * sin( radians( Latitude ) ) ) ) < 100[/!]
ORDER BY distance 
LIMIT 1000

I don't know if this will solve your problem, but I do encourage you to give it a try.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[youradds]

Hi,

Thanks for the reply. Not quite sure why we have 2 WHERE statements TBH! (I know its worked for ages though haha). I've fixed that up, but it still doesn't help that record show up

FYI, I'm gonna use this thread from now on (as its in the proper SQL forum ):

http://www.tek-tips.com/viewthread.cfm?qid=1674684

... just in case I don't reply to this any more <G>

Thanks

Andy