ST_Geometry

Definition

ST_Geometry 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 optimized points (which do not launch an extproc agent and, therefore, process the query more rapidly)

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

  Use optimized point construction when performing batch inserts of large numbers of point data.

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

• For parametric wedges

  sde.st_geometry (x, y, z, m, startangle, endangle, 
  outerradius, innerradius, 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)

• For parametric wedges

  sde.st_geometry (x, y, z, m, startangle, endangle, 
  outerradius, innerradius, numPts, srid)

Return type

ST_Geometry

Examples

Creating and querying point, line string, and polygon features

Create a table, geoms.

CREATE TABLE geoms (id integer, geometry sde.st_geometry);

Insert point, line string, and polygon values into 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)
);

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 parametric circles

Create a table, radii, and insert circles into 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 into 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))

Creating and querying parametric wedges

Create a table, pwedge, and insert a wedge into it.

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

Query the table to return the geometry values as text.

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;