当前位置: 首页 > news >正文

3 PostGIS基础查询

PostGIS 基础查询

数据库维护

ps aux | grep postgres

psql

使用命令登录数据库psql -U postgres -d testdb -h localhost -p 5432postgres用户名,testdb数据库名称,localhost ip地址,可以省略,5432端口,可以省略。

\h :查看 SQL 命令的解释,比如 \h select 。
\? :查看 psql 命令列表。
\l :列出所有数据库。
\c [database_name] :连接其他数据库。
\d :列出当前数据库的所有表格。
\d [table_name] :列出某一张表格的结构。
\du :列出所有用户。
\e :打开文本编辑器。
\conninfo :列出当前数据库和连接的信息\、

数据库版本

select version();--查询postgresql版本
SELECT PostGIS_full_version(); --查询postgis版本
SHOW data_directory;-- 查询数据文件安装路径

数据分析

select pg_conf_load_time(); -- 查询加载配置时间
select pg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location()); --查看当前wal的buffer中有多少字节没有写入到磁盘中
--- 备注:需要开启pg_stat_statements
select * from pg_stat_statements order by total_time desc limit 5;  -- 查询最耗时的5个sql-- 获取执行时间最慢的3条SQL,并给出CPU占用比例
SELECT substring(query, 1, 1000) AS short_query,
round(total_time::numeric, 2) AS total_time,
calls,
round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 3;EXPLAIN ANALYZE SELECT * FROM product; -- 分析sqk执行情况

查看当前长时间执行却不结束的SQL

select datname, usename, client_addr, application_name, state, backend_start, xact_start, xact_stay, query_start, query_stay, replace(query, chr(10), ' ') as query from (select pgsa.datname as datname, pgsa.usename as usename, pgsa.client_addr client_addr, pgsa.application_name as application_name, pgsa.state as state, pgsa.backend_start as backend_start, pgsa.xact_start as xact_start, extract(epoch from (now() - pgsa.xact_start)) as xact_stay, pgsa.query_start as query_start, extract(epoch from (now() - pgsa.query_start)) as query_stay , pgsa.query as query from pg_stat_activity as pgsa where pgsa.state != 'idle' and pgsa.state != 'idle in transaction' and pgsa.state != 'idle in transaction (aborted)') idleconnections order by query_stay desc limit 5;

查出使用表扫描最多的表

select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;

查询读取buffer最多的5个SQL

select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;

获取数据库当前的回滚事务数以及死锁数

select datname,xact_rollback,deadlocks from pg_stat_database

查询指定表的慢查询

select * from pg_stat_activity where query ilike '%<table_name>%' and query_start - now() > interval '10 seconds';

数据库链接

select count(*) from pg_stat_activity; -- 查询数据库链接数量
show max_connections; -- 获取数据库最大连接数
select * from pg_stat_activity; -- 当前链接数详细信息
select usename, count(*) from pg_stat_activity group by usename;  -- 查询数据库中各个用户名对应的数据库连接数

权限部分

创建数据库与授权

---------------------------------建库基本流程----------------------------------------------------
create user eas PASSWORD 'eas_Ytkj2023'; --创建用户eas 密码为eas_Ytkj@2023
CREATE DATABASE xiyimpaf;  --创建数据库
create schema ebms;  --创建schema
grant all on  database xiyimpaf to eas; --数据库授权 给eas
grant select on all tables in schema public to eas; -- table和schema授权给eas
--------------------------------------------------------------------------------------

设置某个(些)表的Owner身份

ALTER TABLE tb_menu OWNER TO jtzq;  --  设置tb_menu所属owner 给jtzq,解决删除报错问题
grant all privileges on table product to username --- 授予予指定用户username指定表product的所有权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;   -- 授予指定用户所有表的所有权限--将所有表owner权限给jtzq
SELECT'Alter table ' || d.relname || ' owner to jtzq;' 
FROM(SELECT C.relnameFROMpg_class C WHEREC.relname IN ( SELECT tablename FROM pg_tables WHERE tablename NOT LIKE'pg%' AND tablename NOT LIKE'sql_%'  and tablename not like 'spatial%' ) ORDER BYC.relname )d

