PostgreSQL 日常SQL语句查询记录
记录开发过程中使用的各种SQL语句！
创建扩展
CREATE EXTENSION POSTGIS;
CREATE EXTENSION POSTGIS_RASTER;查询扩展
SELECT name, default_version, installed_version
FROM pg_available_extensions;查询具体某个函数
-- 查询具体的函数 例如：st_fromgdalraster
SELECT proname, proargtypes, prosrc
FROM pg_proc
JOIN pg_namespace ON pg_namespace.oid = pg_proc.pronamespace
WHERE proname = 'st_fromgdalraster';
-- 或者是
SELECT proname, proargtypes
FROM pg_proc
JOIN pg_namespace ON pg_namespace.oid = pg_proc.pronamespace
WHERE proname LIKE 'st_f%' AND pg_namespace.nspname = 'public';查看表字段的注释
-- tabName 表名
SELECT
b.column_name AS 字段名,
b.data_type AS 类型,
b.character_maximum_length AS 长度,
a.attnotnull AS 非空,
col_description(a.attrelid, a.attnum) AS 注释
FROM
information_schema.columns AS b
INNER JOIN pg_class AS c ON b.table_name = c.relname
INNER JOIN pg_attribute AS a ON a.attrelid = c.oid and a.attnum = b.ordinal_position
WHERE
c.relname = '${tabName}'或者是查询固定多少个字段的注释
-- 例如查询非第一列之后的字段
SELECT a.attname as 字段名,
col_description(a.attrelid, a.attnum) as 注释
FROM pg_class as c,
pg_attribute as a
where a.attrelid = c.oid
and a.attnum > 1
and c.relname = '${tabName}'查询这个直线
-- 查询这条直线
SELECT ST_AsText(geom) FROM sdx.testline;-- 创建表
CREATE TABLE IF NOT EXISTS sdx.linedata (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name varchar(255), geom geometry)
-- 或是
CREATE TABLE IF NOT EXISTS sdx.linedata (id SERIAL PRIMARY KEY, name varchar(255), geom geometry)
-- 插入一条直线，直线由两个点定义
INSERT INTO sdx.linedata (name,geom) VALUES (
'xian',
ST_GeomFromText('MULTILINESTRING((108.12988184124956 34.4826445579113,108.6313532482283 34.621843026752366,108.64919920577202 34.65039655882233,108.6509838015264 34.71821119748849,108.64741461001765 34.73784175078659))',4326)
);
INSERT INTO sdx.linedata (name, geom)
VALUES (
'example_line',
ST_SetSRID(ST_GeomFromText('LINESTRING(108.12988184124956 34.4826445579113, 108.13988184124956 34.4926445579113)'), 4326)
);
-- 点数据
INSERT INTO sdx.pointdata (name, geom)
VALUES (
'xiandian',
ST_SetSRID(ST_MakePoint(108.12988184124956, 34.4826445579113), 4326)
);
-- 面数据
INSERT INTO sdx.polygondata (name, geom)
VALUES (
'example_polygon',
ST_SetSRID(ST_GeomFromText('POLYGON((108.12988184124956 34.4826445579113, 108.13988184124956 34.4826445579113, 108.13988184124956 34.4926445579113, 108.12988184124956 34.4926445579113, 108.12988184124956 34.4826445579113))'), 4326)
);
-- ST_MakePoint(x, y)：用于创建一个点对象，x 是经度，y 是纬度。
-- ST_SetSRID(geometry, srid)：设置空间参考系统的 ID。空间索引：为了提高空间查询的性能，建议在 geom 列上创建一个空间索引：
-- GIST（Generalized Search Tree）是处理空间数据的索引类型。
CREATE INDEX idx_geom ON sdx.pointdata USING GIST (geom);
-- 获取几何数据的SRID
SELECT ST_SRID(geom) FROM sdx.linedata LIMIT 1;-- 查看三维线
SELECT ST_AsText(ST_MakeLine(ST_MakePoint(x, y, elevation))) AS wkt from sdx.tdm1_dem_profile-- 查询插值点
WITH line AS (
select geom from sdx.linedata
)
SELECT
ST_LineInterpolatePoint(
ST_LineMerge(geom),
generate_series(0, 1, 0.001)
) AS point_geom
FROM line;-- 查询该点的海拔
WITH point_geoms AS(
SELECT ST_GeomFromText('POINT(108.13606692491066 34.48436141387417)',4326) as point_geom
)
SELECT
point_geom,
ST_Value(rast, ST_Transform(point_geom, ST_SRID(rast))) AS elevation
FROM sdx.tdm1_dem, point_geoms
WHERE ST_Intersects(rast, ST_Transform(point_geom, ST_SRID(rast)))
-- 查询海拔
WITH line AS (
select geom from sdx.linedata
),
sample_points AS (
SELECT
ST_LineInterpolatePoint(
ST_LineMerge(geom),
generate_series(0, 1, 0.01)
) AS point_geom
FROM line
),
elevations AS (
SELECT
point_geom,
ST_Value(rast, ST_Transform(point_geom, ST_SRID(rast))) AS elevation
FROM sdx.tdm1_dem, sample_points
WHERE ST_Intersects(rast, ST_Transform(point_geom, ST_SRID(rast)))
)
SELECT
ST_AsText(point_geom) AS geom,
elevation
FROM elevations
WHERE elevation IS NOT NULL;
-- 或是
WITH line AS (
SELECT ST_GeomFromText('MULTILINESTRING((108.12988184124956 34.4826445579113,108.6313532482283 34.621843026752366,108.64919920577202 34.65039655882233,108.6509838015264 34.71821119748849,108.64741461001765 34.73784175078659))', 4326) AS geom -- 确保SRID与栅格一致
),
sample_points AS (
SELECT
ST_LineInterpolatePoint(
ST_LineMerge(geom),
generate_series(0, 1, 0.01)
) AS point_geom
FROM line
),
elevations AS (
SELECT
point_geom,
ST_Value(rast, ST_Transform(point_geom, ST_SRID(rast))) AS elevation
FROM sdx.tdm1_dem, sample_points
WHERE ST_Intersects(rast, ST_Transform(point_geom, ST_SRID(rast)))
)
SELECT
point_geom AS geom,
--ST_AsText (point_geom) AS geom,
ST_X(point_geom) AS x,
ST_Y(point_geom) AS y,
elevation
FROM elevations
WHERE elevation IS NOT NULL;-- 查询出一条直线
SELECT ST_MakeLine(geom) AS line
FROM (
SELECT geom
FROM sdx.tdm1_dem_profile
ORDER BY id ASC
) AS ordered_points;
-- 查询出一条直线
SELECT ST_AsEWKB(ST_MakeLine(geom)) AS line
FROM (
SELECT geom
FROM sdx.tdm1_dem_profile
ORDER BY id ASC
) AS ordered_points;
-- 类型转换
SELECT CAST(str_column AS INTEGER) FROM your_table;
-- 或者使用类型转换操作符
SELECT str_column::INTEGER FROM your_table;
-- 统计
WITH gender_count AS (
SELECT
SUM(CASE WHEN xb = '男' THEN 1 ELSE 0 END) AS male_count,
SUM(CASE WHEN xb = '女' THEN 1 ELSE 0 END) AS female_count
FROM tyl_fcfh
WHERE nl ~ '^[0-9]+$' AND xb IN ('男', '女') AND xqbh='1' AND ldhh LIKE '1-1%'
)
SELECT
male_count || ':' || female_count AS gender_ratio
FROM gender_count;-- 首先，添加新字段
ALTER TABLE tyl_poi ADD COLUMN tid INTEGER;
-- 然后，为新字段添加注释
COMMENT ON COLUMN tyl_poi.tid IS '表主键';
-- 删除，表字段
ALTER TABLE tyl_poi DROP COLUMN tid;
-- 1. 添加自增ID字段（假设使用SERIAL类型，它会自动创建一个序列并为主键列提供值）
ALTER TABLE my_table ADD COLUMN id SERIAL PRIMARY KEY;
-- 或者，如果你想要先添加字段，然后再设置为主键：
-- 2.1 添加字段（不设置为主键）
ALTER TABLE my_table ADD COLUMN id SERIAL;
-- 2.2 将字段设置为主键
ALTER TABLE my_table ADD PRIMARY KEY (id);
-- 使用GENERATED ALWAYS AS IDENTITY添加自增ID字段作为主键
ALTER TABLE tyl_poi ADD COLUMN tid INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY;
COMMENT ON COLUMN tyl_poi.tid IS '表主键';-- 首先，添加新字段
ALTER TABLE tyl_poi ADD COLUMN area character varying(255);
-- 然后，为新字段添加注释
COMMENT ON COLUMN tyl_poi.area IS '面积';
-- 最后，更新新字段的数据
UPDATE tyl_poi AS B
SET area = A.area
FROM tyl_dmdzp AS A
WHERE A.entity_nam = B.name
AND B.area IS NULL;