PostgreSQL - 01 PostgreSQL + PostGIS + Docker 空间计算！判断坐标点是否在某个区域中 POINT MULTIPOLYGON ST_Contains

于 2024-06-21 09:53:31 发布
阅读量343 收藏 2
点赞数 10
分类专栏： python 文章标签： postgresql docker 空间计算 数据库 算法
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/w776341482/article/details/139851559
版权

代码仓库

所有东西已经打包放到GitHhub，需要的小伙伴可以直接拉取。顺手可以帮忙点个Star！
https://github.com/turbo-duck/postgre-gis

请添加图片描述

背景介绍

我们有一批坐标点和一批区域点。
比如： 100万个点，和10万个区域块。
我们遇到了这样的需求：这100万个点，分别属于哪个区域块？

初始解决

通过空间的计算方法，我们用 O(n2)的方式：

# 伪代码
for poi in poi_list:
	for aoi in aoi_list:
		# 做一些空间计算
		result = compute(aoi, poi)
		if result:
			# 在这个空间
			save()
		else:
			# 不在这个空间
			other()

但是这样的计算时间实在是太久，于是我们用空间数据库进行优化，于是有了下文：
从 O(n2) -> O(n)，之前需要跑三天三夜的结果，现在只需要短短的几秒就可以跑完。

Docker

FROM postgis/postgis:latest
ENV POSTGRES_DB=space
ENV POSTGRES_USER=postgres
ENV POSTGRES_PASSWORD=123123
EXPOSE 5432
COPY init-db.sh /docker-entrypoint-initdb.d/

init-db.sh

#!/bin/bash
set -e
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE EXTENSION IF NOT EXISTS postgis;
EOSQL

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
  CREATE TABLE "public"."aoi" (
    "name" varchar(500) COLLATE "pg_catalog"."default" NOT NULL,
    "geom" geometry(GEOMETRY) NOT NULL
  );
  ALTER TABLE "public"."aoi"
    OWNER TO "postgres";
  CREATE INDEX "geom" ON "public"."aoi" USING btree (
    "geom" "public"."btree_geometry_ops" ASC NULLS LAST
  );
  CREATE INDEX "name" ON "public"."aoi" USING btree (
    "name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
  );
  CREATE INDEX "name_geom" ON "public"."aoi" USING btree (
    "name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST,
    "geom" "public"."btree_geometry_ops" ASC NULLS LAST
  );
EOSQL

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
  CREATE TABLE "public"."poi" (
    "name" varchar(500) COLLATE "pg_catalog"."default" NOT NULL,
    "geom" geometry(GEOMETRY) NOT NULL
  );
  ALTER TABLE "public"."poi"
    OWNER TO "postgres";
  CREATE INDEX "geom_copy1" ON "public"."poi" USING btree (
    "geom" "public"."btree_geometry_ops" ASC NULLS LAST
  );
  CREATE INDEX "name_copy1" ON "public"."poi" USING btree (
    "name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
  );
  CREATE INDEX "name_geom_copy1" ON "public"."poi" USING btree (
    "name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST,
    "geom" "public"."btree_geometry_ops" ASC NULLS LAST
  );
EOSQL

写入 AOI

请添加图片描述

import pandas as pd
import psycopg2
from openpyxl import Workbook
import re


wb = Workbook()
ac = wb.active
line = ['name', 'data', 'origin']
ac.append(line)
# 是否出现错误
error_flag = 0
conn = psycopg2.connect(database="space", user="postgres", password="123123", host="localhost")
cur = conn.cursor()
aoi_file = pd.read_excel("./aoi.xlsx", engine='openpyxl')
line_num = aoi_file.shape[0]

# 清空表
clean_sql = "DELETE FROM aoi WHERE 1=1"
cur.execute(clean_sql)
conn.commit()

