Use a caixa delimitadora do PostGIS na consulta vanilla Postgres

Question

Eu tenho uma estrutura de banco de dados que é 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);

Quando desejo calcular todos os locais em uma caixa delimitadora onde tenho os cantos inferior esquerdo e superior direito, uso a seguinte consulta:

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

Agora, quero gerar uma caixa delimitadora a partir de um ponto e usar o resultado da caixa delimitadora na consulta de locais. Estou usando a seguinte consulta PostGIS para gerar uma caixa delimitadora:

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

Resultado: BOX(-89.568160053866 40.4285062983089,-85.6903925527536 43.3273499289221)

No entanto, não tenho certeza de como usar os resultados da caixa delimitadora da consulta PostGIS na consulta vanilla lat / lng Postgres em uma chamada. Alguma ideia de como unir os dois? De preferência, de forma que o índice seja preservado.

Answer 1

Se você quiser obter as coordenadas bbox como valores separados talvez você queira dar uma olhada em ST_XMax, ST_YMax, ST_XMin, ST_YMin. O seguinte CTE, que incorpora sua consulta, deve dar uma ideia:

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

Nota lateral : Armazenar valores de geometria como números pode parecer simples, mas dificilmente é a melhor escolha - especialmente ao lidar com polígonos! Portanto, eu realmente sugiro que você armazene esses valores como geometryou geography, o que pode parecer complexo à primeira vista, mas definitivamente compensa no longo prazo.

Esta resposta pode lançar uma luz sobre as consultas de distância / contenção envolvendo polígonos: Getting all Buildings in range of 5 miles from specified coordinates