postgis学习笔记
发布时间：2020-12-13 17:39:14 所属栏目：百科 来源：网络整理
导读：1. 将 .shp 格式数据代入数据库 postgis 中 : shp2pgsql -W"GBK" f:XianCh_point.shp ccvv f:ccvv.sql psql -d postgis -f f:ccvv.sql postgres 表名称： ccvv 数据库名字： postgis 角色名： postgres 2. 创建表空间数据库用户在 pgAdmin 中可设置。 1
|
1.将.shp格式数据代入数据库postgis 中:
shp2pgsql -W"GBK" f:XianCh_point.shp ccvv >f:ccvv.sql
psql -d postgis -f f:ccvv.sql postgres
表名称：ccvv
数据库名字：postgis
角色名：postgres
2.创建表空间数据库用户在pgAdmin中可设置。
1）create tablespace dhsdpgis_tp owner postgres location ‘d:’
2）crate database dhsdpgis_db owner postgres
TABLESPACE dhsdpgis_tp;
3）createuser -U postgres - P Allen
3进入数据库 cd D:PostgreSQL9.2bin
D:PostgreSQL9.2bin psql ?d postgis postgres
创建空间表
CREATE TABLE ROADS ( ID int4,ROAD_NAME varchar(25),geom. geometry(LINESTRING,4326) );
1.插入一列（线）
ALTER TABLE roads ADD COLUMN geom2 geometry(LINESTRINGZ,4326);
SELECT AddGeometryColumn( 'roads','geom',423,'LINESTRING',2);
2.('MULTIPOLYGON')
CREATE TABLE parks (
park_id INTEGER,
park_name VARCHAR,
park_date DATE,
park_type VARCHAR
);
SELECT AddGeometryColumn(‘模式’,'parks','park_geom',128,'MULTIPOLYGON',2 );
3. (GEOMETRY)
CREATE TABLE roads (
road_id INTEGER,
road_name VARCHAR
);
SELECT AddGeometryColumn( 'roads','roads_geom','GEOMETRY',3 );
4
CREATE TABLE pois_ny(gid SERIAL PRIMARY KEY
,poi_name text,cat varchar(20)
,geom geometry(POINT,4326) );
SELECT AddGeometryColumn('pois_ny','geom_2160',2160,'POINT',2,false);
添加点
String sql = "INSERT INTO business(名称,geom) VALUES('cccXXXXcc集团','POINT("+x+" "+y+")')";
修改点
String sql =
"update "
+table+
" set
名称
='
东和盛达
2',geom =ST_GeomFromText('POINT("
+x+
" "
+y+
")') where gid = 4"
;
geometry ST_GeomFromText(text WKT);
geometry ST_GeomFromText(text WKT,integer srid);
ST_AsEWKT(geom)包含SRID ST_AsText(GEOM)不包含
5．
ST_GeomFromText('POINT(22 22)')将其转化为 geometry 类似010100000054B7AB482BB65D40D40D4D6BCEB64240
ST_AsText 转化为 类似POINT(22 22)
获取两个面 之间最短距离的点
ST_ClosestPoint
（geometry g1,geometry g2
）
g2
到
g1
最近的点
该点在
g1
上
SELECT ST_AsText(
ST_ClosestPoint(ST_GeomFromText(’ POLYGON((175 150,20 40,50 60,125 100,175 150))’),
ST_Buffer(ST_GeomFromText(’POINT(110 170)’),20)))
SELECT ST_AsText(ST_ClosestPoint(pt,line)
) AS cp_pt_line,ST_AsText(ST_ClosestPoint(line,pt )) As cp_line_pt FROM (SELECT 'POINT(100 100)'::geometry As pt,'LINESTRING (20 80,98 190,110 180,50 75 )'::geometry As line) As foo ;
结果：
POINT(100 100) | POINT(73.0769230769231 115.384615384615)
或者
SELECT ST_AsText(ST_ClosestPoint(ST_GeomFromText('LINESTRING (20 80,50 75 )'),ST_GeomFromText('POINT(100 100)') ) ) AS cp_pt_line FROM business;
结果："POINT(73.0769230769231 115.384615384615)"
ST_Contains(A,B) B
在
A
内
ST_Within(A,B) A
在
B
内
判断
A
是否被
B
包含
ST_Within(geometry A,geometry B)
判断
A
是否包含
B ST_Contains(geometry A,geometry B)
判断 A 是否覆盖 B ST_Covers(geometry A,geometry B)
SELECT ST_AsText(
ST_ShortestLine(’POINT(100 100) ’::geometry,’LINESTRING (20 80,98 -190,50 75 )’::geometry)) As sline;sline
-----------------
LINESTRING(100 100,73.0769230769231 -
115.384615384615)
SELECT ST_AsText(ST_ShortestLine(ST_GeomFromText(’ POLYGON((175 150,175150))’),ST_Buffer( -ST_GeomFromText(’POINT(110 170)’),20))) As slinewkt;
LINESTRING(140.752120669087 125.695053378061,121.111404660392 153.370607753949)
ST_DWithin(
ST_Transform(ST_GeomFromText('POINT(118.84839183778 37.4231344123688)',4326),2383),
ST_Transform(ST_GeomFromText('MULTIPOLYGON (((118.853945599439 37.4229670850174,118.8658 37.4225250432923,118.853278523793 37.4326615750288,118.853476307826 37.4231018594778,118.853945599439 37.4229670850174)))',
100
多边形查询 多边形内的所有点
SELECT gid,"名称",geom
FROM business where ST_Within(business.geom,
ST_GeomFromText('MULTIPOLYGON (((118.853945599439 37.4229670850174,118.853945599439 37.4229670850174)))')
) ;
缓冲区查询：
String sql = "select *,ST_AsText(ST_Buffer(ST_Transform(ST_GeomFromText('POINT(118.8665,37.420134)',4236),2500)) as BufferFeatures from business where ST_DWithin(ST_Transform(ST_SetSRID(geom,ST_Transform(ST_GeomFromText('POINT(118.8665,2500) ";
空间查询：
1.圆查：
String sql = "select *,gid,ST_GeometryType(geom),GeometryType(geom),ST_Area(ST_Transform(ST_GeomFromText('MULTIPOLYGON((("+geometry+")))',2383)) from "+layerName+" limit 1 ";
2.拉框查询：
String sql =
"select *,ST_AsText(geom) as geometry,GeometryType(geom) as geometryType from "
+
layerName
+
" where ST_Within(geom,ST_GeomFromText('MULTIPOLYGON((("
+
geometry
+
")))'))"
;
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |