xHwYiJYN

1/* Enable postgis */
2CREATE EXTENSION  IF NOT EXISTS postgis;
3
4CREATE TABLE ex1 (
5  id VARCHAR(10) NOT NULL,
6  loc geography(POINT, 4326)
7);
8
9CREATE INDEX  ex1_loc_gix ON  ex1  USING GIST (loc);
10
11INSERT INTO ex1 (id, loc) VALUES ('1', st_geographyfromtext('point(59.3497989 18.0706644)'));
12INSERT INTO ex1 (id, loc) VALUES ('2', st_geographyfromtext('point(59.3463858 18.0659866)'));
13INSERT INTO ex1 (id, loc) VALUES ('3', st_geographyfromtext('point(59.3324452 18.1188154)'));
14
15SELECT id, st_astext(loc), st_distance(loc, st_geographyfromtext('point(59.3497989 18.0706644)')) FROM ex1;
16
17SELECT id, st_astext(loc), st_distance(loc, st_geographyfromtext('point(59.3497989 18.0706644)')) FROM ex1
18  WHERE st_distance(loc, st_geographyfromtext('point(59.3497989 18.0706644)')) <= 2000;
19
20SELECT id, st_astext(loc), st_distance(loc, st_geographyfromtext('point(59.3497989 18.0706644)')) FROM ex1
21  WHERE st_dwithin(loc, st_geographyfromtext('point(59.3497989 18.0706644)'), 2000);
22
23DELETE FROM ex1;