Postgresql中创建包含空间列、uuid、自增长id、时间戳的表格

1、Postgresql数据库安装

安装PG数据库需要注意的是，使用几何空间列必须安装PostGIS 2.5.0 插件。

windows安装教程
linux安装教程

2、创建表格的准备

2.1、uuid生成函数

pgsql默认安装是没有该类函数的,若脚本执行到此处出错,需打开下面注释,安装uuid生成函数

create extension "uuid-ossp";

验证函数

select uuid_generate_v4();

结果如下，说明成功：

2.2、id生成函数

pgsql默认自增长序号。

create sequence auto_id increment by 1 minvalue 1 no maxvalue start with 1;

验证：

select nextval('auto_id'::regclass)

结果如下，说明成功：

2.3、空间列扩展函数

create extension postgis;

验证：

select st_asText(st_geomfromtext('POINT(114 50)',4326))

结果如下，说明成功：

3、创建表格

pg库创建表格时需要注意的是否包含空间几何信息。
列特殊类型的包含空间几何信息，id自增长，uuid自动生成，时间戳等。
注意：通过客户端（如：Navicat）创建表格时空间列（geometry）会默认4326空间坐标系，
导致插入数据时需要geometry带4326的空间坐标系。

3.1、创建包含空间几何信息(geometry)的列的表

CREATE TABLE "public"."table_pgtable" (
    "id" int4 NOT NULL DEFAULT nextval('auto_id'::regclass),
    "gid" varchar(36) DEFAULT uuid_generate_v4(),
    "name" varchar(255) COLLATE "pg_catalog"."default",
    "geometry" "public"."geometry",
    "remarks" varchar(255) COLLATE "pg_catalog"."default",
    "time" timestamp(6) DEFAULT CURRENT_TIMESTAMP
  );
  COMMENT ON COLUMN "public"."table_pgtable"."id" IS '自增长id';
  COMMENT ON COLUMN "public"."table_pgtable"."gid" IS 'uuid';
  COMMENT ON COLUMN "public"."table_pgtable"."name" IS '名称';
  COMMENT ON COLUMN "public"."table_pgtable"."geometry" IS '几何信息';
  COMMENT ON COLUMN "public"."table_pgtable"."remarks" IS '备注';
  COMMENT ON COLUMN "public"."table_pgtable"."time" IS '时间';

表格创建如下：

4、插入包含空间几何信息(geometry)

4.1、插入点的数据

INSERT INTO public.table_pgtable(name,geometry,remarks) VALUES ('点数据',st_GeomFromText('POINT (120.163629 30.2592077)'),'点数据');

或

INSERT INTO public.table_pgtable(name,geometry,remarks) VALUES ('点数据','POINT (120.163629 30.2592077)','点数据');

插入结果如下：

4.2、插入线的数据

INSERT INTO public.table_pgtable(name,geometry,remarks) VALUES ('线数据',st_GeomFromText('LINESTRING (120.163629 30.2592077,120.163599 30.259802,120.163617 30.259803,120.163647 30.259207)'),'线数据');

或

INSERT INTO public.table_pgtable(name,geometry,remarks) VALUES ('线数据','LINESTRING (120.163629 30.2592077,120.163599 30.259802,120.163617 30.259803,120.163647 30.259207)','线数据');

插入结果如下：

4.3、插入面的数据

INSERT INTO public.table_pgtable(name,geometry,remarks) VALUES ('面数据',st_GeomFromText('POLYGON ((120.163629 30.259207,120.163599 30.259802,120.163617 30.259803,120.163647 30.259207,120.163629 30.259207))'),'面数据');

或

INSERT INTO public.table_pgtable(name,geometry,remarks) VALUES ('面数据','POLYGON ((120.163629 30.259207,120.163599 30.259802,120.163617 30.259803,120.163647 30.259207,120.163629 30.259207))','面数据');

插入结果如下：

插入的空间列采用标准的wkt格式文本的几何信息。WKT标准可参考