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;