安装
1
| CREATE EXTENSION postgis
|
添加列
1
2
| ALTER TABLE your_table ADD COLUMN geom geometry(Point, 4326);
UPDATE your_table SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
|
半径查找
1
2
3
4
5
6
7
8
9
10
11
12
13
| SELECT lat,
lon,
location,
cnt
FROM ggg
WHERE 1 = 1
AND cnt > 1
AND ST_DWithin(
ggg.geom,
ST_MakePoint(42.9764, 47.5024)::GEOGRAPHY,
200000
);
|
参考:
https://live.osgeo.org/en/quickstart/postgis_quickstart.html
https://gis.stackexchange.com/questions/145007/creating-geometry-from-lat-lon-in-table-using-postgis