3

How can I make two lines from points, considering time intervals, i.e. from 04:00:00 to 12:00:00 and 12:00:01 to 23:59:59?

"POINT(-46.583483 -23.463208)";"04:49:10"
"POINT(-46.583483 -23.463208)";"04:49:13"
"POINT(-46.57166 -23.50644)";"14:58:28"
"POINT(-46.57166 -23.50644)";"14:58:31"
"POINT(-46.579627 -23.450293)";"04:39:56"
"POINT(-46.579627 -23.450293)";"04:39:54"
"POINT(-46.567625 -23.517135)";"05:18:44"
"POINT(-46.567625 -23.517135)";"05:18:41"
"POINT(-46.582715 -23.461313)";"15:41:42"
"POINT(-46.582715 -23.461313)";"15:41:45"
1
  • Do you mean ordered by the 2nd column by time and split into two groups before and after 12:00 Commented Nov 25, 2015 at 9:20

2 Answers 2

1

Try this:

//TABLE

-- ----------------------------
-- Table structure for teste_p
-- ----------------------------
DROP TABLE IF EXISTS "teste_p";
CREATE TABLE "teste_p" (
"id" int4 DEFAULT nextval('teste_p_id_seq'::regclass) NOT NULL,
"geom" "public"."geometry",
"time_2" time(6)
)
WITH (OIDS=FALSE)

;

-- ----------------------------
-- Records of teste_p
-- ----------------------------
INSERT INTO "teste_p" VALUES ('1', '0101000020E6100000D0622992AF4A47C0DE3EABCC947637C0', '04:49:10');
INSERT INTO "teste_p" VALUES ('2', '0101000020E6100000D0622992AF4A47C0DE3EABCC947637C0', '04:49:13');
INSERT INTO "teste_p" VALUES ('3', '0101000020E61000003737A6272C4947C0E162450DA68137C0', '14:58:28');
INSERT INTO "teste_p" VALUES ('4', '0101000020E61000003737A6272C4947C0E162450DA68137C0', '14:58:31');
INSERT INTO "teste_p" VALUES ('5', '0101000020E61000007670B037314A47C0239EEC66467337C0', '04:39:56');
INSERT INTO "teste_p" VALUES ('6', '0101000020E61000007670B037314A47C0239EEC66467337C0', '04:39:54');
INSERT INTO "teste_p" VALUES ('7', '0101000020E61000002DB29DEFA74847C0F19D98F5628437C0', '05:18:44');
INSERT INTO "teste_p" VALUES ('8', '0101000020E61000002DB29DEFA74847C0F19D98F5628437C0', '05:18:41');
INSERT INTO "teste_p" VALUES ('9', '0101000020E6100000BFF1B567964A47C03B38D89B187637C0', '15:41:42');
INSERT INTO "teste_p" VALUES ('10', '0101000020E6100000BFF1B567964A47C03B38D89B187637C0', '15:41:45');

//QUERY

SELECT
st_makeline(teste_p.geom)
FROM
(SELECT * FROM sema.teste_p ORDER BY sema.teste_p.time_2) teste_p
WHERE
teste_p.time_2 > '04:00:00' AND teste_p.time_2 < '12:00:00'
UNION ALL
(SELECT
st_makeline(teste_p.geom)
FROM
(SELECT * FROM sema.teste_p ORDER BY sema.teste_p.time_2) teste_p
WHERE
teste_p.time_2 > '12:00:00' AND teste_p.time_2 <= '23:59:59')
2
  • 1
    I think you need to order the points according to time. Otherwise, the polyline creation can be a bit random. Adding "ORDER BY time_2" will do. Commented Nov 26, 2015 at 14:42
  • You were right. I redid the query.
    – phsaires
    Commented Nov 26, 2015 at 17:09
0

Here is a small standard boiler plate perl script which I often use for fast an dirty data imports (little bit simplified). I assume you have you way points in the file data in the same directory.

  1. It reads and parses the way points in a dictionary,
  2. sorts the way points by the time and
  3. splits the track if a cut_off value occours
  4. writes the sql code

The Script gives you:

  1. The sorted hash
04:39:54 = -46.579627 -23.450293
04:39:56 = -46.579627 -23.450293
04:49:10 = -46.583483 -23.463208
04:49:13 = -46.583483 -23.463208
05:18:41 = -46.567625 -23.517135
05:18:44 = -46.567625 -23.517135
14:58:28 = -46.57166 -23.50644
14:58:31 = -46.57166 -23.50644
15:41:42 = -46.582715 -23.461313
15:41:45 = -46.582715 -23.461313
  1. The time sorted lines
04:39:54-05:18:44 = -46.579627 -23.450293,-46.579627 -23.450293,-46.583483 -23.463208,-46.583483 -23.463208,-46.567625 -23.517135,-46.567625 -23.517135
14:58:28-15:41:45 = -46.57166 -23.50644,-46.57166 -23.50644,-46.582715 -23.461313,-46.582715 -23.461313
  1. The SQL conform output (straight forward, not tested)
CREATE TABLE wp (id SERIAL PRIMARY KEY, tm_from TIME, tm_to TIME);
SELECT AddGeometryColumn ('public','wp','geom',4326,'LINESTING',2);
INSERT INTO wp (id, tm_from, tm_to, geom) VALUES
(1, '04:39:54', '05:18:44', 
ST_GeomFromText('LINESTING(-46.579627 -23.450293,-46.579627 -23.450293,-46.583483 -23.463208,-46.583483 -23.463208,-46.567625 -23.517135,-46.567625 -23.517135)',EPSG)),
(2, '14:58:28', '15:41:45', 
ST_GeomFromText('LINESTING(-46.57166 -23.50644,-46.57166 -23.50644,-46.582715 -23.461313,-46.582715 -23.461313)',EPSG));

