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

 

[vacunita]

Several things to note:
[ol]
[li] Your 2 queries are exactly the same. [/li]
[li] There is no image attached anywhere[/li]
[li] The query shown couldn't possibly return anything at all
[ol][li] Having a [blue]WHERE[/blue] clause after the [blue]ORDER BY[/blue] clause should be producing a big nasty error. [/li]
[li]Attempting to use an alias in the where clause will also cause an error. The Where clause is evaluated before the select item list, so the field aliases aren't available to it. [/li]
[/ol]
[/li]
[/ol]

Your query should end up looking like:

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 [red]( 6371 * acos( cos( radians('41.7282358') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('9.212059299999964') ) + sin( radians('41.7282358') ) * sin( radians( Latitude ) ) ) )[/red] < 100 ORDER BY distance LIMIT 1000

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[youradds]

Hi,

Thanks for the reply. Not sure why the "image url" didn't get posted.

Anyway, I tried your SQL code and it still doesn't work. I tweaked it a little so we don't limit by the distance < 10, so we have:

SELECT ID,Title,Latitude,Longitude , ( 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 LIMIT 1000

...and it gives the result:

http://andrew-newby.com/test.txt

Notice the 1st result... it has no value for "distance", even though it clearly has a value for lat/lng :/

TIA

Andy

 

[vacunita]

That's because the first one is so tiny of a distance, very very close to 0 that whatever you are using to view the results may not be displaying a 0. Rather just nothing, empty, null etc...

Consider it 0 for all intents and purposes.



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[youradds]

Hi,

Thanks for the reply. Mmm, is there no way to grab that result? Obviously 0 miles from the place they are searching for is ideal!

TIA

Andy