9rH5cdze

1SQL Uniform distribution of points
2id      lat           lon      
3-----------------------
41     39.4600    110.3523410
52     39.4601    110.3523410
63     39.4605    110.3523410
74     39.4609    110.3523410
8	
9SELECT DISTINCT ROUND(lat*250, 0), ROUND(long*250, 0) FROM sometable;
10	
11SELECT AVERAGE(lat), AVERAGE(long)
12FROM sometable
13GROUP BY ROUND(lat*250, 0), ROUND(long*250.0, 0);
14	
15SELECT * FROM mytable a
16WHERE NOT EXISTS ( SELECT *
17    FROM mytable b
18    WHERE ABS (a.long - b.long) < 0.01
19      AND ABS (a.lat - b.lat) < 0.02
20      AND b.id < a.id
21    );
22	
23DROP TABLE tmp.mytable;
24
25CREATE TABLE tmp.mytable
26    ( id INTEGER NOT NULL PRIMARY KEY
27    , zlat REAL NOT NULL
28    , zlong  REAL NOT NULL
29    );
30INSERT INTO tmp.mytable (id, zlat, zlong)
31    SELECT generate_series(1,10000), 0.0, 0.0
32    ;
33
34SET search_path=tmp;
35UPDATE tmp.mytable SET zlat = 39.0 + random() ;
36
37UPDATE tmp.mytable SET zlong = 110.0 + random() ;
38
39CREATE INDEX latlong ON tmp.mytable (zlat, zlong);
40
41VACUUM ANALYZE tmp.mytable;
42/***/
43SET search_path=tmp;
44
45
46EXPLAIN ANALYZE
47SELECT * FROM mytable a
48WHERE NOT EXISTS ( SELECT *
49    FROM mytable b
50    WHERE 1=1
51      AND ABS (a.zlong - b.zlong) < 0.01
52      AND ABS (a.zlat - b.zlat) < 0.02
53      AND b.id < a.id
54    );
55
56EXPLAIN ANALYZE
57SELECT * FROM mytable a
58WHERE NOT EXISTS ( SELECT *
59    FROM mytable b
60    WHERE 1=1
61      AND a.zlong - b.zlong < 0.01  AND b.zlong - a.zlong < 0.01
62      AND a.zlat - b.zlat < 0.02  AND b.zlat - a.zlat < 0.02
63      AND b.id < a.id
64    );
65	
66---------------------------------------------
67 Nested Loop Anti Join  (cost=0.00..1448079.64 rows=9630 width=12) (actual time=0.151..3966.487 rows=1288 loops=1)
68   Join Filter: ((abs((a.zlong - b.zlong)) < 0.01::double precision) AND (abs((a.zlat - b.zlat)) < 0.02::double precision))
69   ->  Seq Scan on mytable a  (cost=0.00..263.00 rows=10000 width=12) (actual time=0.139..3.463 rows=10000 loops=1)
70   ->  Index Scan using mytable_pkey on mytable b  (cost=0.00..58.68 rows=3333 width=12) (actual time=0.005..0.173 rows=1084 loops=10000)
71         Index Cond: (b.id < a.id)
72 Total runtime: 3966.853 ms
73(6 rows)
74
75---------------------------------------------
76 Nested Loop Anti Join  (cost=0.00..1663497.55 rows=9959 width=12) (actual time=0.065..4210.616 rows=1288 loops=1)
77   Join Filter: (((a.zlong - b.zlong) < 0.01::double precision) AND ((b.zlong - a.zlong) < 0.01::double precision) AND ((a.zlat - b.zlat) < 0.02::double precision) AND ((b.zlat - a.zlat) < 0.02::double precision))
78   ->  Seq Scan on mytable a  (cost=0.00..263.00 rows=10000 width=12) (actual time=0.060..2.840 rows=10000 loops=1)
79   ->  Index Scan using mytable_pkey on mytable b  (cost=0.00..58.68 rows=3333 width=12) (actual time=0.005..0.173 rows=1084 loops=10000)
80         Index Cond: (b.id < a.id)
81 Total runtime: 4210.904 ms
82(6 rows)
83	
84foreach point1 in points
85    foreach point2 in points
86        dist = (point1 - point2).length()
87        if dist < epsilon: remove point2 from list
88	
89SELECT a.id, a.lon, a.lat
90FROM points a
91WHERE
92  NOT EXISTS(SELECT *
93    FROM points b
94    WHERE b.id < a.id
95    AND (a.lon - b.lon)**2 + (a.lat - b.lat)**2 < 0.00009)