PostGis地理空间查询比自定义python脚本慢

Question

我在PostGis中很新，我正在使用它来执行地理空间查询，但它在返回所需结果时似乎很慢。

在此之前，我使用了一个python脚本，该脚本通常在大约5秒钟内返回结果（搜索120万个元素）。

为了更快地得到这个结果，我将问题转移到postgis上，但是，正如我之前所写的，同样的工作需要20秒以上的时间。

更准确地说，每个元素由一个点（lat lon）和一个字符串（点的标签）组成

我正在使用一个停靠的postgis(https://hub.docker.com/r/mdillon/postgis)在我的I7 16 gb内存上（ubuntu 18.04）

我用以下方式创建了数据库：

CREATE DATABASE demo;
\c demo
create extension postgis;
CREATE TABLE mypoints ( id serial primary key, name varchar(50), the_geom geometry(POINT,4326) );

并通过python脚本以这种方式插入点（1,2M）

INSERT INTO cities (the_geom, name) VALUES (ST_GeomFromText('POINT(-3.782 40.4351)',4326), 'point_label');

我使用的查询是：

select name from cities where ST_Distance_Sphere(the_geom,ST_GeomFromText('POINT(-3.713 40.4321)',4326))<500;

我做错什么了吗？我的python代码怎么可能比针对地理空间问题优化的查询更快？

Answer 1

您没有充分利用PostGis，因为您还没有使用空间索引。

要在表中创建索引：

create index my_index_points_gist on mypoints using gist(the_geom);

然后在您的表上运行cluster和analyze：

cluster mypoints using my_index_points_gist;
analyze mypoints;

我看到您使用的是球面距离，如果您使用的是地理类型，则会更好：

CREATE TABLE mypoints ( id serial primary key, name varchar(50), geog geography );

以通常的方式插入数据，向Geograhy类型添加强制转换：

INSERT INTO cities (geog, name) VALUES (ST_GeomFromText('POINT(-3.782 40.4351)',4326)::geography, 'point_label');

或者，只需添加一个额外的地理柱：

alter table mypoints add column geog::geography;
update table mypoints set geog = the_geom::geography;

创建索引，但这次使用geog

create index my_index_points_gist_geog on mypoints using gist(geog);
cluster mypoints using my_index_points_gist_geog;
analyze mypoints;

对于查询，您可以使用：

select name from cities
where ST_Distance(geog,ST_GeomFromText('POINT(-3.713 40.4321)',4326)::geog)<500;

或者更好：

select name from cities
where ST_DWITHIN(geog,ST_GeomFromText('POINT(-3.713 40.4321)',4326)::geog,500);

供参考：postgis地理类型