PostgreSQL&PostGIS&pgrouting 的一点笔记。

Part I:POSTGIS SYSTEM TABLE:

1, SPATIAL_REF_SYS:
 SRID: NOT NULL, PRIMARY KEY:数据库中坐标系统的唯一标识符。
 AUTH_NAME：该参考系统的标准body名
 AUTH_SRID:
 SRTEXT: well-known text（WKT） 坐标系
 PROJ4TEXT:proj4的描述。
2, GEOMETRY_COLUMNS:
 F_TABLE_CATALOG:--|
 F_TABLE_SCHEMA:---|-- 
 F_TABLE_NAME:-----|
  The fully qualified name of the feature table containing the geometry column. Note that the terms "catalog" and "schema" are Oracle-ish. There is not PostgreSQL analogue of "catalog" so that column is left blank -- for "schema" the PostgreSQL schema name is used (public  is the default).

 F_GEOMETRY_COLUMN:feature表的GEOMETRY COLUMN名字。
 COORD_DIMENSION: 空间维数
 SRID：坐标系的SRID
 TYPE:空间对象的类型。POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION or corresponding XYM versions POINTM, LINESTRINGM, POLYGONM, MULTIPOINTM, MULTILINESTRINGM, MULTIPOLYGONM, GEOMETRYCOLLECTIONM

Part II: Create a spatial table:
 1,创建一个非空间表，如： CREATE TABLE ROADS_GEOM ( ID int4, NAME varchar(25) )
 2,使用opengis的“AddGeometryColumn” function,或者使用当前的schema：
 如：
语法:

 AddGeometryColumn(<schema_name>,
            <table_name>, <column_name>, <srid>,
            <type>, <dimension>)

 :schema:

 AddGeometryColumn(<table_name>,
            <column_name>, <srid>, <type>,
            <dimension>)

Example1: SELECT AddGeometryColumn('public', 'roads_geom', 'geom', 423, 'LINESTRING', 2)

Example2: SELECT AddGeometryColumn( 'roads_geom', 'geom', 423, 'LINESTRING', 2)

Note: 在创建表时，必须创建主键约束，否则在QGIS中无法进行数据浏览。但是使用pgsql2shp倒是能完整导出。
      如果忘记创建，则可以咋pgAdmin III中手动创建： 数据表---约束。
      创建主键约束的字段必须为具有唯一值的字段。

当创建了一个spatial table，则在数据库的geometry_columns中就添加了一条关于这个spatial table的记录。
主要记录了表的schema，tablename，geometry filed name，coordinate system， srid，geometry type。
当在psql中使用drop table 时，不能自动删除geometry_columns中的记录，需要手动删除。

Part III：创建空间索引。
 B-Tree 索引用于一维。ArcSDE中为非空间数据创建B-Tree索引。
 R-tree索引用于多维数据索引。ArcSDE中为空间数据创建R-Tree索引。PostgreSQL的R-tree索引并没有GiST健壮。
 GiST（Generalized Search Trees）：平衡的，树状结构的访问方法。B+,R-,和其它都可以通过gist实现。
1，创建空间列Gist索引的方法：
 CREATE INDEX [indexname] ON [tablename] USING
        GIST ( [geometryfield] GIST_GEOMETRY_OPS );
   创建gist索引之后要收集表的统计信息来优化查询。
  VACUUM ANALYZE [table_name] [column_name]; --
          This is only needed for PostgreSQL 7.4 installations and below SELECT
          UPDATE_GEOMETRY_STATS([table_name], [column_name]);
Gist索引在PostgreSQL中比R-Tree的优点：
 1），null safe：索引列可以包含null值。
 2），处理比PostgreSQL 8K大的对象时无损。

2，使用索引：
 PostgreSQL query planner不能很好地优化GiST索引。
 1），收集表的统计信息。 PostgreSQL8 要经常执行VACUUM ANALYSE。
 2），如果vaccum无法执行，运行 set enable_seqscan=off 命令。（只在空间查询中使用）。
 3），If you find the planner wrong about the cost of sequential vs index scans try reducing the value of random_page_cost in postgresql.conf or using SET random_page_cost=#. Default value for the parameter is 4, try setting it to 1 or 2. Decrementing the value makes the planner more inclined of using Index scans.

Part IV spaitalSQL
查询空间部分可以使用length，area等函数，对象对空间列即可。
如： SELECT cntry_name,area(the_geom) as area from country ORDER BY area DESC LIMIT 1;
    查询出所有国家中面积最大的国家。（也可以使用max（））；

postgis=# select m.continent,sum(length(r.the_geom)) as river_length from rivers
 as r,continent as m where r.the_geom && m.the_geom AND contains(m.the_geom,r.th
e_geom) GROUP BY m.continent ORDER BY river_length;
   continent   |   river_length
---------------+------------------
 Australia     |  27.334016570006
 Europe        |   149.6875201246
 Africa        | 177.671783018837
 South America | 302.985882715241
 North America | 440.607675642696
 Asia          | 667.731886509972
(6 rows)

大约耗时30s。（建立gist索引之后的运行）

 

Part V work with MapServer
如果使用MapServer进行查询，则必须具有oid索引。
语法：CREATE INDEX [indexname] ON [tablename] ( oid );

 

pgRouting:
使用的数据必须创建拓扑关系，所谓拓扑关系，其实就是获得个edge的fromnode和tonode，以及线数据的length。
可以使用arcinfo的build命令进行。也可以使用grass等其它工具。

postgis=# select * from shortest_path('select gid as id,fnode as source,tnode as
 target,meters as length, ft_minutes as cost from streets',260,302,false,false);

 vertex_id | edge_id |  cost
-----------+---------+--------
       260 |      28 |  0.101
       268 |      29 | 0.1124
       276 |      30 | 0.0981
       279 |      31 |  0.091
       288 |      32 | 0.0998
       296 |      33 | 0.0244
       299 |      34 | 0.0528
       304 |      54 | 0.0238
       311 |      55 | 0.0588
       319 |      20 | 0.2379
       302 |      -1 |      0
(11 rows)
（已验证）