---------------------------------------------- 0.初始化
|
create extension if not exists postgis cascade;
|
|
create extension if not exists "uuid-ossp";
|
|
create schema if not exists nsl;
|
---------------------------------------------- 1.推演模拟
|
-- drop table if exists nsl.simu;
|
create table nsl.simu (
|
id serial primary key,
|
name varchar(200),
|
service_name varchar(200),
|
type smallint default 1,
|
area_type smallint default 0,
|
data varchar(8000),
|
status smallint default 0,
|
result varchar(8000),
|
create_time timestamp(6) without time zone default now(),
|
create_user varchar(50),
|
update_time timestamp(6) without time zone,
|
update_user varchar(50),
|
bak varchar(2000),
|
geom geometry(MultiPolygon, 4490)
|
);
|
-- drop index nsl.idx_nsl_simu_geom;
|
create index idx_nsl_simu_geom on nsl.simu using GIST (geom);
|
|
comment on table nsl.simu is '推演模拟表';
|
comment on column nsl.simu.id is '主键ID';
|
comment on column nsl.simu.name is '名称';
|
comment on column nsl.simu.service_name is '服务名称';
|
comment on column nsl.simu.type is '类别：1-预测模拟，2-实时模拟，3-历史模拟';
|
comment on column nsl.simu.area_type is '区域类别：0-自定义，1-行政区划，2-重点区域，3-重点沟';
|
comment on column nsl.simu.data is '数据(JSON)';
|
comment on column nsl.simu.status is '状态：0-创建仿真，1-预处理，2-分析中，10-完成，20-出错';
|
comment on column nsl.simu.result is '结果(JSON)';
|
comment on column nsl.simu.create_time is '创建时间';
|
comment on column nsl.simu.create_user is '创建人';
|
comment on column nsl.simu.update_time is '更新时间';
|
comment on column nsl.simu.update_user is '更新人';
|
comment on column nsl.simu.bak is '备注';
|
comment on column nsl.simu.geom is '空间位置';
|
|
-- insert into nsl.simu (name, service_name, type, area_type, status, geom) values ('预测模拟-0416', '20250416100000', 1, 1, 0, ST_GeomFromText('MULTIPOLYGON(((116.666748 39.639859,116.659403 39.626287,116.665956 39.654698,116.666748 39.639859)))'));
|
|
select *, st_astext(geom) geom from nsl.simu;
|
---------------------------------------------- 2.推演区域
|
-- drop table if exists nsl.region;
|
create table nsl.region (
|
id serial primary key,
|
name varchar(200),
|
type smallint default 1,
|
geom geometry(MultiPolygon, 4490)
|
);
|
-- drop index nsl.idx_nsl_region_geom;
|
create index idx_nsl_region_geom on nsl.region using GIST (geom);
|
|
comment on table nsl.region is '推演区域表';
|
comment on column nsl.region.id is '主键ID';
|
comment on column nsl.region.name is '名称';
|
comment on column nsl.region.type is '类别：1-行政区划，2-重点区域，3-重点沟';
|
comment on column nsl.region.geom is '空间位置';
|
|
-- insert into nsl.region (name, type, geom) values ('大兴', 1, ST_GeomFromText('MULTIPOLYGON(((116.666748 39.639859,116.659403 39.626287,116.665956 39.654698,116.666748 39.639859)))'));
|
|
select id, name, type, st_astext(geom) from nsl.region order by id;
|
---------------------------------------------- 3.雨量计 *
|
-- drop table if exists nsl.udometer;
|
create table nsl.udometer (
|
id serial primary key,
|
name varchar(200),
|
rainfall numeric(15, 3) default 0.0, -- 总位数/小数位数
|
geom geometry(Point, 4490)
|
);
|
-- drop index nsl.idx_nsl_udometer_geom;
|
create index idx_nsl_udometer_geom on nsl.udometer using GIST (geom);
|
|
comment on table nsl.udometer is '雨量计表';
|
comment on column nsl.udometer.id is '主键ID';
|
comment on column nsl.udometer.name is '名称';
|
comment on column nsl.udometer.rainfall is '降雨量(米)';
|
comment on column nsl.udometer.geom is '空间位置';
|
|
select * from nsl.udometer;
|
----------------------------------------------
|
