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

最新推荐文章于 2024-10-06 18:27:13 发布
最新推荐文章于 2024-10-06 18:27:13 发布
阅读量829 收藏 3
点赞数 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")
武子康
关注
专栏目录
pgsql判断一个点是否存在于MULTIPOLYGON(多多边形)
weixin_47553445的博客
03-27 2495
语法 SELECT ST_Contains( ST_GeomFromText('这里放需要查找的MULTIPOLYGON'),st_point(103.2366313,28.81767612)) demo // An highlighted block SELECT ST_Contains( ST_GeomFromText( 'MULTIPOLYGON(((103.236631342762 28.8176761290501,103.223402196754 28.8067721559103,103.209
PostgreSQL 空间处理函数(整理)
11-12 6686
PostGIS中的常用函数 其它： 1、json转wkt(面) SELECT 'POLYGON((' || replace(replace(replace(replace(replace(replace(replace(data_json,'],','#'),'{',''),'}',''),'[',''),']',''),',',' '),'#',',') ||'))' FROM tabl...
PostGIS--介绍
最新发布
CJPSR的博客
10-06 1118
PostGIS通过增加对空间类型空间索引和空间函数三个功能的支持，将PostgreSQL数据库管理系统转变为空间数据库。
PostgreSQL 常用空间处理函数
番茄Salad
07-26 3486
添加几何字段 AddGeometryColumn(, , , , , )删除几何字段 DropGeometryColumn(, , )检查数据库几何字段并在geometry_columns中归档 Probe_Geometry_Columns()给几何对象设置空间参考（在通过一个范围做空间查询时常用）ST_SetSRID(geometry, integer)
【PostGis】PostgreSQL扩展，支持坐标空间数据计算
小小透明人的博客
11-29 498
PostgreSql安装PostGis扩展
PostGIS 空间计算
baidu_23263735的博客
05-24 1571
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))
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应用，还是进行地理数据分析，这一组合都能提供稳定且高效的解决方案。记得定期...
kartoza-docker-postgis，带postgis的postgresql13版本的docker镜像
09-18
kartoza-docker-postgis，带postgis的postgresql13版本的docker镜像。 可直接通过docker导入镜像命令导入。 一个简单的 Docker 容器，用于运行 PostGIS 访问Docker Hub 的页面：...
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是什么
XiaoCai
09-13 5479
PostGIS是PostgreSQL对象关系数据库的空间数据库扩展器。它增加了对地理对象的支持，允许在SQL中运行位置查询。PostGIS是在对象关系型数据库PostgreSQL上增加了存储管理空间数据的能力的开源空间数据库，空间数据库像存储和操作数据库中其他任何对象一样去存储和操作空间对象。它是PostgreSQL的扩展插件，在PostgreSQL的core（存储、检索、事务等）层之上实现GIS层的功能，它不能独立于PostgreSQL运行。
PostGIS的10个最佳实践
新缸中之脑
05-24 2059
PostGIS 是一个功能强大的开源空间数据库，可用于存储、查询和分析地理空间数据。对于需要存储和分析大量地理空间数据的组织来说，这是一个流行的选择。但是，正确使用 PostGIS 以充分利用它很重要。在本文中，我们将讨论 10 个 PostGIS 最佳实践，它们将帮助你充分利用 PostGIS 数据库。我们将讨论数据类型、索引和查询优化等主题。推荐：用快速搭建可编程3D场景。
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 1041
PostGIS概述：作为对象关系型数据库PostGreSQL的拓展模块，PostGIS可用于存储GIS数据，并提供了对基于GiST的R树索引支持、以及面向GIS对象的分析和处理相关的函数。
postgis空间数据导入及可视化
夜郎King的博客
08-10 1万+
本文重点介绍在postgis中，三种矢量数据的导入管理方式，分别是使用postgis导入工具、gdal的ogr2ogr函数以及编程式​。
PostGIS概述
Civitasv
12-28 3020
PostGIS是一个开源程序，它是对象-关系型数据库PostgreSQL的一个扩展，为其提供了存储空间地理数据的支持，使得PostgreSQL成为了一个空间数据库，能够进行空间数据管理、数量测量和拓扑分析。
空间数据库PostGIS（1）介绍、安装与存储数据
ysqzhatian的博客
08-12 1876
1 引言 空间数据的存储形式可以是单个文件，也可以是像GeoDatabase一样的集成形式，也可以是存储在空间数据库中。 什么是空间数据库呢，简单的说空间数据库就让空间数据像其他数据一样存储在数据库中。SQL Server和Oracle Spatial都是空间数据库，PostGIS也是在PostgreSQL基础上的空间数据库。 2 PostGIS PostGIS基于PostgreSQL数据库，添加了对空间类型、空间索引和空间功能的支持，使其成为了空间数据库。 2.1 安装 PostGIS是基于Postgre
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

武子康

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值