PostGIS 栅格导入导出

PostGIS 栅格导入导出

前置条件

• 找一个tif的栅格数据
• 安装postgis客户端插件(调用命令)
• 新建一个栅格数据库test_raster

栅格数据举例

--美国航天飞机雷达地形测量任务下载高程数据
wget .zip
unzip srtm_12_03.zip
gdalinfo srtm_12_03.tif

使用raster2pgsql导入栅格 

-- 使用raster2pgsql命令导入栅格数据
raster2pgsql -s <SRID> -I <raster_file_path> st_raster | psql -d <your_database_name>raster2pgsql -I -s 4326 -t 32x32 "d:srtm_12_03.tif" | ./psql -h 192.168.8.49 -p 5432 -U postgres -d raster

验证raster2pgsql

--验证导入表结构
test_raster=# d srtm_12_03Table "public.srtm_12_03"Column |  Type   | Collation | Nullable |                 Default
--------+---------+-----------+----------+-----------------------------------------rid    | integer |           | not null | nextval('srtm_12_03_rid_seq'::regclass)rast   | raster  |           |          |
Indexes:"srtm_12_03_pkey" PRIMARY KEY, btree (rid)
"srtm_12_03_st_convexhull_idx" gist (st_convexhull(rast))--验证导入表数据
test_raster=# select count(1) from srtm_12_03;count
-------29768
(1 row)

栅格数据-psql导出

--栅格导出支持的格式--我们使用gtiff
SELECT short_name, long_name, can_write
FROM st_gdaldrivers()
ORDER BY short_name;--栅格导出--使用psql
--编辑数据库配置文件，开启栅格导出功能&#f，文件尾部追加2行配置
postgis.gdal_enabled_drivers = 'ENABLE_ALL'
able_outdb_rasters = True
--重启数据库
pg_ctrl restart
--导出
--psql -c "COPY (SELECT ST_AsGDALRaster(rast, 'GTiff') AS rast FROM test_raster WHERE id = 1) TO '<output_file_path>.tif'" <your_database_name>
psql -c "COPY (SELECT ST_AsGDALRaster(rast, 'GTiff') AS rast FROM srtm_12_03 WHERE rid = 1) TO '/home/postgres/aa.tif'" test_raster

栅格数据-SQL导出(大对象方式)

--栅格SQL导出--a)创建临时表
CREATE TABLE tmp_out AS
SELECT lo_from_bytea(0,ST_AsGDALRaster(ST_Union(rast), 'GTiff')) AS loidFROM srtm_12_03
WHERE rid=1;
--栅格SQL导出--b)文件流导出到本地
SELECT lo_export(loid, '/tmp/aa.tif')    FROM tmp_out;
--栅格SQL导出--c)解除大对象
SELECT lo_unlink(loid) FROM tmp_out;
--栅格导出tiff
SELECT ST_AsGDALRaster(rast, 'GTiff') As rastjpg FROM dummy_rast WHERE rid=2;
--栅格文件(大对象)使用SQL导入 (这里rast是oid类型)
INSERT INTO test_raster_4_1 (rast) VALUES (lo_import('/tmp/aa.tif'));

查询栅格数据的元信息

SELECT ST_Metadata(rast) AS metadata FROM srtm_12_03 WHERE rid = 1;
metadata
----------------------------------
(-124.97333333333333,50,32,32,0.0008333333333333334,-0.0008333333333333334,0,0,4326,1)

获取栅格数据的像素值

SELECT ST_PixelAsPolygons(rast) AS pixels FROM srtm_12_03 WHERE rid = 1;
pixels
----------------------------------
(0103000020E61000000100000005000000703D0AD7A33C5FC0AE47E17A14FE4840FC62C92F963C5FC0AE47E17A14FE4840FC62C92F963C5FC0C6925F2CF9FD4840703D0AD7A33C5FC0C6925F2CF9FD4840703D0AD7A33C5FC0AE47E17A14FE4840,3,32,19)

SHP文件导入

./shp2pgsql -s 4326 -I /opt/110m_physical/ne_110m_geography_regions_points._110m_geography_regions_points | psql -d nx -Upostgres

修改坐标系SQL

UPDATE ne_10m_coastline 
SET geom = ST_SetSRID(geom, 4326)
WHERE ST_SRID(geom) = 0;