My MySQL table contains the following information,
ID User City HomeLatitude HomeLongitude
1 Egon Spengler New York 19.123456 23.43546
2 Mac Taylor New York 19.12343588 23.43546
3 Sarah Connor New York 19.128376 34.35354
4 Jean-Luc Picard La Barre 11.345455 12.4356546
5 Ellen Ripley Nostromo 32.76865 78.345435
From this table I need to query unique HomeLatitude values with first two decimal point because the first 3 location are almost same, so i need only the first row from the three rows..
I used the below given query,
SELECT DISTINCT HomeLatitude, City FROM user;
But that will return all the rows from the table. Can anyone help me on this.
ROUND Function will give you the number of decimal places that you want to show.
SELECT DISTINCT ROUND(HomeLatitude,2), City FROM user GROUP BY ROUND(HomeLatitude,2);
Something like:
SELECT (DISTINCT ROUND(HomeLatitude, 1)) AS HomeLatitude, City FROM user;
While using a solution that is based on rounding a latitude or longitude may give you results, also be prepared for unexpected results. It is also possible for two places to have the exact same latitude but different longitudes and vice verce.
If you are doing any kind of spatial analysis, you should invest in mysql spatial extensions and a function like ST_WITHIN
Second best option is to use the haversine formula or similar to find places that are close to each other.