sql - 在 FROM 子句中创建一个大的虚拟表

Question

背景

我有一个 Java 应用程序，它定期从一个数据库 (MySQL) 获取结果集，并尝试在另一个数据库(具体来说是 Postgres/PostGIS)中查找匹配项。

问题

目前，应用程序查询Postgres数据库一次，结果集中的MySQL记录(可能高达数万条)。我正在尝试更改算法，以便应用程序生成一个查询，如果找到任何匹配项，该查询将产生多个结果。描述我的目标的另一种方式是，如果这两个表存在于同一数据库系统中，则查询的行为应该类似于典型的 JOIN。

当前解决方案

为了解决这个问题，我在 FROM 子句中创建了一个虚拟表。但是，我知道如何从值列表中执行此操作的唯一方法是编写单独的 SELECT 语句并加入 UNION。结果似乎有效，虽然我没有测试数千条记录的性能，但使用数百条这样的 SELECT-UNION 似乎没有任何重大影响> 声明。这是整个查询的相关部分，用于说明我到目前为止所做的工作:

SELECT *, ST_Distance_Sphere(latlng, geom) as distance 
FROM rwis_sites 
    INNER JOIN 
(SELECT 1100 as unit_id, ST_GeomFromText('POINT(-81.19701 32.09279)', 4326) as geom UNION
 SELECT 1100 as unit_id, ST_GeomFromText('POINT(-81.19682 32.09224)', 4326) as geom 
  UNION
 SELECT 1100 as unit_id, ST_GeomFromText('POINT(-81.1968 32.09213)', 4326) as geom 
  UNION
... just a few more...hundred...thousand...
 SELECT 2266 as unit_id, ST_GeomFromText('POINT(-97.98719 29.57656)', 4326) as geom 
  UNION
 SELECT 2266 as unit_id, ST_GeomFromText('POINT(-97.98815 29.57602)', 4326) as geom
) virtualTable 
ON ST_Distance_Sphere(latlng, geom) < 10000 
ORDER BY ST_Distance_Sphere(latlng, geom) ASC limit 1

由于“虚拟表”是通过编程方式生成的，因此我几乎不需要付出任何努力。

问题

但是，我担心这是否是一种“愚蠢”的方法(更不用说我尚未检测到的任何性能问题)，最终我想知道:是否有更好的方法来创建类似的东西而无需成千上万的SELECT-UNION 语句？

Answer 1

这是创建 values 表的更好方法在飞行中

select *
from (
  values
    (1100::int, 'POINT(-81.19701 32.09279)'::geography(Point)),
    (1100::int, 'POINT(-81.19682 32.09224)'::geography(Point))
) as t(unit_id, geom)

但更好的主意也许是使用 foreign data wrapper将您的 mysql 表放入 PG。

编辑

如果主表的索引是 ST_Dwithin(latlng, geom, 0.1) 且 0.1° 只是 slightly more，您可能想尝试从主表中预筛选记录(注意 cos(32.09)) 大于 10000 m。