表字段索引

添加字段

alter table user_role(表命) add column createby(字段名) varchar(50) ;  --创建名
comment on column user_role.createby  is '创建人';
alter table user_role add column  createtime timestamp(0); 
comment on column user_role.createtime is '创建时间';
alter table user_role add column updateby varchar(50) ;
comment  on column user_role.updateby is '更新人';
alter table user_role add column updatetime timestamp(0) ;
comment on column user_role.updatetime is '更新时间';

用户查询

SELECT * FROM pg_roles;--查看所有已有角色及其权限(角色就是用户)
SELECT * FROM pg_user;
CREATE ROLE tigger LOGIN inherit; -- 创建角色 tigger,并设置权限(参考其他库的用户权限)
select * from pg_tables; -- 获取数据库中所有表
-- 查询数据库中所有表及其描述
select relname as TABLE_NAME ,col_description(c.oid, 0) as COMMENTS from pg_class c where relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%'; 

索引

select * from pg_indexes where tablename = 'product'; -- 获取表product的所有索引

PG扩展

查询

select name from pg_available_extensions;  -- 查询那些扩展可用
select * from pg_extension; -- 查询已经打开的扩展

添加

// 添加空间数据库的相关插件
CREATE EXTENSION postgis;					//用于创建空间数据库的插件,仅支持矢量数据扩展
CREATE EXTENSION address_standardizer;		//使空间数据库支持地址标准化
CREATE EXTENSION ogr_fdw;					//使空间数据库支持不同数据库之间的跨库操作
CREATE EXTENSION pgrouting;					//使空间数据库支持网络分析
CREATE EXTENSION pointcloud;				//使空间数据库支持点云数据存储
CREATE EXTENSION pointcloud_postgis;		//使空间数据库支持点云数据操作
CREATE EXTENSION postgis_raster;			//使空间数据库支持栅格数据扩展
CREATE EXTENSION postgis_sfcgal;			//使空间数据库支持2D和3D的数据操作
CREATE EXTENSION fuzzystrmatch;				//使空间数据库支持地理编码的模糊匹配
CREATE EXTENSION postgis_tiger_geocoder;	//使空间数据库支持地理编码
CREATE EXTENSION postgis_topology;			//使空间数据库支持拓扑检查

删除

DROP extension postgis cascade;

数据大小

select pg_size_pretty (pg_database_size('db_product')); -- 查询执行数据库大小
select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database; -- 查询数据库实例中各个数据库大小
select pg_size_pretty(pg_relation_size('geo_wind')) as size; -- 查询表达数据大小
select pg_size_pretty(pg_indexes_size('geo_wind')); --查询表中索引大小
-- 获取各个表中的数据记录数
select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' and relname  not like 'pg_%' and relname not like 'sql_%'; order by rowCounts desc;
select pg_relation_filepath('geo_wind'); -- 查看数据库表对应的数据文件

坐标系

查询表的坐标系

select * from geometry_columns  where f_table_name in('jsline','matrix'); --jsline,matrix是表名select st_srid(geom) from jsline  --获取表的空间srid update t_noisemap set geometry =ST_SetSRID(geometry, 0)  --更新空间参考select updategeometrysrid('jsline','geom',900913);  --更新表空间参考

数据库备份

1、备份postgres库并tar打包

pg_dump -h 127.0.0.1 -p 5432 -U postgres -f postgres.sql.tar -Ft

2、备份postgres库,转储数据为带列名的INSERT命令

pg_dumpall -d postgres -U postgres -f postgres.sql --column-inserts

1、备份postgres库并tar打包

pg_dump -h 127.0.0.1 -p 5432 -U postgres -f postgres.sql.tar -Ft

2、备份postgres库,转储数据为带列名的INSERT命令

pg_dumpall -d postgres -U postgres -f postgres.sql --column-inserts

相关文章:

3 PostGIS基础查询

PostGIS 基础查询 数据库维护 ps aux | grep postgrespsql 使用命令登录数据库psql -U postgres -d testdb -h localhost -p 5432postgres用户名&#xff0c;testdb数据库名称&#xff0c;localhost ip地址&#xff0c;可以省略&#xff0c;5432端口&#xff0c;可以省略。 …...

