在CTE表达式中使用ST_AsMVT报pgis_asmvt_transfn行类型错误如何修复

报错原因 #

你调用ST_AsMVT时第一个参数传了单独的几何字段j.MVTGeom，但该函数第一个参数要求是包含几何列和所有要嵌入MVT属性字段的行记录（rowtype），而非单独的几何值，所以触发了parameter row cannot be other than a rowtype报错。

修复方案 #

核心调整点 #

把需要打包到MVT的几何字段+属性字段包装成完整行结构传入ST_AsMVT，同时你原查询的多表关联存在冗余，未关联的表会产生无效笛卡尔积，修复后逻辑如下：

WITH j AS (
 SELECT 
  geoOfKeyWindowRepresentativeToTreatment,
  geoOfKeyWindowRepresentativeToBuffer,
  fk_gridCell_fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON,
  fk_gridCell_fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON,
  fk_gridCell_fk_site_selectedSiteID,
  fk_gridCell_fk_OpIndependentParticular_isTreatment,
  fk_gridCell_fk_OpIndependentParticular_isBuffer,
  fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfTreatmentToNearestEdge,
  fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfBufferToNearestEdge,
  ST_AsMVTGeom(
   geoOfKeyWindowRepresentativeToTreatment,
   ST_MakeEnvelope(6.741485595703125,51.12335082548443,6.74285888671875,51.12248887705868,4326),
   4096,
   0,
   false
  ) As geom
 FROM Geo 
 WHERE 
  geoOfKeyWindowRepresentativeToTreatment <> 'POLYGON EMPTY' 
  AND fk_gridCell_fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON <> '{}' 
  AND fk_gridCell_fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON IS NOT NULL        
),
x AS (
 SELECT 
  fk_gridCell_fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON,
  fk_gridCell_fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON,
  fk_gridCell_fk_site_selectedSiteID,
  fk_gridCell_fk_OpIndependentParticular_isTreatment,
  fk_gridCell_fk_OpIndependentParticular_isBuffer,
  fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfTreatmentToNearestEdge,
  fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfBufferToNearestEdge,
  fk_OpDependentParticular_AoCForCellsRepresentativeToTreatment,
  fk_OpDependentParticular_AoCForCellsRepresentativeToBuffer,
  fk_OpDependentParticular_AvgHPerWindowRepresentativeToTreatment,
  fk_OpDependentParticular_AvgHPerWindowRepresentativeToBuffer 
 FROM gridcellopdependentparticular
)
-- 把子查询返回的整行作为参数传入ST_AsMVT
SELECT ST_AsMVT(t.*, 'MVTGeometryRow', 4096, 'geom')
FROM (
 SELECT 
  j.geom,
  -- 可自行增减需要存入MVT属性的字段
  j.fk_gridCell_fk_site_selectedSiteID,
  x.fk_OpDependentParticular_AoCForCellsRepresentativeToTreatment,
  x.fk_OpDependentParticular_AoCForCellsRepresentativeToBuffer
 FROM j
 JOIN x ON 
  j.fk_gridCell_fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON = x.fk_gridCell_fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON
  AND j.fk_gridCell_fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON = x.fk_gridCell_fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON
  AND j.fk_gridCell_fk_site_selectedSiteID = x.fk_gridCell_fk_site_selectedSiteID
  AND j.fk_gridCell_fk_OpIndependentParticular_isTreatment = x.fk_gridCell_fk_OpIndependentParticular_isTreatment
  AND j.fk_gridCell_fk_OpIndependentParticular_isBuffer = x.fk_gridCell_fk_OpIndependentParticular_isBuffer
  AND j.fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfTreatmentToNearestEdge = x.fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfTreatmentToNearestEdge
  AND j.fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfBufferToNearestEdge = x.fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfBufferToNearestEdge
 WHERE j.fk_gridCell_fk_site_selectedSiteID = '202107090856'
) t;

极简快速修复 #

如果不需要给MVT加任何属性，只需要返回几何，也可以直接用row()函数把单个几何字段包装成行类型，无需调整其他逻辑：

-- 仅替换原来的ST_AsMVT调用行即可
ST_AsMVT(row(j.MVTGeom), 'MVTGeometryRow', 4096, 'f1')

row()构造的第一个字段默认名为f1，对应ST_AsMVT的第四个参数即可。

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