ST_Geometry
Definition
ST_Geometry is an accessor function that constructs a geometry from a well-known text representation.
When creating spatial tables that will be used with ArcGIS, it is best to create the column as ST_Geometry rather than specifying an ST_Geometry subtype, such as ST_LineString.
Syntax
Oracle
For line strings, polygons, and points
sde.st_geometry (wkt clob, srid integer)
For parametric circles
sde.st_geometry (x,y,z,m,radius,numPts,srid)
For parametric ellipses
sde.st_geometry (x,y,z,m,semiMajor,semiMinor,angle,numPts,srid)
PostgreSQL
For line strings, polygons, and points
sde.st_geometry (wkt, srid integer) sde.st_geometry (esri_shape bytea, srid integer)
For parametric circles
sde.st_geometry (x,y,z,m,radius,numPts,srid)
For parametric ellipses
sde.st_geometry (x,y,z,m,semiMajor,semiMinor,angle,numPts,srid)
Return type
ST_Geometry
Examples
Creating and querying point, line string, and polygon features.
Create a table, geoms, and insert point, line string, and polygon values into it.
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)
);
Select the geometry from the geoms table.
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))
Creating and querying parameteric circles
Create a table, radii, and insert circles to it.
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) );
Query the table to return the geometry values as text.
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))
Creating and querying parametric ellipses
Create a table, track, and insert ellipses to it.
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) );
Query the table to return the geometry values as text.
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))
2/28/2012