PostGIS returns record as datatype. This is unexpected-CodePudding

I have this query

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;

This gives the datatype record in the column hex. This is unexpected. I expect geometry as a datatype. Why is that?

CodePudding user response：

According to the documentation, the function ST_HexagonGrid returns a set of records. These records contain however a geometry stored in the attribute geom, so in order to access the geometry of this record type result set you have to wrap the variable with parenthesis () and access the attribute with a dot ., e.g.

SELECT (hex).geom FROM hexagons;

or just access fetch all attributes using * (in this case, i,j and 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))

As ST_HexagonGrid returns a set of records, you can use its results as such by calling the function in the FROM clause:

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))