PostGre数据库操作

news/2024/7/9 20:32:51 标签: 数据库, postgresql, sql

菜鸟教程

PostgreSQL 教程 | 菜鸟教程PostgreSQL 教程 PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。 PostgreSQL 开发者把它念作 post-gress-Q-L。 PostgreSQL 的 Slogan 是 “世界上最先进的开源关系型数据库”。 参考内容：PostgreSQL 10.1 手册 什么是数据库？ 数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。 每个数据库都..https://www.runoob.com/sql>postgresql/sql>postgresql-tutorial.html

1、字符串函数的操作,如:concat的连接

PostgreSQL提供了许多有用的字符串函数,可以用于处理和操作字符串数据.

  • LENGTH(String):返回给定字符串的字符数,例如:SELECT length('hello')

  • SUBSTRIBNG(String,from,to):返回从字符串中指定位置开始的子字符串.例如:以下命令将从"hello"字符串的第二个字符开始返回一个长度为3的子字符串:SELECT substring('hello', 2 , 3)

  • CONCAT(string1,string2):将两个字符串连接在一起,并返回一个新的字符串.例如,以下命令将返回"Hello World"字符串:SELECT concat('Hello','World')

  • UPPER(string):该函数将字符串里的所有字符转换为大写字母,并返回一个新的字符串.例如:以下命令将返回"HELLO"字符串:SELECT upper('Hello')

  • LOWER(string):该函数将字符串里的所有字符转换为小写字母,并返回一个新的字符串,例如:以下命令将返回"hello"字符串:SELECT lower('HELLO')

  • TRIM(string):该函数从字符串的开始和结束位置删除所有空格,并返回一个新的字符串.例如:以下命令将返回"Hello"字符串:SELECT TRIM(' Hello ')

  • REPLACE(string,old,new):该函数将字符串里的所有旧字符替换为新字符串,并返回一个新的字符串.例如:以下命令将返回"Hello,World"字符串:SELECT "replace"('Hello,John', 'John', 'World')

2.求几何对象中心点,长度,面积等函数

  • 中心点:ST_Centroid(geom):该函数返回几何对象的中心点.例如:以下命令将返回一个点,该点是多边形的中心点:SELECT st_centroid('polygon((0 0,0 1,1 1,1 0,0 0))'::geometry)

  • 长度:ST_Length(geom):该函数返回几何对象的长度.例如:以下命令将返回一个线段的长度:SELECT st_length('LINESTRING(0 0,0 1,1 1,1 0)'::geometry)

  • 面积:ST_Area(geom):该函数返回几何对象的面积.例如:以下命令将返回一个多边形的面积:SELECT st_Area('polygon((0 0,0 1,1 1,1 0,0 0))'::geometry)

3.返回前端geojson函数

在查询里使用ST_AsGeoJSON函数,该函数将几何对象转换为GeoJSON格式.例如:将一个名字为"mytable"表里的所有几何对象的GeoJSON表示:SELECT st_asgeojson(geom) FROM mytable

4.空间相交之类的图层间操作的空间函数

使用PostGIS扩展里提供的空间函数来执行图层间的空间操作.

  • ST_Intersects(geom1,geom2):该函数返回一个布尔值,表示两个几何对象是否相交.例如:以下命令将返回一个表里所有与另一个表里的几何对象相交的行:SELECT * from table1 as a , table2 as b WHERE ST_Intersects(a.geom, b.geom)

  • ST_Contains(geom1,geom2):该函数返回一个布尔值,表示一个几何对象是否包含另一个几何对象.例如:以下命令将返回一个表里所有与另一个表里的几何对象相互包含的行:SELECT * from table1 as a , table2 as b WHERE ST_Contains(b.geom, a.geom)

  • ST_Distance(geom1,geom2):该函数返回两个几何对象之间的距离.例如:以下命令将返回两个表里所有几何对象之间的距离:SELECT ST_Distance(a.geom, b.geom) from table1 as a , table2 as b;

  • ST_Within(geom1,geom2):该函数返回一个布尔值,表示一个几何对象是否被另一个几何对象包含.例如:以下命令将返回一个表里所有被另一个表里的几何对象包含的行:SELECT * from table1 as a , table2 as b WHERE ST_Within(a.geom, b.geom)

  • ST_Crosses(geom1,geom2):该函数返回一个布尔值,表示两个几何对象是否相交,但不包含或被包含.例如:以下命令将返回一个表里所有被另一个表里的几何对象相交但不包含或被包含的行:SELECT * from table1 as a , table2 as b WHERE ST_Crosses(a.geom, b.geom)

  • ST_Overlaps(geom1,geom2):该函数返回一个布尔值,表示两个几何对象是否重叠.例如:以下命令将返回一个表里所有与另一个表中的几何对象重叠的行:SELECT * from table1 as a , table2 as b WHERE ST_Overlaps(a.geom, b.geom)

