postgres 使用技巧

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;