一个 SQL 查询，将列出所有路线、坐标落下或距离该坐标最近的路线

问题描述

我在 PostgreSQL 中有一个名为路由"的 PostgreSQL 表，其结构和数据如下.我想要一个查询，我将在其中提供坐标(纬度和经度)，它将返回最接近起点到终点的路线.例如， (40.690503, -73.840581) 落在大西洋航线上，这意味着查询将返回第一行

I have a PostgreSQL table called "routes" in PostgreSQL with below structure and data. I want a query where i will provide the coordinate(Latitude and Longitude) and it will return a route closest to the startpoint to endpoint of the route.for example, (40.690503, -73.840581) falls along Atlantic Route which means the query will return the first rows

注意:路由是起点和终点之间的路径或方式.

NB: A route is a path or way between startpoint and endpoint.

以下是表结构和示例数据

Below is the table structure and Sample Data

#Table Structure
CREATE TABLE public.route
(
  name text NOT NULL,
  startpoint point NOT NULL,
  endpoint point NOT NULL,
  id integer NOT NULL DEFAULT nextval('route_id_seq'::regclass),
  CONSTRAINT route_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

#Table Data
INSERT INTO public.route (name, startpoint, endpoint, id) VALUES ('Atlantic',     (-73.848838,40.688299), (-73.824869,40.694831), 1);
INSERT INTO public.route (name, startpoint, endpoint, id) VALUES ('Guy Brewer', (-73.7991,40.708257), (-73.78543,40.688334), 2);

推荐答案

在 postgis 中，LINESTRING 几何是这样的:

In postgis a LINESTRING geometry is something like this:

CREATE TABLE map.vzla_rto
(
  link_id bigint,
  geom geometry(LineString)
)

SELECT ST_ASTEXT(geom)
FROM map.vzla_rto;

"LINESTRING(-72.285868 10.291798,
            -72.285604 10.291983,
            -72.285272 10.292124,
            -72.28512 10.292168,
            -72.284727 10.292228)"

这里我使用了 postgis 操作符来找到离该点最近的道路对象.然后使用 ST_Distance 函数知道到最近的道路的距离是多少，然后选择最近的一条.

Here I use the <-> postgis operator to find the closest road object to the point. Then use ST_Distance function to know what is the distance to that closest road and select the closest one.

CREATE OR REPLACE FUNCTION map.get_near_link(
    x numeric,
    y numeric)
  RETURNS map.get_near_link AS
$BODY$
DECLARE
    strPoint text;
    sRow map.get_near_link; -- custom type to return all link fields
  BEGIN
    strPoint = 'POINT('|| X || ' ' || Y || ')';

    with index_query as (
        SELECT Link_ID,
               TRUNC(ST_Distance(ST_GeomFromText(strPoint,4326), geom  )*100000)::integer as distance,
               geom
        FROM map.vzla_seg S
        ORDER BY
            geom <-> ST_GeomFromText(strPoint, 4326)
        LIMIT 101
    )
    SELECT i.Link_ID, i.Distance, i.geom into sRow
    FROM
        index_query i
    ORDER BY
        distance limit 1;

    RAISE DEBUG 'GetLink distance % ', sRow.distance;
    if sRow.distance > 50 then
        sRow.link_id = -1;
    end if;

    RETURN sRow;
  END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;

这篇关于一个 SQL 查询，将列出所有路线、坐标落下或距离该坐标最近的路线的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！