Extract first and last element of path data type in Postgres?

Question

Our postgres database has tables that have a "path" data type consisting of a list of points that make up a line. Is there any way in postGIS or postgres to extract the first and last coordinate pairs from each entry? The purpose is to determine the cardinal direction of each line using ST_Azimuth, but that function requires the coordinates of the first and last point.

I've tried every way I can think of, but the path data type doesn't act like a string or an array, so it's difficult to pull out items from it.

Answer 1

I'm not sure there's any direct way to extract points from the path. You can see a list of functions for the path datatype with:

SELECT proname 
FROM pg_proc, pg_type
WHERE typname = 'path' AND pg_type.oid = ANY(proargtypes);

This list does show a geometry function that converts between the PostgreSQL path type and the PostGIS LineString type. I don't see this function in the PostGIS docs anywhere, but it is apparently available since version 2.1. With this function, you can call PostGIS functions on your path data:

WITH data AS (VALUES (path '[(0,0),(1,1),(2,0)]'), (path '[(3,3),(2,7)]')) 
SELECT ST_Azimuth(
    ST_StartPoint(geometry(column1)), 
    ST_EndPoint(column1::geometry))
FROM data;

Since the geometry function is a casting function, you can use either the geometry(column1) or column1::geometry syntax.

You might consider storing your geometry in the PostGIS geometry type, which has a lot more functionality than path type at the expense of a 24-byte overhead per geometry.

Answer 2

You can use ST_StartPoint and ST_EndPoint

SELECT ST_StartPoint(geom), ST_EndPoint(geom)
FROM tbl;