-
Notifications
You must be signed in to change notification settings - Fork 0
/
2d.sql
58 lines (50 loc) · 1.41 KB
/
2d.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
drop table if exists x_coord;
drop table if exists y_coord;
create table x_coord ( val integer );
create table y_coord ( val integer );
insert into x_coord values ( 1 );
insert into x_coord values ( 4 );
insert into x_coord values ( 8 );
insert into x_coord values ( 5 );
insert into y_coord values ( 10 );
insert into y_coord values ( 13 );
insert into y_coord values ( 19 );
insert into y_coord values ( 12 );
insert into y_coord values ( 16 );
drop table if exists grid_coord;
select
x.val x_from,
y.val y_from,
lead(x.val) over (partition by y.val order by x.val) x_to,
lead(y.val) over (partition by x.val order by y.val) y_to
into
grid_coord
from
x_coord x cross join
y_coord y;
delete from
grid_coord
where
x_to is null or
y_to is null;
select *
from
grid_coord
order by
x_from,
y_from;
--
-- x_from y_from x_to y_to
-- ----------- ----------- ----------- -----------
-- 1 10 4 12
-- 1 12 4 13
-- 1 13 4 16
-- 1 16 4 19
-- 4 10 5 12
-- 4 12 5 13
-- 4 13 5 16
-- 4 16 5 19
-- 5 10 8 12
-- 5 12 8 13
-- 5 13 8 16
-- 5 16 8 19