使用ST_GeomFromGeoJSON触发无效十六进制数字错误如何解决？

尝试使用PostGIS的ST_GeomFromGeoJSON函数通过GeoJSON创建几何对象时，遇到"Invalid hex digit: 'P'"错误，示例查询如下：

SELECT st_geomfromgeojson('{
  "type": "Polygon",
  "coordinates": [
    [
      [
        -101.15663846469602,
        40.37646152866321
      ],
      [
        -108.74819119907102,
        40.37646152866321
      ],
      [
        -108.74819119907102,
        37.04267167068658
      ],
      [
        -101.15663846469602,
        37.04267167068658
      ]
    ]
  ]
}');

执行后返回的错误栈信息：

org.jkiss.dbeaver.model.exec.DBCException: Invalid hex digit: 'P'
    at org.jkiss.dbeaver.ext.postgresql.model.data.PostgreGeometryValueHandler.makeGeometryFromPGGeometry(PostgreGeometryValueHandler.java:200)
    at org.jkiss.dbeaver.ext.postgresql.model.data.PostgreGeometryValueHandler.getValueFromObject(PostgreGeometryValueHandler.java:122)
    at org.jkiss.dbeaver.ext.postgresql.model.data.PostgreGeometryValueHandler.fetchColumnValue(PostgreGeometryValueHandler.java:58)
    at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCAbstractValueHandler.fetchValueObject(JDBCAbstractValueHandler.java:49)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetDataReceiver.fetchRow(ResultSetDataReceiver.java:126)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.fetchQueryData(SQLQueryJob.java:798)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:609)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:486)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:493)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:894)
    at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3726)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4982)
    at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.lang.IllegalArgumentException: Invalid hex digit: 'P'
    at org.locationtech.jts.io.WKBReader.hexToInt(WKBReader.java:96)
    at org.locationtech.jts.io.WKBReader.hexToBytes(WKBReader.java:84)
    at org.jkiss.dbeaver.ext.postgresql.model.data.PostgreGeometryValueHandler.makeGeometryFromWKB(PostgreGeometryValueHandler.java:145)
    at org.jkiss.dbeaver.ext.postgresql.model.data.PostgreGeometryValueHandler.makeGeometryFromWKT(PostgreGeometryValueHandler.java:226)
    at org.jkiss.dbeaver.ext.postgresql.model.data.PostgreGeometryValueHandler.makeGeometryFromPGGeometry(PostgreGeometryValueHandler.java:197)
    ... 17 more

错误原因 #

1. GeoJSON不符合Polygon规范：PostGIS要求Polygon类型的GeoJSON必须闭合，即坐标数组的第一个点和最后一个点必须完全相同。当前示例中的GeoJSON首尾坐标不一致，会生成无效几何对象。
2. DBeaver解析逻辑错误：错误栈显示DBeaver的PostgreGeometryValueHandler模块错误地将几何对象的WKT（文本格式）当作WKB（十六进制二进制格式）解析，而WKT以POLYGON开头，第一个字符P不是十六进制字符，因此触发"Invalid hex digit: 'P'"错误。

修复方法 #

方法1：修正GeoJSON，闭合多边形 #

将GeoJSON坐标数组的最后一个点改为和第一个点完全一致，确保多边形闭合：

SELECT st_geomfromgeojson('{
  "type": "Polygon",
  "coordinates": [
    [
      [
        -101.15663846469602,
        40.37646152866321
      ],
      [
        -108.74819119907102,
        40.37646152866321
      ],
      [
        -108.74819119907102,
        37.04267167068658
      ],
      [
        -101.15663846469602,
        37.04267167068658
      ],
      [
        -101.15663846469602,
        40.37646152866321
      ]
    ]
  ]
}');

方法2：修改查询输出格式，适配DBeaver解析 #

使用ST_AsText将几何对象转换为WKT文本输出，或用ST_AsEWKB输出标准WKB格式，让DBeaver能正确解析：

-- 输出WKT文本格式
SELECT ST_AsText(st_geomfromgeojson('{
  "type": "Polygon",
  "coordinates": [
    [
      [
        -101.15663846469602,
        40.37646152866321
      ],
      [
        -108.74819119907102,
        40.37646152866321
      ],
      [
        -108.74819119907102,
        37.04267167068658
      ],
      [
        -101.15663846469602,
        37.04267167068658
      ],
      [
        -101.15663846469602,
        40.37646152866321
      ]
    ]
  ]
}'));

方法3：调整DBeaver几何显示设置 #

进入DBeaver的数据库连接属性设置，找到数据类型 -> 几何类型，将显示格式改为"WKT"或"自动检测"，避免强制用WKB解析几何对象。

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