for i in range(0, line_num):
    get_name = aoi_file.iat[i, 0]
    '''
    {"type":
        "Polygon",
        "coordinates": [[[
            121.342079,31.417835],[121.342105,31.417812],[121.34214,31.417802],
            [121.342177,31.4178],[121.343274,31.417851],[121.343306,31.41786],[121.343333,31.417879],
            [121.343354,31.417911],[121.343364,31.417945],[121.343424,31.419663],[121.343423,31.419707],
            [121.343414,31.419746],[121.343384,31.419832],[121.343351,31.419903],[121.343318,31.419927],
            [121.343276,31.419931],[121.341537,31.419355],[121.341508,31.419332],[121.341486,31.419301],
            [121.341486,31.419268],[121.341496,31.41923],[121.342056,31.417871],[121.342079,31.417835
        ]]]}
    '''
    get_geom = aoi_file.iat[i, 1]
    origin_geom = get_geom
    get_geom = re.sub(' ', '', str(get_geom))
    get_geom = re.sub('MultiPolygon', 'Polygon', str(get_geom))
    get_geom = re.sub("\[\[\[\[", '[[[', str(get_geom))
    get_geom = re.sub("\]\]\]\]", ']]]', str(get_geom))
    get_geom = re.sub('\{', ' ', str(get_geom))
    get_geom = re.sub('\}', ' ', str(get_geom))
    get_geom = re.sub('"type":"Polygon"', "", str(get_geom))
    get_geom = re.sub('"coordinates":\[\[\[', '', str(get_geom))
    get_geom = re.sub('\]\]\]', '', str(get_geom))
    get_geom = re.sub(' ', '', str(get_geom))
    get_geom = re.sub(',', ' ', str(get_geom))
    get_geom = re.sub('\] \[', ',', str(get_geom))
    print(get_name)
    # INSERT INTO geometries VALUES(
    # 'B0FFG1YVX3',
    # 'MULTIPOLYGON(((120.394543 36.113915,120.39454 36.113898,120.394539 36.113869,120.394548
    # 36.113844,120.394586 36.113812,120.396095 36.11299,120.396118 36.112986,120.396133 36.112988
    # ,120.39615 36.112998,120.39712 36.114184,120.39713 36.114219,120.397129 36.114262,120.397118 36.114297
    # ,120.397101 36.114325,120.397075 36.114359,120.397043 36.114389,120.396994 36.114425,120.39584 36.115065,
    # 120.395818 36.11507,120.395793 36.115071,120.395762 36.115068,120.395719 36.115058,120.395686 36.115043,
    # 120.395661 36.115029,120.394543 36.113915)))');
    sql = "INSERT INTO aoi VALUES('" + str(get_name) + "'," + \
        "'MULTIPOLYGON(((" + str(get_geom) + ")))');"
    try:
        cur.execute(sql)
        conn.commit()
    except Exception as e:
        error_flag = 1
        conn.rollback()
        line = [str(get_name), str(get_geom), str(origin_geom)]
        ac.append(line)
        print(e)
        print(sql)
conn.close()

# 0 no error | 1 have error
if error_flag == 1:
    try:
        wb.save("./error_data.xlsx")
    except PermissionError:
        wb.save("./new_error_data.xlsx")

写入 POI

请添加图片描述

import pandas as pd
import psycopg2
import re

conn = psycopg2.connect(database="space", user="postgres", password="123123", host="localhost")
cur = conn.cursor()
poi_file = pd.read_excel("./poi.xlsx", engine='openpyxl')
line_num = poi_file.shape[0]

# 清空表
clean_sql = "DELETE FROM poi WHERE 1=1"
cur.execute(clean_sql)
conn.commit()

for i in range(0, line_num):
    get_name = poi_file.iat[i, 0]
    '''
    121.56042,31.20459
    '''
    get_poi = poi_file.iat[i, 1]
    origin_poi = get_poi
    get_poi = re.sub(" ", "", str(get_poi))
    get_poi = re.sub("，", " ", str(get_poi))
    get_poi = re.sub(",", " ", str(get_poi))
    print(get_name)
    if str(get_name) == 'nan':
        continue
    # INSERT INTO geometries VALUES(
    # 'B0FFG1YVX3',
    # 'MULTIPOLYGON(((120.394543 36.113915,120.39454 36.113898,120.394539 36.113869,120.394548
    # 36.113844,120.394586 36.113812,120.396095 36.11299,120.396118 36.112986,120.396133 36.112988
    # ,120.39615 36.112998,120.39712 36.114184,120.39713 36.114219,120.397129 36.114262,120.397118 36.114297
    # ,120.397101 36.114325,120.397075 36.114359,120.397043 36.114389,120.396994 36.114425,120.39584 36.115065,
    # 120.395818 36.11507,120.395793 36.115071,120.395762 36.115068,120.395719 36.115058,120.395686 36.115043,
    # 120.395661 36.115029,120.394543 36.113915)))');
    sql = "INSERT INTO poi VALUES('" + str(get_name) + "'," + \
            "'POINT(" + str(get_poi) + ")');"
    try:
        cur.execute(sql)
    except Exception as e:
        print(e)
        print(f"sql => {sql}")
        print(f"get_name => {get_name} | get_poi => {get_poi}")
        exit(0)
conn.commit()
conn.close()

判断 POI IN AOI

请添加图片描述

import psycopg2
from openpyxl import Workbook

wb = Workbook()
ac = wb.active
line = ['poi_name', 'aoi_name']
ac.append(line)

