Regarding storing Lat / Lng coordinates in Postgresql (column type)

Question

I am relatively new to postgresql, so I appeal to the more experienced ones than me.

I save the coordinates in the postgresql database.

They look like this: 35.21076593772987,11.22855348629825 35.210780222605616,11.22826420209139 35.210777635062875,11.228241328291957 35.210766843596794,11.228219799676775 35.210765045075604,11.228213072050166 35.21076234732945,11.228200962345223 35.21076324691649,11.228186161764323 35.21077314123606,11.228083902231146 35.210863083636866,11.227228492401766

They can have a length in the range of 800 to 7000 .

They always include:

• Numbers ( 0-9)
• Spaces ( )
• Punctuation and commas ( . ,)

But they may also include:

• Vertical Columns ( |)

Now I store them as TEXT, but as far as I know, TEXT is stored externally, which affects performance. Would you recommend switching to a different type of column? If so, which one?

Many thanks.

Answer 1

PostGIS ?

, , - PostGIS, geography.

SELECT ST_GeogFromText('POINT(35.21076593772987 11.22855348629825)');

geography, , . , point; , , PostGIS .

geography , , , , "" ..; , ST_Distance_Spheroid, .

PostGIS?

PostGIS , point:

postgres=> SELECT ARRAY[
     point('35.21076593772987','11.22855348629825'), 
     point('35.210780222605616','11.22826420209139'), 
     point('35.210777635062875','11.228241328291957') 
];
                                                       array                                                        
--------------------------------------------------------------------------------------------------------------------
 {"(35.2107659377299,11.2285534862982)","(35.2107802226056,11.2282642020914)","(35.2107776350629,11.228241328292)"}
(1 row)

... , - path polygon .

, , text, - .

:

CREATE TABLE points_text AS SELECT '35.21076593772987,11.22855348629825 35.210780222605616,11.22826420209139 35.210777635062875,11.228241328291957 35.210766843596794,11.228219799676775 35.210765045075604,11.228213072050166 35.21076234732945,11.228200962345223 35.21076324691649,11.228186161764323 35.21077314123606,11.228083902231146 35.210863083636866,11.227228492401766'::text AS p

postgres=> SELECT pg_column_size(points_text.p) FROM points_text;
 pg_column_size 
----------------
            339
(1 row)

CREATE TABLE points_array AS
SELECT array_agg(point(px)) AS p from points_text, LATERAL regexp_split_to_table(p, ' ') split(px);

postgres=> SELECT pg_column_size(p) FROM points_array;
 pg_column_size 
----------------
            168
(1 row)

path , , , :

postgres=> SELECT pg_column_size(path('35.21076593772987,11.22855348629825 35.210780222605616,11.22826420209139 35.210777635062875,11.228241328291957 35.210766843596794,11.228219799676775 35.210765045075604,11.228213072050166 35.21076234732945,11.228200962345223 35.21076324691649,11.228186161764323 35.21077314123606,11.228083902231146 35.210863083636866,11.227228492401766'));
 pg_column_size 
----------------
             96
(1 row)

, polygon.

...

, , . , , " , - ". PostGIS , .

. , text.

, . , , , .

Answer 2

(TEXT, VARCHAR, CHAR) . , , ( TOAST ).

:

• ( 8 )

• , , . , - - . , , , , . ( , ).

TOAST . : - / .

TOAST-ing , ( - 2kb), , , , , 7000 (, , , ).

, , ALTER TABLE... SET STORAGE.

, , . , , :

• , - , , . , , .

  , , , .

• , PostGIS, PostgreSQL, . , , , , .

Answer 3

, . , . , , , ( , ).

(53 ~ 9 10 ^ 15 - LSD ) . . , ( ), .

"" ( GPS- , , , ) , :

unique ID  |  trajectory ID  |     latitude      |      longitude
   1              1            11.2285534862982     35.2107802226056
   2              1            11.2282642020913     35.2107776350628
   3              1            11.2282413282919     35.2107668435967
   4              1            11.2282197996767     35.2107650450756
   5              1            11.2282130720501     35.2107623473294
   6              1            11.2282009623452     35.2107632469164
   7              1            11.2281861617643     35.2107731412360
   8              1            11.2280839022311     35.2108630836368

Converting from text to MUCH string is slower than you think - it takes a lot of operations. If you end up using data as numbers, I highly recommend storing them as numbers ...