5.计算空间距离的相关函数

在PostgreSQL中，可以使用PostGIS扩展中提供的空间函数来计算空间距离。以下是一些常用的函数：

  • ST_Distance(geom1, geom2)该函数返回两个几何对象之间的距离，以与几何对象的投影单位相同的单位表示。例如，以下命令将返回一个表中所有几何对象之间的距离：SELECT ST_Distance(geom1, geom2) FROM mytable;

  • ST_DistanceSphere(geom1, geom2)该函数返回两个几何对象之间的球面距离，以度为单位表示。例如，以下命令将返回一个表中所有几何对象之间的球面距离：SELECT ST_DistanceSphere(geom1, geom2) FROM mytable;

  • ST_DistanceSpheroid(geom1, geom2, spheroid)该函数返回两个几何对象之间的椭球面距离，以与椭球体的单位相同的单位表示。它需要一个椭球体参数，用于指定椭球体的参数，例如长半轴、短半轴和扁率。例如，以下命令将返回一个表中所有几何对象之间的椭球面距离：SELECT ST_DistanceSpheroid(geom1, geom2, 'SPHEROID["GRS 1980",6378137,298.257222101]') FROM mytable;

  • ST_Length(geom)该函数返回一个几何对象的长度，以与几何对象的投影单位相同的单位表示。例如，以下命令将返回一个表中所有几何对象的长度：SELECT ST_Length(geom) FROM mytable;

6.生成缓冲区的相关函数

在PostgreSQL里,可以使用PostGIS扩展里提供的空间函数来生成缓冲区.常用函数如下:

  • ST_Buffer (geom,radius):该函数返回一个表里所有几何对象的缓冲区,其中"radius"参数指定缓冲区的半径.例如,以下命令将返回一个表里所有几何对象的缓冲区;SELECT ST_Buffer (geom,100) from table
  • ST_DumpPoints(geom):该函数将一个几何对象分解为多个点,并返回一个点集合.例如,以下命令将返回一个表里所有几何对象的点集合:SELECT (ST_DumpPoints(geom)).geom from table
  • ST_ConvexHull(geom):该函数返回一个集合对象的凸包多边形.例如,以下命令将返回一个表里所有几何对象的凸包多边形:SELECT ST_ConvexHull(geom) FROM table;
  • ST_Buffer(geom,radius,num_seg_quarter_circle):该函数返回一个几何对象的缓冲区,其中"radius"参数指定缓冲区的半径,"num_seg_quarter_circle"参数指定每个缓冲区象限里的线段数.例如,以下命令将返回一个表里所有几何对象的缓冲区:SELECT ST_Buffer(geom, 100, 8) FROM table;

7.认识geojson,geometry,WKT描述三种格式

GeoJSON、Geometry和WKT（Well-Known Text）都是用于描述空间数据的格式。

GeoJSON

GeoJSON是一种基于JSON（JavaScript Object Notation）格式的地理空间数据交换格式，它支持点、线、面等几何类型，以及它们的属性信息，通常用于Web应用程序和API中。

GeoJSON示例：
{
 "type": "Feature",
 "geometry": {
  "type": "Point",
  "coordinates": [125.6, 10.1]
 },
 "properties": {
  "name": "Dinagat Islands"
 }
}
上述示例描述了一个名为“Dinagat Islands”的点特征，其坐标为[125.6, 10.1]。

Geometry

