PostGIS：找到距离给定点最近的几何体

Question

假设我有许多几何图形在我的数据库中。我想在PostgreSQL内创建一个函数，使得我可以传入经纬度，并返回给定半径内（按线性和道路距离）最接近的几何图形。

我是一个新手，所以任何建议都将不胜感激。

我正在运行以下版本：

PostgreSQL: 9.2

PostGIS: 2.0

以下是架构：

-- ----------------------------
--  Table structure for "cities-usa"
-- ----------------------------
DROP TABLE IF EXISTS "cities-usa";
CREATE TABLE "cities-usa" (
"gid" int4 NOT NULL DEFAULT nextval('"cities-usa_gid_seq"'::regclass),
"st_fips" varchar(4),
"sfips" varchar(2),
"county_fip" varchar(4),
"cfips" varchar(4),
"pl_fips" varchar(7),
"id" varchar(20),
"name" varchar(39),
"elevation" varchar(60),
"pop_1990" numeric,
"population" varchar(30),
"st" varchar(6),
"state" varchar(16),
"warngenlev" varchar(16),
"warngentyp" varchar(16),
"watch_warn" varchar(3),
"zwatch_war" float8,
"prog_disc" int4,
"zprog_disc" float8,
"comboflag" float8,
"land_water" varchar(16),
"recnum" float8,
"lon" float8,
"lat" float8,
"geom" "geometry"
)
WITH (OIDS=FALSE);
ALTER TABLE "cities-usa" OWNER TO "postgres";

-- ----------------------------
--  Primary key structure for table "cities-usa"
-- ----------------------------
ALTER TABLE "cities-usa" ADD CONSTRAINT "cities-usa_pkey" PRIMARY KEY ("gid") NOT DEFERRABLE INITIALLY IMMEDIATE;

- John Smith

---

PostgreSQL和PostGIS的版本？您的数据库架构有哪些信息？请提供详细信息。 - Craig Ringer

对于模式，我有一个美国城市的表格，包括gid（int4），st_fips（varchar（4），county_fip（varchar（4）cfips varchar（4），pl_fips varchar（7），id varchar（20），name varchar（39），elevation varchar（60），pop_1990 numeric，population varchar（30），st varchar（6），state varchar（16），warngenlev varchar（16），warngentyp varchar（16），watch_warn varchar（3），zwatch_war float8，prog_disc int4，zprog_disc float8，comboflag float8，land_water varchar（16），recnum float8，lon float8，lat float8，geom geometry。 - John Smith

@craig-ringer，我想要创建一个函数，可以像这样调用它：WhatAmINear(lat, long, radius, num)，然后它会返回在给定半径内最接近的num个城市。例如，如果我以WhatAmINear(lat1, lon1, 100, 5)的方式调用它，它将返回距离我的位置最近的5个城市，这些城市位于100英里的半径范围内。如果在该范围内少于5个城市，则只返回这些城市。 - John Smith

你似乎没有读完我评论的第一部分。请提供版本信息。编辑你的问题并添加你在评论中添加的模式、版本和任何其他相关信息。 - Craig Ringer

谢谢，@craig-ringer。昨天我最初放置了版本，但保存时出现问题。我可能不小心删除了它们。版本和SQL已发布。感谢您的意见。 - John Smith

Answer 1

逻辑可以封装到一个函数中，但我建议使用查询来测试逻辑。你将遇到的困难是在线性距离（英里）与角度坐标（纬度和经度度数）之间的组合。此外，上面指定的几何类型没有 SRID 或几何类型。我猜它应该是 geometry(Point,4326)。

有一些策略可以尝试，比如使用自动将米用于诸如 ST_Distance 或 ST_DWithin 等函数的 geography 类型。下面的示例只是使用 ST_Distance_Sphere 并将英里转换为米，以帮助您入门。或者，如果您需要性能，则可以尝试使用 <-> 函数进行索引最近邻搜索。

以下是您可以尝试的内容：

CREATE OR REPLACE FUNCTION WhatAmINear(lat float8, lon float8,
                                       radius_mi float8, num int DEFAULT 10)
    RETURNS SETOF "cities-usa" AS
$body$
SELECT *
FROM "cities-usa"
WHERE ST_Distance_Sphere(geom, ST_MakePoint(lon, lat)) <= radius_mi * 1609.34
LIMIT num;
$body$
LANGUAGE sql VOLATILE;

然后：

SELECT WhatAmINear(44.9, -93.1, 100);

- Mike T