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;