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.

6
  • There is no path data-type. It's LINESTRING. just an fyi. ;) Dec 9, 2016 at 22:27
  • 1
    This is what I'm referring to. Are we talking about the same thing?
    – jarch
    Dec 9, 2016 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. Dec 9, 2016 at 22:30
  • I'm new to PostGIS! I'm just going off of what I know in my postgres server.
    – jarch
    Dec 9, 2016 at 22:32
  • 1
    If you're using path then you're not using PostGIS. At least there. Dec 9, 2016 at 22:33

2 Answers 2

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.

1

You can use ST_StartPoint and ST_EndPoint

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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