Geometry是一种常见的表示空间几何对象的格式，它由几何类型和坐标数组组成。例如，一个点的几何可以表示为：

{
 "type": "Point",
 "coordinates": [100.0, 0.0]
}
Geometry示例：
public class Point {
  private double x;
  private double y;
​
  public Point(double x, double y) {
    this.x = x;
    this.y = y;
  }
​
  public double getX() {
    return x;
  }
​
  public double getY() {
    return y;
  }
}
上述示例定义了一个Point类，用于表示一个二维点的几何信息。

WKT

WKT是一种用于描述空间几何对象的文本格式，它使用一定的约定规则表示几何对象的类型和坐标。例如，一个点的WKT表示为：

POINT(100.0 0.0) 需要注意的是，这三种格式中，GeoJSON和WKT都是文本格式，而Geometry则是一种数据结构。在实际使用中，我们可以将它们用于不同的应用场景，并根据需要进行转换。例如，可以使用GeoJSON格式来交换数据，使用Geometry格式在算法中进行计算，使用WKT格式与其他GIS软件进行交互。 以下是GeoJSON、Geometry和WKT描述三种格式的示例：

WKT示例：
POINT(125.6 10.1)
上述示例描述了一个名为“POINT”的点几何对象，其坐标为[125.6, 10.1]。

8.三种空间格式互相转换的函数

在GIS领域中，常见的三种空间格式（GeoJSON、Geometry和WKT）之间可以进行相互转换。以下是一些常见的转换函数及其示例：

依赖:

<dependency>
    <groupId>io.oss84.geotools</groupId>
    <artifactId>gt-geojson</artifactId>
    <version>24.2-oss84-1</version>
</dependency>

GeoJSON转Geometry

可以使用GeoTools库中的函数将GeoJSON格式转换为Geometry格式，示例代码：

//GeoJSON转Geometry
String geoJsonString = "{\"type\":\"Point\",\"coordinates\":[125.6, 10.1]}";
//创建GeometryJSON对象
GeometryJSON geometryJSON = new GeometryJSON();
//将GeometryJSON转为Geometry对象
Geometry geometry = geometryJSON.read(geoJsonString);
System.out.println(geometry.toString());//POINT (125.6 10.1)

上述示例将一个名为“Point”的GeoJSON格式字符串转换为Geometry对象。

Geometry转GeoJSON

可以使用GeoTools库中的函数将Geometry格式转换为GeoJSON格式，示例代码：

//Geometry转GeoJSON
//创建Geometry对象(以point为例子)
GeometryFactory geometryFactory = new GeometryFactory();
Geometry geometry1 = geometryFactory.createPoint(new Coordinate(125.6,10.1));
//创建GeometryJson对象
GeometryJSON geometryJSON1 = new GeometryJSON();
//将Geometry对象转换为GeoJSON格式的字符串
StringWriter stringWriter = new StringWriter();
geometryJSON1.write(geometry1,stringWriter);
//打印字符串
System.out.println(stringWriter.toString());//{"type":"Point","coordinates":[125.6,10.1]}

上述示例将一个名为“Point”的Geometry对象转换为GeoJSON格式字符串。

Geometry转WKT

可以使用JTS库中的函数将Geometry格式转换为WKT格式，示例代码：

Geometry geometry = new Point(125.6, 10.1); String wktString = geometry.toText();

//Geometry转WKT
//创建Geometry对象(以point为例子)
GeometryFactory geometryFactory = new GeometryFactory();
Geometry geometry = geometryFactory.createPoint(new Coordinate(125.6,10.1));
//创建WKTWriter对象
WKTWriter wktWriter = new WKTWriter();
//将Geometry对象转为WKT格式的字符串
String wktString = wktWriter.write(geometry);
System.out.println(wktString);//POINT (125.6 10.1)
​
//第二种办法
String wktString1 = geometry.toText();
System.out.println(wktString1);//POINT (125.6 10.1)

上述示例将一个名为“Point”的Geometry对象转换为WKT格式字符串。

WKT转Geometry

可以使用JTS库中的函数将WKT格式转换为Geometry格式，示例代码:

