ST_Geometry

定義

ST_Geometry は、WKT 表現からジオメトリを構築するアクセサ関数です。

ArcGIS で使用する空間テーブルを作成する場合は、ST_LineString などの ST_Geometry サブタイプを指定するよりも、ST_Geometry として列を作成することをお勧めします。

構文

Oracle

ライン、ポリゴン、およびポイント

sde.st_geometry (wkt clob, srid integer)

パラメータ対応の円

sde.st_geometry (x,y,z,m,radius,numPts,srid)

パラメータ対応の楕円

sde.st_geometry (x,y,z,m,semiMajor,semiMinor,angle,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)

戻り値のタイプ

ST_Geometry

ポイント、ライン ストリング、およびポリゴン フィーチャの作成と検索

geoms というテーブルを作成し、ポイント、ライン ストリング、およびポリゴンの値を挿入します。

Oracle

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)
);

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))

7/10/2012