4

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.

  • There is no path data-type. It's LINESTRING. just an fyi. ;) – Evan Carroll Dec 9 '16 at 22:27
  • 1
    This is what I'm referring to. Are we talking about the same thing? – jarch Dec 9 '16 at 22:29
  • 2
    No. that has nothing to do with PostGIS, and it's terrifying that you're using that. That's the native PostgeSQL path type. – Evan Carroll Dec 9 '16 at 22:30
  • I'm new to PostGIS! I'm just going off of what I know in my postgres server. – jarch Dec 9 '16 at 22:32
  • 1
    If you're using path then you're not using PostGIS. At least there. – Evan Carroll Dec 9 '16 at 22:33
4

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.

| improve this answer | |
1

You can use ST_StartPoint and ST_EndPoint

SELECT ST_StartPoint(geom), ST_EndPoint(geom)
FROM tbl;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.