1.创建空间表
CREATE TABLE public.my_polygon
(
id bigserial,
name character(100) COLLATE pg_catalog."default" NOT NULL,
mypolygon geometry NOT NULL,
salary integer,
CONSTRAINT my_polygon_pkey PRIMARY KEY (id)
)
空间字段:mypolygon; 数据类型为geometry;
bigserial: 主键自增类型;
2.psql表操作
//添加点
1) insert into my_polygon values (1,'点','POINT(0 0)');
//添加线,多边形也是线点存储
2) insert into my_polygon values (2,'线条','LINESTRING(0 0,1 1,1 2)');
//查看所有表记录
3)select * from 表名;
select * from my_polygon; //数据不进行处理,空间字段展示增强的16进制编码
select id,name,ST_AsText(mypolygon) from my_polygon; //空间几何字段处理展示文本类型,POINT(1 1)等
//更新数据
4)UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];
update my_polygon SET name = '点',mypolygon = 'POINT(1 1)' where id = 1
update my_polygon SET name = '点',mypolygon = ST_AsText('POINT(1 1)') where id = 1;
update my_polygon SET name = '点',mypolygon = ST_GeomFromText('POINT(1 1)') where id = 1
注释:效果一样,最终表数据16进制展示;
//删除记录
5)delete from my_polygon where id = 3
//排序查询
6)SELECT column-list FROM table_nam