当前位置: 首页 > 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…...

调用支付宝接口响应40004 SYSTEM_ERROR问题排查

在对接支付宝API的时候&#xff0c;遇到了一些问题&#xff0c;记录一下排查过程。 Body:{"datadigital_fincloud_generalsaas_face_certify_initialize_response":{"msg":"Business Failed","code":"40004","sub_msg…...

Redis相关知识总结(缓存雪崩,缓存穿透,缓存击穿,Redis实现分布式锁,如何保持数据库和缓存一致)

文章目录 1.什么是Redis&#xff1f;2.为什么要使用redis作为mysql的缓存&#xff1f;3.什么是缓存雪崩、缓存穿透、缓存击穿&#xff1f;3.1缓存雪崩3.1.1 大量缓存同时过期3.1.2 Redis宕机 3.2 缓存击穿3.3 缓存穿透3.4 总结 4. 数据库和缓存如何保持一致性5. Redis实现分布式…...

前端倒计时误差!

提示:记录工作中遇到的需求及解决办法 文章目录 前言一、误差从何而来?二、五大解决方案1. 动态校准法(基础版)2. Web Worker 计时3. 服务器时间同步4. Performance API 高精度计时5. 页面可见性API优化三、生产环境最佳实践四、终极解决方案架构前言 前几天听说公司某个项…...

ssc377d修改flash分区大小

1、flash的分区默认分配16M、 / # df -h Filesystem Size Used Available Use% Mounted on /dev/root 1.9M 1.9M 0 100% / /dev/mtdblock4 3.0M...

pam_env.so模块配置解析

在PAM&#xff08;Pluggable Authentication Modules&#xff09;配置中&#xff0c; /etc/pam.d/su 文件相关配置含义如下&#xff1a; 配置解析 auth required pam_env.so1. 字段分解 字段值说明模块类型auth认证类模块&#xff0c;负责验证用户身份&am…...

MVC 数据库

MVC 数据库 引言 在软件开发领域,Model-View-Controller(MVC)是一种流行的软件架构模式,它将应用程序分为三个核心组件:模型(Model)、视图(View)和控制器(Controller)。这种模式有助于提高代码的可维护性和可扩展性。本文将深入探讨MVC架构与数据库之间的关系,以…...

linux 错误码总结

1,错误码的概念与作用 在Linux系统中,错误码是系统调用或库函数在执行失败时返回的特定数值,用于指示具体的错误类型。这些错误码通过全局变量errno来存储和传递,errno由操作系统维护,保存最近一次发生的错误信息。值得注意的是,errno的值在每次系统调用或函数调用失败时…...

鸿蒙中用HarmonyOS SDK应用服务 HarmonyOS5开发一个医院查看报告小程序

一、开发环境准备 ​​工具安装​​&#xff1a; 下载安装DevEco Studio 4.0&#xff08;支持HarmonyOS 5&#xff09;配置HarmonyOS SDK 5.0确保Node.js版本≥14 ​​项目初始化​​&#xff1a; ohpm init harmony/hospital-report-app 二、核心功能模块实现 1. 报告列表…...

多模态大语言模型arxiv论文略读(108)

CROME: Cross-Modal Adapters for Efficient Multimodal LLM ➡️ 论文标题&#xff1a;CROME: Cross-Modal Adapters for Efficient Multimodal LLM ➡️ 论文作者&#xff1a;Sayna Ebrahimi, Sercan O. Arik, Tejas Nama, Tomas Pfister ➡️ 研究机构: Google Cloud AI Re…...

高防服务器能够抵御哪些网络攻击呢?

高防服务器作为一种有着高度防御能力的服务器&#xff0c;可以帮助网站应对分布式拒绝服务攻击&#xff0c;有效识别和清理一些恶意的网络流量&#xff0c;为用户提供安全且稳定的网络环境&#xff0c;那么&#xff0c;高防服务器一般都可以抵御哪些网络攻击呢&#xff1f;下面…...