8000 add postgis test (#1167) · supabase/postgres@800e92b · GitHub
[go: up one dir, main page]
Skip to content

Commit 800e92b

Browse files
oliriceolirice
authored
add postgis test (#1167)
1 parent e00f528 commit 800e92b

File tree

2 files changed

+111
-0
lines changed

2 files changed

+111
-0
lines changed

‎nix/tests/expected/postgis.out

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,59 @@
1+
create schema v;
2+
-- create a table to store geographic points
3+
create table v.places (
4+
id serial primary key,
5+
name text,
6+
geom geometry(point, 4326) -- using WGS 84 coordinate system
7+
);
8+
-- insert some sample geographic points into the places table
9+
insert into v.places (name, geom)
10+
values
11+
('place_a', st_setsrid(st_makepoint(-73.9857, 40.7484), 4326)), -- latitude and longitude for a location
12+
('place_b', st_setsrid(st_makepoint(-74.0060, 40.7128), 4326)), -- another location
13+
('place_c', st_setsrid(st_makepoint(-73.9687, 40.7851), 4326)); -- yet another location
14+
-- calculate the distance between two points (in meters)
15+
select
16+
a.name as place_a,
17+
b.name as place_b,
18+
st_distance(a.geom::geography, b.geom::geography) as distance_meters
19+
from
20+
v.places a,
21+
v.places b
22+
where
23+
a.name = 'place_a'
24+
and b.name = 'place_b';
25+
place_a | place_b | distance_meters
26+
---------+---------+-----------------
27+
place_a | place_b | 4309.25283351
28+
(1 row)
29+
30+
-- find all places within a 5km radius of 'place_a'
31+
select
32+
name,
33+
st_distance(
34+
geom::geography,
35+
(
36+
select
37+
geom
38+
from
39+
v.places
40+
where
41+
name = 'place_a'
42+
)::geography) as distance_meters
43+
from
44+
v.places
45+
where
46+
st_dwithin(
47+
geom::geography,
48+
(select geom from v.places where name = 'place_a')::geography,
49+
5000
50+
)
51+
and name != 'place_a';
52+
name | distance_meters
53+
---------+-----------------
54+
place_b | 4309.25283351
55+
place_c | 4320.8765634
56+
(2 rows)
57+
58+
drop schema v cascade;
59+
NOTICE: drop cascades to table v.places

‎nix/tests/sql/postgis.sql

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,52 @@
1+
create schema v;
2+
3+
-- create a table to store geographic points
4+
create table v.places (
5+
id serial primary key,
6+
name text,
7+
geom geometry(point, 4326) -- using WGS 84 coordinate system
8+
);
9+
10+
-- insert some sample geographic points into the places table
11+
insert into v.places (name, geom)
12+
values
13+
('place_a', st_setsrid(st_makepoint(-73.9857, 40.7484), 4326)), -- latitude and longitude for a location
14+
('place_b', st_setsrid(st_makepoint(-74.0060, 40.7128), 4326)), -- another location
15+
('place_c', st_setsrid(st_makepoint(-73.9687, 40.7851), 4326)); -- yet another location
16+
17+
-- calculate the distance between two points (in meters)
18+
select
19+
a.name as place_a,
20+
b.name as place_b,
21+
st_distance(a.geom::geography, b.geom::geography) as distance_meters
22+
from
23+
v.places a,
24+
v.places b
25+
where
26+
a.name = 'place_a'
27+
and b.name = 'place_b';
28+
29+
-- find all places within a 5km radius of 'place_a'
30+
select
31+
name,
32+
st_distance(
33+
geom::geography,
34+
(
35+
select
36+
geom
37+
from
38+
v.places
39+
where
40+
name = 'place_a'
41+
)::geography) as distance_meters
42+
from
43+
v.places
44+
where
45+
st_dwithin(
46+
geom::geography,
47+
(select geom from v.places where name = 'place_a')::geography,
48+
5000
49+
)
50+
and name != 'place_a';
51+
52+
drop schema v cascade;

0 commit comments

Comments
 (0)
0