使用函数计算gps位置和postgis地理值之间的距离?

问题描述

我有一个用户的经纬度值.我在db中保存了各个位置的地址.我想根据半径值找到最近的优惠.我正在使用 postgis.

I have lat and long values for a user. I have addresses of various locations saved in db. I want to find the nearest offers based on a radius value. I am using postgis.

目前我的位置列是地理类型.

Currently my location column is of type geography.

1. 我应该将数据输入位置"的格式是什么?当我只有纬度和经度时，列地理类型?我无法使用 postgis 函数，因为我正在使用 graphql 进行数据插入.

1. What is the format in which I should enter the data into "location" column geography type when I have only latitude and longitude? I cannot use postgis functions since I am using graphql to do data insert.

我一直在努力为此编写一个函数.这是我想出来的.有没有更好的方法来做到这一点?

I have been tearing my hair apart trying to write a function for this purpose. This is what I came up with.Is there a better way to do this?

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

1. 当我运行这个函数时，所有的查询结果列都合并在一起了.如何在查询结果中将其拆分为不同的列?

我真的很感激在这件事上的任何帮助！

I would really appreciate any help in this matter!

推荐答案

Hasura 在其 GraphQL 架构中内置了对 PostGIS 的支持.由于您的字段已经属于 geography 类型，因此您不需要该函数.

Hasura has built in support for PostGIS in their GraphQL schema. Since your field is already of type geography, you don't need the function.

在 Hasura 中，您需要创建 offers 和 address 表之间的关系，GraphQL 查询应该类似于:

In Hasura you need to create the relationship between the offers and address tables, and the GraphQL query should be something like:

query nearby_offers($point: geography!) {
  offers(where: {address: {location: {_st_d_within: {distance: 200000, from: $point}}}}) {
    id
    offer_fields
    address {
      location
    }
  }
}

Hasura 中的地理类型期望值采用 GEOJSON 格式.您的变量 $point 应该如下所示:

Geography type in Hasura expects the value to be in GEOJSON format. Here is how your variable $point should look like:

{
  "point": {
    "type" : "Point",
    "coordinates": [longitude, latitude]
  }
}

插入值(变异)时需要相同类型的变量(GEOJSON).

The same type of variable (GEOJSON) is expected when inserting values (mutation).

这篇关于使用函数计算gps位置和postgis地理值之间的距离?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！