postGIS基本使用（二）

1.查询  

select * from d_mlp t limit 20;

select ST_AsText(geom) from d_mlp t limit 20;

# 每一条记录都有一个srid
select st_srid(geom) from d_mlp;


select 'srid=4;point(0 0)'::geometry;

select * from geography_columns;

# GeometryType（）返回几何类型的名称
select GeometryType(t.wkt) from xq t

select * from xq t where GeometryType(t.wkt) = 'Polygon'

# 通过ctid 过滤重复记录
# ctid 格式：(blockid,itemid)：拿其中(0,1)来说；0表示块id；1表示在这块第一条记录。
select ctid, * from emp where ctid in (select min(ctid) from emp group by id);

# 删除重复记录：
delete from test where ctid not in (select min(ctid) from test group by id);

2.插入

insert into d_mlp (geom,mpdzmc,mpwzhm,mc) 
values(st_geomfromewkt('srid=4326;point(113.245 23.89741)'),'11111号','11111号','11111号');

3.数据格式转换

# shp2pgsql -c -D -s 4269 -i -I shaperoads.shp myschema.roadstable > roads.sql
# psql -d roadsdb -f roads.sql


shp2pgsql shaperoads.shp myschema.roadstable | psql -d roadsdb

几何转换

wkt转geometry：st_geomfromtext(wkt)
select st_geomfromtext('Point(122 33)');

geometry转wkt：st_astext(geometry)
select st_astext(st_geomfromtext('Point(122 33)'))

geometry转geojson：st_asgeojson(geometry)
select st_asgeojson(st_geomfromtext('Point(122 33)'))

geojson转geometry：st_geomfromgeojson(geojson)
select
st_geomfromgeojson(
st_asgeojson(st_geomfromtext('Point(122 33)')))

geometry转geohash：st_geohash(geometry)
select st_geohash(st_geomfromtext('Point(116 39)'))

geohash转geometry：st_geomfromgeohash
select st_geomfromgeohash('wwfmzesx7yvjugxr3nzv')

获取点对象x、y坐标值 st_x(geom)、st_y(geom)
获取线/面对象四至 st_xmin(geom)、st_ymin(geom)、st_xmax(geom)、st_ymax(geom)
计算两点之间距离 st_distance(geom,geom) / st_distance(wkt,wkt)
计算线的长度 st_length(geom) / st_length(wkt)
计算面积 st_area(geom) / st_area(wkt)
缓冲区计算 st_buffer(geom,distance) / st_buffer(wkt,distance)

4.空间计算

# 空间查询
# 下面这个查询没有用到空间索引，索引比较慢
select * from d_mlp where ST_Distance(geom,'srid=4326;point(113.45678 23.14789)') < 0.0003;

This query is selecting all the geometries in geom_table which are within 2 units of the point (113.45678,23.14789). It will be
slow because it is calculating the distance between each point in the table and our specified point, ie. one ST_Distance()
calculation for each row in the table. We can avoid this by using the single step index accelerated function ST_DWithin to reduce
the number of distance calculations required:

# 空间查询
# 使用空间索引
select * from d_mlp where ST_DWithin(geom,'srid=4326;point(113.45678 23.14789)',0.0003);

根据经纬度，获取两点距离（单位：米）：st_distance_sphere(point a,point b)
select
st_distance_sphere(st_geomfromtext('Point(116 39)'),
st_geomfromtext('Point(117 39)'))

# 计算长度
select sum(ST_Length(geom))/1000 as km_roads from roads;


# 计算面积
select ST_Area(geom)/10000 as hectares from xzqh_qj;

# 计算4326地理坐标系的面状数据的面积：20200731
select st_area(t.geom,false)/1000000 as sqmt from xzqh_pg t limit 1;

# 这个统计的单位是米

select st_length(geom) as length_spheroid,st_length(geom,false) as length_sphere from 
(select st_geograhpyfromtext('srid=4326;
linestring(113.257532 23.174523,113.272449 23.201939)')) as foo;

5.两个geometry之间关系：within、disjoint、intersects、union、intersection,difference

St_within(geom A,geom B)返回A是否处于B中

St_disjoint(geom A,geom B)返回A是否不在B中

St_intersects(geom A,geom B)返回A是否和B有接触

St_union(geom A,geom B)返回A+B两个几何的合并

St_intersection(geom A,geom B)返回A和B的交集

St_difference(geom A,geom B)返回A与B不相交的部分几何

# 查找两个圆不相交的部分
select st_difference(st_buffer(st_geomfromtext('point(116 39)'),0.7),
st_buffer(st_geomfromtext('point(117 39)'),0.7)) geom;

# 上面的语句转换成text
select st_astext(st_difference(st_buffer(st_geomfromtext('point(116 39)'),0.7),
st_buffer(st_geomfromtext('point(117 39)'),0.7))) geom;