zkbtHuangw
commented
1 year ago 把tbase版本降到2.1.0后，select * from spatial_ref_sys能查出数据， SELECT ST_Distance( ST_GEOMFROMTEXT('POINT(113.907783490367706 22.39120737493609)',4326), ST_GEOMFROMTEXT('POINT(113.907783490367706 23.39120737493609)',4326) ); 与 SELECT ST_Distance( ST_GEOMFROMTEXT('POINT(113.907783490367706 22.39120737493609)',4326)::geography, ST_GEOMFROMTEXT('POINT(113.907783490367706 23.39120737493609)',4326)::geography ); 都能正确输出结果，但如果是查表， SELECT ST_Distance(t.geo_detail::geography, ST_GeomFromText('POINT(0 1)', 4326)::geography) from test_geom t where t.geo_id=3; 与 SELECT ST_Distance(t.geo_detail, ST_GeomFromText('POINT(0 1)', 4326)) from test_geom t where t.geo_id=3; 只有平面几何才能正确计算，球面计算就会报错： ERROR: node:dn001, backend_pid:1739835, nodename:dn001,backend_pid:1739835,message:Cannot find SRID (4326) in spatial_ref_sys SQL state: XX000
在opencloud8.6上部署的单机版的tbase, 安装了postgis3.0.1, 连接cn执行create extension postgis，插件加载成功，spatial_ref_sys表成功创建，pg_class里显示spatial_ref_sys有8500条记录， INSERT INTO "spatial_ref_sys" ("srid","auth_name","auth_srid","srtext","proj4text") VALUES (4326,'EPSG',4326,'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]','+proj=longlat +datum=WGS84 +no_defs '); 会提示主键冲突，说明表里是有数据的，但select * from spatial_ref_sys查不出来数据(rows: 0)，该问题导致很多空间地理函数无法使用，会提示找不到相应的坐标参考系。