cells 转换

1. 先用 cs2cs 将4326的坐标系转换成900913的坐标系
cs2cs +proj=latlong +datum=WGS84 +to +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=y_0=0 +k=1.0 +units=m +no_defs cell.txt > cell9.err

2. 然后用ruby脚本处理成sql语句。
#usage cell.rb cell9.err
k=0;
File.open(ARGV[0],'r').each_line do |line|
k=k+1
ss = line.chomp.split("\t")
lat = ss[1].split[0]
puts "insert into cell5 values (" + (k-1).to_s +
",'#{ss[2]}',#{ss[3]},#{ss[4]},#{ss[5]},'#{ss[6]}',#{ss[7]},#{ss[8]},#{ss[9]},#{ss[10]},#{ss[11]},#{ss[12]},'#{ss[13]}','#{ss[14]}','#{ss[15]}','#{ss[16]}','#{ss[17]}',"+"geomFromText('Point(#{ss[0]} #{lat})',900913));" if (k > 1);
end
ruby cell.rb cell9.err > cell5.sql


3. 创建cell5 数据表
-- Table: cell5

-- DROP TABLE cell5;

CREATE TABLE cell5
(
  id serial NOT NULL,
  cell_id character varying(255),
  height double precision,
  direction character varying(255),
  vange integer,
  "type" character varying(255),
  bspwrb double precision,
  antennagain double precision,
  bcchno integer,
  bsic integer,
  geotype character varying(255),
  ci character varying(255),
  basename character varying(255),
  addr character varying(255),
  "zone" character varying(255),
  msc_id character varying(255),
  bsc_id character varying(255),
  geom geometry NOT NULL,
  CONSTRAINT cell5_pkey PRIMARY KEY (id),
  CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL),
  CONSTRAINT enforce_srid_geom CHECK (srid(geom) = 900913)
)
WITH (OIDS=FALSE);
ALTER TABLE cell5 OWNER TO postgres;


4. 然后用psql执行
psql -f cell5.sql -d GZ125