//WKT转Geometry
String wktString = "POINT(125.6 10.1)";
WKTReader wktReader = new WKTReader();
Geometry geometry = wktReader.read(wktString);
System.out.println(geometry.toString());//POINT (125.6 10.1)

上述示例将一个名为“POINT”的WKT格式字符串转换为Geometry对象。

需要注意的是，在实际应用中，转换函数的选择取决于具体的需求和使用场景。同时，转换过程中还需要考虑空间坐标系、精度等因素，以确保数据的正确性和可靠性。

9.建立索引进行优化

PostgreSQL是一种常见的关系型数据库管理系统，它支持多种类型的索引，包括B-tree索引、哈希索引、GiST索引、SP-GiST索引、GIN索引和BRIN索引等。以下是在PostgreSQL中建立索引进行优化的步骤：

选择合适的索引类型

在建立索引之前，需要选择合适的索引类型。常见的索引类型包括B-tree索引、哈希索引、GiST索引等，需要根据具体的需求进行选择。例如，B-tree索引适合对有序数据进行查询，而GiST索引适合对复杂数据类型进行查询。

对查询频率高的列建立索引

在建立索引时，需要优先考虑对查询频率高的列建立索引，以提高查询效率。可以使用CREATE INDEX语句创建索引，例如：

CREATE INDEX idx_name ON table_name (column_name); 上述示例在名为table_name的表的列column_name上创建了名为idx_name的索引。

对复合查询条件建立复合索引

如果查询条件包含多个列，那么可以使用复合索引进行优化。可以使用CREATE INDEX语句创建复合索引，例如：

CREATE INDEX idx_name ON table_name (column1, column2); 上述示例在名为table_name的表的列column1和column2上创建了名为idx_name的复合索引。

定期维护索引

索引需要定期维护，包括重建索引、优化索引等操作。可以使用REINDEX语句重建索引，例如：

REINDEX INDEX idx_name; 上述示例重建名为idx_name的索引。

避免过度索引

过度索引会占用大量的存储空间和计算资源，同时会影响数据的插入、更新、删除等操作。因此，需要避免过度索引，只对必要的列建立索引。

综上所述，通过选择合适的索引类型、对查询频率高的列建立索引、对复合查询条件建立复合索引、定期维护索引和避免过度索引等步骤，可以在PostgreSQL中建立索引进行优化，提高系统的性能和效率。

10.postgres和qgis的交互

PostgreSQL是一种常见的关系型数据库管理系统，而QGIS是一种开源的桌面地理信息系统软件。它们可以通过PostGIS插件进行交互，实现空间数据的存储、查询、分析和可视化等功能。以下是在QGIS中与PostgreSQL进行交互的一些常见操作：

连接PostgreSQL数据库

在QGIS中连接PostgreSQL数据库，需要在浏览器窗口中选择“PostgreSQL”选项，并填写相应的连接信息，例如主机名、端口号、数据库名称、用户名和密码等。连接成功后，可以在浏览器窗口中看到PostgreSQL数据库中的所有表和视图等。

加载PostgreSQL数据

在QGIS中加载PostgreSQL数据，需要在浏览器窗口中选择相应的表或视图，并将其拖动到地图窗口中。加载成功后，可以在地图窗口中看到PostgreSQL数据的地理位置和属性信息等。

查询PostgreSQL数据

在QGIS中查询PostgreSQL数据，可以使用自带的查询工具或SQL查询工具。自带的查询工具可以通过属性表窗口进行访问，而SQL查询工具可以通过“数据库”菜单中的“SQL查询”选项进行访问。可以使用SQL语句进行高级查询和空间分析等操作。

编辑PostgreSQL数据

在QGIS中编辑PostgreSQL数据，需要确保PostgreSQL数据库和表具有适当的编辑权限。可以通过属性表窗口或“编辑”工具栏中的编辑按钮进行编辑，可以添加、删除或更新数据库中的记录。

保存PostgreSQL数据

在QGIS中保存PostgreSQL数据，需要确保PostgreSQL数据库和表具有适当的写入权限。可以通过属性表窗口或“编辑”工具栏中的保存按钮进行保存，可以将更新后的数据保存回PostgreSQL数据库中。

