Create point geometry in PostGIS from a X Y Z M coordinate format

Question

I would like to get a PostGIS point table from a PostGreSQL table containing OpenDataKit data where the coordinates are contained into in a column in a X Y Z M format (M being I think the value of the GPS precision in meters). E.g:

10.384643333333335 -23.214940000000002 -0.8 5.0

My first question is: what should be the column type for my coordinates_column with such coordinates format? Just text?

Otherwise I try creating the geometry this way:

INSERT INTO schema.posgistable SELECT _id,column1,column2,...,ST_MakePoint(coordinates_column) as geom FROM schema.postgresql_odk_table

as ST_MakePoint seems to be the one able to retrieve X Y Z M data (but separated by ,) but I got this error message:

ERROR:  parse error - invalid geometry

Any clue?

Answer 1

This is a fun little problem. Here I setup the coordinate you specified as a string in a table, using a WITH Statement. This avoided having to create a table with the column in order to demonstrate how to get it out again.

In your example, the string has the coordinates delimited by a space. Using the function SPLIT_PART, I separated the string into each of it's numbers. Then I cast them to the type the ST_MAKEPOINT expects (float). The point you specified is at 10 degrees by -23 degrees. which is in the south western hemisphere in the middle of the ocean.

WITH coordinate_table AS ( 
   SELECT '10.384643333333335 -23.214940000000002 -0.8 5.0'::text as coords
)
SELECT ST_MAKEPOINT( SPLIT_PART(coords, ' ', 1)::float, 
                     SPLIT_PART(coords, ' ', 2)::float,
                     SPLIT_PART(coords, ' ', 3)::float, 
                     SPLIT_PART(coords, ' ', 4)::float)
FROM coordinate_table

Using WITH Statements: https://www.postgresql.org/docs/current/queries-with.html Using SPLIT_PART: https://www.postgresql.org/docs/13/functions-string.html Using ST_MAKEPOINT: https://postgis.net/docs/ST_MakePoint.html

Answer 2

Here's a way that could be considered either hacky or slick, depending on your perspective. Since the ordinates values are handily in the sequence XYZM they can easily be converted directly to WKT, and then parsed (a ::geometry cast could be used as well - not sure if that's any more performant):

WITH data(coordinates_column) AS (VALUES 
  ('10.384643333333335 -23.214940000000002 -0.8 5.0')
)
SELECT ST_AsText( ST_GeomFromText( 'POINT ZM (' || data.coordinates_column || ')') ) FROM data;

It would be interesting to hear if this is any more or less performant.