PostGIS学习-1

1.准备工作

1. 创建数据库

CREATE DATABASE gis;

2. 安装postgis拓展

CREATE EXTENSION postgis;
select postgis_full_version();--检查pg是否安装ok

3. shp文件:通常是指shp（几何信息），shx（索引信息），dbf（属性信息）三个文件组成。

2.导入数据

1. 使用postgis直接将shp文件相关导入即可，导入时要注意设置想对应的SRID。

3.基本操作

1. SQL语句中的基本操作：create，select，update，delete，insert；
2. 常用的聚合函数：avg（），sum（），count（）；
3. 求纽约市的总人口？

select sum(popn_total) as population from nyc_census_blocks;

4. Bronx的人口数量是多少？

select sum(popn_total) as population from nyc_census_blocks where boroname = 'The Bronx'

5. 对于每一个行政区来说，白人占总人口的百分比是多少?

select boroname, sum(popn_white)/sum(popn_total) as white_pct from nyc_census_blocks group by boroname

6. 创建一张表

create table geometries(
	id serial primary key,--主键而且自增
	name varchar(100) not null,
	geom geometry
)

7. 插入数据

insert into geometries (name,geom) values
('Point', 'Point(0 0)'),
('Line', 'LineString(0 0,1 1)'),
('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),
('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');

DELETE FROM geometries
 WHERE <condition>;
 
 UPDATE geometries
   SET id=?, name=?, geom=?
 WHERE <condition>;

8. 元数据表：pg中为了更好的维护数据，专门有两张表用来追踪数据：spatial_ref_sys（用来记录空间参考系的坐标），另一张表实际上是视图geometry_columns（用来提供当前空间数据库中所有空间数据表的描述信息）
9. 对没有定义SRID的数据表追加SRID

select UpdateGeometrySRID('TableName', 'geom', '4326')

10. pg数据库中又一个简单的语法形式，可以将数据由一种类型快速的转换到另一种类型

select 0.9::text; --将0.9转换text类型
select 'POINT(0 0);SRID=4326'::GEOMETRY --将text转为geometry类型

11. 一些常用的空间函数：

sum(expression) aggregate to return a sum for a set of records
count(expression) aggregate to return the size of a set of records
ST_GeometryType(geometry) returns the type of the geometry
ST_NDims(geometry) returns the number of dimensions of the geometry
ST_SRID(geometry) returns the spatial reference identifier number of the geometry
ST_X(point) returns the X ordinate
ST_Y(point) returns the Y ordinate
ST_Length(linestring) returns the length of the linestring
ST_StartPoint(geometry) returns the first coordinate as a point
ST_EndPoint(geometry) returns the last coordinate as a point
ST_NPoints(geometry) returns the number of coordinates in the linestring
ST_Area(geometry) returns the area of the polygons
ST_NRings(geometry) returns the number of rings (usually 1, more if there are holes)
ST_ExteriorRing(polygon) returns the outer ring as a linestring
ST_InteriorRingN(polygon, integer) returns a specified interior ring as a linestring
ST_Perimeter(geometry) returns the length of all the rings
ST_NumGeometries(multi/geomcollection) returns the number of parts in the collection
ST_GeometryN(geometry, integer) returns the specified part of the collection
ST_GeomFromText(text) returns geometry
ST_AsText(geometry) returns WKT text
ST_AsEWKT(geometry) returns EWKT text
ST_GeomFromWKB(bytea) returns geometry
ST_AsBinary(geometry) returns WKB bytea
ST_AsEWKB(geometry) returns EWKB bytea
ST_GeomFromGML(text) returns geometry
ST_AsGML(geometry) returns GML text
ST_GeomFromKML(text) returns geometry
ST_AsKML(geometry) returns KML text
ST_AsGeoJSON(geometry) returns JSON text
ST_AsSVG(geometry) returns SVG text

12. group by xxx order by yyy desc，order by一定要在group by的后面