PostgreSQL和PostGIS学习笔记

一、建空间数据库# 1.创建普通关系型数据库 CREATE DATABASE gisdb; # 2.连接数据库 /c gisdb; # 3.启用 PostGIS 扩展模块(以下为postgis不同的扩张模块) CREATE EXTENSION postgis; # 拓扑扩展 CREATE EXTENSION postgis_topology; # 地址解析扩展 CREATE EXTENSION postgis_tiger_geocoder;二、建空间表# 创建点表 CREATE TABLE sample_point ( id SERIAL PRIMARY KEY, name VARCHAR(100), soil_moisture NUMERIC(5,3), # Point:表示集合类型4326:World Geodetic System 1984(坐标系 SRID) geom geometry(Point,4326) ); #创建线表 CREATE TABLE road ( id SERIAL PRIMARY KEY, road_name VARCHAR(100), geom geometry(LineString,4326) );三、插入空间数据插入点数据 INSERT INTO sample_point ( name, soil_moisture, geom ) VALUES ( 样点1, 0.23, ST_SetSRID( ST_Point(116.4,39.9), 4326 ) ); 插入线数据 INSERT INTO road ( road_name, geom ) VALUES ( 测试道路, ST_GeomFromText( LINESTRING( 116.4 39.9, 116.5 39.95, 116.6 40.0 ), 4326 ) );四、创建索引# 索引的目的是让距离查询、范围查询、相交查询等的性能提升几十倍到上千倍。 # 索引提高查询的速度但是降低删改查的速度所以索引要建在经常被查询的字段上。 CREATE INDEX idx_sample_geom ON sample_point USING GIST(geom); # 联合索引最左匹配原则 CREATE INDEX idx_sat_time ON soil_sample( satellite, sample_time );四、修改空间数据UPDATE sample_point SET geom ST_SetSRID( ST_Point(116.5,40.0), 4326 ) WHERE id 1; # WKT格式 UPDATE sample_point SET geom ST_GeomFromText( POINT(116.5 40.0), 4326 ) WHERE id 1; # 修改四边形 UPDATE satellite_image SET geom ST_GeomFromText( POLYGON(( 120.71 29.9278, -- TopLeft: 经度 纬度 126.376 30.0674, -- TopRight 125.162 24.6759, -- BottomRight 119.86 24.5777, -- BottomLeft 120.71 29.9278 -- TopLeft: 闭合点 )), 4326 ) WHERE id 4;五、空间查询六、缓冲区# 1.查找时临时生成缓冲区 CREATE TABLE sample_point( id SERIAL PRIMARY KEY, name VARCHAR(50), geom geometry(Point,4326) ); SELECT id, ST_Buffer(geom,100) AS buffer_geom FROM sample_point; # 2.创建缓冲区表 CREATE TABLE camera_buffer( id SERIAL PRIMARY KEY, camera_id INT, geom geometry(Polygon,3857) ); INSERT INTO camera_buffer( camera_id, geom ) SELECT id, ST_Buffer( ST_Transform(geom,3857), 100 ) FROM camera; # 注:这里将4326转换成了3857因为4326 经纬度单位是度;3857 Web Mercator单位是米。 # 3.给表新增缓冲区字段 ALTER TABLE sample_point ADD COLUMN buffer_geom geometry(Polygon,3857); UPDATE sample_point SET buffer_geom ST_Buffer( ST_Transform(geom,3857), 100 ); # 查询缓冲区内的对象 # 查询样点100米范围内的道路 SELECT r.* FROM road r, sample_point s WHERE ST_Intersects( r.geom, ST_Buffer( ST_Transform(s.geom,3857), 100 ) );七、相交# 查询两个面相交 SELECT ST_Intersects( geom1, geom2 ); # 查询点在面内包括点在面上 SELECT ST_Intersects( district.geom, sample_point.geom );八、距离计算# 1.两点间的距离 SELECT ST_Distance( ST_Transform( ST_SetSRID( ST_Point(116.4,39.9), 4326 ), 3857 ), ST_Transform( ST_SetSRID( ST_Point(116.5,39.95), 4326 ), 3857 ) ); # 2.使用 geography推荐计算真实地球距离 SELECT ST_Distance( ST_SetSRID( ST_Point(116.4,39.9), 4326 )::geography, ST_SetSRID( ST_Point(116.5,39.95), 4326 )::geography ); # 3.计算表中两个对象的距离 CREATE TABLE sample_point( id SERIAL PRIMARY KEY, geom geometry(Point,4326) ); SELECT a.id, b.id, ST_Distance( a.geom::geography, b.geom::geography ) AS distance_m FROM sample_point a, sample_point b WHERE a.id 1 AND b.id 2; # 4.查询最近的对象 # 查询距离样点最近的摄像头 SELECT id, ST_Distance( geom::geography, ST_SetSRID( ST_Point(116.4,39.9), 4326 )::geography ) AS distance FROM camera ORDER BY distance # 只取第一条记录 LIMIT 1; # 5.查询 100 米内的对象 SELECT * FROM sample_point WHERE ST_DWithin( geom::geography, ST_SetSRID( ST_Point(116.4,39.9), 4326 )::geography, 100 );九、GeoJson互转# 1.GeoJSON 点转 SQL INSERT INTO sample_point(name, geom) VALUES ( 样点1, ST_SetSRID( ST_GeomFromGeoJSON({ type: Point, coordinates: [116.4, 39.9] }), 4326 ) ); #2.表字段中的 GeoJSON 转换 CREATE TABLE geojson_temp( id SERIAL PRIMARY KEY, name VARCHAR(100), geojson TEXT ); CREATE TABLE sample_point( id SERIAL PRIMARY KEY, name VARCHAR(100), geom geometry(Point,4326) ); INSERT INTO sample_point(name, geom) SELECT name, ST_SetSRID( ST_GeomFromGeoJSON(geojson), 4326 ) FROM geojson_temp; # 3.PostGIS 转 GeoJson SELECT id, name, ST_AsGeoJSON(geom) AS geojson FROM sample_point;PostGis方法总结# ST_SetSRID():给几何对象贴坐标系标签. # ST_Transform():修改坐标. # ST_Buffer():创建缓冲区. # ST_Distance():计算距离. # ST_Point():创建对象. # ST_GeomFromText():从字符串创建对象. # ST_Intersects():判断是否相交. # ST_Contains():判断点是否在面内. # ST_Touches():判断是否接触。 # ST_Overlaps():判断是否重叠。 # ST_Transform():平移. # ST_Rotate():旋转. # ST_Scale():缩小。 # ST_Area():面积. # ST_Perimeter():周长. # ST_Length():线长度. # ST_GeomFromGeoJSON():把 GeoJSON转成 PostGIS