PostGIS地理字段插入格式、附近查询函数优化及结果列拆分问题

1. 向geography字段插入经纬度的格式（GraphQL场景） #

因为你用GraphQL没办法直接调用PostGIS函数，所以可以直接用WKT（Well-Known Text）格式来传递地理坐标，这里要注意一个关键细节：PostGIS里的坐标顺序是「经度在前，纬度在后」，和日常习惯的lat, lon相反。

举个例子，如果你要插入纬度39.9075、经度116.397222的地点，直接传递这个字符串即可：

POINT(116.397222 39.9075)

如果需要明确指定空间参考系（比如常用的WGS84，SRID=4326），可以用EWKT格式：

SRID=4326;POINT(116.397222 39.9075)

直接把这个字符串赋值给location字段，PostgreSQL会自动解析为geography类型（前提是你的location字段定义是geography(Point, 4326)这类带SRID的类型）。

2. 优化你的查询函数 #

先指出当前函数里的几个小问题，再给出优化版本：

• 坐标顺序错误：ST_MakePoint(lat, lon)搞反了，PostGIS的ST_MakePoint是(x, y)，对应经度、纬度；
• 返回类型不对：如果要返回多个优惠记录，应该用SETOF "Offers"而不是"Offers"，否则只能返回一行；
• 不必要的类型转换：A.location已经是geography类型，不需要再::Geography转换；
• 稳定性标记：把VOLATILE改成STABLE，因为相同输入参数会返回相同结果，PostgreSQL可以做查询优化；

另外，一定要给Address.location字段加空间索引，否则大表查询会很慢：

CREATE INDEX idx_address_location ON public.Address USING GIST(location);

优化后的函数：

CREATE OR REPLACE FUNCTION public.getNearByOffers(
    lat double precision, 
    lon double precision, 
    radius integer
) RETURNS SETOF public."Offers" 
LANGUAGE 'sql' 
STABLE 
COST 100 
AS $BODY$
SELECT O.* 
FROM public."Offers" O 
JOIN public."Address" A ON A.id = O."addressId" 
WHERE ST_DWithin(
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography, 
    A.location, 
    radius
);
$BODY$;

3. 拆分查询结果的列 #

你说的“所有列合并在一起”，应该是调用函数时用了SELECT getNearByOffers(...)的方式，这种写法会把每一行返回成一个复合类型，看起来是合并的。

解决方法很简单，用SELECT * FROM getNearByOffers(...)来调用函数，这样PostgreSQL会自动把Offers表的所有字段拆分开显示。

如果需要单独指定某些列，也可以这样写：

SELECT 
    offer.id, 
    offer.title, 
    offer.discount 
FROM getNearByOffers(39.9075, 116.397222, 1000) AS offer;

内容的提问来源于stack exchange，提问作者regShank