常用SQL语句

修改表里面字段时间

UPDATE  table  SET InsertTime = DATEADD(YEAR, -1, InsertTime);

插入数据到另一张表

select * into table from table1

创建视图

create view ViewCode as
SELECT wellCode,warnType,('well') as tableName from well union all
SELECT wellcode, warntype,('pump') as tableName FROM pump union all
SELECT wellcode, warntype,('waterquality') as tableName FROM waterquality


PG语句

ALTER TABLE wellline 
  ALTER COLUMN geom 
  TYPE Geometry(LineString) 
  USING ST_GeometryN(geom,1)



创建数据库


createdb -U postgres routing


让数据库支持PostGIS和pgRouting的函数和基础表


CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;




//添加起点id


ALTER TABLE public.beijing ADD COLUMN source integer;


//添加终点id


ALTER TABLE public.beijing ADD COLUMN target integer;


//添加道路权重值


ALTER TABLE public.beijing ADD COLUMN length double precision;


 


//为sampledata表创建拓扑布局，即为source和target字段赋值


SELECT pgr_createTopology('public.beijing',0.00001, 'geom', 'gid');


 


//为source和target字段创建索引


CREATE INDEX source_idx ON beijingmodified("source");


CREATE INDEX target_idx ON beijingmodified("target");


 


//为length赋值


update beijingmodified set length =st_length(geom);


//或者用已有的字段长度赋值，下面shape_length为shp中已有的长度属性


UPDATE beijingmodified SET length = shape_length;


 


//为beijingmodified表添加reverse_cost字段并用length的值赋值


ALTER TABLE beijingmodified ADD COLUMN reverse_cost double precision;


UPDATE beijingmodified SET reverse_cost =length;

List<Map> list =findBySql("{call sp_Zw_Test('rltWaterLevel')}");
System.out.println(list.size());

ALTER TABLE public.wellline ADD COLUMN source integer;
ALTER TABLE public.wellline ADD COLUMN target integer;
ALTER TABLE public.wellline ADD COLUMN length double precision;
SELECT pgr_createTopology('public.wellline',0.00001, 'geom', 'gid');
CREATE INDEX source_idx ON wellline("source");
CREATE INDEX target_idx ON wellline("target");
update wellline set length =st_length(geom);
ALTER TABLE wellline ADD COLUMN reverse_cost double precision;
UPDATE wellline SET reverse_cost =length;


ALTER TABLE wellline ADD COLUMN x1 double precision;


ALTER TABLE wellline ADD COLUMN y1 double precision;


ALTER TABLE wellline ADD COLUMN x2 double precision;


ALTER TABLE wellline ADD COLUMN y2 double precision;
UPDATE wellline SET x1 =ST_x(ST_PointN(geom, 1));


UPDATE wellline SET y1 =ST_y(ST_PointN(geom, 1));


UPDATE wellline SET x2 =ST_x(ST_PointN(geom, ST_NumPoints(geom)));


UPDATE wellline SET y2 =ST_y(ST_PointN(geom, ST_NumPoints(geom)));

http://192.168.0.3:12345/geoserver/wfs?service=WFS&version=1.1.0&request=GetFeature&typename=minhang:pg_Line&viewparams=x1:121.36785507202148;y1:31.06543333046595;x2:121.39566421508788;y2:31.107477886669926&outputFormat=json&post

http://192.168.0.3:12345/geoserver/wfs?service=WFS&version=1.1.0&

request=GetFeature&typename=minhang:pg_Line&viewparams=

x1:121.36751174926755;y1:31.06572741291953;x2:121.404333114624;

y2:31.11034388346897&outputFormat=json