Geo-coordinate calculation

/* Select records by radius (1000 meters) */
SELECT * from Pointlocation where Earth_box (Ll_to_earth (40.057031,116.307852), 4000) @> Ll_to_earth ( Pointlocation.lat, Pointlocation.lon);

/* The distance between two points is awarded */
SELECT earth_distance (Ll_to_earth (40.057031,116.307852), Ll_to_earth (Pointlocation.lat, Pointlocation.lon)) from Pointlocation where id=1112

2. /*
4. * PostgreSQL's earthdistance study notes
6. * Author:wusuopubupt
8. * date:2013-03-31
10. */
14. /* CREATE TABLE */
16. CREATE TABLE Picture (
18. ID serial PRIMARY KEY,
20. P_uid char () not NULL,
22. P_key char (at) not NULL,
24. Lat real not null,
26. LNG real not NULL,
28. the up int is not NULL,
30. the down int is not NULL,
32. IP varchar (+) DEFAULT NULL,
34. Address varchar (DEFAULT) NULL
36. );
40. /* Insert Record */
42. INSERT into Picture (P_uid, P_key, lat, LNG, up, down, IP, address)
44. VALUES (' AAAABBBBCCCC ', ' 2014032008164023279.png ', 40.043945, 116.413668, 0, 0, ' , ');
48. /* Insert Record */
50. INSERT into Picture (P_uid, P_key, lat, LNG, up, down, IP, address)
52. VALUES (' xxxxccccmmmm ', ' 2014032008164023111.png ', 40.067183, 116.415230, 0, 0, ' , ');
56. /* Select Record */
58. SELECT * from Picture ;
62. /* Update record */
64. UPDATE picture SET address=' Lishuiqiao ' WHERE id=1;
66. UPDATE picture SET address=' Tiantongyuan ' WHERE id=2;
70. /* Create an index on the latitude column */
72. CREATE INDEX ll_idx on the picture USING gist (Ll_to_earth (lat, LNG));
76. /* Select records by radius (1000 meters) */
78. SELECT * from the picture where Earth_box (Ll_to_earth (40.059286,116.418773),,) @> Ll_to_earth (Picture.lat,   PICTURE.LNG);
82. /* Select the distance from the current user */
84. SELECT picture.id, Earth_distance (Ll_to_earth (Picture.lat, PICTURE.LNG), Ll_to_earth (40.059286,116.418773))
86. As Dis from picture
88. ORDER by dis ASC;
92. /*
94. * The following is a tutorial on the Web
96. * Address: Http://www.cse.iitb.ac. in/dbms/data/courses/cs631/postgresql-resources/postgresql-9.2.4/contrib/earthdistance/expected/ Earthdistance. out
98. */

Geo-coordinate calculation