Shell错误:/bin/bash^M: bad interpreter: No such file or directory

目录 错误原因和现象 解决方案 错误原因和现象 在执行shell脚本的时候&#xff0c;报错&#xff1a;/bin/bash^M: bad interpreter: No such file or directory。 是由于该脚本文件是在Windows平台编写&#xff0c;然后在MacOS平台中执行。 在Windows平台上文件是dos格式&…...

Golang之路---01 Golang的安装与配置

Golang之路—01 Golang语言安装与配置 官网上下载Windows环境下的安装包 官网下载地址 双击下载后的文件进行安装&#xff0c;可根据需要自定义选择解压后的文件位置。 接着新创建一个文件夹&#xff0c;保存Golang语言项目。 在里面新建bin,pkg,src三个文件夹。 环境变量…...

Anolis OS 8.8服务器采用docker容器方式搭建gerrit3.8.1服务

采用docker容器方式搭建gerrit3.8.1服务 一、选择管理帐户密码的方式二、部署gerrit服务1. 采用docker compose部署单服务的方式部分gerrit(1) docker-compose.yaml文件内容(2) 在docker-compose.yaml文件所在目录调用下面命令先进行初始化操作 2. 在宿主机上部署httpd服务用于…...

PyTorch 中的多 GPU 训练和梯度累积作为替代方案

动动发财的小手&#xff0c;点个赞吧&#xff01; 在本文[1]中&#xff0c;我们将首先了解数据并行&#xff08;DP&#xff09;和分布式数据并行&#xff08;DDP&#xff09;算法之间的差异&#xff0c;然后我们将解释什么是梯度累积&#xff08;GA&#xff09;&#xff0c;最后…...

Appium+python自动化(三十五)- 命令启动appium之 appium服务命令行参数(超详解)

简介 前边介绍的都是通过按钮点击启动按钮来启动appium服务&#xff0c;有的小伙伴或者童鞋们乍一听可能不信&#xff0c;或者会问如何通过命令行启动appium服务呢&#xff1f;且听一一道来。 一睹为快 其实相当的简单&#xff0c;不看不知道&#xff0c;一看吓一跳&#xf…...

vmware的window中安装GNS3

1.向vmware中的windows虚拟机传送文件 点击虚拟机-安装VMwaretools 安装在虚拟机上面 此图标代表已经成功&#xff0c;将文件复制到虚拟机上里面 2.安装 安装gns3&#xff0c;需要先安装winpcap&#xff08;检查网卡&#xff09;和wireshark&#xff08;对winpcap上数据进行抓…...

FPGA XDMA 中断模式实现 PCIE3.0 AD7606采集 提供2套工程源码和QT上位机源码

目录 1、前言2、我已有的PCIE方案3、PCIE理论4、总体设计思路和方案AD7606数据采集和缓存XDMA简介XDMA中断模式QT上位机及其源码 5、vivado工程1--BRAM缓存6、vivado工程2--DDR4缓存7、上板调试验证8、福利&#xff1a;工程代码的获取 1、前言 PCIE&#xff08;PCI Express&am…...

某某大学某学院后台Phar反序列化GetShell

觉得这个洞还算有点意思&#xff0c;可以记录一下 首先在另一个二级学院进行目录扫描时发现源码www.rar&#xff0c;并且通过一些页面测试推测这两个二级学院应该是使用了同一套CMS 分析源码&#xff0c;发现使用的是ThinkPHP 5.1.34 LTS框架 通过APP、Public得到后台访问路径…...

【ChatGPT辅助学Rust | 基础系列 | 基础语法】变量,数据类型,运算符,控制流

文章目录 简介&#xff1a;一&#xff0c;变量1&#xff0c;变量的定义2&#xff0c;变量的可变性3&#xff0c;变量的隐藏 二、数据类型1&#xff0c;标量类型2&#xff0c;复合类型 三&#xff0c;运算符1&#xff0c;算术运算符2&#xff0c;比较运算符3&#xff0c;逻辑运算…...

使用云服务器和Frp(快速反向代理)框架快速部署实现内网穿透

