PostGIS：地理查询

PostGIS 是一个 Postgres 扩展，它允许你在 Postgres 中处理地理数据。你可以按地理位置对数据进行排序，获取特定地理范围内的数据，还可以做更多操作。

概览 #

🌐 Overview

虽然你可以把经纬度地理坐标存储为一组小数，但当你尝试在大量数据中查询时，这种方式并不太好扩展。PostGIS 提供了高效且可索引的特殊数据类型，适合高扩展性。

🌐 While you may be able to store lat/long geographic coordinates as a set of decimals, it does not scale very well when you try to query through a large data set. PostGIS comes with special data types that are efficient, and indexable for high scalability.

PostGIS 提供的额外数据类型包括 Point、Polygon、LineString 等，用于表示不同类型的地理数据。在本指南中，我们主要关注如何与 Point 类型交互，它表示单一的经纬度集合。如果你想深入了解，可以在 PostGIS 文档的数据管理部分 学习更多关于不同数据类型的内容。

🌐 The additional data types that PostGIS provides include Point, Polygon, LineString, and many more to represent different types of geographical data. In this guide, we will mainly focus on how to interact with Point type, which represents a single set of latitude and longitude. If you are interested in digging deeper, you can learn more about different data types on the data management section of PostGIS docs.

启用扩展 #

🌐 Enable the extension

你可以通过在 Supabase 仪表板中启用 PostGIS 扩展来开始使用 PostGIS。

🌐 You can get started with PostGIS by enabling the PostGIS extension in your Supabase dashboard.

示例 #

🌐 Examples

要开始使用 PostGIS，可以先创建一个表，然后看看如何用 PostGIS 实现一些典型的用例。想象一下创建一个基本的餐厅搜索应用。

🌐 To get started with PostGIS, create a table and see to use PostGIS for some typical use cases. Imagine creating a basic restaurant-searching app.

创建表格。每一行代表一家餐厅，其位置存储在 location 列，类型为 Point。

🌐 Create the table. Each row represents a restaurant with its location stored in location column as a Point type.

1
create table if not exists public.restaurants (
2
	id int generated by default as identity primary key,
3
	name text not null,
4
	location extensions.geography(POINT) not null
5
);

然后我们可以在这个表的 location 列上设置一个空间索引。

🌐 We can then set a spatial index on the location column of this table.

1
create index restaurants_geo_index
2
  on public.restaurants
3
  using GIST (location);

正在插入数据 #

🌐 Inserting data

你可以通过 SQL 或我们的 API 插入地理数据。

🌐 You can insert geographical data through SQL or through our API.

注意传递纬度和经度的顺序。经度要放在前面，因为经度表示位置的x轴。另一件要注意的事是，当从客户端库插入数据时，这两个值之间没有逗号，只有一个空格。

🌐 Notice the order in which you pass the latitude and longitude. Longitude comes first, and is because longitude represents the x-axis of the location. Another thing to watch for is when inserting data from the client library, there is no comma between the two values, only a single space.

这时，如果你进入你的 Supabase 仪表板并查看数据，你会发现 location 列的值看起来大概是这样的。

🌐 At this point, if you go into your Supabase dashboard and look at the data, you will notice that the value of the location column looks something like this.

1
0101000020E6100000A4DFBE0E9C91614044FAEDEBC0494240

我们可以直接查询 restaurants 表，但它会以你上面看到的格式返回 location 列。 我们将创建 数据库函数，这样我们就可以使用 st_y() 和 st_x() 函数将其转换回纬度和经度的浮点值。

🌐 We can query the restaurants table directly, but it will return the location column in the format you see above. We will create database functions so that we can use the st_y() and st_x() function to convert it back to lat and long floating values.

按距离排序 #

🌐 Order by distance

将数据集按从近到远排序，有时叫做最近邻排序，在地理查询中是非常常见的用例。PostGIS 可以使用 <-> 操作符来处理。<-> 操作符返回两个几何体之间的二维距离，并且在 order by 语句中使用时会利用空间索引。你可以创建下面的数据库函数，通过传入当前位置信息作为参数来按距离将餐厅从近到远排序。

🌐 Sorting datasets from closest to farthest, sometimes called nearest-neighbor sort, is a very common use case in Geo-queries. PostGIS can handle it with the use of the <-> operator. <-> operator returns the two-dimensional distance between two geometries and uses the spatial index when used within order by clause. You can create the following database function to sort the restaurants from closest to farthest by passing the current locations as parameters.

1
create or replace function nearby_restaurants(lat float, long float)
2
returns table (id public.restaurants.id%TYPE, name public.restaurants.name%TYPE, lat float, long float, dist_meters float)
3
set search_path = ''
4
language sql
5
as $$
6
  select id, name, extensions.st_y(location::extensions.geometry) as lat, extensions.st_x(location::extensions.geometry) as long, extensions.st_distance(location, extensions.st_point(long, lat)::extensions.geography) as dist_meters
