如何将含CTE的PostGIS查询改写为指定列的嵌套SELECT语句

SELECT ST_AsMVT(ROW(
  id,
  isTreatment,
  isBuffer,
  geometryOfCellRepresentativeToTreatment,
  mvt_geom
)) 
FROM (
  SELECT
    id,
    isTreatment,
    isBuffer,
    geometryOfCellRepresentativeToTreatment,
    ST_AsMVTGeom(
      ST_GeomFromText(geometryOfCellRepresentativeToBuffer),
      ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096,4096))
    ) AS mvt_geom
  FROM grid_cell_data 
  WHERE id = 3
) AS nested_query

调整点说明 #

• 完全替换了原语句的CTE写法，改用嵌套子查询实现相同逻辑
• 子查询内明确列出了grid_cell_data表的所有原生字段，无遗漏
• 外层查询未使用任何通配符，所有传入ST_AsMVT的字段都逐一明确列出，满足要求

内容的提问来源于stack exchange，提问作者Amrmsmb