开发者

Selecting Unique records

I'm working on a Geo/Spatial search where I'm looking for nearby points. I have this Haversine query being run against a table:

SELECT
 uid, adrLat, adrLng,
 round(3956 * 2 * ASIN(SQRT(POWER(SIN((39.97780609 - abs(adrLat)) * pi() / 180 / 2), 2) + COS(39.97780609 * pi() / 180) * COS(abs(adrLat) * pi() / 180) * POWER(SIN((-105.25861359 - adrLng) * pi() / 180 / 2), 2))), 2) AS distance
FROM dataPoints
WHERE adrLng BETWEEN -105.2680699902 AND -105.2491571898
AND adrLat BETWEEN 39.970559713188 AND 39.985052466812
HAVING distance <= 0.30 and distance > 0.00
ORDER BY distance;

This would give me a result much like this:

+-----+-------------+---------------+----------+
| uid | adrLat      | adrLng        | distance |
+-----+-------------+---------------+----------+
| 191 | 39.97764587 | -105.25627136 |     0.12 |
| 520 | 39.97746658 | -105.25627136 |     0.13 |
| 265 | 39.97560120 | -105.25814056 |     0.15 |
| 266 | 39.97560120 | -105.25814056 |     0.15 |
| 274 | 39.97710037 | -105.25589752 |     0.15 |
|  98 | 39.97764969 | -105.26172638 |     0.17 |
| 576 | 39.97967911 | -105.25613403 |     0.18 |
| 575 | 39.97967911 开发者_开发问答| -105.25613403 |     0.18 |
| 469 | 39.97895813 | -105.25386810 |     0.26 |
| 470 | 39.97895813 | -105.25386810 |     0.26 |
|   1 | 39.98003006 | -105.25471497 |     0.26 |
| 383 | 39.97621155 | -105.26350403 |     0.28 |
| 431 | 39.97459793 | -105.25507355 |     0.29 |
| 430 | 39.97459793 | -105.25507355 |     0.29 |
| 429 | 39.97459793 | -105.25507355 |     0.29 |
| 428 | 39.97459793 | -105.25507355 |     0.29 |
+-----+-------------+---------------+----------+

However, as you can probably tell, some records are duplicated in the table (that's the way the data is provided to me, and I have to retain it that way.) 265:266, 576:575, 469:470, and 431-428 are all duplicates.

Is there a way to modify the query to pick unique records only? It looks like I have to match adrLat and adrLng to filter duplicates out, but I'm not sure if I can do it all within the same query, or if I have to do some post processing on the result.


SELECT  adrLat, adrLng,
        round(3956 * 2 * ASIN(SQRT(POWER(SIN((39.97780609 - abs(adrLat)) * pi() / 180 / 2), 2) + COS(39.97780609 * pi() / 180) * COS(abs(adrLat) * pi() / 180) * POWER(SIN((-105.25861359 - adrLng) * pi() / 180 / 2), 2))), 2) AS distance
FROM    mytable
WHERE   adrLng BETWEEN -105.2680699902 AND -105.2491571898
        AND adrLat BETWEEN 39.970559713188 AND 39.985052466812
GROUP BY
        adrLat, adrLng
HAVING  distance <= 0.30 
        AND distance > 0.00
ORDER BY
        distance


SELECT DISTINCT  colum_name FROM table

The SELECT keyword allows us to grab all information from a column (or columns) on a table. This, of course, necessarily mean that there will be redundancies. What if we only want to select each DISTINCT element? This is easy to accomplish in SQL. All we need to do is to add DISTINCT after SELECT. The syntax is as follows:


I still need all four columns returned

You've already got the unique data there (e.g. uid 576 and 575 return the same coordinates - but the uid is obviously different).

Your definition of 'unique' is obviously different from ours - can you provide an example of wht you expect to see?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