PostGIS Sql语句

查询schema下的所有表：

SELECT * FROM "geometry_columns" WHERE "f_table_schema" = '201904171640390241'
SELECT COUNT(*) FROM "geometry_columns" WHERE "f_table_schema" = '201904171640390241' AND 
"f_table_name" = 'XZQ_date_2019_08_07_11_49_461071' AND "f_geometry_column"='geometry';

创建表：

CREATE TABLE "201904171640390241"."XZQ_date_2019_08_07_11_49_461071"(id integer primary key , BSM integer, 
YSDM character varying(1000), KZMJ double precision);

添加Geometry列：

SELECT AddGeometryColumn('201904171640390241', 'XZQ_date_2019_08_07_11_49_461071', 'geometry', 2360, 'GEOMETRY', 2);

插入值：

@params = excmd.Parameters;
@params.AddRange(new[]
{ 
   new NpgsqlParameter("PGeom0", NpgsqlDbType.Bytea)
});
INSERT INTO "201904171640390241"."XZQ_date_2019_08_07_11_49_461071" (id,  BSM, YSDM, KZMJ, geometry) VALUES (1,  2710, '1000600100', 232461.72, @PGeom0);

修改列数据类型：

ALTER TABLE "201904171640390241"."XZQ_date_2019_08_07_11_49_461071" ALTER COLUMN geometry SET DATA TYPE GEOMETRY(MULTIPOLYGON, 2360) USING ST_Multi(geometry);

查询数据：

SELECT "id", "bsm", "ysdm", "kzmj", "geometry"::bytea AS "_smtmp_" FROM "201904171640390241"."XZQ_date_2019_08_07_11_49_212191";

查询坐标系:

SELECT "srid" FROM public."geometry_columns" WHERE "f_table_schema"='201904171640390241' and "f_table_name"='XZQ_date_2019_08_07_14_18_338954' AND "f_geometry_column"='geometry';　

查询所有列:

SELECT "column_name" FROM "information_schema"."columns" WHERE "table_schema"='201904171640390241' AND "table_name"='XZQ_date_2019_08_07_14_18_338954';

.

转载于:https://www.cnblogs.com/lili9696189/p/11314525.html