python - 在 SQLAlchemy 中为子查询添加别名

Question

过去 2 小时这让我抓狂:

我正在尝试查询重叠的多边形。这与 PostGIS 函数配合得很好，但我很难用 SQLAlchemy 编写这个查询。

正确的 SQL 查询是:

SELECT id FROM track WHERE ST_Overlaps(
  (
    SELECT ST_GeomFromText(
      'POLYGON((
        16.15 47.25, 
        16.15 46.57, 
        13.85 46.57, 
        13.85 47.25, 
        16.15 47.25
      ))', 
    4326)
  ), 
  track.extent
);

(track.extent 是保存地理格式多边形的列，ST_GeomFromText() 创建我将它们与多边形进行比较)

我正在使用 ORM，到目前为止我的 python 代码如下所示:

viewport = select([func.ST_GeomFromText(('POLYGON(( \
  16.15 47.25, \
  16.15 46.57, \
  13.85 46.57, \
  13.85 47.25, \
  16.15 47.25))' \
  ,4326))])

DBSession.query(Track.id).filter( \
  func.ST_Overlaps(viewport, Track.extent)).all()

上面的代码被 SQLAlchemy 解释为以下 SQL 查询:

SELECT track.id AS track_id FROM track, 
(SELECT ST_GeomFromText('
  POLYGON((
    16.15 47.25, 
    16.15 46.57, 
    13.85 46.57, 
    13.85 47.25, 
    16.15 47.25
  ))', 
  4326) AS "ST_GeomFromText_1"
) 
WHERE ST_Overlaps(
  (
    SELECT ST_GeomFromText(
      'POLYGON((
        16.15 47.25, 
        16.15 46.57, 
        13.85 46.57, 
        13.85 47.25, 
        16.15 47.25
      ))', 
      4326) AS "ST_GeomFromText_1"
    ), 
  track.extent
);

这会产生错误:

 ProgrammingError: (ProgrammingError) subquery in FROM must have an alias
 LINE 2: FROM track, (SELECT ST_GeomFromText('POLYGON((16.15759034023...
                     ^
 HINT:  For example, FROM (SELECT ...) [AS] foo.

我如何告诉 sqlalchemy 删除 FROM 子句中不必要的视口(viewport)-子查询或向视口(viewport)添加别名-查询？

Answer 1

我相信您正在寻找.label()构造:

viewport = select([func.ST_GeomFromText(...)]).label("viewport")

然后，将其放入 WHERE 子句中并不会将其放入 FROM 子句中:

str(session.query(Track.id).filter(func.ST_Overlaps(viewport, Track.extent))
# 'SELECT track.id AS track_id \nFROM track \nWHERE ST_Overlaps((SELECT ST_GeomFromText(...) AS "ST_GeomFromText_1"), track.extent)'