Definition
ST_Y takes an ST_Point as an input parameter and returns its y-coordinate. In SQLite, ST_Y can also update the y-coordinate of an ST_Point.
Syntax
Oracle and PostgreSQL
sde.st_y (point1 sde.st_point)
SQLite
double st_y (point1 geometryblob) geometry st_y (input_shape geometryblob, new_Yvalue double)
Return type
Double precision
The ST_Y function can be used with SQLite to update the y-coordinate of a point. In that case, a geometryblob is returned.
Example
The y_test table is created with two columns: the gid column, which uniquely identifies the row, and the pt1 point column.
The INSERT statements insert two rows. One is a point without a z-coordinate or measure. The other has both a z-coordinate and a measure.
The SELECT query uses the ST_Y function to return the y-coordinate of each point.
Oracle
CREATE TABLE y_test (
gid integer unique,
pt1 sde.st_point
);
INSERT INTO Y_TEST VALUES (
1,
sde.st_pointfromtext ('point (10.02 20.02)', 4326)
);
INSERT INTO Y_TEST VALUES (
2,
sde.st_pointfromtext ('point zm(10.1 20.01 5.0 7.0)', 4326)
);
SELECT gid, sde.st_y (pt1) "The Y coordinate"
FROM Y_TEST;
GID The Y coordinate
1 20.02
2 20.01
PostgreSQL
CREATE TABLE y_test (
gid integer unique,
pt1 sde.st_point
);
INSERT INTO y_test VALUES (
1,
sde.st_point ('point (10.02 20.02)', 4326)
);
INSERT INTO y_test VALUES (
2,
sde.st_point ('point zm(10.1 20.01 5.0 7.0)', 4326)
);
SELECT gid, sde.st_y (pt1)
AS "The Y coordinate"
FROM y_test;
gid The Y coordinate
1 20.02
2 20.01
SQLite
CREATE TABLE y_test (gid integer);
SELECT AddGeometryColumn(
NULL,
'y_test',
'pt1',
4326,
'pointzm',
'xyzm',
'null'
);
INSERT INTO y_test VALUES (
1,
st_point ('point (10.02 20.02)', 4326)
);
INSERT INTO y_test VALUES (
2,
st_point ('point zm(10.1 20.01 5.0 7.0)', 4326)
);
SELECT gid, st_y (pt1)
AS "The Y coordinate"
FROM y_test;
gid The Y coordinate
1 20.02
2 20.01
The ST_Y function can also be used to update the coordinate value of an existing point. In this example, ST_Y is used to update the y-coordinate value of the second point in y_test.
UPDATE y_test
SET pt1=st_y(
(SELECT pt1 FROM y_test WHERE gid=2),
20.1
)
WHERE gid=2;