名称
ST_StartPoint — Returns the first point of a LineString, CircularLineString, or NURBSCurve.
大纲
geometry ST_StartPoint(geometry geomA);
描述
Returns the first point of a LINESTRING, CIRCULARLINESTRING, or NURBSCURVE geometry as a POINT. For other geometries, returns the first point in coordinate order.
该方法实现了SQL/MM规范。 SQL-MM 3: 7.1.3
该函数支持 3d 并且不会丢失 z-index。
此方法支持圆形字符串和曲线。
增强：3.2.0 返回所有几何图形的点。 如果输入不是 LineString，则先前的行为将返回 NULL。
更改：2.0.0 不再适用于单个几何体 MultiLineStrings。 在旧版本的 PostGIS 中，单行 MultiLineString 可以与此函数很好地配合并返回起点。 在 2.0.0 中，它像任何其他 MultiLineString 一样只返回 NULL。 旧的行为是一个未记录的功能，但是那些假设将数据存储为 LINESTRING 的人可能会在 2.0.0 中遇到这些返回 NULL 的情况。
示例
LineString起点
SELECT ST_StartPoint('LINESTRING(0 1,0 2)'::geometry);
POINT(0 1)
Start point of a Point is the Point itself
SELECT ST_StartPoint('POINT(0 1)'::geometry);
POINT(0 1)
3D LineString起点
SELECT ST_StartPoint('LINESTRING(0 1 1,0 2 2)'::geometry);
POINT(0 1 1)
CircularString起点
SELECT ST_StartPoint('CIRCULARSTRING(5 2,-3 2,-2 1,-4 2,6 3)'::geometry);
POINT(5 2)
Start point of a NURBSCurve
SELECT ST_StartPoint('NURBSCURVE(2, (0 0, 1 1, 2 0))'::geometry);
POINT(0 0)
Build edge node references from the start and end points of a LineString table. The example uses exact endpoint equality, so linework with nearly coincident endpoints should be snapped or otherwise cleaned first.
WITH roads(road_id, geom) AS (
VALUES
(1, 'LINESTRING(0 0, 1 0)'::geometry),
(2, 'LINESTRING(1 0, 1 1)'::geometry),
(3, 'LINESTRING(1 0, 2 0)'::geometry)
),
endpoints AS (
SELECT road_id, 'from' AS end_name, ST_StartPoint(geom) AS geom,
ST_AsEWKB(ST_StartPoint(geom)) AS endpoint_key
FROM roads
UNION ALL
SELECT road_id, 'to' AS end_name, ST_EndPoint(geom) AS geom,
ST_AsEWKB(ST_EndPoint(geom)) AS endpoint_key
FROM roads
),
nodes AS (
SELECT dense_rank() OVER (ORDER BY ST_X(geom), ST_Y(geom), endpoint_key) AS node_id,
endpoint_key
FROM (
SELECT DISTINCT ON (endpoint_key) endpoint_key, geom
FROM endpoints
ORDER BY endpoint_key
) AS distinct_endpoints
)
SELECT e.road_id,
max(n.node_id) FILTER (WHERE e.end_name = 'from') AS from_node,
max(n.node_id) FILTER (WHERE e.end_name = 'to') AS to_node
FROM endpoints AS e
JOIN nodes AS n USING (endpoint_key)
GROUP BY e.road_id
ORDER BY e.road_id;
road_id | from_node | to_node
---------+-----------+---------
1 | 1 | 2
2 | 2 | 3
3 | 2 | 4