ST_Geometry
定义
ST_Geometry 通过熟知文本表示构造几何。
创建将用于 ArcGIS 的空间表时，最好将列创建为 ST_Geometry，而不要指定一个 ST_Geometry 子类型，如 ST_LineString。
语法
Oracle
- 对于线串、面和点
sde.st_geometry (wkt clob, srid integer)
- 对于优化的点（不启动 extproc 代理，因此查询的处理速度更快）
sde.st_geometry (x,y,z,m,srid)
在批量插入大量的点数据时，请使用优化的点构造。
- 对于参数圆
sde.st_geometry (x,y,z,m,radius,numPts,srid)
- 对于参数椭圆
sde.st_geometry (x,y,z,m,semiMajor,semiMinor,angle,numPts,srid)
- 对于参数楔形
sde.st_geometry (x, y, z, m, startangle, endangle, outerradius, innerradius, numPts, srid)
PostgreSQL
- 对于线串、面和点
sde.st_geometry (wkt, srid integer) sde.st_geometry (esri_shape bytea, srid integer)
- 对于参数圆
sde.st_geometry (x,y,z,m,radius,numPts,srid)
- 对于参数椭圆
sde.st_geometry (x,y,z,m,semiMajor,semiMinor,angle,numPts,srid)
- 对于参数楔形
sde.st_geometry (x, y, z, m, startangle, endangle, outerradius, innerradius, numPts, srid)
返回类型
ST_Geometry
示例
创建并查询点、线串和面要素
创建表 geoms。
CREATE TABLE geoms (id integer, geometry sde.st_geometry);
将点、线串和面值插入 geoms。
Oracle
INSERT INTO GEOMS (id, geometry) VALUES (
1901,
sde.st_geometry ('point (1 2)', 0)
);
--To insert the same point using optimized point construction:
INSERT INTO GEOMS (id, geometry) VALUES (
1901,
sde.st_geometry (1,2,null,null,0)
);
INSERT INTO GEOMS (id, geometry) VALUES (
1902,
sde.st_geometry ('linestring (33 2, 34 3, 35 6)', 0)
);
INSERT INTO GEOMS (id, geometry) VALUES (
1903,
sde.st_geometry ('polygon ((3 3, 4 6, 5 3, 3 3))', 0)
);
PostgreSQL
CREATE TABLE geoms (id integer, geometry sde.st_geometry);
INSERT INTO geoms (id, geometry) VALUES (
1901,
sde.st_geometry ('point (1 2)', 0)
);
INSERT INTO geoms (id, geometry) VALUES (
1902,
sde.st_geometry ('linestring (33 2, 34 3, 35 6)', 0)
);
INSERT INTO geoms (id, geometry) VALUES (
1903,
sde.st_geometry ('polygon ((3 3, 4 6, 5 3, 3 3))', 0)
);
从 geoms 表中选择几何。
Oracle
SELECT id, sde.st_astext (geometry) geometry
FROM GEOMS;
ID GEOMETRY
1901 POINT (1.00000000 2.00000000)
1902 LINESTRING (33.00000000 2.00000000, 34.00000000 3.00000000, 35.00000000 6.00000000)
1903 POLYGON ((3.00000000 3.00000001, 5.00000000 3.00000000, 4.00000000 6.00000000, 3.00000000 3.00000000))
PostgreSQL
SELECT id, st_astext (geometry) AS geometry
FROM geoms;
id geometry
1901 POINT (1 2)
1902 LINESTRING (33 2, 34 3, 35 6)
1903 POLYGON ((3 3, 5 3, 4 6, 3 3))
创建并查询参数圆
创建表 radii，并将圆插入其中。
Oracle
CREATE TABLE radii (id integer, geometry sde.st_geometry);
INSERT INTO RADII (id, geometry) VALUES (
1904,
sde.st_geometry (10,10,NULL,NULL,25,50,0)
);
INSERT INTO RADII (id, geometry) VALUES (
1905,
sde.st_geometry (5,15,NULL,NULL,10,20,0)
);
PostgreSQL
CREATE TABLE radii (id integer, geometry sde.st_geometry);
INSERT INTO radii (id, geometry) VALUES (
1904,
sde.st_geometry (10,10,NULL,NULL,25,50,0)
);
INSERT INTO radii (id, geometry) VALUES (
1905,
sde.st_geometry (5,15,NULL,20,10,30,0)
);
查询表以将几何值作为文本返回。
Oracle
SELECT id, sde.st_astext (geometry) description
FROM RADII;
ID DESCRIPTION
1904 | POLYGON ((35 10, 34.79475035 13.19692904, 34.18237157 16.34136460, 33.17291893 19.38167512, 31.78296760 22.26793880,
30.03534055 24.95276326, 27.995873375 27.39206376, 25.28724505 29.54578706, 22.95981421 31.37856907, 20.11958358 32.86031558,
17.11318966 33.9669632, 13.98999737 34.67954458, 10.80128944 34.98715541, 7.59942435 34.88447782, 4.43697665 34.37319780,
1.36587364 33.46171055, -1.56345762 32.16498266, -4.30291650 30.50430636, -6.80752226 28.50694993, -9.03614896 26.20570988,
-10.95220262 23.63837253, -12.52422170 20.84709348, -13.72639368 17.87770545, -14.53897892 14.77896572, -14.94863482 11.60175550,
-14.94863482 8.39824450, -14.53897892 5.22103428, -13.72639368 2.12229455, -12.52422170 -0.84709348, -10.95220262 -3.63837253,
-9.03614896 -6.20570988, -6.80752226 -8.50694993, -4.30291650 -10.50430636, -1.56345726 -12.16498266, 1.36587364 -13.46171055,
4.43697665 -14.37319781, 7.59942435 -14.88447782, 10.80128944 -14.98715541, 13.98999737 -14.67954459, 17.11318966 -13.96669633,
20.11958358 -12.86031558, 22.95981421 -11.37856908, 25.58724505 -9.54578706, 27.95873375 -7.39206377, 30.03534055 -4.95276326,
31.78296760 -2.26793880, 33.17291893 0.61832488, 34.18237157 3.65863540, 34.79475035 6.80307096, 35 10))
1905 | POLYGON (( 15 15, 14.76620556 17.14970440, 14.07575420 19.19889102, 12.96093066 21.05174215, 11.47386285 22.62162055,
9.68408441 23.83512044, 7.67528339 24.63549992, 5.54138908 24.98533414, 3.38218003 24.86826523, 1.29861845 24.28976720,
-0.61187065 23.27688998, -2.25995492 21.87699459, -3.56857176 20.15553857, -4.47653171 18.19301530, -4.94137957 16.08119018,
-4.94137957 13.91880981, -4.47653171 11.80698470, -3.56857176 9.84446143, -2.25995492 8.12300541, -0.61187065 6.72311002,
1.29861845 5.71023280, 3.38218003 5.13173477, 5.54138908 5.01466586, 7.67528339 5.36450007, 9.68408441 6.16487956,
11.47386285 7.37837945, 12.96093066 8.94825785, 14.07575420 10.80110898, 14.76620556 12.85029560, 15 15))
PostgreSQL
SELECT id, sde.st_astext (geometry) AS description
FROM radii;
id description
1904 | POLYGON ((35 10, 34.79475035 13.19692904, 34.18237157 16.34136460, 33.17291893 19.38167512, 31.78296760 22.26793880,
30.03534055 24.95276326, 27.995873375 27.39206376, 25.28724505 29.54578706, 22.95981421 31.37856907, 20.11958358 32.86031558,
17.11318966 33.9669632, 13.98999737 34.67954458, 10.80128944 34.98715541, 7.59942435 34.88447782, 4.43697665 34.37319780,
1.36587364 33.46171055, -1.56345762 32.16498266, -4.30291650 30.50430636, -6.80752226 28.50694993, -9.03614896 26.20570988,
-10.95220262 23.63837253, -12.52422170 20.84709348, -13.72639368 17.87770545, -14.53897892 14.77896572, -14.94863482 11.60175550,
-14.94863482 8.39824450, -14.53897892 5.22103428, -13.72639368 2.12229455, -12.52422170 -0.84709348, -10.95220262 -3.63837253,
-9.03614896 -6.20570988, -6.80752226 -8.50694993, -4.30291650 -10.50430636, -1.56345726 -12.16498266, 1.36587364 -13.46171055,
4.43697665 -14.37319781, 7.59942435 -14.88447782, 10.80128944 -14.98715541, 13.98999737 -14.67954459, 17.11318966 -13.96669633,
20.11958358 -12.86031558, 22.95981421 -11.37856908, 25.58724505 -9.54578706, 27.95873375 -7.39206377, 30.03534055 -4.95276326,
31.78296760 -2.26793880, 33.17291893 0.61832488, 34.18237157 3.65863540, 34.79475035 6.80307096, 35 10))
1905 | POLYGON (( 15 15, 14.76620556 17.14970440, 14.07575420 19.19889102, 12.96093066 21.05174215, 11.47386285 22.62162055,
9.68408441 23.83512044, 7.67528339 24.63549992, 5.54138908 24.98533414, 3.38218003 24.86826523, 1.29861845 24.28976720,
-0.61187065 23.27688998, -2.25995492 21.87699459, -3.56857176 20.15553857, -4.47653171 18.19301530, -4.94137957 16.08119018,
-4.94137957 13.91880981, -4.47653171 11.80698470, -3.56857176 9.84446143, -2.25995492 8.12300541, -0.61187065 6.72311002,
1.29861845 5.71023280, 3.38218003 5.13173477, 5.54138908 5.01466586, 7.67528339 5.36450007, 9.68408441 6.16487956,
11.47386285 7.37837945, 12.96093066 8.94825785, 14.07575420 10.80110898, 14.76620556 12.85029560, 15 15))
创建并查询参数椭圆
创建表 track，并将椭圆插入其中。
Oracle
CREATE TABLE track (id integer, geometry sde.st_geometry);
INSERT INTO TRACK (id, geometry) VALUES (
1907,
sde.st_geometry (0,0,NULL,NULL,10,5,0,50,0)
);
INSERT INTO TRACK (id, geometry) VALUES (
1908,
sde.st_geometry (4,19,10,20,10,5,0,40,0)
);
PostgreSQL
CREATE TABLE track (id integer, geometry sde.st_geometry);
INSERT INTO track (id, geometry) VALUES (
1907,
sde.st_geometry (0,0,NULL,NULL,10,5,0,50,0)
);
INSERT INTO track (id, geometry) VALUES (
1908,
sde.st_geometry (4,19,10,20,10,5,0,40,0)
);
查询表以将几何值作为文本返回。
Oracle
SELECT id, sde.st_astext (geometry) definition
FROM TRACK;
ID DEFINITION
1907 | POLYGON (( 10 0, 9.91790014 0.63938581, 9.67294863 1.26827292, 9.2691675 7 1.87633502, 8.71318704 2.45358776,
8.01413622 2.99055265, 7.18349350 3.47841275, 6.23489802 3.90915741, 5.18392568 4.27571382, 4.04783343 4.57206311, 2.845275 87 4.79333926,
1.59599895 4.93590892, 0.32051577 4.99743108, -0.96023026 4.97689556, -2.22520934 4.87463956, -3.45365055 4.69234211, -4.62538290 4.43299653,
-5.72116660 4.10086127, -6.72300890 3.70138998, -7.61445958 3.24114198, -8.38088105 2.72767451, -9.00968868 2.16941869,
-9.49055747 1.57554109, -9.81559157 0.95579314, -9.97945393 0.32035110, -9.97945393 -0.32035110, -9.81559157 -0.95579314,
-9.49055747 -1.57554109, -9.00968868 -2.16941870, -8.38088105 -2.72767451, -7.61445958 -3.24114198, -6.72300890 -3.70138999,
-5.72116660 -4.10086127, -4.62538290 -4.43299653, -3.45365055 -4.69234211, -2.22520934 -4.87463956, -0.96023026 -4.97689557,
0.32051577 -4.99743108, 1.59599895 -4.93590892, 2.84527587 -4.79333927, 4.04783343 -4.57206312, 5.18392568 -4.27571382,
6.23489802 -3.90915741, 7.18349350 -3.47841275, 8.01413622 -2.99055265, 8.71318704 -2.45358776, 9.26916757 -1.87633503,
9.67294863 -1.26827292, 9.91790014 -0.63938581, 10 0))
1908 | POLYGON (( 14 19, 13.87050263 19.80205640, 13.48536442 20.58333997, 12.85456026 21.32361586, 11.99442763 22.00371132,
10.92724354 22.60601224, 9.68064747 23.11491933, 8.28692561 23.51725217, 6.78217464 23.80259056, 5.20536680 23.96354437,
3.59734060 23.99594499, 1.99974306 23.89895326, 0.45395113 23.67508121, -1.00000000 23.33012702, -2.32445376 22.87302481,
-3.48510748 22.31561329, -4.45190086 21.67232913, -5.19979444 20.95983305, -5.70941817 20.19657832, -5.96757308 19.40233284,
-5.96757308 18.59766716, -5.70941817 17.80342168, -5.19979444 17.04016695, -4.45190086 16.32767087, -3.48510748 15.68438671,
-2.32445376 15.12697519, -1.00000000 14.66987298, 0.45395113 14.32491879, 1.99974306 14.10104674, 3.59734060 14.00405501,
5.20536680 14.03645563, 6.78217464 14.19740944, 8.28692561 14.48274783, 9.68064747 14.88508067, 10.92724354 15.39398776,
11.99442763 15.99628868, 12.85456026 16.67638414, 13.48536442 17.41666003, 13.87050263 18.19794360, 14 19))
PostgreSQL
SELECT id, sde.st_astext (geometry) AS definition
FROM track;
id definition
1907 | POLYGON (( 10 0, 9.91790014 0.63938581, 9.67294863 1.26827292, 9.2691675 7 1.87633502, 8.71318704 2.45358776,
8.01413622 2.99055265, 7.18349350 3.47841275, 6.23489802 3.90915741, 5.18392568 4.27571382, 4.04783343 4.57206311, 2.845275 87 4.79333926,
1.59599895 4.93590892, 0.32051577 4.99743108, -0.96023026 4.97689556, -2.22520934 4.87463956, -3.45365055 4.69234211, -4.62538290 4.43299653,
-5.72116660 4.10086127, -6.72300890 3.70138998, -7.61445958 3.24114198, -8.38088105 2.72767451, -9.00968868 2.16941869,
-9.49055747 1.57554109, -9.81559157 0.95579314, -9.97945393 0.32035110, -9.97945393 -0.32035110, -9.81559157 -0.95579314,
-9.49055747 -1.57554109, -9.00968868 -2.16941870, -8.38088105 -2.72767451, -7.61445958 -3.24114198, -6.72300890 -3.70138999,
-5.72116660 -4.10086127, -4.62538290 -4.43299653, -3.45365055 -4.69234211, -2.22520934 -4.87463956, -0.96023026 -4.97689557,
0.32051577 -4.99743108, 1.59599895 -4.93590892, 2.84527587 -4.79333927, 4.04783343 -4.57206312, 5.18392568 -4.27571382,
6.23489802 -3.90915741, 7.18349350 -3.47841275, 8.01413622 -2.99055265, 8.71318704 -2.45358776, 9.26916757 -1.87633503,
9.67294863 -1.26827292, 9.91790014 -0.63938581, 10 0))
1908 | POLYGON (( 14 19, 13.87050263 19.80205640, 13.48536442 20.58333997, 12.85456026 21.32361586, 11.99442763 22.00371132,
10.92724354 22.60601224, 9.68064747 23.11491933, 8.28692561 23.51725217, 6.78217464 23.80259056, 5.20536680 23.96354437,
3.59734060 23.99594499, 1.99974306 23.89895326, 0.45395113 23.67508121, -1.00000000 23.33012702, -2.32445376 22.87302481,
-3.48510748 22.31561329, -4.45190086 21.67232913, -5.19979444 20.95983305, -5.70941817 20.19657832, -5.96757308 19.40233284,
-5.96757308 18.59766716, -5.70941817 17.80342168, -5.19979444 17.04016695, -4.45190086 16.32767087, -3.48510748 15.68438671,
-2.32445376 15.12697519, -1.00000000 14.66987298, 0.45395113 14.32491879, 1.99974306 14.10104674, 3.59734060 14.00405501,
5.20536680 14.03645563, 6.78217464 14.19740944, 8.28692561 14.48274783, 9.68064747 14.88508067, 10.92724354 15.39398776,
11.99442763 15.99628868, 12.85456026 16.67638414, 13.48536442 17.41666003, 13.87050263 18.19794360, 14 19))
创建并查询参数楔形
创建表 pwedge，并将楔形插入其中。
Oracle
CREATE TABLE pwedge (uid integer, label varchar2(8), shape sde.st_geometry);
INSERT INTO PWEDGE (uid, label, shape) VALUES (
1,
'Wedge1',
sde.st_geometry (10,30,NULL,NULL,45,145,5,2,60,4236)
);
PostgreSQL
CREATE TABLE pwedge (uid integer, label varchar(8), shape sde.st_geometry);
INSERT INTO pwedge (uid, label, shape) VALUES (
1,
'Wedge',
sde.st_geometry(10,30,NULL,NULL,45,145,5,2,60,4236)
);
查询表以将几何值作为文本返回。
Oracle
SELECT label,
w.shape.minx,w.shape.miny,w.shape.maxx,w.shape.maxy,
w.shape.area,w.shape.len,w.shape.entity entity
FROM pwedge w;
PostgreSQL
SELECT label,
sde.st_minx(w.shape), sde.st_miny(shape),
sde.st_maxx(shape), sde.st_maxy(shape),
sde.st_area(shape), sde.st_length(shape), sde.st_geometrytype(shape)
FROM pwedge w;
9/15/2013