2

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?

2
  • It would help to consult the documentation, to see how parameters are provided to functions. SQL is a strongly typed language, so you will either need to parse the terms, change the types, and apply them appropriately, or chose a format like Well-Known Text, massage your string to meet WKT requirements, then apply that through the appropriate constructor function.
    – Vince
    Nov 26 '20 at 1:44
  • I did some benchmarking, and it turns out String_To_Array is (by far) the least performant (it was part of my answer; it is deleted now), followed by Split_Part, and for this very specific case, using the concatenated WKT string (@dr_jts answer) is even slightly faster.
    – geozelot
    Nov 27 '20 at 19:50
3

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

enter image description here

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

2
  • Yes, I had modified randomly the coordinates sample and unfortunately they ended in the ocean :) Seems you created a table with only the coordinates sample. How would you do with your process if not only with a pair of coordinates but all the rows contained in a column (currently in text format, but that could be changed)?
    – Severin
    Nov 26 '20 at 14:20
  • If you have table "coordinate_table" with the coordinates in a field named "coords", all you would need to do is exclude the WITH statement and use only the SELECT statement. If you have dozens of coordinates in one text field, then you don't have one row per point, but one row with many points. In that case it is more complicated and I would look into how you ingested the data into Postgresql. Usually with text it would be 1 row per line of text or something along those lines.
    – Daniel
    Nov 26 '20 at 19:26
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.

3
  • 1
    Good catch, probably the winner. Contrary to my intuition, according to the DBA thread I made (comment on my answer), string_to_array is actually to be considered slower than what @Daniel suggested. I'll investigate.
    – geozelot
    Nov 27 '20 at 19:32
  • Actually the coordinates provided by KoBoToolbox are weirdly ordered Y X Z M (this is why the sample coordinates are in the ocean on the map on top of the thread) therefore @geozelot method was really useful as I only had to order the coordinates this way: ST_MakePoint(coords[2], coords[1], coords[3]), 4326) The process is super quick for me, less than one second for +15K rows in the pg table
    – Severin
    Dec 2 '20 at 12:25
  • @Severin use Daniels answer instead...it may not make a difference for a table of your size, but it's always good to look for performance, and while counter-intuitively to me, using SPLIT_PART is a lot faster. You can simply change the ordinate order there, too.
    – geozelot
    Dec 2 '20 at 13:14

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.