Work with coordinates with altitude and accuracy in PostgreSQL + Postgis

Question

In PostgreSQL, I have a table with three columns, one for points, one for lines and one for polygons, obtained from Kobotoolbox. The data is written like this:

• Point: Latitude Longitude Altitude Accuracy
• Line: Latitude Longitude Altitude Accuracy; Latitude Longitude Altitude Accuracy
• Polygon: Latitude Longitude Altitude Accuracy; Latitude Longitude Altitude Accuracy; Latitude Longitude Altitude Accuracy

How can I transform them into points, lines and polygons that I can use later in other operations?

A sample of the data:

CREATE TABLE data_test (point text,line text, polygon text);
insert into data_test VALUES 
('12.126441 -86.298396 0 0', '12.125673 -86.297157 0 0;12.125367 -86.299428 0 0;12.125839 -86.299374 0 0;12.126174 -86.299063 0 0;12.126877 -86.298322 0 0;12.126867 -86.297936 0 0;12.126835 -86.29727 0 0;12.125673 -86.297157 0 0', '12.12614 -86.299082 0 0;12.126392 -86.29885 0 0;12.126544 -86.298705 0 0;12.126429 -86.29842 0 0;12.12645 -86.298276 0 0;12.126471 -86.298125 0 0;12.126492 -86.298007 0 0'), 
('13.665354 -89.278524 0 0', '13.666044 -89.278656 0 0;13.666023 -89.278065 0 0;13.666138 -89.278049 0 0;13.666326 -89.278097 0 0;13.666264 -89.278282 0 0;13.666364 -89.278314 0 0;13.666435 -89.278205 0 0;13.666607 -89.278124 0 0;13.666904 -89.278028 0 0;13.66717 -89.27806 0 0;13.66717 -89.278226 0 0;13.666951 -89.278248 0 0;13.666555 -89.278656 0 0;13.666044 -89.278656 0 0', '13.665354 -89.278524 0 0;13.665358 -89.278663 0 0;13.665373 -89.278902 0 0;13.665368 -89.279106 0 0;13.665368 -89.279299 0 0;13.665769 -89.279299 0 0;13.666113 -89.279289 0 0;13.666129 -89.279047 0 0;13.666108 -89.278779 0 0;13.666103 -89.278618 0 0'), 
('-31.635851 -60.774175 0 0', '-31.635975 -60.774324 0 0;-31.63602 -60.773981 0 0;-31.636074 -60.774321 0 0;-31.635975 -60.774324 0 0', '-31.635863 -60.774313 0 0;-31.636091 -60.774325 0 0'), 
('-24.840938 -65.435611 1202 0', '-24.833342 -65.437115 0 0;-24.832563 -65.435223 0 0;-24.832398 -65.433348 0 0;-24.833268 -65.432525 0 0;-24.834146 -65.432114 0 0;-24.835607 -65.432157 0 0;-24.837437 -65.435485 0 0;-24.838197 -65.436709 0 0;-24.834891 -65.438848 0 0;-24.833411 -65.438289 0 0;-24.833342 -65.437115 0 0', '-24.841016 -65.435613 0 0;-24.840587 -65.436 0 0;-24.840062 -65.435142 0 0;-24.839463 -65.435598 0 0;-24.838046 -65.436552 0 0;-24.837121 -65.436216 0 0'), 
('13.664852 -89.278977 904.1 13.1899244', '13.677341 -89.283306 0 0;13.677091 -89.279078 0 0;13.675131 -89.27912 0 0;13.675069 -89.280215 0 0;13.674485 -89.280279 0 0;13.674698 -89.283357 0 0;13.677341 -89.283306 0 0', '13.664924 -89.278894 0 12.244;13.66493 -89.279219 0 0;13.665451 -89.279219 0 0;13.665446 -89.279326 0 0;13.666176 -89.279312 0 0;13.666171 -89.282736 0 0;13.672801 -89.282456 0 0;13.672885 -89.283684 0 0;13.676304 -89.283392 0 0;13.676179 -89.282139 0 0'), 
('1.331093 -75.974085 300 3', '1.644977 -76.231295 0 0;1.638842 -76.057188 0 0;1.515293 -75.961053 0 0;1.485091 -76.079262 0 0;1.380753 -76.189193 0 0;1.290141 -76.252404 0 0;1.229731 -76.312859 0 0;1.259936 -76.463997 0 0;1.408211 -76.48044 0 0;1.493328 -76.395238 0 0;1.567459 -76.321034 0 0;1.644977 -76.231295 0 0', '1.331994 -75.972156 0 0;1.407503 -75.990544 0 0'), 
('14.595962 -90.530973 0 0', '14.596218 -90.530673 0 0;14.596291 -90.531027 0 0;14.595829 -90.53121 0 0;14.595699 -90.530839 0 0;14.596218 -90.530673 0 0', '14.599893 -90.535245 0 4107.503292443888;14.600378 -90.535292 0 0;14.60045 -90.53526 0 0;14.600414 -90.535061 0 0;14.60003 -90.533814 0 0;14.600336 -90.533334 0 0;14.600352 -90.53454 0 0;14.598493 -90.530363 0 0;14.599376 -90.532509 0 0;14.596479 -90.530738 0 0'), 
('13.664776 -89.278983 0 0', '13.665024 -89.27906 0 0;13.66492 -89.278969 0 0;13.664933 -89.27887 0 0;13.665006 -89.278946 0 0;13.665024 -89.27906 0 0', '13.664933 -89.279011 0 14.404;13.664933 -89.279011 0 14.404'), 
('1.328949 -75.9738 360 13.166', '1.328949 -75.9738 0 13.166;1.328871 -75.973761 0 0;1.328812 -75.973638 0 0;1.328726 -75.97374 0 0;1.328721 -75.973826 0 0;1.328949 -75.9738 0 13.166', '1.328949 -75.9738 0 13.166;1.328955 -75.973801 0 12.265;1.328642 -75.973665 0 0;1.328653 -75.973778 0 0;1.328706 -75.973826 0 0;1.328803 -75.973858 0 0;1.328915 -75.973761 360 0'), 
('13.780375 -89.117772 0 0', '13.780312 -89.118426 0 0;13.780208 -89.117739 0 0;13.779521 -89.117859 0 0;13.779635 -89.118496 0 0;13.780312 -89.118426 0 0', '13.780079 -89.11995 0 0;13.779855 -89.119965 0 0;13.779594 -89.118274 0 0'), 
('14.595925 -90.53099 0 0', '14.595711 -90.531217 0 0;14.595564 -90.530912 0 0;14.596266 -90.530738 0 0;14.59628 -90.531038 0 0;14.595711 -90.531217 0 0', '14.596188 -90.530608 0 0;14.596318 -90.530668 0 0;14.596365 -90.530759 0 0;14.596391 -90.531006 0 0;14.596303 -90.531059 0 0;14.59611 -90.531016 0 0;14.596001 -90.530947 0 0;14.596017 -90.530818 0 0'), 
('13.664903 -89.279061 904 1', '13.667471 -89.284878 0 0;13.667815 -89.284812 0 0;13.667655 -89.281555 0 0;13.667315 -89.281592 0 0;13.667471 -89.284878 0 0', '13.664972 -89.279136 0 0;13.665233 -89.279351 0 0;13.666119 -89.279307 0 0;13.666233 -89.281576 0 0;13.667307 -89.281563 0 0');`

I looked in the Postgis documentation and I can't figure out how to operate with this data. It is not necessary to keep the accuracy of each node, but I would like to keep the altitude. Any help please?

Answer 1

You can use st_geomFromText after replacing ; with ,, and after specifying the geometry type. Since the data is expressed as lat/long, you must also swap the coordinates.

select st_AsText(ST_FlipCoordinates(st_geomfromText('POINT(12.126441 -86.298396 0 0)')));

select st_AsText(ST_FlipCoordinates(st_geomfromText('POLYGON((' || replace('12.125673 -86.297157 0 0;12.125367 -86.299428 0 0;12.125839 -86.299374 0 0;12.126174 -86.299063 0 0;12.126877 -86.298322 0 0;12.126867 -86.297936 0 0;12.126835 -86.29727 0 0;12.125673 -86.297157 0 0))',';',','))));

Or directly from a table:

SELECT ST_FlipCoordinates(ST_GeomFromText('POINT(' || point_txt_column || ')')),
       ST_FlipCoordinates(ST_GeomFromText('LINESTRING(' || line_txt_column || ')')),
       ST_FlipCoordinates(ST_GeomFromText('POLYGON((' || replace(polygon_txt_column,';',',') || '))'))
FROM myTable;

PS: from the sample data, it seems you have swapped the line and polygon data, as the last point of a polygon must be the same as its first point.