An uniform way to get the first point from geometries of different type
Published On May 15, 2016
Is it difficult to extract the first point from a postgis geometry?
Have you ever try to get if from geometry of different types? The standard approach will be to use the most suitable postgis function for every geometry type:
Geomtry Type | How to get first point |
POINT | the point itself |
MULTIPOINT | the first part of the geometry |
LINESTRING | use ST_StartingPoint |
MULTILINESTRING | get the first part and then use ST_StartingPoint |
POLYGON | use ST_DumpPoints and filter the first |
MULTIPOLYGON | use ST_DumpPoints and filter the first |
GEOMETRYCOLLECTION | use ST_DumpPoints and filter the first |
But really? In my opinion putting everything together in a single query will be extremely cumbersome.
So after some time spent to find an elegant approach to the problem I gave up, and I build an ugly but effective query:
DROP FUNCTION IF EXISTS get_first_point(geometry);
CREATE OR REPLACE FUNCTION get_first_point(geom geometry)
RETURNS geometry AS
$BODY$
DECLARE
retval geometry;
BEGIN
select ST_PointFromText('POINT(' || replace(
reverse(
split_part(
reverse(
split_part(
split_part(
left(
st_asewkt(geom),100
),',',1
),';',2
)
), '(',1 )
) ,')',''
)||')',ST_SRID(geom)
) into retval;
return retval;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE COST 1;
This works quite well in my reference environment.
Have you found a better approach? I’m open to your suggestions.
Tags: postgis postgres sql