我有這個查詢
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;
這給出datatype record了列中的hex。這是出乎意料的。我希望geometry作為datatype. 這是為什么？
uj5u.com熱心網友回復：
根據documentation，該函式ST_HexagonGrid回傳一個setof record。這些記錄包含但是一個geometry稱為屬性geom，所以為了進入geometry這個record你必須包裝用括號中的變數()，并以點呼叫屬性.，如
SELECT (hex).geom FROM hexagons;
或僅使用*（在本例中為i、j和geom）訪問獲取所有屬性：
SELECT (hex).* FROM hexagons;
演示（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回傳 a setof record，您可以通過呼叫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))
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/331671.html
標籤：PostgreSQL 邮局
下一篇：持久化到PostgreSQL`timestampwithtimezone`時，如何防止Hibernate將ZonedDateTime轉換為UTC