在香草Postgres查询中使用PostGIS中的边界框 [英] Use bounding box from PostGIS in vanilla Postgres query

问题描述

我的数据库结构是普通Postgres:

I have a db structure that is vanilla Postgres:

CREATE TABLE IF NOT EXISTS locations (
  name text NOT NULL,
  lat double precision NOT NULL,
  lng double precision NOT NULL,
);
CREATE INDEX ON locations(lat,lng);

当我想在边界框中计算左下角和右上角的所有位置时，请使用以下查询:

When I want to calculate all locations in a bounding box where I have the lower left and upper right corners I use the following query:

SELECT * FROM locations 
WHERE lat >= min_lat AND
WHERE lat <= max_lat AND
WHERE lng >= min_lng AND
WHERE lng <= max_lng; 

现在，我想生成一个给定点的边界框，并在位置查询中使用边界框结果.我正在使用以下PostGIS查询来生成边界框:

Now, I want to generate a bounding box given a point and use the bounding box result in the locations query. I'm using the following PostGIS query to generate a bounding box:

SELECT 
  ST_Extent(
   ST_Envelope(
    ST_Rotate(
     ST_Buffer(
      ST_GeomFromText('POINT (-87.6297982 41.8781136)',4326)::GEOGRAPHY,160934)::GEOMETRY,0)));

结果:BOX(-89.568160053866 40.4285062983089,-85.6903925527536 43.3273499289221)

但是，我不确定一次调用如何将PostGIS查询边界框中的结果用于原始lat/lng Postgres查询中.关于如何将两者合并的任何想法?最好这样以保留索引.

However, I'm not sure how use the results from the PostGIS query bounding box into the vanilla lat / lng Postgres query in one call. Any ideas on how to merge the two? Preferably such that the index is preserved.

推荐答案

如果要获取bbox坐标作为分隔值，则可以查看ST_XMax，ST_YMax，ST_XMin，ST_YMin .嵌入您的查询的以下CTE应该给您一个提示:

If you want to get the bbox coordinates as separated values you might wanna take a look at ST_XMax, ST_YMax, ST_XMin, ST_YMin. The following CTE, that embeds your query, should give you an idea:

WITH j (geom) AS (
 SELECT 
  ST_Extent(ST_Envelope(
   ST_Rotate(ST_Buffer(
    ST_GeomFromText('POINT(-87.6297982 41.8781136)',4326)::GEOGRAPHY,160934)::GEOMETRY,0)))
)
SELECT
    ST_XMax(geom),ST_YMax(geom),
    ST_XMin(geom),ST_YMin(geom)
FROM j


      st_xmax      |     st_ymax     |      st_xmin      |     st_ymin      
-------------------+-----------------+-------------------+------------------
 -85.6903925527536 | 43.327349928921 | -89.5681600538661 | 40.4285062983098

附带说明:将几何值存储为数字可能看起来很简单，但几乎永远不是更好的选择-特别是在处理多边形时！因此，我真的建议您将这些值存储为geometry或geography，乍一看似乎很复杂，但从长远来看肯定会有所收获.

Side note: Storing geometry values as numbers might look straightforward but it is hardly ever the better choice - specially when dealing with polygons! So I would really suggest you to store these values as geometry or geography, which might seem complex at first a glance but definitely pays off on the long run.

此答案可能有助于阐明涉及多边形的距离/围护性查询: Getting all Buildings in range of 5 miles from specified coordinates

This answer might shed a light on distance/containment queries involving polygons: Getting all Buildings in range of 5 miles from specified coordinates

这篇关于在香草Postgres查询中使用PostGIS中的边界框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！