An uniform way to get the first point from geometries of different type
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|
|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:
This works quite well in my reference environment.
Have you found a better approach? I’m open to your suggestions.
Tags: postgis postgres sql