目录 一. 背景1.1 内网穿透1.2 Frp介绍1.3 Frp配置流程 二. 云服务器配置2.1 配置安全组2.2 编写frps.ini 三. 内网主机配置3.1 编辑frpc.ini文件3.2 启动服务并配置开机自启动 四. 参考文献 一. 背景 现在有一台ubuntu云服务器&#xff0c;我想通过内网穿透将一台内网的主机当…...

Mac 上使用 Tesseract OCR 识别图片文本

Tesseract OCR 引擎&#xff1a;Tesseract是一个开源的OCR引擎&#xff0c;你需要先安装它。可以从Tesseract官方网站&#xff08;https://github.com/tesseract-ocr/tesseract&#xff09;下载适用于你的操作系统的安装程序或源代码&#xff0c;并按照官方文档进行安装。 Tes…...

《MapboxGL 基础知识点》- 放大/缩小/定位/级别

中心点 getCenter&#xff1a;获取中心点 const {lng, lat} map.getCenter(); setCenter&#xff1a;设置中心点 // lng, lat map.setCenter([134, 28]); 缩放级别 getZoom&#xff1a;获取当前缩放级别 map.getZoom(); setZoom&#xff1a;设置缩放级别 map.setZoom(5…...

VScode的简单使用

一、VScode的安装 Visual Studio Code简称VS Code&#xff0c;是一款跨平台的、免费且开源的现代轻量级代码编辑器&#xff0c;支持几乎主流开发语言的语法高亮、智能代码补全、自定义快捷键、括号匹配和颜色区分、代码片段提示、代码对比等特性&#xff0c;也拥有对git的开箱…...

# Unity 如何获取Texture 的内存大小

Unity 如何获取Texture 的内存大小 在Unity中&#xff0c;要获取Texture的内存文件大小&#xff0c;可以使用UnityEditor.TextureUtil类中的一些函数。这些函数提供了获取存储内存大小和运行时内存大小的方法。由于UnityEditor.TextureUtil是一个内部类&#xff0c;我们需要使…...

dolphinscheduler switch+传参无坑版

dolphinscheduler 的前后传参有较多的坑&#xff0c;即便是3.0.5版本仍然有一些bug 下面是目前能无坑在3.0.5版本上使用的操作 前置任务 在界面上设置变量和参数名称 跟官方网站不一样&#xff0c;注意最后一行一定使用echo ${setValue(key$query)}的方式&#xff0c;注意引…...

VINS-fusion安装

VINS-fusion中用的opencv3&#xff0c;如果安装的opencv4要做一系列替换 VINS-Mono在opencv4环境下的安装问题和解决方法 https://zhuanlan.zhihu.com/p/548140724 Vins-Fusion安装记录 https://zhuanlan.zhihu.com/p/432167383 CV_FONT_HERSHEY_SIMPLEX -> cv::FONT_HER…...

智慧消防:如何基于视频与智能分析技术搭建可视化风险预警平台?

一、背景分析 消防安全是一个重要的话题&#xff0c;涉及到每个人的生活和安全。每年都会发生大量的火灾&#xff0c;给人们带来极大的危害&#xff0c;摧毁了大量的财产&#xff0c;甚至造成了可怕的人员伤亡。而消防安全监督管理部门人员有限&#xff0c;消防安全监管缺乏有…...

selenium定位元素的方法

Selenium可以驱动浏览器完成各种操作&#xff0c;比如模拟点击等。要想操作一个元素&#xff0c;首先应该识别这个元素。人有各种的特征&#xff08;属性&#xff09;&#xff0c;我们可以通过其特征找到人&#xff0c;如通过身份证号、姓名、家庭住址。同理&#xff0c;一个元…...

RISC-V特权级别

特权级别 RISC-V共有6个特权级别&#xff1a; 机器模式&#xff08;M模式&#xff09; M模式全称为Machine mode&#xff08;机器模式&#xff09;运行在这个模式下的程序为最高权限&#xff0c;它属于RISC-V里的最高权限模式&#xff0c;它具有访问所有资源的权限&#xff…...

使用docker在3台服务器上搭建基于redis 6.x的一主两从三台均是哨兵模式