7
  from public.restaurants
8
  order by location operator(extensions.<->) extensions.st_point(long, lat)::extensions.geography;
9
$$;

现在你可以像这样从客户端使用 rpc() 调用这个函数：

🌐 Now you can call this function from your client using rpc() like this:

1
const { data, error } = await supabase.rpc('nearby_restaurants', {
2
  lat: 40.807313,
3
  long: -73.946713,
4
})

找到边界框内的所有数据点 #

🌐 Finding all data points within a bounding box

当你在开发一个基于地图的应用时，用户会在地图上滚动，你可能希望每次用户滚动时加载地图边界框内的数据。PostGIS 可以通过提供左下角和右上角的坐标来返回边界框内的行。函数看起来是这样的：

🌐 When you are working on a map-based application where the user scrolls through your map, you might want to load the data that lies within the bounding box of the map every time your users scroll. PostGIS can return the rows that are within the bounding box by supplying the bottom left and the top right coordinates. The function looks like this:

1
create or replace function restaurants_in_view(min_lat float, min_long float, max_lat float, max_long float)
2
returns table (id public.restaurants.id%TYPE, name public.restaurants.name%TYPE, lat float, long float)
3
set search_path to ''
4
language sql
5
as $$
6
	select id, name, extensions.st_y(location::extensions.geometry) as lat, extensions.st_x(location::extensions.geometry) as long
7
	from public.restaurants
8
	where location operator(extensions.&&) extensions.ST_SetSRID(extensions.ST_MakeBox2D(extensions.ST_Point(min_long, min_lat), extensions.ST_Point(max_long, max_lat)), 4326)
9
$$;

这里在 where 语句中使用的 && 操作符会返回一个布尔值，用来判断两个几何体的边界框是否相交。它会根据两个点创建一个边界框，并找出落在边界框内的点。它还使用了一些 PostGIS 函数：

🌐 The && operator used in the where statement here returns a boolean of whether the bounding box of the two geometries intersect or not. It creates a bounding box from the two points and finds those points that fall under the bounding box. It also uses a few PostGIS functions:

• ST_MakeBox2D：从两个点创建一个二维盒子。
• ST_SetSRID：设置 SRID，它是用来指定几何体使用哪种坐标系的标识符。4326 是标准的经纬度坐标系。

你可以像这样在客户端使用 rpc() 调用这个函数：

🌐 You can call this function from your client using rpc() like this:

1
const { data, error } = await supabase.rpc('restaurants_in_view', {
2
  min_lat: 40.807,
3
  min_long: -73.946,
4
  max_lat: 40.808,
5
  max_long: -73.945,
6
})

故障排除 #

🌐 Troubleshooting

从 PostGIS 2.3 或更新版本开始，PostGIS 扩展不再可以从一个 schema 移动到另一个 schema。如果你出于某种原因需要将它从一个 schema 移到另一个（例如出于安全原因从 public schema 移到 extensions schema），通常你会运行 ALTER EXTENSION 来重新定位 schema。不过，现在你需要做以下步骤：

🌐 As of PostGIS 2.3 or newer, the PostGIS extension is no longer relocatable from one schema to another. If you need to move it from one schema to another for any reason (e.g. from the public schema to the extensions schema for security reasons), you would normally run a ALTER EXTENSION to relocate the schema. However, you will now to do the following steps:

1. 备份你的数据库以防止数据丢失——你可以通过 CLI 或 Postgres 备份工具，比如 pg_dumpall 来操作
2. 删除你创建的所有依赖和 PostGIS 扩展 - DROP EXTENSION postgis CASCADE;
3. 在新模式 CREATE EXTENSION postgis SCHEMA extensions; 中启用 PostGIS 扩展
4. 如果需要，可以使用你选择的工具从第1步的备份中恢复丢失的数据。

或者，你可以联系Supabase 支持团队，并请他们在你的实例上运行以下 SQL：

🌐 Alternatively, you can contact the Supabase Support Team and ask them to run the following SQL on your instance:

1
BEGIN;
2
	UPDATE pg_extension
3
	  SET extrelocatable = true
4
	WHERE extname = 'postgis';
5
6
	ALTER EXTENSION postgis
7
	  SET SCHEMA extensions;
8
9
	ALTER EXTENSION postgis
10
	  UPDATE TO "<POSTGIS_VERSION>next";
11
12
	ALTER EXTENSION postgis UPDATE;
13
14
	UPDATE pg_extension
15
	  SET extrelocatable = false
16
	WHERE extname = 'postgis';
17
COMMIT;

资源 #

🌐 Resources

• 官方 PostGIS 文档