第17章-编程语言集成
第17章：编程语言集成
17.1 概述
PostGIS 可以与多种编程语言集成，本章介绍 Python、Java 和 .NET 的连接和使用方法。
17.2 Python 集成
17.2.1 依赖安装
pip install psycopg2-binary # PostgreSQL 驱动
pip install geoalchemy2 # SQLAlchemy 空间扩展
pip install shapely # 几何处理
pip install geopandas # 空间数据分析
pip install pyproj # 坐标转换
17.2.2 使用 psycopg2
import psycopg2
from psycopg2 import sql
# 建立连接
conn = psycopg2.connect(
host="localhost",
port=5432,
database="gis_db",
user="postgres",
password="password"
)
# 创建游标
cur = conn.cursor()
# 执行查询
cur.execute("""
SELECT id, name, ST_AsGeoJSON(geom) AS geojson
FROM poi
WHERE ST_DWithin(
geom::geography,
ST_SetSRID(ST_MakePoint(%s, %s), 4326)::geography,
%s
)
""", (116.4, 39.9, 1000))
# 获取结果
rows = cur.fetchall()
for row in rows:
print(f"ID: {row[0]}, Name: {row[1]}, GeoJSON: {row[2]}")
# 插入数据
cur.execute("""
INSERT INTO poi (name, category, geom)
VALUES (%s, %s, ST_SetSRID(ST_MakePoint(%s, %s), 4326))
RETURNING id
""", ("新餐厅", "餐饮", 116.5, 39.95))
new_id = cur.fetchone()[0]
conn.commit()
# 关闭连接
cur.close()
conn.close()
17.2.3 使用 SQLAlchemy + GeoAlchemy2
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from geoalchemy2 import Geometry
from geoalchemy2.functions import ST_DWithin, ST_SetSRID, ST_MakePoint
# 创建引擎
engine = create_engine('postgresql://postgres:password@localhost:5432/gis_db')
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
# 定义模型
class POI(Base):
__tablename__ = 'poi'
id = Column(Integer, primary_key=True)
name = Column(String(100))
category = Column(String(50))
geom = Column(Geometry('POINT', srid=4326))
# 查询示例
from sqlalchemy import func
# 查询附近的 POI
query_point = func.ST_SetSRID(func.ST_MakePoint(116.4, 39.9), 4326)
nearby_pois = session.query(POI).filter(
func.ST_DWithin(
POI.geom.cast(Geometry).ST_Transform(3857),
query_point.ST_Transform(3857),
1000
)
).all()
for poi in nearby_pois:
print(f"{poi.name}: {poi.category}")
# 插入数据
new_poi = POI(
name="新地点",
category="景点",
geom=f'SRID=4326;POINT(116.5 39.95)'
)
session.add(new_poi)
session.commit()
17.2.4 使用 GeoPandas
import geopandas as gpd
from sqlalchemy import create_engine
# 创建连接
engine = create_engine('postgresql://postgres:password@localhost:5432/gis_db')
# 读取数据
gdf = gpd.read_postgis(
"SELECT * FROM districts WHERE province = '北京'",
engine,
geom_col='geom'
)
# 空间分析
gdf['area_km2'] = gdf.to_crs(epsg=3857).area / 1e6
gdf['centroid'] = gdf.centroid
# 空间连接
poi_gdf = gpd.read_postgis("SELECT * FROM poi", engine, geom_col='geom')
joined = gpd.sjoin(poi_gdf, gdf, how='inner', predicate='within')
# 写入数据库
gdf.to_postgis('districts_analysis', engine, if_exists='replace', index=False)
# 可视化
import matplotlib.pyplot as plt
fig, ax = plt.subplots(figsize=(10, 10))
gdf.plot(ax=ax, column='area_km2', legend=True, cmap='YlOrRd')
plt.savefig('districts.png')
17.3 Java 集成
17.3.1 依赖配置
<!-- pom.xml -->
<dependencies>
<!-- PostgreSQL JDBC -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.6.0</version>
</dependency>
<!-- JTS 几何库 -->
<dependency>
<groupId>org.locationtech.jts</groupId>
<artifactId>jts-core</artifactId>
<version>1.19.0</version>
</dependency>
<!-- Hibernate Spatial -->
<dependency>
<groupId>org.hibernate.orm</groupId>
<artifactId>hibernate-spatial</artifactId>
<version>6.3.1.Final</version>
</dependency>
</dependencies>
17.3.2 JDBC 直接连接
import java.sql.*;
import org.postgresql.PGConnection;
public class PostGISExample {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/gis_db";
String user = "postgres";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, user, password)) {
// 查询
String sql = """
SELECT id, name, ST_AsText(geom) as wkt
FROM poi
WHERE ST_DWithin(
geom::geography,
ST_SetSRID(ST_MakePoint(?, ?), 4326)::geography,
?
)
""";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setDouble(1, 116.4);
pstmt.setDouble(2, 39.9);
pstmt.setDouble(3, 1000);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.printf("ID: %d, Name: %s, WKT: %s%n",
rs.getInt("id"),
rs.getString("name"),
rs.getString("wkt")
);
}
}
// 插入
String insertSql = """
INSERT INTO poi (name, category, geom)
VALUES (?, ?, ST_SetSRID(ST_MakePoint(?, ?), 4326))
""";
try (PreparedStatement pstmt = conn.prepareStatement(insertSql)) {
pstmt.setString(1, "新地点");
pstmt.setString(2, "景点");
pstmt.setDouble(3, 116.5);
pstmt.setDouble(4, 39.95);
pstmt.executeUpdate();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
17.3.3 JPA/Hibernate Spatial
// Entity
import jakarta.persistence.*;
import org.locationtech.jts.geom.Point;
@Entity
@Table(name = "poi")
public class POI {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String category;
@Column(columnDefinition = "geometry(Point,4326)")
private Point geom;
// Getters and Setters
}
// Repository
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
public interface POIRepository extends JpaRepository<POI, Long> {
@Query(value = """
SELECT * FROM poi
WHERE ST_DWithin(
geom::geography,
ST_SetSRID(ST_MakePoint(:lon, :lat), 4326)::geography,
:distance
)
""", nativeQuery = true)
List<POI> findNearby(double lon, double lat, double distance);
}
// Service
@Service
public class POIService {
@Autowired
private POIRepository repository;
public List<POI> findNearbyPOIs(double lon, double lat, double distanceMeters) {
return repository.findNearby(lon, lat, distanceMeters);
}
}
17.4 .NET 集成
17.4.1 依赖配置
<!-- .csproj -->
<ItemGroup>
<PackageReference Include="Npgsql" Version="8.0.0" />
<PackageReference Include="Npgsql.NetTopologySuite" Version="8.0.0" />
<PackageReference Include="NetTopologySuite" Version="2.5.0" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="8.0.0" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.NetTopologySuite" Version="8.0.0" />
</ItemGroup>
17.4.2 Npgsql 直接连接
using Npgsql;
using NetTopologySuite.Geometries;
using NetTopologySuite.IO;
var connectionString = "Host=localhost;Port=5432;Database=gis_db;Username=postgres;Password=password";
// 配置 NetTopologySuite
var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
dataSourceBuilder.UseNetTopologySuite();
await using var dataSource = dataSourceBuilder.Build();
// 查询
await using var conn = await dataSource.OpenConnectionAsync();
await using var cmd = new NpgsqlCommand(@"
SELECT id, name, geom
FROM poi
WHERE ST_DWithin(
geom::geography,
ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography,
$3
)", conn);
cmd.Parameters.AddWithValue(116.4);
cmd.Parameters.AddWithValue(39.9);
cmd.Parameters.AddWithValue(1000);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
var id = reader.GetInt32(0);
var name = reader.GetString(1);
var geom = reader.GetFieldValue<Point>(2);
Console.WriteLine($"ID: {id}, Name: {name}, Point: {geom.X}, {geom.Y}");
}
// 插入
await using var insertCmd = new NpgsqlCommand(@"
INSERT INTO poi (name, category, geom)
VALUES ($1, $2, ST_SetSRID(ST_MakePoint($3, $4), 4326))", conn);
insertCmd.Parameters.AddWithValue("新地点");
insertCmd.Parameters.AddWithValue("景点");
insertCmd.Parameters.AddWithValue(116.5);
insertCmd.Parameters.AddWithValue(39.95);
await insertCmd.ExecuteNonQueryAsync();
17.4.3 Entity Framework Core
// DbContext
using Microsoft.EntityFrameworkCore;
using NetTopologySuite.Geometries;
public class GisDbContext : DbContext
{
public DbSet<POI> POIs { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseNpgsql(
"Host=localhost;Database=gis_db;Username=postgres;Password=password",
o => o.UseNetTopologySuite()
);
}
}
// Entity
public class POI
{
public int Id { get; set; }
public string Name { get; set; }
public string Category { get; set; }
public Point Geom { get; set; }
}
// 使用
using var context = new GisDbContext();
// 查询
var queryPoint = new Point(116.4, 39.9) { SRID = 4326 };
var nearbyPOIs = context.POIs
.Where(p => p.Geom.Distance(queryPoint) < 0.01)
.ToList();
// 空间查询（使用原生 SQL）
var results = context.POIs
.FromSqlRaw(@"
SELECT * FROM poi
WHERE ST_DWithin(
geom::geography,
ST_SetSRID(ST_MakePoint({0}, {1}), 4326)::geography,
{2}
)", 116.4, 39.9, 1000)
.ToList();
// 插入
var newPOI = new POI
{
Name = "新地点",
Category = "景点",
Geom = new Point(116.5, 39.95) { SRID = 4326 }
};
context.POIs.Add(newPOI);
await context.SaveChangesAsync();
17.5 Node.js 集成
17.5.1 使用 node-postgres
const { Pool } = require('pg');
const pool = new Pool({
host: 'localhost',
port: 5432,
database: 'gis_db',
user: 'postgres',
password: 'password'
});
// 查询
async function findNearbyPOIs(lon, lat, distance) {
const query = `
SELECT id, name, ST_AsGeoJSON(geom) as geojson
FROM poi
WHERE ST_DWithin(
geom::geography,
ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography,
$3
)
`;
const result = await pool.query(query, [lon, lat, distance]);
return result.rows.map(row => ({
...row,
geojson: JSON.parse(row.geojson)
}));
}
// 插入
async function createPOI(name, category, lon, lat) {
const query = `
INSERT INTO poi (name, category, geom)
VALUES ($1, $2, ST_SetSRID(ST_MakePoint($3, $4), 4326))
RETURNING id
`;
const result = await pool.query(query, [name, category, lon, lat]);
return result.rows[0].id;
}
// 使用
(async () => {
const pois = await findNearbyPOIs(116.4, 39.9, 1000);
console.log(pois);
const newId = await createPOI('新餐厅', '餐饮', 116.5, 39.95);
console.log(`Created POI with ID: ${newId}`);
})();
17.6 本章小结
本章详细介绍了多种编程语言与 PostGIS 的集成：
- Python：psycopg2、SQLAlchemy/GeoAlchemy2、GeoPandas
- Java：JDBC、JPA/Hibernate Spatial
- .NET：Npgsql、Entity Framework Core
- Node.js：node-postgres
17.7 下一步
在下一章中，我们将学习高级应用与案例，包括实战项目和最佳实践。
相关资源：
浙公网安备 33010602011771号