一、环境及版本说明 如果服务器已经安装了docker,则忽略此步骤,如果没有安装,则可以按照一下方式安装: 1. 在线安装(有互联网环境): 请看我这篇文章 传送阵>> 点我查看 2. 离线安装(内网环境):请看我这篇文章 传送阵>> 点我查看 说明&#xff1a;假设每台服务器已…...

C++初阶-list的底层

目录 1.std::list实现的所有代码 2.list的简单介绍 2.1实现list的类 2.2_list_iterator的实现 2.2.1_list_iterator实现的原因和好处 2.2.2_list_iterator实现 2.3_list_node的实现 2.3.1. 避免递归的模板依赖 2.3.2. 内存布局一致性 2.3.3. 类型安全的替代方案 2.3.…...

Lombok 的 @Data 注解失效,未生成 getter/setter 方法引发的HTTP 406 错误

HTTP 状态码 406 (Not Acceptable) 和 500 (Internal Server Error) 是两类完全不同的错误&#xff0c;它们的含义、原因和解决方法都有显著区别。以下是详细对比&#xff1a; 1. HTTP 406 (Not Acceptable) 含义&#xff1a; 客户端请求的内容类型与服务器支持的内容类型不匹…...

R语言AI模型部署方案:精准离线运行详解

R语言AI模型部署方案:精准离线运行详解 一、项目概述 本文将构建一个完整的R语言AI部署解决方案,实现鸢尾花分类模型的训练、保存、离线部署和预测功能。核心特点: 100%离线运行能力自包含环境依赖生产级错误处理跨平台兼容性模型版本管理# 文件结构说明 Iris_AI_Deployme…...

可靠性+灵活性:电力载波技术在楼宇自控中的核心价值

可靠性灵活性&#xff1a;电力载波技术在楼宇自控中的核心价值 在智能楼宇的自动化控制中&#xff0c;电力载波技术&#xff08;PLC&#xff09;凭借其独特的优势&#xff0c;正成为构建高效、稳定、灵活系统的核心解决方案。它利用现有电力线路传输数据&#xff0c;无需额外布…...

VTK如何让部分单位不可见

最近遇到一个需求&#xff0c;需要让一个vtkDataSet中的部分单元不可见&#xff0c;查阅了一些资料大概有以下几种方式 1.通过颜色映射表来进行&#xff0c;是最正规的做法 vtkNew<vtkLookupTable> lut; //值为0不显示&#xff0c;主要是最后一个参数&#xff0c;透明度…...

使用 SymPy 进行向量和矩阵的高级操作

在科学计算和工程领域&#xff0c;向量和矩阵操作是解决问题的核心技能之一。Python 的 SymPy 库提供了强大的符号计算功能&#xff0c;能够高效地处理向量和矩阵的各种操作。本文将深入探讨如何使用 SymPy 进行向量和矩阵的创建、合并以及维度拓展等操作&#xff0c;并通过具体…...

网站指纹识别

网站指纹识别 网站的最基本组成&#xff1a;服务器&#xff08;操作系统&#xff09;、中间件&#xff08;web容器&#xff09;、脚本语言、数据厍 为什么要了解这些&#xff1f;举个例子&#xff1a;发现了一个文件读取漏洞&#xff0c;我们需要读/etc/passwd&#xff0c;如…...

动态 Web 开发技术入门篇

一、HTTP 协议核心 1.1 HTTP 基础 协议全称 &#xff1a;HyperText Transfer Protocol&#xff08;超文本传输协议&#xff09; 默认端口 &#xff1a;HTTP 使用 80 端口&#xff0c;HTTPS 使用 443 端口。 请求方法 &#xff1a; GET &#xff1a;用于获取资源&#xff0c;…...

提升移动端网页调试效率:WebDebugX 与常见工具组合实践

在日常移动端开发中&#xff0c;网页调试始终是一个高频但又极具挑战的环节。尤其在面对 iOS 与 Android 的混合技术栈、各种设备差异化行为时&#xff0c;开发者迫切需要一套高效、可靠且跨平台的调试方案。过去&#xff0c;我们或多或少使用过 Chrome DevTools、Remote Debug…...