postgresql保存空间数据

1、postgis扩展

1.1、安装postgis（先安装postgresql）

注意postgis和postgresql的版本要一致，我自己装的是postgresql10。下载后直接安装即可（postgis的安装路径是在postgresql下）

1.2、在指定的数据库下添加postgis扩展

CREATE EXTENSION postgis

2、空间字段的创建和空间数据的格式

--创建空间字段点
SELECT addgeometrycolumn('public','tablename','point_geom',4326,'POINT',2)

--创建空间字段线
SELECT addgeometrycolumn('public','tablename','line_geom',4326,'LINESTRING',2)

--创建空间字段面
SELECT addgeometrycolumn('public','tablename','polygon_geom',4326,'POLYGON',2)

2、使用postgresql写入空间数据

#!usr/bin/env python
# -*- coding: utf-8 -*-
"""
@Author  : 
@Email   : 
@Time    : 2020/5/26 11:31
@File    : base.py
@Software: PyCharm
"""

import psycopg2

database='task_algorithm'
user='postgres'
password='zgh@@123'
host='localhost'
port='5432'

#连接数据库
def connect_db():
    try:
        conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
    except Exception as e:
        print('数据库连接失败')
    else:
        return conn
    return None

#关闭数据库连接
def close_db_connection(conn):
    conn.commit()
    conn.close()

def join_path(point_list):
    """
    将离散度的点拼接成线
    :param point_list:
    :return:
    """
    lines=""
    for i in range(len(point_list)-1):
        lines+=str(point_list[i][0])+" "+str(point_list[i][1])+","


    lines+=str(point_list[-1][0])+" "+str(point_list[-1][1])

    linestring = "srid=4326;LINESTRING({line})".format(line=lines)

    return linestring

def join_polygon(point_list):
    """
    将离散的点拼接成面
    :param point_list:
    :return:
    """
    poly=""

    for i in range(len(point_list)):
        poly+=str(point_list[i][0])+" "+str(point_list[i][1])+","


    poly+=str(point_list[0][0])+" "+str(point_list[0][1])

    polygon = "srid=4326;POLYGON(({poly}))".format(poly=poly)
    return polygon

#创建表
conn = connect_db()
cur = conn.cursor()

create_sql = "CREATE TABLE if not exists {copy_table}(id SERIAL PRIMARY KEY,type int2,typename text)".format(
    copy_table="test")
cur.execute(create_sql)

#创建空间字段点
point_geom_sql="SELECT addgeometrycolumn('public','{copy_table}','point_geom',4326,'POINT',2)".format(
    copy_table="test")
cur.execute(point_geom_sql)

#创建空间字段线
line_geom_sql="SELECT addgeometrycolumn('public','{copy_table}','line_geom',4326,'LINESTRING',2)".format(
    copy_table="test")
cur.execute(line_geom_sql)

# 创建空间字段面
polygon_geom_sql="SELECT addgeometrycolumn('public','{copy_table}','polygon_geom',4326,'POLYGON',2)".format(
    copy_table="test")
cur.execute(polygon_geom_sql)

#插入点数据
point_list = [[120,30],[121,30],[120.5,31]]

for point in point_list:
    #空间数据第一种组织方式
    #point_geom= "srid=4326;POINT("+str(point[0])+" "+str(point[1])+")"
    #空间数据第二种组织方式
    point_geom = "ST_GeomFromText('POINT("+str(point[0])+" "+str(point[1])+")'"+",4326)"
    insert_point_sql = "INSERT INTO {table}(type,typename,point_geom) VALUES('{type}','{typename}',{point_geom})".format(
        table='test',type=1,typename='点',point_geom=point_geom)
    print(insert_point_sql)
    cur.execute(insert_point_sql)

#插入线数据
linstring = join_path(point_list)

insert_line_sql="INSERT INTO {table}(type,typename,line_geom)VALUES('{type}','{typename}','{line_geom}')".format(
    table='test',type=2,typename='线',line_geom=linstring)
cur.execute(insert_line_sql)

#插入面数据
polygon = join_polygon(point_list)
insert_poly_sql="INSERT INTO {table}(type,typename,polygon_geom)VALUES('{type}','{typename}','{polygon_geom}')".format(
    table='test',type=3,typename='面',polygon_geom=polygon)
print(insert_poly_sql)
cur.execute(insert_poly_sql)

cur.close()
close_db_connection(conn)

3、在qgis中查看结果
3.1、qgis连接postgis数据库

3.2、加载矢量结果