Here is the code:

#!/usr/bin/perl -w
# ---------------------------------
# Collect way points into a line
# ---------------------------------
# Settings
# ----------------------------------
use strict;
use constant TEST_LINE => '"POINT(-46.583483 -23.463208)";"04:49:10"';
use constant FILE      => './data'; # File with way points
use constant DEBUG     => 0;        # Show debung messages
use constant CUT_OFF   => 3600;     # CUT_OFF Value in seconds for a point group
use constant EPSG      => 4326;     # The spatial reference ID

# ----------------------------------
# 1. Read the file into a hash with the
# times as key (hopfully no duplicates
# timestamps here)
# ----------------------------------
my %points; # Dictionary for the time keyed points

# Open the data file
open(FH, '<', FILE) || die "Cannot open FILE!\n";
# Iterate through the lines
while (my $line = <FH>) {

  next if $line =~ /^$/;     # Skip empty lines
  next if $line =~ /^\s+$/;  # Skip lines with whitespace

  # parse the point an time for each line
  my ($pt, $tm) = &parsePoint($line);

  # store it in the dictionary
  $points{$tm} = $pt;
}
close(FH);

print "\n1. The sorted hash\n";
&listSortHash(\%points);

# ----------------------------------
# 2. Group the data
# ----------------------------------
my $keyFirst  = ''; # The time key of the first wp in the group
my $keyLast   = ''; # The time key of the last wp in the group
my $tmLast    = -1; # The time of the wp before
my $tmCurrent = -1; # The time of the current wp
my %lines;          # Dictionary where collect our points;
my @coords = ();    # Coordinate entry for the linestring

# Iterate over the way points
for my $time (sort keys %points) {

    # Get the point for the time
    my $point = $points{$time};

    # get the time params and calc the second
    my ($hr, $min, $sec) = split(/:/, $time);
    $tmCurrent = $hr *3600 + $min * 60 + $sec;

    # Init the time marks for the first wp
    if ( $tmLast == -1 ) {
       $keyFirst = $time;
       $keyLast  = $time;
       $tmLast   = $tmCurrent;
    }

    # The time gap
    my $deltaTime = $tmCurrent - $tmLast;
    print "DT: $deltaTime TC $tmCurrent TL $tmLast  \n"
      if DEBUG;

    # If the gap between two way points is to large
    if ( $deltaTime > CUT_OFF) {

      # Build the from to key as $keyFirst.'-'.$keyLast;
      # Store the data in the dictionary as a
      # comma seperated string
      $lines{$keyFirst.'-'.$keyLast} = join(',', @coords);

      # Clear the coordinate stack
      @coords = ($point);

      # set the frist time key to current
      $keyFirst =  $time;
      # Clear the prevoiuse time marks
      # ..we have a new line string
      $keyLast = ''; $tmLast = $tmCurrent;
    }
    # If we are in the time track
    else {
      # store the data at the point stack
      push( @coords, $point);

      # and update the prevoiuse time marks
      $keyLast = $time; $tmLast = $tmCurrent;
    }
}
# Flush the last line
$lines{$keyFirst.'-'.$keyLast} = join(',', @coords) if $#coords>0;

# Print out the stuff
print "\n2. The time sorted lines\n";
&listSortHash(\%lines);


# ----------------------------------
# 3. Print it as a set of POSTGIS
# conform linestrings
# ----------------------------------
my $cnt = 0;
print "\n3. The SQL conform output\n\n";

# Create table statement
print "CREATE TABLE wp (id SERIAL PRIMARY KEY, tm_from TIME, tm_to TIME);\n";

# Create the geometry column
print "SELECT AddGeometryColumn ('public','wp','geom',4326,'LINESTRING',2);\n";

# An the data dump
print "INSERT INTO wp (id, tm_from, tm_to, geom) VALUES\n";
for my $time (sort keys %lines) {
  $cnt++;
  my $coord = $lines{$time};
  my ($from, $to) = split(/\-/,$time);
  print ",\n" if $cnt > 1;
  print "($cnt, '$from', '$to', \nST_GeomFromText('LINESTRING($coord)',EPSG))";
}
print";\n";

# ----------------------------------
# Parsing something like that
# "POINT(-46.583483 -23.463208)";"04:49:10"
# &parsePoint(TEST_LINE);
# ----------------------------------
sub parsePoint($) {
  my $line = shift;
  # remove linefeed
  chomp($line);
  print "PP 1: ", $line, "\n" if DEBUG;

  # remove " POINT ( )
  $line =~ s/\"//g;
  $line =~ s/POINT//g;
  $line =~ s/\(//g;
  $line =~ s/\)//g;
  print "PP 2: ", $line, "\n" if DEBUG;

  # split points an time
  my ($point, $time) = split(/;/, $line);
  print "pp 3: point:", $point,  "\n" if DEBUG;
  print "pp 4: time: ", $time,   "\n" if DEBUG;

  # return the result
  return ($point, $time);
}

# ---------------------------------
# List the hash prints you hash
# ---------------------------------
sub listSortHash($) {
  my %dict = %{$_[0]};
  for my $key (sort keys %dict) {
    my $value = $dict{$key};
    print $key," = ", $value, "\n";
  }
}

# ----------------------------------
# EOF
# ----------------------------------

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.