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
1

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.