1

I want to compress a bunch of sequentially recorded positions with timestamps into a linestring with XYZ an M dimension (LineStringXYZM). Each set of points is represented by a constant part (ship data from AIS like MMSI, length, width, type) and a dynamic part (x y z timestamp). I want to compress the repeated static info by creating a trajectory like LineString and the additional static attributes. The timestamps have a granularity of milliseconds (YYYY-MM-DD.HH:MM:SS.FFF).

I want to know of which datatype M is (int32, int64 or float32, float32) in the Esri-Shapefile and the PostGIS context to feed in that info into the geo-object.

Example operations posgresql:

INTEGER

 SELECT mmsi, lon, lat, 
        extract(epoch FROM (dt||' '||tm)::timestamp(3))*1000 AS ts
 FROM   ais_pos 
 WHERE  year = 2018 AND month = 1 
 ORDER  BY mmsi, ts LIMIT 4;

   mmsi    |   lon    |    lat    |      ts       
-----------+----------+-----------+---------------
 100000000 | 8.173233 | 53.573867 | 1514792874239
 100000000 | 8.173200 | 53.574033 | 1514792882455
 100000000 | 8.173183 | 53.574067 | 1514792884476
 100000000 | 8.173133 | 53.574283 | 1514792895864

FLOAT

 SELECT mmsi, lon, lat, 
        extract(epoch FROM (dt||' '||tm)::timestamp(3)) AS ts
 FROM   ais_pos 
 WHERE  year = 2018 AND month = 1 
 ORDER  BY mmsi, ts LIMIT 4;

   mmsi    |   lon    |    lat    |       ts       
-----------+----------+-----------+----------------
 100000000 | 8.173233 | 53.573867 | 1514792874.239
 100000000 | 8.173200 | 53.574033 | 1514792882.455
 100000000 | 8.173183 | 53.574067 | 1514792884.476
 100000000 | 8.173133 | 53.574283 | 1514792895.864

Which range has the measured value M (ordinal type/ float type) to prevent overflow errors (..see example).

 #!/usr/bin/perl 
 # -- test overflow

 # sample from the sql query
 my $x = 1514792874239;

 # max unsigned 32 bit int     
 my $y = 2**32-1; 

 # make string 
 my $s = $x>$y ? 'overflow' : 'in.range'; 

 # show result
 print "$x  $y $s \n"

 ! 1514792874239  4294967295 overflow

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.