PostGIS返回记录作为数据类型.这是出乎意料的

Question

我有这个查询

WITH buffered AS (
SELECT 
ST_Buffer(geom , 10, 'endcap=round join=round') AS geom,
id
FROM line),
hexagons AS (
SELECT 
ST_HexagonGrid(10, buffered.geom) AS hex,
buffered.id
FROM buffered
) SELECT * FROM hexagons;

给出hex列中的datatyperecord。这是出乎意料的。我期望geometry成为datatype。为什么呢?

Answer 1

根据documentation，函数ST_HexagonGrid返回一个setof record。然而，这些记录包含一个名为geom的geometry属性，所以为了访问这个record的geometry，你必须用括号()包裹变量，并用点.调用属性，例如

SELECT (hex).geom FROM hexagons;

或仅访问获取所有属性使用*(在本例中，i,j和geom):

SELECT (hex).* FROM hexagons;

Demo (PostGIS 3.1)):

WITH j (hex) AS (
SELECT 
ST_HexagonGrid(
10,ST_Buffer('LINESTRING(-105.55 41.11,-115.48 37.16,-109.29 29.38,-98.34 27.13)',1))    
)
SELECT ST_AsText((hex).geom,2) FROM j;
st_astext                                        
----------------------------------------------------------------------------------------
POLYGON((-130 34.64,-125 25.98,-115 25.98,-110 34.64,-115 43.3,-125 43.3,-130 34.64))
POLYGON((-115 25.98,-110 17.32,-100 17.32,-95 25.98,-100 34.64,-110 34.64,-115 25.98))
POLYGON((-115 43.3,-110 34.64,-100 34.64,-95 43.3,-100 51.96,-110 51.96,-115 43.3))
POLYGON((-100 34.64,-95 25.98,-85 25.98,-80 34.64,-85 43.3,-95 43.3,-100 34.64))

由于ST_HexagonGrid返回setof record，您可以使用LATERAL访问记录属性，如here所述，或者仅调用FROM子句中的函数:

SELECT i,j,ST_AsText(geom,2) FROM 
ST_HexagonGrid(
10,ST_Buffer('LINESTRING(-105.55 41.11,-115.48 37.16,-109.29 29.38,-98.34 27.13)',1));
i  | j |                                       st_astext                                        
----+---+----------------------------------------------------------------------------------------
-8 | 2 | POLYGON((-130 34.64,-125 25.98,-115 25.98,-110 34.64,-115 43.3,-125 43.3,-130 34.64))
-7 | 1 | POLYGON((-115 25.98,-110 17.32,-100 17.32,-95 25.98,-100 34.64,-110 34.64,-115 25.98))
-7 | 2 | POLYGON((-115 43.3,-110 34.64,-100 34.64,-95 43.3,-100 51.96,-110 51.96,-115 43.3))
-6 | 2 | POLYGON((-100 34.64,-95 25.98,-85 25.98,-80 34.64,-85 43.3,-95 43.3,-100 34.64))

进一步阅读:How to divide world into cells (grid)