conn = psycopg2.connect(database="nyc", user="postgres", password="123123", host="localhost")
cur = conn.cursor()
# 建立索引
# sql = "CREATE INDEX aoi_idx ON aoi USING GIST (geom);"
# cur.execute(sql)
# conn.commit()
# 查询结果
sql = "SELECT aoi.name, poi.name FROM aoi, poi where ST_Contains(aoi.geom, poi.geom);"
print("正在判断···")
cur.execute(sql)
rows = cur.fetchall()
for row in rows:
    result_aoi = row[0]
    result_poi = row[1]
    line = [str(result_poi), str(result_aoi)]
    ac.append(line)
    print(result_poi)
conn.close()
print("save xlsx")
wb.save("./poiInAoiResult.xlsx")
print("done")
武子康
关注
  • 10
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
PostGIS教程一：PostGIS介绍
挣钱花388的博客
08-11 2452
"这个问题，对多边形进行计算，计算量非常大而且难以计算，但在计算矩形的情况下，计算比较容易，而且速度非常快。PostGIS 0.1的开发花费了不到一个月的时间，但做一个“MyGIS" 0.1可能需要更长的时间，可能永远也不会成功。而构建的专有的、独立的系统，即应用程序和平面文件之间的耦合性非常高，平面文件里的空间数据没有数据独立性。在PostGIS 1.0中，这种新的、更快的、轻量级的数据表示成为了默认的数据表示。时，PostGIS团队查看了它们的代码，这坚定了最初使用PostgreSQL的决定。
PostgreSQL 空间处理函数(整理)
11-12 6507
PostGIS中的常用函数 其它： 1、json转wkt(面) SELECT 'POLYGON((' || replace(replace(replace(replace(replace(replace(replace(data_json,'],','#'),'{',''),'}',''),'[',''),']',''),',',' '),'#',',') ||'))' FROM tabl...
【PostGis】PostgreSQL扩展，支持坐标空间数据计算
小小透明人的博客
11-29 474
PostgreSql安装PostGis扩展
PostGIS 空间计算
baidu_23263735的博客
05-24 1514
1、度/秒互转 UPDATE 表名 SET geom = st_scale(shape, 1/3600.0,1/3600.0) 2、geom转wkt字符串 SELETC st_astext(t.shape) 3、wkt字符串转geom 并设置坐标系 SELECT st_setsrid(ST_GeomFromText('POINT(428626.908 110737.8)'), 4326) 4、获取图形中心点坐标 SELECT ST_X(st_centroid(c.shape))
PostGIS是什么
XiaoCai
09-13 5307
PostGIS是PostgreSQL对象关系数据库的空间数据库扩展器。它增加了对地理对象的支持，允许在SQL中运行位置查询。PostGIS是在对象关系型数据库PostgreSQL上增加了存储管理空间数据的能力的开源空间数据库，空间数据库像存储和操作数据库中其他任何对象一样去存储和操作空间对象。它是PostgreSQL的扩展插件，在PostgreSQL的core（存储、检索、事务等）层之上实现GIS层的功能，它不能独立于PostgreSQL运行。
PostGIS的10个最佳实践
新缸中之脑
05-24 1860
PostGIS 是一个功能强大的开源空间数据库，可用于存储、查询和分析地理空间数据。对于需要存储和分析大量地理空间数据的组织来说，这是一个流行的选择。但是，正确使用 PostGIS 以充分利用它很重要。在本文中，我们将讨论 10 个 PostGIS 最佳实践，它们将帮助你充分利用 PostGIS 数据库。我们将讨论数据类型、索引和查询优化等主题。推荐：用快速搭建可编程3D场景。
PostgreSQL-PostGIS-TimescaleDB:PostgreSQL + PostGIS + TimescaleDB泊坞窗图片:elephant::globe_showing_Americas::chart_increasing:
05-24
PostgreSQL-PostGIS-TimescaleDB PostgreSQL + PostGIS + TimescaleDB即用型Docker映像 :elephant: :globe_showing_Americas: :chart_increasing: 基于 。 Docker映像具有：当前组件版本： PostgreSQL： 12.5 （） ...
postgresql14+postgis3.3+pgrouting3.4的docker镜像
08-17
docker load 后执行命令 docker run --name "pg14-3-3" --restart always -e TZ=Asia/Shanghai -p 5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e PASSWORD_AUTHENTICATION="scram-sha-256...
postgresql-14.1-1+postgis3.1.4 windows版
12-29
在Windows环境中使用PostgreSQL 14.1 + PostGIS 3.1.4，用户可以享受到高性能的数据库服务以及强大的地理空间处理能力。无论是开发GIS应用，还是进行地理数据分析，这一组合都能提供稳定且高效的解决方案。记得定期...
postgresql14+postgis32_14
05-27
PostGIS 提供了丰富的 SQL 函数，如 ST_GeomFromText 用于创建几何对象，ST_Contains 用于判断一个几何对象是否包含另一个，ST_Distance 用于计算两点之间的距离等。 在实际应用中，PostgreSQL 14 和 PostGIS 3.2 ...
postgresql12-docker19-postgis
07-28
压缩包中包含postgresql12-docker19-postgis三个组件，每个组件都是当下比较常用的版本，并且其中还包含了现在比较流行的postgis，目前只有一个docker的安装文档，其他两个我这边还没总结，有需要的先自行百度一下...
PostGIS的安装与初步使用
热门推荐
不睡觉的怪叔叔的博客
07-23 6万+
想学习postgis推荐看我翻译的官方教程文档：https://blog.csdn.net/qq_35732147/article/details/85256640 目录 一、安装PostGIS 1.下载安装程序 2.安装PostgreSQL 二、创建空间数据库 1.打开pgAdmin 4 2.登录到服务器 3.创建空间数据库 ...
PostGIS(1):PostGIS概述
席木木的博客
06-06 955
PostGIS概述：作为对象关系型数据库PostGreSQL的拓展模块，PostGIS可用于存储GIS数据，并提供了对基于GiST的R树索引支持、以及面向GIS对象的分析和处理相关的函数。
PostGIS概述
Civitasv
12-28 2745
PostGIS是一个开源程序，它是对象-关系型数据库PostgreSQL的一个扩展，为其提供了存储空间地理数据的支持，使得PostgreSQL成为了一个空间数据库，能够进行空间数据管理、数量测量和拓扑分析。
空间数据库PostGIS（1）介绍、安装与存储数据
ysqzhatian的博客
08-12 1722
1 引言 空间数据的存储形式可以是单个文件，也可以是像GeoDatabase一样的集成形式，也可以是存储在空间数据库中。 什么是空间数据库呢，简单的说空间数据库就让空间数据像其他数据一样存储在数据库中。SQL Server和Oracle Spatial都是空间数据库，PostGIS也是在PostgreSQL基础上的空间数据库。 2 PostGIS PostGIS基于PostgreSQL数据库，添加了对空间类型、空间索引和空间功能的支持，使其成为了空间数据库。 2.1 安装 PostGIS是基于Postgre
PostGIS介绍
awangdea99的博客
11-02 2504
一、简介 PostGIS是一个PostgreSQL数据库扩展，它允许GIS（地理信息系统）对象存储在数据库中。PostGIS包括对基于GiST的R-Tree空间索引的支持，以及用于分析和处理GIS对象的功能，它是一种根据知识共享署名，相同方式共享3.0许可证授权的开源软件。PostgreSQL是usinf PostGIS功能的依赖项，因此，安装PostGIS之前，首先需要安装PostgreSQL。 PostGIS通过向PostgreSQL添加对空间数据类型、空间索引和空间函数...
Postgis使用
yyb1369584682的博客
10-11 1203
Postgis使用
PostgresSQL中的常用空间函数
m0_72744405的博客
07-14 2146
首先需要说明一下，这里许多函数是以ST_[X]yyy形式命名的，事实上很多函数也可以通过xyyy的形式访问，在PostGIS的函数库中我们可以看到这两种函数定义完全一样。
搭建postgresql + postgis空间数据引擎
最新发布
09-03
要搭建PostgreSQL PostGIS空间数据引擎，需按照以下步骤进行操作。 首先，确保已经安装了PostgreSQL数据库服务器。可以从官方网站上下载并安装最新版本的PostgreSQL。 完成安装后，打开命令行终端并切换到PostgreSQL的安装目录下的bin文件夹。在命令行中输入以下命令来启动PostgreSQL： ``` pg_ctl -D 数据库存储路径 start ``` 数据库存储路径是指定数据库文件将保存的目录路径。将其替换为你想要使用的路径。 启动后，会在命令行中显示一些数据库连接信息，包括端口号和数据库用户名等。 接下来，需要创建一个新的数据库。在命令行中输入以下命令： ``` createdb -U 数据库用户名 -h localhost -p 端口号 新数据库名称 ``` 将数据库用户名、端口号和新数据库名称替换为真实值。 创建数据库后，需要在其中安装PostGIS扩展。在命令行中输入以下命令来连接到新数据库： ``` psql -U 数据库用户名 -h localhost -p 端口号 新数据库名称 ``` 进入数据库后，运行以下命令来创建PostGIS扩展： ``` CREATE EXTENSION postgis; ``` 该命令将在数据库中创建PostGIS所需的表和函数。安装完成后，就可以在数据库中存储和查询空间数据了。 最后，可以使用数据库管理工具，如pgAdmin等，连接到数据库进行操作和管理。可以使用这些工具来创建数据表、导入和导出空间数据，以及执行空间查询等操作。 总结一下，搭建PostgreSQL PostGIS空间数据引擎的主要步骤是：安装PostgreSQL，启动数据库服务器，创建新数据库，安装PostGIS扩展，然后使用数据库管理工具进行操作和管理。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值