--验证postgis的analyze信息能否使用成功
set current_schema=postgis;
SELECT count(1) from geom_polygon as p1 join geom_line as p2 ON ST_INTERSECTS(p1.the_geom, p2.the_geom) where p1.id<1000;
count
-------
1522
(1 row)
/*
###############################################################################
# TESTCASE NAME : postgis_accessors.sql
# COMPONENT(S) : test function accessors
# PREREQUISITE :
# PLATFORM : SUSE11.3
# DESCRIPTION : 验证postgis几何对象存取函数
# TAG : ST_CeometryType,ST_Boundary,ST_CoordDim,ST_Dimension
# TC LEVEL : Level 1
################################################################################
*/
--S1.验证函数ST_CeometryType返回几何图形的类型
SELECT GeometryType(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
geometrytype
--------------
LINESTRING
(1 row)
--S2.验证函数ST_Boundary返回几何的边界
SELECT ST_AsText(ST_Boundary(ST_GeomFromText('LINESTRING(1 1,0 0, -1 1)')));
st_astext
----------------------
MULTIPOINT(1 1,-1 1)
(1 row)
--S3.验证函数ST_CoordDim返回几何对象所处的坐标维度
SELECT ST_CoordDim('CIRCULARSTRING(1 2 3, 1 3 4, 5 6 7, 8 9 10, 11 12 13)');
st_coorddim
-------------
3
(1 row)
--S4.验证函数ST_Dimension返回几何对象的固有维度
SELECT ST_Dimension('GEOMETRYCOLLECTION(LINESTRING(1 1,0 0),POINT(0 0))');
st_dimension
--------------
1
(1 row)
--S5.验证函数ST_EndPoint返回几何的最后一个点
SELECT ST_AsText(ST_EndPoint('LINESTRING(1 1, 2 2, 3 3)'::geometry));
st_astext
------------
POINT(3 3)
(1 row)
--S6.验证函数ST_Envelope返回几何图形的边界框
SELECT ST_AsText(ST_Envelope('POINT(1 3)'::geometry));
st_astext
------------
POINT(1 3)
(1 row)
--S7.验证函数ST_ExteriorRing返回表示POLYGON几何体外环的线串
SELECT ST_AsEWKT( ST_ExteriorRing(ST_GeomFromEWKT('POLYGON((0 0 1, 1 1 1, 1 2 1, 1 3 1, 0 0 1))')));
st_asewkt
-------------------------------------------
LINESTRING(0 0 1,1 1 1,1 2 1,1 3 1,0 0 1)
(1 row)
--S8.验证函数ST_GeometryN返回几何体第N个几何
SELECT n, ST_AsEWKT(ST_GeometryN(the_geom, n)) As geomewkt
FROM (
VALUES (ST_GeomFromEWKT('MULTIPOINT(1 2 7, 3 4 7, 5 6 7, 8 9 10)') ),
( ST_GeomFromEWKT('MULTICURVE(CIRCULARSTRING(2.5 2.5,4.5 2.5, 3.5 3.5), (10 11, 12 11))') )
)As foo(the_geom)
CROSS JOIN generate_series(1,100) n
WHERE n <= ST_NumGeometries(the_geom);
n | geomewkt
---+-----------------------------------------
1 | POINT(1 2 7)
1 | CIRCULARSTRING(2.5 2.5,4.5 2.5,3.5 3.5)
2 | POINT(3 4 7)
2 | LINESTRING(10 11,12 11)
3 | POINT(5 6 7)
4 | POINT(8 9 10)
(6 rows)
--S9.验证函数ST_CeometryType返回几何图形的类型
SELECT ST_GeometryType(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
st_geometrytype
-----------------
ST_LineString
(1 row)
--S10.验证函数ST_InteriorRingN返回几何的第N个内部线串环
SELECT ST_AsText(ST_InteriorRingN(the_geom, 1)) As the_geom
FROM (SELECT ST_BuildArea(
ST_Collect(ST_Buffer(ST_Point(1,2), 20,3),
ST_Buffer(ST_Point(1, 2), 10,3))) As the_geom
) as foo;
the_geom
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LINESTRING(11 2,9.66025403784436 7.00000000000005,5.99999999999995 10.6602540378444,0.999999999999954 12,-4.00000000000003 10.6602540378444,-7.66025403784441 6.99999999999997,-9 1.99999999999997,-7.66025403784437 -3.00000000000002,-3.99999999999998 -6.6602540378444,1.00000000000001 -8,6.00000000000001 -6.66025403784438,9.66025403784439 -3,11 2)
(1 row)
--S11.验证函数ST_IsClosed几何封闭
SELECT ST_IsClosed('LINESTRING(0 0, 1 1, 0 0)'::geometry);
st_isclosed
-------------
t
(1 row)
--S12.验证函数ST_IsCollection几何是集合
SELECT ST_IsCollection('LINESTRING(0 0, 1 1)'::geometry);
st_iscollection
-----------------
f
(1 row)
SELECT ST_IsCollection('MULTIPOINT EMPTY'::geometry);
st_iscollection
-----------------
t
(1 row)
--S13.验证函数ST_IsEmpty几何为空
SELECT ST_IsEmpty(ST_GeomFromText('GEOMETRYCOLLECTION EMPTY'));
st_isempty
------------
t
(1 row)
--S14.验证函数ST_IsRing几何简单且封闭
SELECT ST_IsRing(the_geom), ST_IsClosed(the_geom), ST_IsSimple(the_geom)
FROM (SELECT 'LINESTRING(0 0, 0 1, 1 1, 1 0, 0 0)'::geometry AS the_geom) AS foo;
st_isring | st_isclosed | st_issimple
-----------+-------------+-------------
t | t | t
(1 row)
--S15.验证函数ST_IsSimple几何无相交的点
SELECT ST_IsSimple(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))'));
st_issimple
-------------
t
(1 row)
--S16.验证函数ST_IsValid几何格式正确
SELECT ST_IsValid(ST_GeomFromText('LINESTRING(0 0, 1 1)')) As good_line,
ST_IsValid(ST_GeomFromText('POLYGON((0 0, 1 1, 1 2, 1 1, 0 0))')) As bad_poly;
NOTICE: Self-intersection at or near point 0 0
CONTEXT: referenced column: bad_poly
good_line | bad_poly
-----------+----------
t | f
(1 row)
--S17.验证函数ST_IsValidReason返回几何无效原因
SELECT ST_IsValidReason('LINESTRING(220227 150406,2220227 150407,222020 150410)');
st_isvalidreason
------------------
Valid Geometry
(1 row)
--S18.验证函数ST_IsValidDetail返回几何无效详细信息
SELECT * FROM ST_IsValidDetail('LINESTRING(220227 150406,2220227 150407,222020 150410)');
valid | reason | location
-------+--------+----------
t | |
(1 row)
--S19.验证函数ST_M返回该点M坐标
SELECT ST_M(ST_GeomFromEWKT('POINT(1 2 3 4)'));
st_m
------
4
(1 row)
--S20.验证函数ST_NDims返回几何坐标尺寸
SELECT ST_NDims(ST_GeomFromText('POINT(1 1)')) As d2point,
ST_NDims(ST_GeomFromEWKT('POINT(1 1 2)')) As d3point,
ST_NDims(ST_GeomFromEWKT('POINTM(1 1 0.5)')) As d2pointm;
d2point | d3point | d2pointm
---------+---------+----------
2 | 3 | 3
(1 row)
--S21.验证函数ST_NPoints返回几何顶点
SELECT ST_NPoints(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
st_npoints
------------
4
(1 row)
--S22.验证函数ST_NRings返回几何环的数量
SELECT ST_NRings(the_geom) As Nrings, ST_NumInteriorRings(the_geom) As ninterrings
FROM (SELECT ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))') As the_geom) As foo;
nrings | ninterrings
--------+-------------
1 | 0
(1 row)
--S23.验证函数ST_NumGeometries返回几何体的面数
SELECT ST_NumGeometries(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
st_numgeometries
------------------
1
(1 row)
--S24.验证函数ST_NumInteriorRings返回几何内环数
--SELECT ST_NumInteriorRings(the_geom) AS numholes FROM GEOM_POLYGON;
--S25.验证函数ST_NumPatches返回几何的面数
SELECT ST_NumPatches(ST_GeomFromEWKT('POLYHEDRALSURFACE( ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)),
((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),
((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)),
((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) )'));
st_numpatches
---------------
6
(1 row)
--S26.验证函数ST_NumPoints返回几何点的个数
SELECT ST_NumPoints(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
st_numpoints
--------------
4
(1 row)
--S27.验证函数ST_PatchN返回几何的第N个面
SELECT ST_AsEWKT(ST_PatchN(geom, 2)) As geomewkt
FROM (
VALUES (ST_GeomFromEWKT('POLYHEDRALSURFACE( ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),
((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)), ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) )')) ) As foo(geom);
geomewkt
------------------------------------------
POLYGON((0 0 0,0 1 0,1 1 0,1 0 0,0 0 0))
(1 row)
--S28.验证函数ST_PointN返回几何的第N个点
SELECT ST_AsText(
ST_PointN(
column1,
generate_series(1, ST_NPoints(column1))
))
FROM ( VALUES ('LINESTRING(0 0, 1 1, 2 2)'::geometry) ) AS foo;
st_astext
------------
POINT(0 0)
POINT(1 1)
POINT(2 2)
(3 rows)
--S29.验证函数ST_SRID返回几何的空间参考值
SELECT ST_SRID(ST_GeomFromText('POINT(-71.1043 42.315)',4326));
st_srid
---------
4326
(1 row)
--S30.验证函数ST_StartPoint返回几何的起始点
SELECT ST_AsText(ST_StartPoint('LINESTRING(0 1, 0 2)'::geometry));
st_astext
------------
POINT(0 1)
(1 row)
--S31.验证函数ST_Summary返回几何的文本摘要
SELECT ST_Summary(ST_GeomFromText('LINESTRING(0 0, 1 1)')) as geom,
ST_Summary(ST_GeogFromText('POLYGON((0 0, 1 1, 1 2, 1 1, 0 0))')) geog;
geom | geog
----------------------------+---------------------------
LineString[] with 2 points | Polygon[BGS] with 1 rings+
| ring 0 has 5 points
(1 row)
--S32.验证函数ST_X返回该点的X坐标
SELECT ST_X(ST_GeomFromEWKT('POINT(1 2 3 4)'));
st_x
------
1
(1 row)
--S33.验证函数ST_XMax返回几何的X坐标最大值
SELECT ST_XMax('BOX3D(1 2 3, 4 5 6)');
st_xmax
---------
4
(1 row)
--S34.验证函数ST_XMin返回几何的X坐标最小值
SELECT ST_XMin('BOX3D(1 2 3, 4 5 6)');
st_xmin
---------
1
(1 row)
--S35.验证函数ST_Y返回该点的Y坐标
SELECT ST_Y(ST_GeomFromEWKT('POINT(1 2 3 4)'));
st_y
------
2
(1 row)
--S36.验证函数ST_YMax返回几何的Y坐标最大值
SELECT ST_YMax('BOX3D(1 2 3, 4 5 6)');
st_ymax
---------
5
(1 row)
--S37.验证函数ST_YMin返回几何的Y坐标最小值
SELECT ST_YMin('BOX3D(1 2 3, 4 5 6)');
st_ymin
---------
2
(1 row)
--S38.验证函数ST_Z返回该点的Z坐标
SELECT ST_Z(ST_GeomFromEWKT('POINT(1 2 3 4)'));
st_z
------
3
(1 row)
--S39.验证函数ST_ZMax返回几何的Z坐标最大值
SELECT ST_ZMax('BOX3D(1 2 3, 4 5 6)');
st_zmax
---------
6
(1 row)
--S40.验证函数ST_Zmflag返回几何的ZM标志
SELECT ST_Zmflag(ST_GeomFromEWKT('LINESTRING(1 2, 3 4)'));
st_zmflag
-----------
0
(1 row)
--S41.验证函数ST_ZMin返回几何的Z坐标最小值
SELECT ST_ZMin('BOX3D(1 2 3, 4 5 6)');
st_zmin
---------
3
(1 row)
--I2.验证复杂函数join功能
/*
###############################################################################
# TESTCASE NAME : postgis_constructors.sql
# COMPONENT(S) : test function constructors
# PREREQUISITE :
# PLATFORM : SUSE11.3
# DESCRIPTION : 验证几何对象创建功能
# TAG :
# TC LEVEL : Level 1
################################################################################
*/
--ST_BdPolyFromText 给定任意集合的封闭线串作为MultiLineStringWell-已知文本形式构造一个多边形
--ST_BdMPolyFromText 给定一个MultiLolyString文本表示的闭合线串的任意集合构造一个MultiPolygon众所周知的文本表示
--S1.验证函数ST_Box2dFromGeoHash：从GeoHash字符串中返回一个BOX2D值，第二个参数值越大，精度越高
SELECT ST_Box2dFromGeoHash(ST_GeoHash(the_geom), 5) from geom_point where id=10;
ERROR: Geohash requires inputs in decimal degrees, got (0 100, 0 100).
CONTEXT: referenced column: st_box2dfromgeohash
--S2.验证函数ST_GeogFromText：给定WKT返回指定的地理值。
SELECT ST_GeogFromText(ST_AsText(the_geom)) from geom_line where id=15;
st_geogfromtext
-----------------
(0 rows)
--S3.验证函数ST_GeographyFromText：给定WKT返回指定的地理值
SELECT ST_GeographyFromText(ST_AsText(the_geom)) from geom_polygon where id<5 order by id;
st_geographyfromtext
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0103000020E610000001000000050000000000000000000000000000000000000000000000000000000000000000001440000000000000144000000000000014400000000000001440000000000000000000000000000000000000000000000000
0103000020E610000001000000050000000000000000000000000000000000244000000000000000000000000000003940000000000000144000000000000034400000000000001440000000000000144000000000000000000000000000002440
0103000020E610000001000000050000000000000000000000000000000000344000000000000000000000000000804640000000000000144000000000008041400000000000001440000000000000244000000000000000000000000000003440
0103000020E6100000010000000500000000000000000000000000000000003E40000000000000000000000000004050400000000000001440000000000000494000000000000014400000000000002E4000000000000000000000000000003E40
0103000020E610000001000000050000000000000000000000000000000000444000000000000000000000000000405540000000000000144000000000004050400000000000001440000000000000344000000000000000000000000000004440
(5 rows)
--S4.验证函数ST_GeogFromWKB：给定WKB或EWKB返回地理值
SELECT ST_GeogFromWKB(ST_AsBinary(the_geom)) from geom_point where id=11;
st_geogfromwkb
----------------
(0 rows)
--S5.验证函数ST_GeomCollFromText：给定集合WKT创建一个集合几何体。如果没有给出SRID，则默认为0
SELECT ST_GeomCollFromText(ST_AsText(the_geom)) from geom_collection where id=10;
st_geomcollfromtext
---------------------
(0 rows)
--S6.验证函数ST_GeomFromEWKB：给定EWKB返回指定的ST_Geometry值
SELECT ST_GeomFromEWKB(St_AsEWKB(the_geom)) from geom_point where id < 20 order by id;
st_geomfromewkb
--------------------------------------------
010100000000000000000000000000000000000000
010100000000000000000000000000000000002440
010100000000000000000000000000000000003440
010100000000000000000000000000000000003E40
010100000000000000000000000000000000004440
010100000000000000000000000000000000004940
010100000000000000000000000000000000004E40
010100000000000000000000000000000000805140
010100000000000000000000000000000000005440
010100000000000000000000000000000000805640
010100000000000000000000000000000000005940
(11 rows)
--S7.验证函数ST_GeomFromEWKT：给定EWKT返回指定的ST_Geometry值
SELECT ST_GeomFromEWKT(ST_AsEWKT(the_geom)) from geom_point where id=1;
st_geomfromewkt
--------------------------------------------
010100000000000000000000000000000000002440
(1 row)
SELECT ST_GeomFromEWKT(St_AsEWKT(the_geom)) from geom_multipolygon where id<5 order by id;
st_geomfromewkt
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0106000000020000000103000000010000000500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000103000000010000000400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0106000000020000000103000000010000000500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000103000000010000000400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0106000000020000000103000000010000000500000000000000000000000000000000002440000000000000000000000000000034400000000000000000000000000000264000000000000000000000000000000040000000000000000000000000000024400103000000010000000400000000000000000000000000000000002440000000000000000000000000000034400000000000000000000000000000004000000000000000000000000000002440
0106000000020000000103000000010000000500000000000000000000000000000000002440000000000000000000000000000034400000000000000000000000000000264000000000000000000000000000000040000000000000000000000000000024400103000000010000000400000000000000000000000000000000002440000000000000000000000000000034400000000000000000000000000000004000000000000000000000000000002440
0106000000020000000103000000010000000500000000000000000000000000000000003440000000000000000000000000000044400000000000000000000000000000364000000000000000000000000000001040000000000000000000000000000034400103000000010000000400000000000000000000000000000000003440000000000000000000000000000044400000000000000000000000000000104000000000000000000000000000003440
0106000000020000000103000000010000000500000000000000000000000000000000003440000000000000000000000000000044400000000000000000000000000000364000000000000000000000000000001040000000000000000000000000000034400103000000010000000400000000000000000000000000000000003440000000000000000000000000000044400000000000000000000000000000104000000000000000000000000000003440
0106000000020000000103000000010000000500000000000000000000000000000000003E4000000000000000000000000000004E40000000000000000000000000008040400000000000000000000000000000184000000000000000000000000000003E400103000000010000000400000000000000000000000000000000003E4000000000000000000000000000004E400000000000000000000000000000184000000000000000000000000000003E40
0106000000020000000103000000010000000500000000000000000000000000000000003E4000000000000000000000000000004E40000000000000000000000000008040400000000000000000000000000000184000000000000000000000000000003E400103000000010000000400000000000000000000000000000000003E4000000000000000000000000000004E400000000000000000000000000000184000000000000000000000000000003E40
0106000000020000000103000000010000000500000000000000000000000000000000004440000000000000000000000000000054400000000000000000000000000000464000000000000000000000000000002040000000000000000000000000000044400103000000010000000400000000000000000000000000000000004440000000000000000000000000000054400000000000000000000000000000204000000000000000000000000000004440
0106000000020000000103000000010000000500000000000000000000000000000000004440000000000000000000000000000054400000000000000000000000000000464000000000000000000000000000002040000000000000000000000000000044400103000000010000000400000000000000000000000000000000004440000000000000000000000000000054400000000000000000000000000000204000000000000000000000000000004440
(10 rows)
--S8.验证函数ST_GeometryFromText：给定几何的Text表示格式输出几何
SELECT ST_GeometryFromText(ST_AsTExt(the_geom)) from geom_polygon where name='ZZZZ' order by id;
st_geometryfromtext
---------------------
(0 rows)
--S9.验证函数ST_GeomFromGeoHash：给定GeoHash字符串返回几何
SELECT ST_AsText(ST_GeomFromGeoHash(ST_GeoHash(the_geom))) from geom_point order by id limit 10;
--?.*ERROR: Geohash requires inputs in decimal degrees, got.*
CONTEXT: referenced column: st_astext
--S10.验证函数ST_GeomFromGML：给定几何体的GML表示形式输出PostGIS几何体对象
SELECT ST_AsText(ST_GeomFromGML(ST_AsGML(the_geom))) from geom_multilinestring order by id limit 10;
st_astext
------------------------------------
MULTILINESTRING((0 0,0 0,0 0))
MULTILINESTRING((0 10,0 20,0 11))
MULTILINESTRING((0 20,0 40,0 22))
MULTILINESTRING((0 30,0 60,0 33))
MULTILINESTRING((0 40,0 80,0 44))
MULTILINESTRING((0 50,0 100,0 55))
MULTILINESTRING((0 60,0 120,0 66))
MULTILINESTRING((0 70,0 140,0 77))
MULTILINESTRING((0 80,0 160,0 88))
MULTILINESTRING((0 90,0 180,0 99))
(10 rows)
--s11.验证函数ST_GeomFromGeoJSON：给定几何JSON表示形式输出几何体对象
SELECT 'ST_AsGeoJson',ST_GeomFromGeoJSON(ST_AsGeoJson(the_geom)) from geom_line where citiId=100 ;
?column? | st_geomfromgeojson
----------+--------------------
(0 rows)
SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"LineString","coordinates ":[[1,2,3],[4,5,6],[7,8,9]]}')) As wkt;
ERROR: Unable to find 'coordinates' in GeoJSON string
CONTEXT: referenced column: wkt
--s12.验证函数ST_GeomFromKML：给定几何的KML表示形式输出几何对象
SELECT ST_AsText(ST_GeomFromKML(ST_AsKML(the_geom))) from geom_multilinestring order by id limit 10;;
ERROR: Input geometry has unknown (0) SRID
CONTEXT: referenced column: _st_askml
SQL function "st_askml" statement 1
referenced column: st_astext
--S13.验证函数ST_GeomFromText：给定几何Text表示形式输出几何对象
SELECT ST_GeomFromText(ST_AsText(the_geom)) from geom_point where name='UUUU';
st_geomfromtext
-----------------
(0 rows)
--S14.验证函数ST_GeomFromWKB：给定几何WKB表示形式格式输出几何对象
SELECT ST_GeomFromWKB(ST_AsEWKB(the_geom)) from geom_multipolygon where id<10 order by id;
st_geomfromwkb
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0106000000020000000103000000010000000500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000103000000010000000400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0106000000020000000103000000010000000500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000103000000010000000400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0106000000020000000103000000010000000500000000000000000000000000000000002440000000000000000000000000000034400000000000000000000000000000264000000000000000000000000000000040000000000000000000000000000024400103000000010000000400000000000000000000000000000000002440000000000000000000000000000034400000000000000000000000000000004000000000000000000000000000002440
0106000000020000000103000000010000000500000000000000000000000000000000002440000000000000000000000000000034400000000000000000000000000000264000000000000000000000000000000040000000000000000000000000000024400103000000010000000400000000000000000000000000000000002440000000000000000000000000000034400000000000000000000000000000004000000000000000000000000000002440
0106000000020000000103000000010000000500000000000000000000000000000000003440000000000000000000000000000044400000000000000000000000000000364000000000000000000000000000001040000000000000000000000000000034400103000000010000000400000000000000000000000000000000003440000000000000000000000000000044400000000000000000000000000000104000000000000000000000000000003440
0106000000020000000103000000010000000500000000000000000000000000000000003440000000000000000000000000000044400000000000000000000000000000364000000000000000000000000000001040000000000000000000000000000034400103000000010000000400000000000000000000000000000000003440000000000000000000000000000044400000000000000000000000000000104000000000000000000000000000003440
0106000000020000000103000000010000000500000000000000000000000000000000003E4000000000000000000000000000004E40000000000000000000000000008040400000000000000000000000000000184000000000000000000000000000003E400103000000010000000400000000000000000000000000000000003E4000000000000000000000000000004E400000000000000000000000000000184000000000000000000000000000003E40
0106000000020000000103000000010000000500000000000000000000000000000000003E4000000000000000000000000000004E40000000000000000000000000008040400000000000000000000000000000184000000000000000000000000000003E400103000000010000000400000000000000000000000000000000003E4000000000000000000000000000004E400000000000000000000000000000184000000000000000000000000000003E40
0106000000020000000103000000010000000500000000000000000000000000000000004440000000000000000000000000000054400000000000000000000000000000464000000000000000000000000000002040000000000000000000000000000044400103000000010000000400000000000000000000000000000000004440000000000000000000000000000054400000000000000000000000000000204000000000000000000000000000004440
0106000000020000000103000000010000000500000000000000000000000000000000004440000000000000000000000000000054400000000000000000000000000000464000000000000000000000000000002040000000000000000000000000000044400103000000010000000400000000000000000000000000000000004440000000000000000000000000000054400000000000000000000000000000204000000000000000000000000000004440
01060000000200000001030000000100000005000000000000000000000000000000000049400000000000000000000000000000594000000000000000000000000000804B4000000000000000000000000000002440000000000000000000000000000049400103000000010000000400000000000000000000000000000000004940000000000000000000000000000059400000000000000000000000000000244000000000000000000000000000004940
01060000000200000001030000000100000005000000000000000000000000000000000049400000000000000000000000000000594000000000000000000000000000804B4000000000000000000000000000002440000000000000000000000000000049400103000000010000000400000000000000000000000000000000004940000000000000000000000000000059400000000000000000000000000000244000000000000000000000000000004940
0106000000020000000103000000010000000500000000000000000000000000000000004E4000000000000000000000000000005E40000000000000000000000000008050400000000000000000000000000000284000000000000000000000000000004E400103000000010000000400000000000000000000000000000000004E4000000000000000000000000000005E400000000000000000000000000000284000000000000000000000000000004E40
0106000000020000000103000000010000000500000000000000000000000000000000004E4000000000000000000000000000005E40000000000000000000000000008050400000000000000000000000000000284000000000000000000000000000004E400103000000010000000400000000000000000000000000000000004E4000000000000000000000000000005E400000000000000000000000000000284000000000000000000000000000004E40
0106000000020000000103000000010000000500000000000000000000000000000000805140000000000000000000000000008061400000000000000000000000000040534000000000000000000000000000002C400000000000000000000000000080514001030000000100000004000000000000000000000000000000008051400000000000000000000000000080614000000000000000000000000000002C4000000000000000000000000000805140
0106000000020000000103000000010000000500000000000000000000000000000000805140000000000000000000000000008061400000000000000000000000000040534000000000000000000000000000002C400000000000000000000000000080514001030000000100000004000000000000000000000000000000008051400000000000000000000000000080614000000000000000000000000000002C4000000000000000000000000000805140
0106000000020000000103000000010000000500000000000000000000000000000000005440000000000000000000000000000064400000000000000000000000000000564000000000000000000000000000003040000000000000000000000000000054400103000000010000000400000000000000000000000000000000005440000000000000000000000000000064400000000000000000000000000000304000000000000000000000000000005440
0106000000020000000103000000010000000500000000000000000000000000000000005440000000000000000000000000000064400000000000000000000000000000564000000000000000000000000000003040000000000000000000000000000054400103000000010000000400000000000000000000000000000000005440000000000000000000000000000064400000000000000000000000000000304000000000000000000000000000005440
01060000000200000001030000000100000005000000000000000000000000000000008056400000000000000000000000000080664000000000000000000000000000C0584000000000000000000000000000003240000000000000000000000000008056400103000000010000000400000000000000000000000000000000805640000000000000000000000000008066400000000000000000000000000000324000000000000000000000000000805640
01060000000200000001030000000100000005000000000000000000000000000000008056400000000000000000000000000080664000000000000000000000000000C0584000000000000000000000000000003240000000000000000000000000008056400103000000010000000400000000000000000000000000000000805640000000000000000000000000008066400000000000000000000000000000324000000000000000000000000000805640
(20 rows)
--S15.验证函数ST_LineFromMultiPoint：给定多个点返回一条线
SELECT ST_LineFromMultiPoint(the_geom) from geom_multipoint where id=18;
st_linefrommultipoint
-----------------------
(0 rows)
--S16.验证函数ST_LineFromText：给定线条的WKB表示形式，返回一条线
SELECT ST_LineFromText(ST_Astext(the_geom)) from geom_Line AS null_return where name='AAAA' ;
st_linefromtext
------------------------------------------------------------------------------------
0102000000020000000000000000000000000000000000000000000000000014400000000000001440
(1 row)
--S17.验证函数ST_LineStringFromWKB：给定线条WKB格式返回一条线
SELECT
ST_LineStringFromWKB(
ST_AsBinary(the_geom)) from geom_point AS geom_return where id=22 ;
st_linestringfromwkb
----------------------
(0 rows)
--S18.验证函数ST_MakeBox2D：给定几何点创建 box2d
select ST_AsText(ST_SetSRID(ST_MakeBox2D(ST_Point(-989502.1875, 528439.5625), ST_Point(-987121.375 ,529933.1875)),2163));
st_astext
---------------------------------------------------------------------------------------------------------------------------------------
POLYGON((-989502.1875 528439.5625,-989502.1875 529933.1875,-987121.375 529933.1875,-987121.375 528439.5625,-989502.1875 528439.5625))
(1 row)
--S19.验证函数ST_3DMakeBox：给定几何点创建box3d
SELECT ST_3DMakeBox(ST_MakePoint(-989502.1875, 528439.5625, 10),
ST_MakePoint(-987121.375 ,529933.1875, 10)) As abb3d;
abb3d
---------------------------------------------------------------
BOX3D(-989502.1875 528439.5625 10,-987121.375 529933.1875 10)
(1 row)
--S20.验证函数ST_MakeLine：给定点创建线
SELECT ST_AsText(ST_MakeLine(ST_MakePoint(1,2), ST_MakePoint(3,4)));
st_astext
---------------------
LINESTRING(1 2,3 4)
(1 row)
--S21.验证函数ST_MakeEnvelope：给定x坐标最小值最大值，y坐标最小值最大值创建矩形
SELECT ST_AsText(ST_MakeEnvelope(10, 10, 11, 11,4326)); --S22.验证函数ST_MakePolygon：给定线条创建多边形
st_astext
------------------------------------------
POLYGON((10 10,10 11,11 11,11 10,10 10))
(1 row)
SELECT ST_MakePolygon(ST_GeomFromText(ST_AsText(the_geom))) from geom_line where id=9;
ERROR: lwpoly_from_lwlines: shell must have at least 4 points
CONTEXT: referenced column: st_makepolygon
--S23.验证函数ST_MakePoint：给定x，y，z，m坐标创建点
SELECT ST_AsText(ST_MakePoint(1, 2,1.5,3));
st_astext
----------------------
POINT ZM (1 2 1.5 3)
(1 row)
--S24.验证函数ST_MakePointM：给定x，y，m坐标创建点
SELECT ST_AsEWKT(ST_MakePointM(-71.1043443253471, 42.3150676015829, 10));
st_asewkt
-----------------------------------------------
POINTM(-71.1043443253471 42.3150676015829 10)
(1 row)
--S25.验证函数ST_MLineFromText：给定WKT返回指定的multilinestring
SELECT ST_AsText(ST_MLineFromText(ST_AsText(the_geom))) from geom_multilinestring where id=25;
st_astext
-----------
(0 rows)
--S26.验证函数ST_MPointFromText：给定WKT返回指定的multipoint
SELECT ST_AsText(ST_MPointFromText(ST_AsText(the_geom))) from geom_multipoint where id=19;
st_astext
-----------
(0 rows)
--S27.验证函数ST_MPolyFromText：给定WKT返回指定的multipolygon
SELECT ST_AsText(ST_MPolyFromText(ST_AsText(the_geom))) from geom_multipolygon where id<5 order by id;
st_astext
------------------------------------------------------------------
MULTIPOLYGON(((0 0,0 0,0 0,0 0,0 0)),((0 0,0 0,0 0,0 0)))
MULTIPOLYGON(((0 0,0 0,0 0,0 0,0 0)),((0 0,0 0,0 0,0 0)))
MULTIPOLYGON(((0 10,0 20,0 11,0 2,0 10)),((0 10,0 20,0 2,0 10)))
MULTIPOLYGON(((0 10,0 20,0 11,0 2,0 10)),((0 10,0 20,0 2,0 10)))
MULTIPOLYGON(((0 20,0 40,0 22,0 4,0 20)),((0 20,0 40,0 4,0 20)))
MULTIPOLYGON(((0 20,0 40,0 22,0 4,0 20)),((0 20,0 40,0 4,0 20)))
MULTIPOLYGON(((0 30,0 60,0 33,0 6,0 30)),((0 30,0 60,0 6,0 30)))
MULTIPOLYGON(((0 30,0 60,0 33,0 6,0 30)),((0 30,0 60,0 6,0 30)))
MULTIPOLYGON(((0 40,0 80,0 44,0 8,0 40)),((0 40,0 80,0 8,0 40)))
MULTIPOLYGON(((0 40,0 80,0 44,0 8,0 40)),((0 40,0 80,0 8,0 40)))
(10 rows)
/*
###############################################################################
# TESTCASE NAME : postgis_geometry_editors.sql
# COMPONENT(S) : test function geometry_editors
# PREREQUISITE :
# PLATFORM : SUSE11.3
# DESCRIPTION : 验证postgis函数编辑功能
# TAG : ST_AddPoint,ST_Affine,ST_Force2D,ST_LineMerge,ST_Rotate
# TC LEVEL : Level 1
################################################################################
*/
--S1.验证函数ST_AddPoint添加一个点到linestring
SELECT ST_AsEWKT(ST_AddPoint(ST_GeomFromEWKT(ST_AsText(the_geom)), ST_MakePoint (1, 2, 3), 1)) from geom_line where geom_line.id=1;
st_asewkt
---------------------------
LINESTRING(0 10,1 2,5 15)
(1 row)
SELECT ST_AsEWKT(ST_AddPoint(ST_GeomFromEWKT(ST_AsText(the_geom)),table_1.point_1, 1)) from geom_line,(select ST_AsText(the_geom) as point_1 from geom_point where id=1) table_1 where geom_line.id=1;
st_asewkt
----------------------------
LINESTRING(0 10,0 10,5 15)
(1 row)
SELECT ST_AsEWKT(ST_AddPoint(ST_GeomFromEWKT('LINESTRING(0 0 1, 1 1 1)'), ST_MakePoint (1, 2, 3)));
st_asewkt
-------------------------------
LINESTRING(0 0 1,1 1 1,1 2 3)
(1 row)
--S2.验证函数ST_Affine几何三维仿射变换
SELECT ST_AsEWKT(ST_Affine(the_geom, cos(pi()), -sin(pi()), 0, sin(pi()), cos(pi()), 0, 0, 0, 1, 0, 0, 0)) As using_affine,
ST_AsEWKT(ST_Rotate(the_geom, pi())) As using_rotate
FROM (SELECT ST_GeomFromEWKT('LINESTRING(1 2 3, 1 4 3)') As the_geom) As foo;
using_affine | using_rotate
-----------------------------+-----------------------------
LINESTRING(-1 -2 3,-1 -4 3) | LINESTRING(-1 -2 3,-1 -4 3)
(1 row)
SELECT ST_AsEWKT(ST_Affine(the_geom, cos(pi()), -sin(pi()), 0, sin(pi()), cos(pi()), -sin(pi()), 0, sin(pi()), cos(pi()), 0, 0, 0))
FROM (SELECT ST_GeomFromEWKT('LINESTRING(1 2 3, 1 4 3)') As the_geom) As foo;
st_asewkt
-------------------------------
LINESTRING(-1 -2 -3,-1 -4 -3)
(1 row)
--S3.验证函数ST_Force2D几何强制转换为2D表示
SELECT ST_AsEWKT(ST_Force2D(ST_GeomFromEWKT('CIRCULARSTRING(1 1 2, 2 3 2, 4 5 2, 6 7 2, 5 6 2)')));
st_asewkt
-------------------------------------
CIRCULARSTRING(1 1,2 3,4 5,6 7,5 6)
(1 row)
SELECT ST_AsEWKT(ST_Force2D(the_geom)) from geom_polygon where id<5 order by citiID;
st_asewkt
-----------------------------------------------
SRID=4326;POLYGON((0 0,0 5,5 5,5 0,0 0))
SRID=4326;POLYGON((0 10,0 25,5 20,5 5,0 10))
SRID=4326;POLYGON((0 20,0 45,5 35,5 10,0 20))
SRID=4326;POLYGON((0 30,0 65,5 50,5 15,0 30))
SRID=4326;POLYGON((0 40,0 85,5 65,5 20,0 40))
(5 rows)
SELECT ST_AsEWKT(ST_Force2D(the_geom)) from geom_line where citiID < 5 order by id;
st_asewkt
---------------------------------
SRID=4326;LINESTRING(0 0,5 5)
SRID=4326;LINESTRING(0 10,5 15)
SRID=4326;LINESTRING(0 20,5 25)
SRID=4326;LINESTRING(0 30,5 35)
SRID=4326;LINESTRING(0 40,5 45)
(5 rows)
--S4.验证函数ST_Force3D几何强制转换为3D表示
SELECT ST_AsEWKT(ST_Force3D(ST_GeomFromEWKT('CIRCULARSTRING(1 1 2, 2 3 2, 4 5 2, 6 7 2, 5 6 2)')));
st_asewkt
-----------------------------------------------
CIRCULARSTRING(1 1 2,2 3 2,4 5 2,6 7 2,5 6 2)
(1 row)
SELECT ST_AsEWKT(ST_Force3D(the_geom)) from geom_polygon where name='CCCC' order by id;
st_asewkt
-----------------------------------------------------------
SRID=4326;POLYGON((0 20 0,0 45 0,5 35 0,5 10 0,0 20 0))
SRID=4326;POLYGON((5 10 0,15 25 0,25 20 0,15 5 0,5 10 0))
SRID=4326;POLYGON((10 0 0,30 5 0,45 5 0,25 0 0,10 0 0))
(3 rows)
--S5.验证函数ST_Force3DZ几何强制转换为3DZ表示
SELECT ST_AsEWKT(ST_Force3DZ(ST_GeomFromEWKT('CIRCULARSTRING(1 1 2, 2 3 2, 4 5 2, 6 7 2, 5 6 2)')));
st_asewkt
-----------------------------------------------
CIRCULARSTRING(1 1 2,2 3 2,4 5 2,6 7 2,5 6 2)
(1 row)
SELECT ST_AsEWKT(ST_Force3DZ(the_geom)) from geom_polygon where name='BBBB' order by id;
st_asewkt
--------------------------------------------------------
SRID=4326;POLYGON((0 10 0,0 25 0,5 20 0,5 5 0,0 10 0))
SRID=4326;POLYGON((5 0 0,15 5 0,25 5 0,15 0 0,5 0 0))
(2 rows)
--S6.验证函数ST_Force3DM几何强制转换3DM表示
SELECT ST_AsEWKT(ST_Force3DM(ST_GeomFromEWKT('CIRCULARSTRING(1 1 2, 2 3 2, 4 5 2, 6 7 2, 5 6 2)')));
st_asewkt
------------------------------------------------
CIRCULARSTRINGM(1 1 0,2 3 0,4 5 0,6 7 0,5 6 0)
(1 row)
SELECT ST_AsEWKT(ST_Force3DM(the_geom)) from geom_multipolygon order by id limit 10;
st_asewkt
-------------------------------------------------------------------------------------
MULTIPOLYGONM(((0 0 0,0 0 0,0 0 0,0 0 0,0 0 0)),((0 0 0,0 0 0,0 0 0,0 0 0)))
MULTIPOLYGONM(((0 0 0,0 0 0,0 0 0,0 0 0,0 0 0)),((0 0 0,0 0 0,0 0 0,0 0 0)))
MULTIPOLYGONM(((0 10 0,0 20 0,0 11 0,0 2 0,0 10 0)),((0 10 0,0 20 0,0 2 0,0 10 0)))
MULTIPOLYGONM(((0 10 0,0 20 0,0 11 0,0 2 0,0 10 0)),((0 10 0,0 20 0,0 2 0,0 10 0)))
MULTIPOLYGONM(((0 20 0,0 40 0,0 22 0,0 4 0,0 20 0)),((0 20 0,0 40 0,0 4 0,0 20 0)))
MULTIPOLYGONM(((0 20 0,0 40 0,0 22 0,0 4 0,0 20 0)),((0 20 0,0 40 0,0 4 0,0 20 0)))
MULTIPOLYGONM(((0 30 0,0 60 0,0 33 0,0 6 0,0 30 0)),((0 30 0,0 60 0,0 6 0,0 30 0)))
MULTIPOLYGONM(((0 30 0,0 60 0,0 33 0,0 6 0,0 30 0)),((0 30 0,0 60 0,0 6 0,0 30 0)))
MULTIPOLYGONM(((0 40 0,0 80 0,0 44 0,0 8 0,0 40 0)),((0 40 0,0 80 0,0 8 0,0 40 0)))
MULTIPOLYGONM(((0 40 0,0 80 0,0 44 0,0 8 0,0 40 0)),((0 40 0,0 80 0,0 8 0,0 40 0)))
(10 rows)
--S7.验证函数ST_Force4D几何强制转换4D表示
SELECT ST_AsEWKT(ST_Force4D(ST_GeomFromEWKT('CIRCULARSTRING(1 1 2, 2 3 2, 4 5 2, 6 7 2, 5 6 2)')));
st_asewkt
---------------------------------------------------------
CIRCULARSTRING(1 1 2 0,2 3 2 0,4 5 2 0,6 7 2 0,5 6 2 0)
(1 row)
SELECT ST_AsEWKT(ST_Force4D(the_geom)) from geom_line order by id limit 10;
st_asewkt
-----------------------------------------
SRID=4326;LINESTRING(0 0 0 0,5 5 0 0)
SRID=4326;LINESTRING(0 10 0 0,5 15 0 0)
SRID=4326;LINESTRING(0 20 0 0,5 25 0 0)
SRID=4326;LINESTRING(0 30 0 0,5 35 0 0)
SRID=4326;LINESTRING(0 40 0 0,5 45 0 0)
SRID=4326;LINESTRING(0 50 0 0,5 55 0 0)
SRID=4326;LINESTRING(0 60 0 0,5 65 0 0)
SRID=4326;LINESTRING(0 70 0 0,5 75 0 0)
SRID=4326;LINESTRING(0 80 0 0,5 85 0 0)
SRID=4326;LINESTRING(0 90 0 0,5 95 0 0)
(10 rows)
--S8.验证函数ST_ForceCollection几何强制转换集合表示
SELECT ST_AsEWKT(ST_ForceCollection('POLYGON((0 0 1,0 5 1,5 0 1,0 0 1),(1 1 1,3 1 1,1 3 1,1 1 1))'));
st_asewkt
----------------------------------------------------------------------------------
GEOMETRYCOLLECTION(POLYGON((0 0 1,0 5 1,5 0 1,0 0 1),(1 1 1,3 1 1,1 3 1,1 1 1)))
(1 row)
SELECT ST_AsText(ST_ForceCollection('CIRCULARSTRING(220227 150406,2220227 150407,220227 150406)'));
st_astext
--------------------------------------------------------------------------------
GEOMETRYCOLLECTION(CIRCULARSTRING(220227 150406,2220227 150407,220227 150406))
(1 row)
SELECT ST_AsEWKT(ST_ForceCollection('POLYHEDRALSURFACE(((0 0 0,0 0 1,0 1 1,0 1 0,0 0 0)),
((0 0 0,0 1 0,1 1 0,1 0 0,0 0 0)),
((0 0 0,1 0 0,1 0 1,0 0 1,0 0 0)),
((1 1 0,1 1 1,1 0 1,1 0 0,1 1 0)),
((0 1 0,0 1 1,1 1 1,1 1 0,0 1 0)),
((0 0 1,1 0 1,1 1 1,0 1 1,0 0 1)))'));
st_asewkt
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GEOMETRYCOLLECTION(POLYGON((0 0 0,0 0 1,0 1 1,0 1 0,0 0 0)),POLYGON((0 0 0,0 1 0,1 1 0,1 0 0,0 0 0)),POLYGON((0 0 0,1 0 0,1 0 1,0 0 1,0 0 0)),POLYGON((1 1 0,1 1 1,1 0 1,1 0 0,1 1 0)),POLYGON((0 1 0,0 1 1,1 1 1,1 1 0,0 1 0)),POLYGON((0 0 1,1 0 1,1 1 1,0 1 1,0 0 1)))
(1 row)
--S9.ST_ForceSFS
--S10.验证函数ST_ForceRHR几何强制装换RHR表示
SELECT ST_AsEWKT(ST_ForceRHR(the_geom)) from geom_polygon order by id limit 7;
st_asewkt
------------------------------------------------
SRID=4326;POLYGON((0 0,0 5,5 5,5 0,0 0))
SRID=4326;POLYGON((0 10,0 25,5 20,5 5,0 10))
SRID=4326;POLYGON((0 20,0 45,5 35,5 10,0 20))
SRID=4326;POLYGON((0 30,0 65,5 50,5 15,0 30))
SRID=4326;POLYGON((0 40,0 85,5 65,5 20,0 40))
SRID=4326;POLYGON((0 50,0 105,5 80,5 25,0 50))
SRID=4326;POLYGON((0 60,0 125,5 95,5 30,0 60))
(7 rows)
--S11.验证函数ST_LineMerge合并多条线
SELECT ST_AsText(ST_LineMerge(ST_GeomFromText(ST_AsText(the_geom)))) from geom_multilinestring where id=3;
st_astext
----------------------------
LINESTRING(0 30,0 60,0 33)
(1 row)
--S12.验证函数ST_CollectionExtract截取指定类型元素的几何
SELECT ST_AsText(ST_CollectionExtract(ST_GeomFromText('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(0 0)))'),1));
st_astext
-----------------
MULTIPOINT(0 0)
(1 row)
SELECT ST_AsText(ST_CollectionExtract(ST_GeomFromText('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(LINESTRING(0 0, 1 1)),LINESTRING(2 2, 3 3))'),2));
st_astext
--------------------------------------
MULTILINESTRING((0 0,1 1),(2 2,3 3))
(1 row)
--S13.验证函数ST_CollectionHomogenize返回几何最简单表示
SELECT ST_AsText(ST_CollectionHomogenize('GEOMETRYCOLLECTION(POINT(0 0))'));
st_astext
------------
POINT(0 0)
(1 row)
SELECT ST_AsText(ST_CollectionHomogenize('GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0, 1 1))'));
st_astext
----------------------------------------------------
GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,1 1))
(1 row)
--S14.验证函数ST_Multi返回几何multi表示
SELECT ST_AsText(ST_Multi(ST_GeomFromText(ST_Astext(the_geom)))) from geom_point where id<5 order by id;
st_astext
------------------
MULTIPOINT(0 0)
MULTIPOINT(0 10)
MULTIPOINT(0 20)
MULTIPOINT(0 30)
MULTIPOINT(0 40)
(5 rows)
SELECT ST_AsText(ST_Multi(ST_GeomFromText(ST_Astext(the_geom)))) from geom_line where id<5 order by id;
st_astext
------------------------------
MULTILINESTRING((0 0,5 5))
MULTILINESTRING((0 10,5 15))
MULTILINESTRING((0 20,5 25))
MULTILINESTRING((0 30,5 35))
MULTILINESTRING((0 40,5 45))
(5 rows)
SELECT ST_AsText(ST_Multi(ST_GeomFromText(ST_Astext(the_geom)))) from geom_polygon where id<5 order by id;
st_astext
--------------------------------------------
MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)))
MULTIPOLYGON(((0 10,0 25,5 20,5 5,0 10)))
MULTIPOLYGON(((0 20,0 45,5 35,5 10,0 20)))
MULTIPOLYGON(((0 30,0 65,5 50,5 15,0 30)))
MULTIPOLYGON(((0 40,0 85,5 65,5 20,0 40)))
(5 rows)
SELECT ST_AsText(ST_Multi(ST_GeomFromText(ST_Astext(the_geom)))) from geom_multipoint where id<5 order by id;
st_astext
----------------------------
MULTIPOINT(0 0,0 0,0 0)
MULTIPOINT(0 10,0 20,0 11)
MULTIPOINT(0 20,0 40,0 22)
MULTIPOINT(0 30,0 60,0 33)
MULTIPOINT(0 40,0 80,0 44)
(5 rows)
--S15.验证函数ST_RemovePoint从线上移除一个点
SELECT ST_RemovePoint(ST_GeomFromEWKT('LINESTRING(1 2 3, 1 4 3, 1 5 5)'), 1);
st_removepoint
--------------------------------------------------------------------------------------------------------------------
010200008002000000000000000000F03F00000000000000400000000000000840000000000000F03F00000000000014400000000000001440
(1 row)
SELECT ST_RemovePoint(ST_GeomFromEWKT(the_geom), 1) from geom_line where id=7;
ERROR: Can't remove points from a single segment line
CONTEXT: referenced column: st_removepoint
SELECT ST_RemovePoint(ST_GeomFromEWKT('point(1 2 3)'), 1);
ERROR: First argument must be a LINESTRING
CONTEXT: referenced column: st_removepoint
--S16.验证函数ST_Reverse翻转几何图形
SELECT ST_AsText(the_geom) as line, ST_AsText(ST_Reverse(the_geom)) As reverseline FROM (SELECT ST_MakeLine(ST_MakePoint(1,2), ST_MakePoint(1,10)) As the_geom) as foo;
line | reverseline
----------------------+----------------------
LINESTRING(1 2,1 10) | LINESTRING(1 10,1 2)
(1 row)
--S17.验证函数ST_Rotate围绕原点逆时针旋转的几何
SELECT ST_AsEWKT(ST_Rotate(the_geom, pi())) from geom_point where id=6;
st_asewkt
----------------------------------
POINT(-7.34788079488412e-15 -60)
(1 row)
SELECT ST_AsEWKT(ST_Rotate(the_geom, -pi()/3)) from geom_line where id=6;
st_asewkt
-----------------------------------------------------------------------------
SRID=4326;LINESTRING(51.9615242270663 30,58.7916512459885 28.1698729810778)
(1 row)
SELECT ST_AsEWKT(ST_Rotate(the_geom, pi()/2)) from geom_polygon where id=6;
st_asewkt
-----------------------------------------------------------------------------------------------------------------------------
SRID=4326;POLYGON((-60 3.67394039744206e-15,-125 7.65404249467096e-15,-95 5.00000000000001,-30 5,-60 3.67394039744206e-15))
(1 row)
SELECT ST_AsEWKT(ST_Rotate(the_geom, -pi())) from geom_multilinestring where id=6;
st_asewkt
------------------------------------------------------------------------------------------------
MULTILINESTRING((7.34788079488412e-15 -60,1.46957615897682e-14 -120,8.08266887437253e-15 -66))
(1 row)
--S18.验证函数ST_RotateX围绕X轴旋转的几何
SELECT ST_AsEWKT(ST_RotateX(the_geom, pi()/2)) from geom_point order by id limit 10;
st_asewkt
-------------------------------
POINT(0 0)
POINT(0 6.12323399573677e-16)
POINT(0 1.22464679914735e-15)
POINT(0 1.83697019872103e-15)
POINT(0 2.44929359829471e-15)
POINT(0 3.06161699786838e-15)
POINT(0 3.67394039744206e-15)
POINT(0 4.28626379701574e-15)
POINT(0 4.89858719658941e-15)
POINT(0 5.51091059616309e-15)
(10 rows)
SELECT ST_AsEWKT(ST_RotateX(the_geom, -pi()/3)) from geom_line order by id limit 5;
st_asewkt
-----------------------------------
SRID=4326;LINESTRING(0 0,5 2.5)
SRID=4326;LINESTRING(0 5,5 7.5)
SRID=4326;LINESTRING(0 10,5 12.5)
SRID=4326;LINESTRING(0 15,5 17.5)
SRID=4326;LINESTRING(0 20,5 22.5)
(5 rows)
SELECT ST_AsEWKT(ST_RotateX(the_geom, pi()/2)) from geom_polygon where id=9;
st_asewkt
----------------------------------------------------------------------------------------------------------------------------------------
SRID=4326;POLYGON((0 5.51091059616309e-15,0 1.1327982892113e-14,5 8.57252759403147e-15,5 2.75545529808154e-15,0 5.51091059616309e-15))
(1 row)
--S19.验证函数ST_RotateY围绕Y轴旋转的几何
SELECT ST_AsEWKT(ST_RotateY(the_geom, pi()/2)) from geom_multipoint where name='VVVV' order by id;
st_asewkt
-----------
(0 rows)
SELECT ST_AsEWKT(ST_RotateY(the_geom, pi())) from geom_multilinestring where name='CCCC' order by id;
st_asewkt
----------------------------------------
MULTILINESTRING((0 20,0 40,0 22))
MULTILINESTRING((-10 10,-5 20,-15 11))
MULTILINESTRING((-20 0,-10 0,-30 0))
(3 rows)
--S20.验证函数ST_RotateZ围绕Z轴旋转的几何
SELECT ST_AsEWKT(ST_RotateZ(the_geom, pi()/2)) from geom_multipoint where name='VVVV' order by id;
st_asewkt
-----------
(0 rows)
SELECT ST_AsEWKT(ST_RotateZ(the_geom, pi()/2)) from geom_multipolygon where name='UUUU' order by citiID;
st_asewkt
--------------------------------------------------------------------------------------------------
MULTIPOLYGON(((-100 100,-200 50,-110 150,-20 30,-100 100)),((-100 100,-200 50,-20 30,-100 100)))
MULTIPOLYGON(((-100 100,-200 50,-110 150,-20 30,-100 100)),((-100 100,-200 50,-20 30,-100 100)))
(2 rows)
--S21.验证函数ST_Scale放大或缩小几何
SELECT ST_AsEWKT(ST_Scale(ST_GeomFromEWKT(ST_AsText(the_geom)), 0.5, 0.75, 0.8)) from geom_point where id=13;
st_asewkt
-----------
(0 rows)
SELECT ST_AsEWKT(ST_Scale(ST_GeomFromEWKT(ST_AsText(the_geom)), 0.5, 0.5, 0.1)) from geom_line where id=25;
st_asewkt
-----------
(0 rows)
SELECT ST_AsEWKT(ST_Scale(ST_GeomFromEWKT(ST_AsText(the_geom)), 0.3, 0.9, 0.4)) from geom_polygon where id=37;
st_asewkt
-----------
(0 rows)
--S22.验证函数ST_Segmentize
SELECT ST_AsText(ST_Segmentize(ST_GeomFromText(the_geom),5)) from geom_multilinestring order by id limit 10;
ERROR: parse error - invalid geometry
--?HINT:.*
CONTEXT: referenced column: st_astext
SELECT ST_AsText(ST_Segmentize(ST_GeomFromText(ST_AsText(the_geom)),10)) from geom_polygon where id<10 order by id;
st_astext
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
POLYGON((0 0,0 5,5 5,5 0,0 0))
POLYGON((0 10,0 20,0 25,5 20,5 10,5 5,0 10))
POLYGON((0 20,0 30,0 40,0 45,4.47213595499958 36.0557280900008,5 35,5 25,5 15,5 10,0.52786404500042 18.9442719099992,0 20))
POLYGON((0 30,0 40,0 50,0 60,0 65,3.16227766016838 55.5131670194949,5 50,5 40,5 30,5 20,5 15,1.83772233983162 24.4868329805051,0 30))
POLYGON((0 40,0 50,0 60,0 70,0 80,0 85,2.42535625036333 75.2985749985467,4.85071250072666 65.5971499970934,5 65,5 55,5 45,5 35,5 25,5 20,2.57464374963667 29.7014250014533,0.14928749927334 39.4028500029066,0 40))
POLYGON((0 50,0 60,0 70,0 80,0 90,0 100,0 105,1.96116135138184 95.1941932430908,3.92232270276368 85.3883864861816,5 80,5 70,5 60,5 50,5 40,5 30,5 25,3.03883864861816 34.8058067569092,1.07767729723632 44.6116135138184,0 50))
POLYGON((0 60,0 70,0 80,0 90,0 100,0 110,0 120,0 125,1.64398987305357 115.136060761679,3.28797974610715 105.272121523357,4.93196961916072 95.4081822850357,5 95,5 85,5 75,5 65,5 55,5 45,5 35,5 30,3.35601012694643 39.8639392383214,1.71202025389285 49.7278784766429,0.0680303808392821 59.5918177149643,0 60))
POLYGON((0 70,0 80,0 90,0 100,0 110,0 120,0 130,0 140,0 145,1.41421356237309 135.100505063388,2.82842712474619 125.201010126777,4.24264068711928 115.301515190165,5 110,5 100,5 90,5 80,5 70,5 60,5 50,5 40,5 35,3.58578643762691 44.8994949366117,2.17157287525381 54.7989898732233,0.757359312880714 64.698484809835,0 70))
POLYGON((0 80,0 90,0 100,0 110,0 120,0 130,0 140,0 150,0 160,0 165,1.24034734589208 155.077221232863,2.48069469178417 145.154442465727,3.72104203767625 135.23166369859,4.96138938356833 125.308884931453,5 125,5 115,5 105,5 95,5 85,5 75,5 65,5 55,5 45,5 40,3.75965265410792 49.9227787671367,2.51930530821583 59.8455575342734,1.27895796232375 69.76833630141,0.0386106164316615 79.6911150685467,0 80))
POLYGON((0 90,0 100,0 110,0 120,0 130,0 140,0 150,0 160,0 170,0 180,0 185,1.10431526074847 175.061162653264,2.20863052149693 165.122325306528,3.3129457822454 155.183487959791,4.41726104299386 145.244650613055,5 140,5 130,5 120,5 110,5 100,5 90,5 80,5 70,5 60,5 50,5 45,3.89568473925153 54.9388373467362,2.79136947850307 64.8776746934724,1.6870542177546 74.8165120402086,0.582738957006139 84.7553493869448,0 90))
(10 rows)
--S23.验证函数ST_SetPoint替换线中的点
SELECT ST_AsText(ST_SetPoint(the_geom, 0, 'POINT(-1 1)')) from geom_line where id=5;
st_astext
-----------------------
LINESTRING(-1 1,5 55)
(1 row)
SELECT ST_AsEWKT(ST_SetPoint(foo.the_geom, ST_NumPoints(foo.the_geom) - 1, ST_GeomFromEWKT ('POINT(-1 1 3)')))
FROM (SELECT ST_GeomFromEWKT('LINESTRING(-1 2 3,-1 3 4, 5 6 7)') As the_geom) As foo;
st_asewkt
----------------------------------
LINESTRING(-1 2 3,-1 3 4,-1 1 3)
(1 row)
--S24.验证函数ST_SetSRID设置指定SRID
SELECT ST_SetSRID(ST_Point(-123.365556, 48.428611),4326) As wgs84long_lat;
wgs84long_lat
----------------------------------------------------
0101000020E6100000D236FE4465D75EC059DAA9B9DC364840
(1 row)
SELECT ST_Transform(ST_SetSRID(ST_Point(-123.365556, 48.428611),4326),3785) As spere_merc;
spere_merc
----------------------------------------------------
0101000020C90E000010DC02DC8F316AC16457B6BDA6915741
(1 row)
--S25.验证函数ST_SnapToGrid
SELECT ST_AsText(ST_SnapToGrid(
ST_GeomFromText('LINESTRING(1.1115678 2.123, 4.111111 3.2374897, 4.11112 3.23748667)
'),
0.001)
);
st_astext
-------------------------------------
LINESTRING(1.112 2.123,4.111 3.237)
(1 row)
SELECT ST_AsEWKT(ST_SnapToGrid(
ST_GeomFromEWKT('LINESTRING(-1.1115678 2.123 2.3456 1.11111,
4.111111 3.2374897 3.1234 1.1111, -1.11111112 2.123 2.3456 1.1111112)'),
ST_GeomFromEWKT('POINT(1.12 2.22 3.2 4.4444)'),
0.1, 0.1, 0.1, 0.01) );
st_asewkt
------------------------------------------------------------------------------
LINESTRING(-1.08 2.12 2.3 1.1144,4.12 3.22 3.1 1.1144,-1.08 2.12 2.3 1.1144)
(1 row)
SELECT ST_AsEWKT(ST_SnapToGrid(ST_GeomFromEWKT('LINESTRING(-1.1115678 2.123 3 2.3456,
4.111111 3.2374897 3.1234 1.1111)'),
0.01) );
st_asewkt
---------------------------------------------------------
LINESTRING(-1.11 2.12 3 2.3456,4.11 3.24 3.1234 1.1111)
(1 row)
--S26.验证函数ST_Snap
SELECT ST_AsText(ST_Snap(poly,line, ST_Distance(poly,line)*1.01)) AS polysnapped
FROM (SELECT
ST_GeomFromText('MULTIPOLYGON(
((26 125, 26 200, 126 200, 126 125, 26 125 ),
( 51 150, 101 150, 76 175, 51 150 ) ),
(( 151 100, 151 200, 176 175, 151 100 )))') As poly,
ST_GeomFromText('LINESTRING (5 107, 54 84, 101 100)') As line
) As foo;
polysnapped
-----------------------------------------------------------------------------------------------------------------------------------
MULTIPOLYGON(((26 125,26 200,126 200,126 125,101 100,26 125),(51 150,101 150,76 175,51 150)),((151 100,151 200,176 175,151 100)))
(1 row)
SELECT ST_AsText(
ST_Snap(poly,line, ST_Distance(poly, line)*1.25)
) AS polysnapped
FROM (SELECT
ST_GeomFromText('MULTIPOLYGON(
(( 26 125, 26 200, 126 200, 126 125,26 125 ),
( 51 150, 101 150, 76 175, 51 150 ) ),
(( 151 100, 151 200, 176 175, 151 100 )))') As poly,
ST_GeomFromText('LINESTRING (5 107, 54 84, 101 100)') As line
) As foo;
polysnapped
---------------------------------------------------------------------------------------------------------------------------------------
MULTIPOLYGON(((5 107,26 200,126 200,126 125,101 100,54 84,5 107),(51 150,101 150,76 175,51 150)),((151 100,151 200,176 175,151 100)))
(1 row)
SELECT ST_AsText(
ST_Snap(line, poly, ST_Distance(poly, line)*1.01)
) AS linesnapped
FROM (SELECT
ST_GeomFromText('MULTIPOLYGON(
((26 125, 26 200, 126 200, 126 125, 26 125),
(51 150, 101 150, 76 175, 51 150 )) ,
((151 100, 151 200, 176 175, 151 100)))') As poly,
ST_GeomFromText('LINESTRING (5 107, 54 84, 101 100)') As line
) As foo;
linesnapped
----------------------------------------
LINESTRING(5 107,26 125,54 84,101 100)
(1 row)
SELECT ST_AsText(
ST_Snap(line, poly, ST_Distance(poly, line)*1.25)
) AS linesnapped
FROM (SELECT
ST_GeomFromText('MULTIPOLYGON(
(( 26 125, 26 200, 126 200, 126 125, 26 125 ),
(51 150, 101 150, 76 175, 51 150 )),
((151 100, 151 200, 176 175, 151 100 )))') As poly,
ST_GeomFromText('LINESTRING (5 107, 54 84, 101 100)') As line
) As foo;
linesnapped
----------------------------------
LINESTRING(26 125,54 84,101 100)
(1 row)
--S27.验证函数ST_Transform不同空间参考下的几何
SELECT ST_AsText(ST_Transform(ST_GeomFromText('POLYGON((743238 2967416,743238 2967450,
743265 2967450,743265.625 2967416,743238 2967416))',2249),4326)) As wgs_geom;
wgs_geom
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
POLYGON((-71.1776848522251 42.3902896512902,-71.1776843766326 42.3903829478009,-71.1775844305465 42.3903826677917,-71.1775825927231 42.3902893647987,-71.1776848522251 42.3902896512902))
(1 row)
SELECT ST_AsEWKT(ST_Transform(ST_GeomFromEWKT('SRID=2249;CIRCULARSTRING(743238 2967416 1,743238 2967450 2,743265 2967450 3,743265.625 2967416 3,743238 2967416 4)'),4326));
st_asewkt
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SRID=4326;CIRCULARSTRING(-71.1776848522251 42.3902896512902 0.304800609601219,-71.1776843766326 42.3903829478009 0.609601219202438,-71.1775844305465 42.3903826677917 0.914401828803657,-71.1775825927231 42.3902893647987 0.914401828803657,-71.1776848522251 42.3902896512902 1.21920243840488)
(1 row)
--S28.验证函数ST_Translate偏移几何
SELECT ST_AsText(ST_Translate(ST_GeomFromText('POINT(-71.01 42.37)',4326),1,0)) As wgs_transgeomtxt;
wgs_transgeomtxt
---------------------
POINT(-70.01 42.37)
(1 row)
SELECT ST_AsText(ST_Translate(ST_GeomFromText('LINESTRING(-71.01 42.37,-71.11 42.38)',4326),1,0.5)) As wgs_transgeomtxt;
wgs_transgeomtxt
---------------------------------------
LINESTRING(-70.01 42.87,-70.11 42.88)
(1 row)
SELECT ST_AsEWKT(ST_Translate(CAST('POINT(0 0 0)' As geometry), 5, 12,3));
st_asewkt
---------------
POINT(5 12 3)
(1 row)
SELECT ST_AsText(ST_Translate(ST_Collect('CURVEPOLYGON(CIRCULARSTRING(4 3,3.12 0.878,1 0,-1.121 5.1213,6 7, 8 9,4 3))','POINT(1 3)'),1,2));
st_astext
------------------------------------------------------------------------------------------------------------
GEOMETRYCOLLECTION(CURVEPOLYGON(CIRCULARSTRING(5 5,4.12 2.878,2 2,-0.121 7.1213,7 9,9 11,5 5)),POINT(2 5))
(1 row)
--S29.验证函数ST_TransScale放大或缩小几何
SELECT ST_AsEWKT(ST_TransScale(ST_GeomFromEWKT(ST_AsText(the_geom)), 0.5, 1, 1, 2)) from geom_line order by id limit 10;
st_asewkt
-----------------------------
LINESTRING(0.5 2,5.5 12)
LINESTRING(0.5 22,5.5 32)
LINESTRING(0.5 42,5.5 52)
LINESTRING(0.5 62,5.5 72)
LINESTRING(0.5 82,5.5 92)
LINESTRING(0.5 102,5.5 112)
LINESTRING(0.5 122,5.5 132)
LINESTRING(0.5 142,5.5 152)
LINESTRING(0.5 162,5.5 172)
LINESTRING(0.5 182,5.5 192)
(10 rows)
/*
###############################################################################
# TESTCASE NAME : postgis_managementsql
# COMPONENT(S) : test management function
# PREREQUISITE : 无
# PLATFORM : all
# DESCRIPTION : 列存表添加几何列，删除几何列，查看各种库的版本信息
# TAG : AddGeometryColumn，DropGeometryColumn，DropGeometryTable，version
# TC LEVEL : Level 1
################################################################################
*/
--I1验证行存表添加和删除几何列的功能
--S1清理以前创建的表
DROP TABLE IF EXISTS my_spatial_table;
NOTICE: table "my_spatial_table" does not exist, skipping
--S2创建行存表表字段为id，cities
CREATE TABLE IF NOT EXISTS my_spatial_table (id serial, cities varchar(20));
NOTICE: CREATE TABLE will create implicit sequence "my_spatial_table_id_seq" for serial column "my_spatial_table.id"
--S3使用postgis函数为创建的表添加几何列，参数依次为schema，table，几何列名，SRID，几何类型，坐标维度
SELECT AddGeometryColumn ('my_spatial_table','geom',4326,'POINT',2);
--?.*
--?.*
postgis.my_spatial_table.geom SRID:4326 TYPE:POINT DIMS:2
(1 row)
--S4插入几何数据
INSERT INTO my_spatial_table(cities, geom) VALUES('xian', ST_GeomFromText('POINT(1 2)',4326) );
--S5查询插入的数据
SELECT ST_AsEWKT(ST_SetSRID(geom, 3857)) from my_spatial_table where id=1;
st_asewkt
----------------------
SRID=3857;POINT(1 2)
(1 row)
--S6Populate_Geometry_Columns 确保几何列使用类型修饰符定义或具有适当的空间约束
SELECT Populate_Geometry_Columns('my_spatial_table'::regclass);
populate_geometry_columns
---------------------------
0
(1 row)
--S7UpdateGeometrySRID 更新几何列中所有要素的SRID，geometry_columns元数据和srid
SELECT UpdateGeometrySRID('my_spatial_table','geom',3857);
ERROR: invalid SRID: 3857 not found in spatial_ref_sys
CONTEXT: referenced column: updategeometrysrid
SQL statement "SELECT UpdateGeometrySRID('','',$1,$2,$3)"
PL/pgSQL function updategeometrysrid(character varying,character varying,integer) line 5 at SQL statement
referenced column: updategeometrysrid
--S8使用postgis函数从表中删除几何列
SELECT DropGeometryColumn ('my_spatial_table','geom');
--?.*
--?.*
postgis.my_spatial_table.geom effectively removed.
(1 row)
--S9查询删除的几何列不存在
SELECT geom from my_spatial_table;
ERROR: column "geom" does not exist
LINE 1: SELECT geom from my_spatial_table;
^
CONTEXT: referenced column: geom
--S10DropGeometryTable 在geometry_columns中删除表及其所有引用
SELECT DropGeometryTable ('my_spatial_table');
ERROR: table postgis.my_spatial_table not found in geometry_columns
CONTEXT: referenced column: dropgeometrytable
--S11.查询删除的表不存在
SELECT geom from my_spatial_table;
ERROR: column "geom" does not exist
LINE 1: SELECT geom from my_spatial_table;
^
CONTEXT: referenced column: geom
--I2验证查看版本信息函数功能
--S1验证函数PostGIS_Full_Version返回postgis版本和构建的配置信息
SELECT PostGIS_Full_Version();
postgis_full_version
------------------------------------------------------------------------------------------------------------------------------------------
POSTGIS="2.4.2 r2.1.1" PGSQL="92" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.2, 08 September 2015" LIBXML="2.7.1" LIBJSON="0.12.1"
(1 row)
--S2验证函数PostGIS_GEOS_Version返回GEOS库的版本号
SELECT PostGIS_GEOS_Version();
postgis_geos_version
----------------------------
3.6.2-CAPI-1.10.2 4d2925d6
(1 row)
--S3验证函数PostGIS_LibXML_Version返回libxml2库的版本号
SELECT PostGIS_LibXML_Version();
postgis_libxml_version
------------------------
2.7.1
(1 row)
--S5验证函数PostGIS_Lib_Version返回PostGis库的版本号
SELECT PostGIS_Lib_Version();
postgis_lib_version
---------------------
2.4.2
(1 row)
--S6验证函数PostGIS_PROJ_Version返回PROJ4库的版本号
SELECT PostGIS_PROJ_Version();
postgis_proj_version
-------------------------------
Rel. 4.9.2, 08 September 2015
(1 row)
--S7验证函数PostGIS_Scripts_Build_Date返回PostGis scripts的创建时间
SELECT PostGIS_Scripts_Build_Date();
postgis_scripts_build_date
----------------------------
2018-05-03 06:33:31
(1 row)
--S8验证函数PostGIS_Scripts_Installed返回此数据库中安装的postgis脚本的版本
SELECT PostGIS_Scripts_Installed();
postgis_scripts_installed
---------------------------
2.4.2 r2.1.1
(1 row)
--S9验证函数PostGIS_Scripts_Released返回随安装的postgis库发布的postgissql脚本的版本号
SELECT PostGIS_Scripts_Released();
postgis_scripts_released
--------------------------
2.4.2 r2.1.1
(1 row)
--S10验证函数PostGIS_Version返回postgis版本号和编译时选项
SELECT PostGIS_Version();
postgis_version
---------------------------------------
2.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)
SELECT (g.gdump).path, ST_AsEWKT((g.gdump).geom) as wkt
FROM
(SELECT
ST_DumpPoints(ST_GeomFromEWKT('POLYHEDRALSURFACE( ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0
0)),
((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),
((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)), ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) )') ) AS gdump
) AS g;
path | wkt
---------+--------------
{1,1,1} | POINT(0 0 0)
{1,1,2} | POINT(0 0 1)
{1,1,3} | POINT(0 1 1)
{1,1,4} | POINT(0 1 0)
{1,1,5} | POINT(0 0 0)
{2,1,1} | POINT(0 0 0)
{2,1,2} | POINT(0 1 0)
{2,1,3} | POINT(1 1 0)
{2,1,4} | POINT(1 0 0)
{2,1,5} | POINT(0 0 0)
{3,1,1} | POINT(0 0 0)
{3,1,2} | POINT(1 0 0)
{3,1,3} | POINT(1 0 1)
{3,1,4} | POINT(0 0 1)
{3,1,5} | POINT(0 0 0)
{4,1,1} | POINT(1 1 0)
{4,1,2} | POINT(1 1 1)
{4,1,3} | POINT(1 0 1)
{4,1,4} | POINT(1 0 0)
{4,1,5} | POINT(1 1 0)
{5,1,1} | POINT(0 1 0)
{5,1,2} | POINT(0 1 1)
{5,1,3} | POINT(1 1 1)
{5,1,4} | POINT(1 1 0)
{5,1,5} | POINT(0 1 0)
{6,1,1} | POINT(0 0 1)
{6,1,2} | POINT(1 0 1)
{6,1,3} | POINT(1 1 1)
{6,1,4} | POINT(0 1 1)
{6,1,5} | POINT(0 0 1)
(30 rows)
SELECT Box2D(ST_GeomFromText('LINESTRING(1 2, 3 4, 5 6)'));
box2d
--------------
BOX(1 2,5 6)
(1 row)
SELECT Box2D(ST_GeomFromText('CIRCULARSTRING(220268 150415,220227 150505,220227 150406)')
);
box2d
----------------------------------------------------------------
BOX(220186.995121892 150406,220288.248780547 150506.126829327)
(1 row)
select '114', ST_perimeter2d('MULTIPOLYGON( ((0 0, 10 0, 10 10, 0 10, 0 0)),( (0 0, 10 0, 10 10, 0 10, 0 0),(5 5, 7 5, 7 7 , 5 7, 5 5) ) ,( (0 0, 10 0, 10 10, 0 10, 0 0),(5 5, 7 5, 7 7, 5 7, 5 5),(1 1,2 1, 2 2, 1 2, 1 1) ) )'::GEOMETRY) as value;
?column? | value
----------+-------
114 | 140
(1 row)
select ST_GeogFromText('POINT(-69.83262 43.43636)') ;
st_geogfromtext
----------------------------------------------------
0101000020E6100000B77F65A5497551C02BA4FCA4DAB74540
(1 row)
select ST_GeogFromText('SRID=4326;POINT(-11.1111111 40)');
st_geogfromtext
----------------------------------------------------
0101000020E6100000F771D98DE33826C00000000000004440
(1 row)
SELECT ST_AsText(ST_SnapToGrid(
ST_GeomFromText('LINESTRING(1.1115678 2.123, 4.111111 3.2374897, 4.11112 3.23748667)
'),
0.001)
);
st_astext
-------------------------------------
LINESTRING(1.112 2.123,4.111 3.237)
(1 row)
SELECT ST_AsEWKT(ST_SnapToGrid(
ST_GeomFromEWKT('LINESTRING(-1.1115678 2.123 2.3456 1.11111,
4.111111 3.2374897 3.1234 1.1111, -1.11111112 2.123 2.3456 1.1111112)'),
ST_GeomFromEWKT('POINT(1.12 2.22 3.2 4.4444)'),
0.1, 0.1, 0.1, 0.01) );
st_asewkt
------------------------------------------------------------------------------
LINESTRING(-1.08 2.12 2.3 1.1144,4.12 3.22 3.1 1.1144,-1.08 2.12 2.3 1.1144)
(1 row)
SELECT ST_AsEWKT(ST_SnapToGrid(ST_GeomFromEWKT('LINESTRING(-1.1115678 2.123 3 2.3456,
4.111111 3.2374897 3.1234 1.1111)'),
0.01) );
st_asewkt
---------------------------------------------------------
LINESTRING(-1.11 2.12 3 2.3456,4.11 3.24 3.1234 1.1111)
(1 row)
SELECT ST_Length_Spheroid( the_geom, sph_m ) As tot_len,
ST_Length_Spheroid(ST_GeometryN(the_geom,1), sph_m) As len_line1,
ST_Length_Spheroid(ST_GeometryN(the_geom,2), sph_m) As len_line2
FROM (SELECT ST_GeomFromText('MULTILINESTRING((-118.584 38.374,-118.583 38.5),
(-71.05957 42.3589 , -71.061 43))') As the_geom,
CAST('SPHEROID["GRS_1980",6378137,298.257222101]' As spheroid) As sph_m) as foo;
tot_len | len_line1 | len_line2
------------------+------------------+------------------
--? 85204.52077.* | 13986.87252.* | 71217.64824.*
(1 row)
SELECT ST_Length_Spheroid( the_geom, sph_m ) As tot_len,
ST_Length_Spheroid(ST_GeometryN(the_geom,1), sph_m) As len_line1,
ST_Length_Spheroid(ST_GeometryN(the_geom,2), sph_m) As len_line2
FROM (SELECT ST_GeomFromEWKT('MULTILINESTRING((-118.584 38.374 20,-118.583 38.5 30)
,
(-71.05957 42.3589 75, -71.061 43 90))') As the_geom,
CAST('SPHEROID["GRS_1980",6378137,298.257222101]' As spheroid) As sph_m) as foo;
tot_len | len_line1 | len_line2
------------------+------------------+------------------
--? 85204.52592.* | 13986.87610.* | 71217.64982.*
(1 row)
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;
cp_pt_line | cp_line_pt
----------------+------------------------------------------
POINT(100 100) | POINT(73.0769230769231 115.384615384615)
(1 row)
SELECT ST_DFullyWithin(geom_a, geom_b, 10) as DFullyWithin10, ST_DWithin(geom_a,
geom_b, 10) as DWithin10, ST_DFullyWithin(geom_a, geom_b, 20) as DFullyWithin20 from
(select ST_GeomFromText('POINT(1 1)') as geom_a,ST_GeomFromText('LINESTRING(1 5, 2 7, 1
9, 14 12)') as geom_b) t1;
dfullywithin10 | dwithin10 | dfullywithin20
----------------+-----------+----------------
f | t | t
(1 row)
SELECT '#146', ST_Distance(g1,g2), ST_Dwithin(g1,g2,0.01), ST_AsEWKT(g2) FROM (SELECT ST_geomFromEWKT('LINESTRING(1 2, 2 4)') As g1, ST_Collect(ST_GeomFromEWKT('LINESTRING(0 0, -1 -1)'), ST_GeomFromEWKT('MULTIPOINT(1 2,2 3)')) As g2) As foo;
?column? | st_distance | st_dwithin | st_asewkt
----------+-------------+------------+---------------------------------------------------------------
#146 | 0 | t | GEOMETRYCOLLECTION(LINESTRING(0 0,-1 -1),MULTIPOINT(1 2,2 3))
(1 row)
SELECT '#657.3',ST_DWithin(ST_Project('POINT(10 10)'::geography, 2000, pi()/2), 'POINT(10 10)'::geography, 2000);
?column? | st_dwithin
----------+------------
#657.3 | t
(1 row)
SELECT '#1264', ST_DWithin('POLYGON((-10 -10, -10 10, 10 10, 10 -10, -10 -10))'::geography, 'POINT(0 0)'::geography, 0);
?column? | st_dwithin
----------+------------
#1264 | t
(1 row)
SELECT ST_Intersects('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
st_intersects
---------------
f
(1 row)
SELECT ST_Intersects('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
st_intersects
---------------
t
(1 row)
SELECT ST_Intersects(
ST_GeographyFromText('SRID=4326;LINESTRING(-43.23456 72.4567,-43.23456 72.4568)'),
ST_GeographyFromText('SRID=4326;POINT(-43.23456 72.4567772)')
);
st_intersects
---------------
t
(1 row)
SELECT ST_AsEWKT(ST_Affine(the_geom, cos(pi()), -sin(pi()), 0, sin(pi()), cos(pi()), 0,
0, 0, 1, 0, 0, 0)) As using_affine,
ST_AsEWKT(ST_Rotate(the_geom, pi())) As using_rotate
FROM (SELECT ST_GeomFromEWKT('LINESTRING(1 2 3, 1 4 3)') As the_geom) As foo;
using_affine | using_rotate
-----------------------------+-----------------------------
LINESTRING(-1 -2 3,-1 -4 3) | LINESTRING(-1 -2 3,-1 -4 3)
(1 row)
select '#1023.b', postgis_addbbox('POINT(10 4)'::geometry) = 'POINT(10 4)'::geometry;
?column? | ?column?
----------+----------
#1023.b | t
(1 row)
SELECT 'T1B', ST_Summary(postgis_addbbox('POINT(0 0)'::geometry));
?column? | st_summary
----------+------------
T1B | Point[B]
(1 row)
SELECT 'T1ZMB', ST_Summary(postgis_addbbox('POINT(0 0 0 0)'::geometry));
?column? | st_summary
----------+------------
T1ZMB | Point[ZMB]
(1 row)
SELECT ST_Length2D(ST_GeomFromText('LINESTRING(743238 2967416,743238 2967450,743265 2967450,
743265.625 2967416,743238 2967416)',2249));
st_length2d
------------------
122.630744000095
(1 row)
SELECT ST_AsText(ST_Centroid('MULTIPOINT ( -1 0, -1 2, -1 3, -1 4, -1 7, 0 1, 0 3, 1 1, 2
0, 6 0, 7 8, 9 8, 10 6 )'));
st_astext
------------------------------------------
POINT(2.30769230769231 3.30769230769231)
(1 row)
SELECT ST_PolygonFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866
42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917
42.3902909739571))');
st_polygonfromtext
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
010300000001000000050000006285C7C15ECB51C0ED88FC0DF531454028A46F245FCB51C009075EA6F731454047DED1E65DCB51C0781C510EF83145404871A7835DCB51C0EBDAEE75F53145406285C7C15ECB51C0ED88FC0DF5314540
(1 row)
SELECT ST_PolygonFromText('POINT(1 2)') IS NULL as point_is_notpoly;
point_is_notpoly
------------------
t
(1 row)
SELECT ST_AsEWKT(ST_Force3D(ST_GeomFromEWKT('CIRCULARSTRING(1 1 2, 2 3 2, 4 5 2, 6 7 2,
5 6 2)')));
st_asewkt
-----------------------------------------------
CIRCULARSTRING(1 1 2,2 3 2,4 5 2,6 7 2,5 6 2)
(1 row)
SELECT ST_AsEWKT(ST_Force3D('POLYGON((0 0,0 5,5 0,0 0),(1 1,3 1,1 3,1 1))'));
st_asewkt
--------------------------------------------------------------
POLYGON((0 0 0,0 5 0,5 0 0,0 0 0),(1 1 0,3 1 0,1 3 0,1 1 0))
(1 row)
SELECT ST_Npoints(the_geom) As np_before, ST_NPoints(ST_Simplify(the_geom,0.1)) As
np01_notbadcircle, ST_NPoints(ST_Simplify(the_geom,0.5)) As np05_notquitecircle,
ST_NPoints(ST_Simplify(the_geom,1)) As np1_octagon, ST_NPoints(ST_Simplify(the_geom,10)) As
np10_triangle,
(ST_Simplify(the_geom,100) is null) As np100_geometrygoesaway
FROM (SELECT ST_Buffer('POINT(1 3)', 10,12) As the_geom) As foo;
np_before | np01_notbadcircle | np05_notquitecircle | np1_octagon | np10_triangle | np100_geometrygoesaway
-----------+-------------------+---------------------+-------------+---------------+------------------------
49 | 33 | 17 | 9 | 4 | t
(1 row)