Finding nearest latitude longitude points query in MySQL not working

Question

I used the answer here to setup my query:

https://stackoverflow.com/a/1006668/211457

I'm running this query:

SELECT  * 
FROM    users 
WHERE   MBRContains(LineFromText(CONCAT( 
    '('    , 39.76784253493 + 10 / ( 111.1 / cos(RADIANS(39.76784253493)))
    , ' '
    , -75.871009379625 + 10 / 111.1
    , ','
    , 39.76784253493 - 10 / ( 111.1 / cos(RADIANS(-75.871009379625)))
    , ' '
    , -75.871009379625 - 10 / 111.1 
    , ')' ))
    ,location)

My database has these entries:

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `location` point NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `sx_place_location` (`location`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `users` (`id`, `location`)
VALUES
    (1,X'000000000101000000E17A14AE47E1434088855AD3BCF752C0'),   
    (2,X'000000000101000000E9FF1F9EBEF752C0440008AA48E24340');

No results are returned, when I would expect the second row to be returned, since that's the values I used in the query. One thing I checked is if I switched the values of longitude and latitude, but in both cases nothing is returned. When I ran explain on the query it says "Impossible WHERE noticed after reading const tables". I'm using MySQL 5.1.39.

Since the export is in some encoding or something for being a point here's a screenshot. Ignore the extra columns.

screenshot

edit: I ended up switching to Postgresql. A similar query is shorter and it works.

Andy · Accepted Answer · 2014-03-29T01:32:28.163

I used Postgresql for this instead it was much easier.

My query that I used for finding nearby users is:

$sql="SELECT *, st_astext(location_geometry) as latlon FROM users where id != ? and age between ? and ? $gender_sql $interested_in_sql and id not in (select to_id from rated where from_id = ?) ORDER BY location_geometry <->" ." st_setsrid(st_makepoint(?,?),4326) LIMIT 10";

This uses the <-> operator which in this case sorts longitude lattidude columns. You can ignore $gender_sql and $interested_in_sql as that is app specific.

The two parameters in st_makepoint are latitude and longitude.

This uses the PostGIS extension. I used Bitnami to install the server software and that extension was enabled by default.

Finding nearest latitude longitude points query in MySQL not working

1 Answers1