PostGIS: 地理空间查询

PostGIS 是一个 Postgres 扩展，允许您在 Postgres 中处理地理空间数据。您可以按地理位置排序数据、获取特定地理边界内的数据，以及实现更多功能。

概述

虽然您可以将简单的经纬度坐标存储为一组小数，但在查询大规模数据集时这种方式扩展性不佳。PostGIS 提供了特殊的数据类型，这些类型不仅高效，而且可索引以实现高度可扩展性。

PostGIS 提供的额外数据类型包括 Point（点）、Polygon（多边形）、LineString（线串）等，用于表示不同类型的地理数据。本指南将主要介绍如何操作表示单一经纬度坐标的 Point 类型。如果您想深入了解，可以在 PostGIS 文档的数据管理部分 学习更多关于不同数据类型的知识。

启用扩展

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

示例

现在我们已经准备好开始使用 PostGIS，让我们创建一个表并看看如何利用 PostGIS 处理一些典型用例。假设我们正在创建一个简单的餐厅搜索应用。

首先创建我们的表。每一行代表一个餐厅，其位置以Point类型存储在location列中。

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

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

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

插入数据

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

请注意经纬度的传递顺序。经度在前，因为经度代表位置的x轴坐标。另一个需要注意的地方是，当从客户端库插入数据时，两个值之间没有逗号，只有一个空格分隔。

此时，如果您进入Supabase仪表板查看数据，会发现location列的值看起来像这样：

1
0101000020E6100000A4DFBE0E9C91614044FAEDEBC0494240

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

按距离排序

按从近到远排序数据集（有时称为最近邻排序）是地理查询中非常常见的用例。PostGIS 可以使用 <-> 运算符来处理这种排序。<-> 运算符返回两个几何图形之间的二维距离，当在 order by 子句中使用时会利用空间索引。您可以通过传递当前位置作为参数，创建以下数据库函数来按距离从近到远排序餐厅。

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

在从客户端调用此函数之前，我们需要授予对 gis 模式的访问权限：

1
grant usage on schema gis to anon, authenticated;

现在您可以使用 rpc() 从客户端调用此函数，如下所示：

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

查找边界框内的所有数据点

当您开发基于地图的应用程序时，用户滚动地图时可能需要加载当前地图边界框内的数据。PostGIS 只需提供左下角和右上角坐标，就能返回位于边界框内的数据行。让我们看看这个函数的具体实现：

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

在 where 语句中使用的 && 运算符会返回两个几何图形的边界框是否相交的布尔值。我们基本上是从两个点创建一个边界框，并查找位于该边界框内的点。我们还使用了几个不同的 PostGIS 函数：

• ST_MakeBox2D：从两个点创建二维边界框
• ST_SetSRID：设置 SRID（空间参考系统标识符），4326 是标准的经纬度坐标系

您可以通过客户端使用 rpc() 调用此函数：

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

故障排除

从PostGIS 2.3或更高版本开始，PostGIS扩展不再支持从一个schema迁移到另一个schema。如果您出于任何原因需要迁移(例如出于安全考虑从public schema迁移到extensions schema)，通常您会执行ALTER EXTENSION来迁移schema。但现在您需要按照以下步骤操作：

1. 备份数据库以防止数据丢失 - 您可以通过CLI或Postgres备份工具如pg_dumpall来完成

2. 删除所有创建的依赖项和PostGIS扩展 - DROP EXTENSION postgis CASCADE;

3. 在新schema中启用PostGIS扩展 - CREATE EXTENSION postgis SCHEMA extensions;

4. 如有必要，使用您选择的工具从步骤1的备份中恢复被删除的数据

相关资源

• 官方PostGIS文档