Postgis学习笔记.doc

目录POSTGIS使用命令11.1初识POSTGIS11.2 数据库的一些操作4PostgreSQL的配置文件及用户权限6使用过的命令：9POSTGIS使用命令1.1初识POSTGIS1. 创建空间数据库CREATE DATABASE my_spatial_db TEMPLATE=template_postgis；2. 创建表CREATE TABLE gtest ( ID int4, NAME varchar(20) );3. 创建几何体SELECT AddGeometryColumn(, gtest,geom,-1,LINESTRING,2);4. 插入数据INSERT INTO gtest (ID, NAME, GEOM) VALUES (1,First Geometry,ST_GeomFromText(LINESTRING(2 3,4 5,6 5,7 8), -1);5.查看表类似show talbes（MY SQL）6. 导入数据Once you have created a spatial table, you are ready to upload GIS data to the database. Currently, there are two ways to get data into a PostGIS/PostgreSQL database: using formatted SQL statements or using the Shape le loader/dumper。首先是创建表极其几何关系：CREATE TABLE roads (road_id int4, road_name varchar(20) );SELECT AddGeometryColumn(, roads, roads_geom,-1,LINESTRING,2);然后用导入脚本文件：psql -U postgres -W -f E:road.sql my_spatial_db7. 检验函数SELECTst_isvalid(LINESTRING(0 0, 1 1),st_isvalid(LINESTRING(0 0, 0 0);By default, PostGIS does not apply this validity check on geometry input, because testing for validity needs lots of CPU time for complex geometries, especially polygons. If you do not trust your data sources, you can manually enforce such a check to your tables by adding a check constraint:ALTER TABLE mytableADD CONSTRAINT geometry_valid_checkCHECK (isvalid(the_geom);8. 查询SELECT road_id, ST_AsText(roads_geom) AS geom, road_name FROM roads;SELECT road_id, road_name FROM roads WHERE roads_geom = ST_GeomFromText(LINESTRING(191232 243118,191108 243242),-1);SELECT ST_AsText(roads_geom) AS geom FROM roads WHERE roads_geom & SetSRID(BOX3D(191232 243117,191232 243119):box3d,-1);补充：& This operator tells whether the bounding box of one geometry intersects the bounding box of another.= This operators tests whether two geometries are geometrically identical. For example, if POLYGON(0 0,1 1,1 0,0 0) isthe same as POLYGON(0 0,1 1,1 0,0 0) (it is).= This operator is a little more naive, it only tests whether the bounding boxes of two geometries are the same9查看POSTGIS版本号SELECT PostGIS_Full_Version();1.2 数据库的一些操作1.列出所有配置用户或是只列出那些匹配pattern的用户du2.查看编码方式encoding3.查看服务器上所有数据库（类似show databases之MYSQL）l 或者是list4.查看当前数据库所有表(类似show tables之MYSQL)5. 查看表的结构d roads 即d 表名6.创建索引创建一个表：CREATE TABLE emp(id serial, empname varchar(50), sal numeric(9,2);添加新的字段ALTER TABLE emp ADD COLUMN currency varchar(4);变更字段ALTER TABLE emp ALTER COLUMN currency SET DEFAULT USD;创建索引CREATE INDEX idx_oldemp_salcur ON emp (salary,currency);PostgreSQL的配置文件及用户权限PostgreSQL的配置文件及用户权限作者：小P来自：LinuxSir.Org摘要：为了能够远程修改和维护PostgreSQL数据库，我们会涉及到配置文件和用户权限的问题，本文主要介绍了如何配置PostgreSQL的主要配置文件，修改用户权限，使我们能够远程打开数据库;目录1. 修改配置文件; 1.1 postgresql.conf; 1.2 pg_hba.conf; 1.3 重新启动postgresql服务器；2. 配置用户权限; 2.1 创建一个用户; 2.2 创建一个数据库data1,属主为test1; 2.3 创建一个数据表;2.4 修改数据表的属主;3. 关于本文; 4. 更新日志;5. 参考文档; 6. 相关文档; +正文+为了能远程使用postgresql数据库,比如说我要在网络上另外一台Windows电脑上使用pgAdmin3来管理使用该Postgresql服务器,您就需要进行一些修改，这些修改主要包括两方面的修改:一是修改Postgresql的两个配置文件，二是修改数 据库的一些用户权限;1. 修改配置文件;注：记得修改任何东西之前都需要备份一下;1.1 postgresql.conf;首先我们需要修改Postgresql的配置文件postgresql.conf，该文件在数据目录/etc/postgresql/8.2/main/下，终端下执行：rootxiaop-laptop:/home/xiaop# vim /etc/postgresql/8.2/main/postgresql.conf将其中的一句：listen_address=localhost前面的注释#去掉，并把localhost该为*;1.2 pg_hba.conf;然后我们再来修改Postgresql的配置文件pg_hba.conf，该文件在数据目录/etc/postgresql/8.2/main/下，终端下执行rootxiaop-laptop:/home/xiaop# vim /etc/postgresql/8.2/main/pg_hba.conf在文件后面加一句：host all all /24 password这句的意思是：同网络中192.168.1.*的机器可以以密码的形式使用所有的数据库。更具体的参数意义直接看该配置文件中的注释就可以了;这里一定要配置正确，否则无法在远程连接postgresql数据库。1.3 重新启动postgresql服务器；rootxiaop-laptop:/home/xiaop# /etc/init.d/postgresql-8.2 restart2. 配置用户权限;这里介绍一些数据库用户权限的简单配置，以下是在psql中使用：2.1 创建一个用户;xiaop=# CREATE USER test1 ;CREATE ROLE2.2 创建一个数据库data1,属主为test1;xiaop=# CREATE DATABASE data1 owner test1;CREATE DATABASE2.3 创建一个数据表;xiaop=# CREATE TABLE test11() ;CREATE TABLE2.4 修改数据表的属主;xiaop=# ALTER TABLE test11 OWNER TO test1;ALTER TABLE现在您便可以在局域网内的其他电脑使用其他postgresql客户端工具，以用户名test1打开data1数据库了。3. 关于本文;本文大部分资料都是参照中文文档，目的是让兄弟们查找方便一些，详细的东西在中文文档都有，多谢各位弟兄们指点 ：）4. 更新日志;5. 参考文档;PostgreSQL 8.1 中文文档6. 相关文档;PostgreSQL安装和简单使用Postgresql备份和恢复-SQL转储篇PostgreSQL数据库用户认证PostgreSQL数据库的日常维护工作使用过的命令：1.备份和还原数据表copy test11 to c: data.csv delimiter as , csv quote as 可以将表中的数据输出到 文件或标准输出。delimiter as 分隔符 ：说明输出设备的文本中表的每个字段用什么字符分割，默认是tab；null as null表示的字符串：说明输出设备的文本中表的NULL值的表示方法，默认为“N”；csv quote as 引号类型 ：说明导出的csv文件中的引号类型是什么，对于Postgres7.4以前的版本，不支持csv的导入与导出，这时不要使用这个可选项。copy 表名 form 文件名|标准输入 delimiter as 分隔符 null as null表示的字符串 csv quote as 引号的类型2.导入和导出数据库这里介绍的是数据库的导入与导出，其实，单个数据表的导入与导出也是这些命令，只是携带的”参数“不同而已。具体的方法，可以在Shell中运行 “命令 help” 来查看。下面只是介绍常用的对数据库的导入与导出方法。导出数据库。命令：$pg_dump testdb testdump.dmp解释：将数据库testdb备份成文件testdump.dmp。包括表的建立与表中的数据。导入数据库。命令：$create -E UTF8 testdb1$psql testdb1 testdump.dmp解释：1、先创建一个数据库testdb1，为了将dump文件导入这个数据库，-E选项是为了指定这个数据库的字符集。取决于导出数据库的字符集。2、psql 命令使用重定向符”createdbU postgres W T template-postgis nyc导入数据D:pgbinpsql -U postgres -W -f C:Documents and SettingsAdministrator桌面pgnyc_buildings.sql nyc导入数据成功提示4. shp文件6. 创建空间数据库CREATE DATABASE mydb TEMPLATE=template_postgis；shp2pgsql nyc_roads.shp nycroadnycroad.sql导入数据库表psql U postgres d mydb nycroad.sql库PostgreSQL开发入门C/C+/Database/Postgre/73654_4.html本文介绍使用 C/ 结合数据库自带的 libpq 和 libPq+ 库来开发 Linux 下有名的免费数据库 PostgreSQL，详细描述了怎样使用开发库来操作数据的步骤和流程。PostgreSQL数据库介绍 WWW.SYUE.COM PostgreSQL 是一种运行在Unix和Linux操作系统(在NT平台借助Cygnus也可以运行)平台上的免费的开放源码的关系数据库。最早是由美国加州大学伯克利分 校开发的，开始只是作为一个演示系统发表，但是随着时间的推移，逐步分发，得到很多实际的应用，才逐步流行起来。现在版本发展到了7.3.3。 信息来源岁月联盟 荣誉： 来源岁月联盟 1999年获得Linux World杂志的该年度最佳数据库产品称号。 2000年荣获Linux Journal杂志编辑选择的最佳数据库奖。 2003年，也就是最近再一次荣获Linux Journal杂志编辑选择的 最佳数据库奖。 2.主要功能和特性 来源岁月联盟 本文来自岁月联盟 支持SQL。作为关系数据库，它支持SQL89标准也支持部分SQL92大部分功能。 有丰富的数据类型。许多数据类型是一些商业数据库都没有提供的。 面向对象。它包含了一些面向对象的技术，如继承和类。 支持大数据库，它不同于一般的桌面数据库，能够支持几乎不受限制大小的数据库，而且性能稳定。 方便集成web，提供一些接口方便 PHP，Perl等语言操作数据库。 事务处理。相对一些其他免费数据库如MySQL，他提供了事务处理，可以满足一些商业领域的数据需要。3.数据类型 岁月联盟提供PostgreSQL支持丰富的数据类型，许多数据类型在商业数据库中都无法提供。 来源岁月联盟 来源岁月联盟 数值类型。包括整数、任意精度、浮点类型和序列类型数值。 货币类型。范围-21474836.48 到 +21474836.47。 字符类型。分为定长类型，不定长有限制类型和不定长不限制长度类型。 二进制字符串。 日期和时间类型 布尔类型。 几何类型。包括点、线、方形、路径、多边形和圆。 网络地址类型。 位串类型。就是包含1和0的串。 对象标识符类型 数组类型。4.数据库极限 (www.S)参数名称 最大范围 数据库大小 无限制 表大小 16TB(所有系统) 一条记录大小 1.6GB 字段大小 1GB 表记录条数 无限制 表字段数 250-1600(取决于字段的数据类型) 表索引 无限制 事实上不可能存在无限制的情况。支持16TB大小的表不是单一文件保存，而是分为多个文件的，所以不受限制于操作系统。 岁月联盟4安装 内容来自岁月联盟我们可以到 网站，具体怎么安装我就不赘述，大家可以看文档,也可以看 参考中的中文网站的文档。在这里我就说说安装中需要注意的几个问题。 本文来自岁月联盟 注意 信息来源岁月联盟 不能以root登陆Linux来操作数据库(启动数据库服务除外)，请以root身份建立postgres用户，设置密码后登录。 安装完postgreSQL，可能需要初始化数据库，需要以postgres用户登录系统，运行 initdb -pqlib=/usr/lib/pqsql -pqdata=/var/lib/pqsal www.S 如果你的pqlib目录不同则需要按照你的实际路径修改，pqdata也是这样的。 数据库用户与Linux用户是不同的概念，只有数据库用户才能操作数据库，以postgres登录Linux，然后是使用createuser来添加用户，dropuser来删除用户，也可以使用SQL命令CREATE USER来创建。5常用命令 信息来源岁月联盟 PostgreSQL数据库常用操作命令： 信息来自岁月联盟 Createdb 创建数据库 Createuser 创建数据库用户 Dropdb 删除数据库 Dropuser 删除数据库用户 Psql 交互式PostgreSQL前端工具,可以用它来操作数据库 Initdb 初始化postgreSQL数据库 这里不是介绍怎样使用PostgreSQL数据库，所以对这些命令也不详细解释，可以参见文档说明和帮助。我在这里是假设读者已经安装并初始化好了数据库，并且会使用psql和熟练SQL来交互操作数据。 信息来自岁月联盟PostgreSQL 的C/接口介绍 岁月联盟提供PostgreSQL提供很多不同语言的接口，有C、C+、Perl、Tcl等等，这里主要是介绍C/C+怎样连接到数据库，对数据进行添加，查询等操作。 1.C语言接口 头文件libqp-fe.h 连接时需要添加连接参数-lpq也就是连接libpq库。 建立数据库连接函数 PGconn *PQconnectdb( const char *conninfo) PGconn *PQsetdbLogin(const char *pghost, const char *pgport, const char *pgoptions, const char *pgtty, const char *dbName, const char *login, const char *pwd) 岁月联盟提供 建立非阻塞连接函数 www.SPGconn *PQconnectStart(const char *conninfo)PostgresPollingStatusType *PQconnectPoll(PQconn *conn) 信息来源岁月联盟 执行查询函数 PGresult *PQexec(PGconn *conn,const char *query) 返回查询的结构集。int PQntuples(const Pgresult *res) 返回查询结果里的记录个数。int PQnfields(const Pgresult *res) 返回记录中的字段的个数。char *PQfname(const Pgresult *res,int field_index)返回结构集中某一字段的名称。Oid PQftype(const Pgresult *res, int field_index) 返回结构集中某一字段的数据类型。char *PQgetvalue(const Pgresult *res,int tup_num, int field_index)返回结构集中某一条记录中某一个字段的值。 S 这里只列举最常用的一些函数，具体说明可以参见文档。 内容来自岁月联盟 大对象数据操作,主要使用下列函数在SQL语句中操作大对象数据。 Oid lo_creat(PGconn *conn, int mode) 岁月联盟 创建一个新的大对象mode 是一个位掩码，描述新对象的不同属性 Oid lo_import(PGconn *conn, const char *filename) S把文件作为大对象导入数据库 int lo_export(PGconn *conn, Oid lobjId, const char *filename) 来源岁月联盟 大对象导入出数据库，保存为文件 int lo_open(PGconn *conn, Oid lobjId, int mode) 来源岁月联盟打开一个现存的大对象 int lo_write(PGconn *conn, int fd, const char *buf, size_t len) 本文来自岁月联盟大对象写入数据 int lo_read(PGconn *conn, int fd, char *buf, size_t len) 岁月联盟 从大对象读取数据 int lo_lseek(PGconn *conn, int fd, int offset, int whence) 岁月联盟提供 对大对象数据进行查找 int lo_close(PGconn *conn, int fd) 关闭大对象描述符 函数在fe-lobj.c文件中定义，具体使用参见 Accessing Large Objects from libpq WWW.SYUE.COM 上面函数的具体介绍请参见 PostgreSQL v7.0文档 在这里就不详细介绍了。这里省略列举状态检查函数，参见参考文档。 内容来自岁月联盟 使用C语言访问数据库的除了libpq库，还有其他如 libpqeasy，ecpg 等接口。 信息来自岁月联盟 2.C+语言接口 本文来自岁月联盟www.S 头文件 libpq+.h 连接需要的库位 pq+，连接参数是 -lpq+ libpq+ 类继承关系图 内容来自岁月联盟图 1 类简介 PgConnection成员函数 PgConnection(const char* conninfo) 参数同下 Connect(const char* conninfo) 同 PQconnectdb Exec(const char* query) 同 PQexec ExecCommandOk(const char* query) 运行命令并检查状态是否PGRES_COMMAND_OK ExecTuplesOk(const char* query) 运行查询并检查状态是否PGRES_TUPLES_OK IntToString(int n) 数字转换为字符串 ConnectionBad() 连接是否失败 ErrorMessage() 返回错误信息 DBName() 同 PQdb 详细说明参见文档 SYUE.COM PgDatabase 的常用成员函数 PgDatabase(const char* conninfo) 构造函数连接到数据库，跟 Pqconnectdb参数相同 Tuples() 同 PQntuples(); CmdTuples() 同 PQcmdTuples Fields() 同 PQnfields FieldName(int field_num) 同 PQfname FieldNum(const char* field_name) 同 PQfnumber FieldType(int ield_num) FieldType(const char* field_name) 信息来源岁月联盟 同 PQftype FieldSize(int field_num) FieldSize(const char* field_name) 信息来源岁月联盟同 PQfsize GetValue(int tup_num, int field_num) GetValue(int tup_num, const char* field_name) 岁月联盟 同 PQgetvalue GetIsNull(int tup_num, int field_num) GetIsNull(int tup_num, const char* field_name) 信息来自岁月联盟 同 PQgetisnull GetLength(int tup_num, int field_num) GetLength(int tup_num, const char* field_name) 来源岁月联盟同 PQgetlength GetLine(char* string, int length) 同 PQgetline PutLine(const char* string) 同 PQputline EndCopy() 同 PQendcopy 大对象操作使用 PgLargeObject 类来操作。例子程序 信息来自岁月联盟以 postgres 用户或者具有建立数据库用户权限的用户登录 Linux，先建立数据库。 来源岁月联盟 zuojinitpark85 zuojin$createdb test_db 岁月联盟 来源岁月联盟 然后使用 psql 连接到数据库 岁月联盟zuojinitpark85 zuojin$psql test_db 内容来自岁月联盟 信息来源岁月联盟 连接到数据库建立表，如下图所示 (S) S 插入测试用数据，如图： 信息来源岁月联盟 资源来自岁月联盟(注意：psql中输入SQL语句要以; 结束才执行，帮助命令是?,退出命令是)。图 21)C语言例子#include #include int main() PGconn *conn; PGresult *res; char *pghost = NULL; char *pgport =NULL; char *pgoptions =NULL; char *pgtty = NULL; char *dbname =test_db; /*数据库名*/ int i = 0,t = 0,s,k; conn = PQsetdb(pghost,pgport,pgoptions,pgtty,dbname); if (PQstatus(conn) = CONNECTION_BAD) fprintf(stderr,Connection to database %s failed!n,dbname); PQfinish(conn); eturn 0; res = PQexec(conn,SELECT * FROM test); /*运行查询命令*/ if( PQresultStatus(res) != PGRES_TUPLES_OK) fprintf(stderr,Exec Query Fauled!n); PQclear(res); return 0; i = PQntuples(res); /*取得查询的结果的记录的数量*/ t = PQnfields(res); /*取得字段数量*/ for(s=0; si;s+) for (k = 0; kt; k+) printf(%s,PQgetvalue(res,s,k); printf( ); printf(n); PQfinish(conn); PQclear(res