PostGIS数据载入与检索 - CadGIS - 博客园
PostGIS数据载入与导出常见的方法有三种：1.使用SQL语句这是最简单最直观的加载数据的方法，只是稍稍不同于普通的insert语句如数据库中的表结构如下&&&&&&& CREATE TABLE "sites" (gid serial PRIMARY KEY,&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& "id"&&&&&&&& int4,&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& "cover"&& varchar(20));&&&&&&& SELECT AddGeometryColumn('','sites','the_geom','4269','POINT',2);--加载数据的脚本roads.sqlBEGIN;INSERT INTO "sites" ("id","cover",the_geom) VALUES ('1','shrubs','& & & & & & & & & & & & & POINT(8)');INSERT INTO "sites" ("id","cover",the_geom) VALUES ('2','trees','&&&&&&&&&&&&&&&&&&&&&&&&& POINT(9.)');INSERT INTO "sites" ("id","cover",the_geom) VALUES ('3','rocks','& & & & & & & & & & & & & POINT(6)');。。。 COMMIT;可以通过psql执行脚本从而将数据导入到PostgreSQL中#psql -d postgis -f roads.sql&从数据库中检索数据，和普通的sql查询没有什么区别，如下面这个语句是检索cover字段值为‘grass’的所有要素 &postgis=# select ogc_fid,ST_AsEWKT(wkb_geometry),cover from sites where cover='grass';&2.shp2pgsql和pgsql2shp、shp2pgsql-gui(1)shp2pgsql命令行用法: shp2pgsql [&选项&] &shp文件& [&schema&.]&表名&shp2pgsql最简单的形式就是 shp2pgsql shapefile.shp形式，如下 [postgres@localhost ~]$ shp2pgsql sites.shp Shapefile type: Point& &==要素类型Postgis type: POINT[2]----------------------------------------------------------------------SET CLIENT_ENCODING TO UTF8;SET STANDARD_CONFORMING_STRINGS TO ON;BEGIN;CREATE TABLE "sites" (gid serial PRIMARY KEY, -- 其实默认情况下表名和shp文件名相同&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& "id" int4,&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& "cover" varchar(20));SELECT AddGeometryColumn('','sites','the_geom','-1','POINT',2);INSERT INTO "sites" ("id","cover",the_geom) VALUES&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& ('1','shrubs','CA66AC01CE1B41B141');INSERT INTO "sites" ("id","cover",the_geom) VALUES ('2','trees','DF761B2AD5141');INSERT INTO "sites" ("id","cover",the_geom) VALUES ('3','rocks','F31C9DC6ED1AAC59E5141');。。。 上面的一长窜数字是geometry的wkb形式，到这里是不是就很明了啊，其实shp2pgsql的作用就是把shp文件转换为SQL语句，再将产生的定义表结构和插入数据sql语句用 "&" 重定向到指定文件中，如下面的例子将转换结果重定向到sites.sql[postgres@localhost ~]$ shp2pgsql sites.shp sites & sites.sql可以通过shp2pgsql -？来查看shp2pgsql的用法常用的选项是-&&&& -(c|a|d|p) 互斥选项&& &&& &-c 默认选项，创建新表和插入数据&& &&& &-a 追加shape文件到已存在的表中&& &&& &-d 先删除表，再创建表和插入数据&& &&& &-p 只是创建表&& &-g 指定几何要素的列名&& &-I 创建空间索引&& &-s 生成简单要素，而不是几何对象集合(multiGeometry)[postgres@localhost ~]$shp2pgsql -c -s 4269 -I sites.shp sites & sites.sql(2)pgsql2shp用法：pgsql2shp [&选项&] &数据库& &表名&pgsql2shp [&选项&] &数据库& &查询语句&常用选项：&& &-f 导出文件名&& &-h 主机地址&& &-p 端口号&& &-P 密码&& &-u 用户名将数据库postgis中的sites表导出到sites.shp,指定用户名和密码[postgres@localhost ~]$pgsql2shp -f sites2.shp -P 123456 -u postgres postgis sites将数据库postgis中的sites表按照sql查询语句导出到sites.shp,指定用户名和密码[postgres@localhost ~]$pgsql2shp -f sites3.shp -P 123456 -u postgres postgis "select * from sites where cover='grass'"(3)shp2pgsql-gui 就不多说了，傻瓜型的图形界面的导入工具3.利用ogr2ogr命令行工具&&&&& shp2pgsql等工具虽然很好用，但是他们最大的缺点就在于其使用范围有限，只能够针对Shape文件格式。而实际应用过程中可能会碰到各种各样的数据格式，这个时候就得需要ogr2ogr上场了，它gdal的一个命令行工具，支持目前主流的数据格式，如ESRI Shapefile、MapInfo等数据格式，在命令行上直接敲ogr2ogr就会显示其帮助信息，帮助信息中包括它支持的所有格式的详细信息。下面就简单地介绍ogr2ogr常用的一些操作ogr2ogr --help& &==查看帮助信息数据转换 (1)MapInfo ==& ESRI Shape[postgres@localhost ~]$ ogr2ogr -f "ESRI Shapefile" mydata.shp mydata.tab(2)ESRI Shape ==&MapInfo[postgres@localhost ~]$ ogr2ogr -f "MapInfo File" tabsites.tab sites.shp(3)MapInfo ==& PostGIS[postgres@localhost ~]$ ogr2ogr -f "PostgreSQL" PG:"host=localhost user=postgres dbname=postgis password=123456"(4)postgis==&ESRI Shapefile[postgres@localhost ~]$ ogr2ogr -f "ESRI Shapefile" mydata.shp PG:"host=localhost dbname=postgis user=postgres password=123456" "mytable"(5)PostGIS ==& KML[postgres@localhost ~]$ ogr2ogr -f "KML" neighborhoods.kml PG:"host=localhost dbname=postgis user=postgres password=123456" -sql "select gid,name,the_geom from neighborhoods"(6)批量转换将postgis中所有的表都导出到mydatadump文件夹下，导出格式是ESRI Shapefile[postgres@localhost ~]$& ogr2ogr -f "ESRI Shapefile" mydatadump PG:"host=myhost user=myloginname dbname=mydbname password=mypassword"部分导出，将指定的表导出到mydatadump中，格式为ESRI Shapefile[postgres@localhost ~]$ ogr2ogr -f "ESRI Shapefile" mydatadump PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" neighborhood parcels(7)ESRI GeoDatabase (*.mdb) ==&PostGIS[postgres@localhost ~]$ ogr2ogr -f "PostgreSQL" PG:"host=localhost user=someuser dbname=somedb password=somepassword port=5432"&C:\GISData\Geonames.mdb -a_srs EPSG:26986导入指定的featureclass，重投影，重命名geometry列[postgres@localhost ~]$ ogr2ogr -f "PostgreSQL" PG:"host=localhost user=someuser dbname=somedb" /home/postgres/Data/Geonames.mdb GEONAMES_ANNO_HYDRO -a_srs EPSG: 26986 -t_srs EPSG:4269 -nln ma_hydro -lco GEOMETRY_NAME=the_geom_4269(8)ESRI Shapefile ==&MySQL[postgres@localhost ~]$ ogr2ogr -f "MySQL" MYSQL:"mydb,host=myhost,user=mylogin,password=mypassword,port=3306" -nln "world" -a_srs "EPSG:4326" path/to/world_adm0.shp (9)Non-spatial Data ==&PostgreSQL[postgres@localhost ~]$& ogr2ogr -f "PostgreSQL" PG:"host=myserver user=myusername dbname=mydbname password=mypassword" sometable.dbf -nln "sometable"下面是如何将shp和tab文件导入到postgis数据库中的命令方式[postgres@localhost ~]$ ogr2ogr -f PostgreSQL PG:"host=localhost dbname=postgis user=postgres password=850315" sites.shp[postgres@localhost ~]$ ogr2ogr -f PostgreSQL PG:"host=localhost dbname=postgis user=postgres password=850315" mytabfile.tab通过 -nln指定导入数据库中的表名，而不是默认的文件名作为表名[postgres@localhost ~]$ ogr2ogr -f "PostgreSQL" PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" mytabfile.tab -nln newtablename通过 -a_srs 选项指定输出的投影[postgres@localhost ~]$ ogr2ogr -f "PostgreSQL" -a_srs "EPSG:2249" PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" mytabfile.tab通过-sql 选项后面的SQL语句对postgis中的数据经行筛选后到处到shp文件中ogr2ogr -f "ESRI Shapefile" mydata.shp PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" -sql "SELECT name, the_geom FROM neighborhoods" 可以通过ogrinfo命令来查看元数据[postgres@localhost ~]$ ogrinfo sites.shp此外，也可以通过编程实现数据的导入导出，而且编程实现数据的导出导入灵活性很大&&& 编程语言：python&&& 使用到的开发包：ogr，psycopg2&&& 以后有机会整理一下代码再补充上来，现在事情比较多，而且代码也比较零乱扫扫二维码，随身浏览文档
手机或平板扫扫即可继续访问
ArcMAP操作指南
举报该文档为侵权文档。
举报该文档含有违规或不良信息。
反馈该文档无法正常浏览。
举报该文档为重复文档。
推荐理由：
将文档分享至：
分享完整地址
文档地址：
粘贴到BBS或博客
flash地址：
支持嵌入FLASH地址的网站使用
html代码：
&embed src='/DocinViewer-4.swf' width='100%' height='600' type=application/x-shockwave-flash ALLOWFULLSCREEN='true' ALLOWSCRIPTACCESS='always'&&/embed&
450px*300px480px*400px650px*490px
支持嵌入HTML代码的网站使用
您的内容已经提交成功
您所提交的内容需要审核后才能发布，请您等待！
3秒自动关闭窗口推荐这篇日记的豆列
······创建File GeoDataBase 和栅格目录及向栅格目录中添加影像 - WY_laoK - 博客园
Powered by:
模板提供：
&&&&& 实现过程：创建或打开一个FileGDB--&创建或打开一个栅格目录--&选择一个文件夹，把此文件夹下的栅格数据导入栅格目录：
下面为核心代码
using System.IO;
using ESRI.ArcGIS.DataSourcesGDB;
using ESRI.ArcGIS.DataSourcesR
using ESRI.ArcGIS.G
using ESRI.ArcGIS.G
using ESRI.ArcGIS.DataManagementT
using ESRI.ArcGIS.G
using ESRI.ArcGIS.G
using ESRI.ArcGIS.esriS
using ESRI.ArcGIS.DataSourcesF
using ESRI.ArcGIS.C
namespace RasterOperation
public class RasterCatalogOperation
/// &summary&
/// fileGDB路径
/// &/summary&
public string FileGDBPath
/// &summary&
/// 栅格数据目录
/// &/summary&
public string SourceRasterDic
/// &summary&
/// 栅格目录名称
/// &/summary&
public string RasterDSName
/// &summary&
/// 栅格坐标系
/// &/summary&
public ISpatialReference RasterSpRf
/// &summary&
/// 矢量坐标系
/// &/summary&
public ISpatialReference GeometrySpRf
/// &summary&
/// 栅格目录转换为图层
/// &/summary&
/// &param name="folderName"&目录名&/param&
/// &param name="datasetName"&数据集名&/param&
/// &returns&要素图层&/returns&
public ILayer AddRasterCatalogLayer(string folderName, string datasetName)
IWorkspaceFactory workspaceFactory = new FileGDBWorkspaceFactoryClass();
IWorkspace workspc = workspaceFactory.OpenFromFile(folderName, 0);
IRasterWorkspaceEx pRaterWs = (IRasterWorkspaceEx)
IRasterCatalog rasterCatalog = pRaterWs.OpenRasterCatalog(datasetName);
ESRI.ArcGIS.Carto.IGdbRasterCatalogLayer rastercatalogLayer = new
GdbRasterCatalogLayerClass();
rastercatalogLayer.Setup((ITable)rasterCatalog);
//Add it to map if the layer is valid.
if (!(rastercatalogLayer == null))
return rastercatalogLayer as IL
return null;
return null;
//Create a raster catalog layer.
/// &summary&
/// 创建FileGDB
/// &/summary&
/// &param name="fullPath"&路径名&/param&
public void CreateFileGDB(string fullPath)
if (!Directory.Exists(fullPath))
IWorkspaceFactory2 wsFctry = new FileGDBWorkspaceFactoryClass();
wsFctry.Create(System.IO.Path.GetDirectoryName(fullPath),
System.IO.Path.GetFileName(fullPath), null, 0);
FileGDBPath = fullP
wsFctry = null;
/// &summary&
/// 创建RasterCatalog
/// &/summary&
/// &param name="path"&路径&/param&
/// &param name="catalogName"&名称&/param&
/// &param name="rasterCoordSys"&栅格坐标系&/param&
/// &param name="geometryCoordsys"&矢量坐标系&/param&
public void CreateRasterCatalog_GP(string path, string catalogName, ISpatialReference rasterCoordSys, ISpatialReference geometryCoordsys)
//坐标转换
//Coordinate system for raster column
IGPCoordinateSystem rSR = new GPCoordinateSystemClass();
rSR.SpatialReference = rasterCoordS
//Coordinate system for geometry column
IGPSpatialReference gSR = new GPSpatialReferenceClass();
gSR.SpatialReference = geometryC
//初始化GeoProcessor
ESRI.ArcGIS.Geoprocessor.Geoprocessor geoProcessor = new ESRI.ArcGIS.Geoprocessor.Geoprocessor();
RasterDSName = catalogN
//创建工具
CreateRasterCatalog createRasterCatalog = new CreateRasterCatalog();
//设置参数
createRasterCatalog.out_path =
createRasterCatalog.out_name = catalogN
createRasterCatalog.raster_spatial_reference = rSR;
createRasterCatalog.spatial_reference = gSR;
//利用工具创建RasterCatalog
geoProcessor.Execute(createRasterCatalog, null);
//ReturnMessages(geoProcessor);
//GP message handling
public void ReturnMessages(Geoprocessor gp)
if (gp.MessageCount & 0)
for (int Count = 0; Count &= gp.MessageCount - 1; Count++)
Console.WriteLine(gp.GetMessage(Count));
//static string rasterFolder = @"c:\tempdata";
//static string outRC = @"Database Connections\connection to raster.sde\rc_203";
//LoadDirtoRasterCatalog(outRC, rasterFolder);
/// &summary&
/// 输入整个目录到库中
/// &/summary&
/// &param name="outRasterCatalog"&目标栅格库全路径&/param&
/// &param name="inputDir"&输入目录&/param&
public void LoadDirtoRasterCatalog(string outRasterCatalog, string inputDir)
if (!Directory.Exists(inputDir))
System.Windows.Forms.MessageBox.Show("路径不正确，请重新输入", "提示");
//初始化 GeoProcessor
ESRI.ArcGIS.Geoprocessor.Geoprocessor geoProcessor = new ESRI.ArcGIS.Geoprocessor.Geoprocessor();
//设置参数
IVariantArray parameters = new VarArrayClass();
//输入的文件夹目录
parameters.Add(inputDir);
//目标栅格库路径
parameters.Add(outRasterCatalog);
//Execute the tool to load rasters in the directory to raster catalog
geoProcessor.Execute("WorkspaceToRasterCatalog", parameters, null);
// ReturnMessages(geoProcessor);
/// &summary&
/// 选择要素的坐标系
/// &/summary&
/// &param name="refFileName"&&/param&
/// &returns&&/returns&
public ISpatialReference InputReferece()
System.Windows.Forms.OpenFileDialog openFileDialog = new System.Windows.Forms.OpenFileDialog();
if (SourceRasterDic != string.Empty || SourceRasterDic != " ")
openFileDialog.InitialDirectory = SourceRasterD
openFileDialog.Title = "加载图层数据";
openFileDialog.Filter = "Tif文件(*.tif)|*.tif|Shp文件(*.shp)|*.shp|Jpg文件(*.jpg)|*.jpg|Bmp文件(*.bmp)|*.bmp|Gif文件(*.gif)|*.gif|Img文件(*.img)|*.img|Png文件(*.png)|*.png|Tiff文件(*.tiff)|*.tiff";
openFileDialog.Multiselect = false;
openFileDialog.DefaultExt = "*.tif|*.shp";
openFileDialog.SupportMultiDottedExtensions = true;
string refFileName = string.E
if (openFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
refFileName = openFileDialog.FileN
return null;
IWorkspaceFactory pWorkspaceFactory = null;
string p = System.IO.Path.GetExtension(refFileName);
string fileNameWithoutExtentsion = System.IO.Path.GetFileNameWithoutExtension(refFileName);
string pathName = System.IO.Path.GetDirectoryName(refFileName);
string fileName = System.IO.Path.GetFileName(refFileName);
if (p == ".shp" || p == ".SHP")
IFeatureClass pFeatureC
pWorkspaceFactory = new ShapefileWorkspaceFactory();
ws = pWorkspaceFactory.OpenFromFile(pathName, 0) as IFeatureW
pFeatureClass = ws.OpenFeatureClass(fileName);
ws = null;
return (pFeatureClass as IGeoDataset).SpatialR
if (p == ".jpg" || p == ".bmp" || p == ".gif" || p == ".img" || p == ".png" || p == ".tif" || p == ".tiff"
|| p == ".JPG" || p == ".BMP" || p == ".GIF" || p == ".IMG" || p == ".PNG" || p == ".TIF" || p == ".TIFF")
IRasterLayer pRasterLayer = new RasterLayerClass();
IRasterWorkspace pRasterW
IRaster pR
IRasterDataset pRasterD
IWorkspace pW
pWorkspaceFactory = new RasterWorkspaceFactoryClass();
pWorkspace = pWorkspaceFactory.OpenFromFile(pathName, 0);//0
pRasterWorkspace = pWorkspace as IRasterW
pRasterDataset = pRasterWorkspace.OpenRasterDataset(fileName);
pRaster = pRasterDataset.CreateDefaultRaster();
pRasterLayer.CreateFromRaster(pRaster);
IRasterProps pRasterProps = pRasterLayer.Raster as IRasterP
return pRasterProps.SpatialR
return null;
/// &summary&
/// 获取要素数据集
/// &/summary&
/// &param name="workspace"&工作空间&/param&
/// &returns&要素数据集&/returns&
public IFeatureClass GetFirstFClass(IWorkspace workspace)
IFeatureClass FClass = null;
IEnumDataset enumDataset = workspace.get_Datasets(esriDatasetType.esriDTAny);
enumDataset.Reset();
IDataset perFeatSet = enumDataset.Next();
while (perFeatSet != null)
esriDatasetType getDatasetType = perFeatSet.T
switch (getDatasetType)
case esriDatasetType.esriDTFeatureClass:
ILayerFactoryHelper layerFactoryHelper = new LayerFactoryHelperClass();
IEnumLayer enumLayer = layerFactoryHelper.CreateLayersFromName(perFeatSet.FullName);
enumLayer.Reset();
ILayer layer = enumLayer.Next();
while (layer != null)
//筛选出第一个面状要素数据集
if (layer is IFeatureLayer &&
(layer as IFeatureLayer).FeatureClass.ShapeType
== ESRI.ArcGIS.Geometry.esriGeometryType.esriGeometryPolygon)
return (layer as IFeatureLayer).FeatureC
layer = enumLayer.Next();
System.Runtime.InteropServices.Marshal.ReleaseComObject(enumLayer);
case esriDatasetType.esriDTFeatureDataset:
case esriDatasetType.esriDTTable:
case esriDatasetType.esriDTRasterDataset:
perFeatSet = enumDataset.Next();
System.Runtime.InteropServices.Marshal.ReleaseComObject(enumDataset);
阅读(...) 评论()