Make two lines from points

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"
``````
• 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

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')
``````
• 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. Commented Nov 26, 2015 at 17:09

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);
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

# 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
# ----------------------------------
``````