Postgis——Postgis学习笔记

转载声明：本文转载自：https://blog.csdn.net/allen_oscar/article/details/8789909

 

 

创建空间表

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', 4326, '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', 0, '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, 98 190, 110 180, 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, 110 180, 50 75 )’::geometry)) As sline;sline

-----------------

LINESTRING(100 100,73.0769230769231  -

115.384615384615)

 

 

SELECT ST_AsText(ST_ShortestLine(ST_GeomFromText(’ POLYGON((175 150, 20 40, 50 60, 125 100, 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)))',4326),2383),

       100

 

 

 

多边形查询   多边形内的所有点

SELECT gid, "名称", geom

  FROM business where ST_Within(business.geom,

        ST_GeomFromText('MULTIPOLYGON (((118.853945599439 37.4229670850174, 118.8658 37.4225250432923, 118.853278523793 37.4326615750288, 118.853476307826 37.4231018594778, 118.853945599439 37.4229670850174)))')

) ;

 

缓冲区查询：

 

String sql = "select  *,ST_AsText(ST_Buffer(ST_Transform(ST_GeomFromText('POINT(118.8665,37.420134)',4236),4236),2500)) as BufferFeatures from business  where ST_DWithin(ST_Transform(ST_SetSRID(geom, 4236),2383),ST_Transform(ST_GeomFromText('POINT(118.8665,37.420134)',4236),4236),2500) ";

空间查询：

 

1.圆查：

String sql = "select *,gid,ST_GeometryType(geom),GeometryType(geom) ,ST_Area(ST_Transform(ST_GeomFromText('MULTIPOLYGON((("+geometry+")))',4236),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+")))'))";