PostGre数据库操作

最新推荐文章于 2023-10-19 11:32:29 发布

小曹爱编程！

最新推荐文章于 2023-10-19 11:32:29 发布

阅读量771

点赞数 1

文章标签：数据库 postgresql sql

本文链接：https://blog.csdn.net/weixin_62993347/article/details/130113580

版权

菜鸟教程

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