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)
（已验证）
转载自：https://blog.csdn.net/vistawn/article/details/1863838