综上所述，通过PostGIS插件，在QGIS中可以方便地与PostgreSQL进行交互，实现空间数据的存储、查询、分析和可视化等功能。


http://www.niftyadmin.cn/n/214777.html

相关文章

OCR数据集

OCR数据集

1、SynthText in the Wild dataset 数据集下载链接&#xff1a; ​ ​http://www.robots.ox.ac.uk/~vgg/data/scenetext/​​ 数据集介绍&#xff1a;一个综合生成的数据集&#xff0c;其中单词实例放置在自然场景图像中&#xff0c;同时考虑场景布局。数据集由大约80万个合…
阅读更多...
STC89C52串口通信当中的UART

STC89C52串口通信当中的UART

1.串口介绍 串口是一种应用十分广泛的通讯接口&#xff0c;串口成本低、容易使用、通信线路简单&#xff0c;可实现两个设备的互相通信。 单片机的串口可以使单片机与单片机、单片机与电脑、单片机与各式各样的模块互相通信&#xff0c;极大的扩展了单片机的应用范围&#xf…
阅读更多...
day20-RTC实验

day20-RTC实验

RTC RTC(Real Time Clock)即实时时钟&#xff0c;它是一个可以为系统提供精确的时间基准的元器件&#xff0c;RTC一般采用精度较高的晶振作为时钟源&#xff0c;有些RTC为了在主电源掉电时还可以工作&#xff0c;需要外加电池供电 RTC寄存器详解 BCDYEAR 若设置year为2023&am…
阅读更多...
【web自动化测试】

【web自动化测试】

文章目录web自动化测试第一章 web自动化入门1.什么是自动化&#xff1f;1.1 优点2.什么是自动化测试&#xff1f;2.1 自动化测试能解决什么问题&#xff1f;2.2 自动化相关知识2.2.1优点2.2.2 误区2.3 自动化测试分类3.什么是Web自动化测试&#xff1f;3.1 什么Web项目适合做自…
阅读更多...
JAVA 冒泡排序

JAVA 冒泡排序

JAVA 冒泡排序 目录JAVA 冒泡排序冒泡排序1 概念2 排序思路3 实现冒泡排序冒泡排序 1 概念 冒泡排序&#xff08;Bubble Sort&#xff09;&#xff0c;是计算机科学领域中较简单的一种排序算法。 它重复地走访需要进行排序的元素&#xff0c;依次比较两个相邻的元素&#xff…
阅读更多...
Houdini＞RBD(搅拌大米效果)

Houdini＞RBD(搅拌大米效果)

Houdini&#xff1e;RBD(搅拌大米效果) 效果展示&#xff1a; 动图录制软件&#xff1a;Cockos Incorporated | LICEcap 参考链接&#xff1a;导出除了ABC外&#xff0c;比较小的FBX文件用法 目录&#xff1a; 一、引用模型的处理&#xff1a; 1、大米 模型创建 多层复制…
阅读更多...
【单片机】添加printf函数后，gcc编译出来的固件大小明显比armcc大

【单片机】添加printf函数后，gcc编译出来的固件大小明显比armcc大

在使用 GCC 编译器&#xff08;如 arm-none-eabi-gcc&#xff09;和 ARMCC 编译器时&#xff0c;可能会注意到它们生成的固件大小有所不同。这种差异可能是由于以下原因造成的&#xff1a; 优化设置&#xff1a;不同编译器的优化选项和优化策略可能导致生成的二进制文件大小不同…
阅读更多...
YOLOv5：IoU、GIoU、DIoU、CIoU、EIoU

YOLOv5：IoU、GIoU、DIoU、CIoU、EIoU

YOLOv5&#xff1a;IoU、GIoU、DIoU、CIoU、EIoU前言前提条件相关介绍IoU&#xff08;Intersection over Union&#xff09;GIoU&#xff08;Generalized-IoU&#xff09;DIoU&#xff08;Distance-IoU&#xff09;CIoU &#xff08;Complete-IoU&#xff09;EIoU&#xff08;E…
阅读更多...
最新文章

Copyright @ 2022~2023