gis.md

# 邻近查找

我们有一个简化的表结构如下：

```postgresql
create table city
(
    id   serial8,
    city text,
    poi  point
);
create index idx_tbl_point on city using gist (poi) with (buffering = on);
```

要查找给定的点位于哪个城市，或者离哪个城市最近，应该是下列哪一项？

## 答案

```postgresql
select city, poi <-> point($1, $2) dist
from city
where poi <-> point($1, $2) < 100
order by poi <-> point($1, $2)
limit 1;
```

## 选项

### A

```postgresql
select city, poi <-> point($1, $2) dist
from city
where poi <-> point($1, $2) < 100
order by poi <-> point($1, $2) desc
limit 1;
```

### B

```postgresql
select *, poi <-> point($1, $2) dist
from city
where poi <-> point($1, $2) < 100
limit 1;
```

### C

```postgresql
select *, poi - point($1, $2) dist
from city
where poi - point($1, $2) < 100
order by poi - point($1, $2)
limit 1;
```

### D

```postgresql
select *, abs(poi - point($1, $2)) dist
from city
where abs(poi - point($1, $2)) < 100
order by abs(poi - point($1, $2))
limit 1;
```