postgres 使用技巧

最新推荐文章于 2025-06-12 09:59:04 发布

最新推荐文章于 2025-06-12 09:59:04 发布 · 267 阅读

webgis 专栏收录该内容

26 篇文章

订阅专栏

本文介绍了如何使用SQL语句生成新的线条数据，并完成了坐标系的转换及字段类型的更改。通过具体的SQL指令，实现了从点到线的数据转换，并保持了坐标的一致性；同时对原有表中的字段进行了类型转换。

1. 生成新表

drop table dtline; --asText(makeline(dtcells.the_geom, cell5.geom))
select dtcells.id, cell5_id, makeline(the_geom, geom) as the_geom
into dtline
from dtcells, cell5
where dtcells.cell5_id = cell5.id;

ALTER TABLE dtline
ADD CONSTRAINT dtline_pkey PRIMARY KEY(id);
ALTER TABLE dtline
ADD CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
ALTER TABLE dtline
ADD CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 900913);

用Select语句完成新线条的生成。因为原来的点是900913 坐标系的，所有生成的坐标系也是900913的，这一点非常让我满意。

2. updae 老表

postgres 做类型转换：

UPDATE field9s
SET cq_val=cast(cq as double precision);

//修改Value
UPDATE field9s
SET "cq_val" = "cq_val" - 18
FROM grids
WHERE field9s.grid_id = grids.id AND grids.build_type = 1;

//再修改回去
UPDATE field9s
SET cq = cast(cq_val as character varying(255));

综合起来就是：

select id, cast( (cast(cq as double precision) - 18) as character varying(255)) from field9s limit 1;

UPDATE field9s
SET "cq" = cast( (cast(cq as double precision) - 18) as character varying(255))
FROM grids
WHERE field9s.grid_id = grids.id AND grids.build_type = 1;