- 入库(postgrepSQL远程将某个文件传递到psql某个数据表中)
cat RSRP.txt|psql -U dbuser -h 172.17.4.41 -d npgsql_db -Atc "copy gld_20190719_rsrp (x_offset, y_offset, rsrp) from stdin with DELIMITER ’ ’ NUll ‘N.C.’ " - 根据偏移量update
目标表gld_20190719_rsrp定义了7列,我们入库了三列,根据已有的信息更新其他几列。 - 更新x_new y_new:
a) update gld_20190719_rsrp set x_new=x_offset+ 645723.0731 y_new=y_offset+4569018.169; - 将当前utm坐标转换成经纬度
update gld_20190719_rsrp set lng=p_x,lat=p_y
from (
select st_x§ as p_x,st_y§ as p_y , x1 ,y1 from (
select st_transform(st_setsrid(st_point(x_new, y_new),32635),4326) as p, x_new as x1, y_new as y1 from gld_20190719_rsrp)
a)b where b.x1=x_new and b.y1=y_new - 数据导入为csv文件(new.csv)
psql -U dbuser -h 172.17.4.41 -d npgsql_db -Atc “copy gld_20190719_rsrp to stdout with DELIMITER ‘,’ NUll ‘\N’” > new.csv
最后目标文件打包tar:tar cfz new.tar.gz new.csv
postgrepSQL
最新推荐文章于 2024-03-24 15:50:20 发布