Hi Stefan,
note that the use of generate_series() in the SELECT clause is deprecated by the PostgreSQL folks. That functionality may go away in future releases.
An alternative formulation of your segment expansion query would be the following (I assume that your input table is segments(meter, distance, speed)):
SELECT t.meter, s.distance, s.speed
FROM segments AS s, LATERAL generate_series(s.meter, s.meter+s.distance+1) AS t(meter);
Note the use of LATERAL such that in the invocation of generate_series() we may refer to row variable s.
Now, as I understand it, this query should work in MonetDB. LATERAL support is in MonetDB and has received recent bug fixes. The query work does not work, though: MonetDB 5 server v11.27.13 "Jul2017-SP4" (on macOS Sierra 10.12.6) goes into an infinite loop already when I try to plan the query. Which turns this reply into a bug report, I guess. ¯\_(ツ)_/¯
Best wishes,
—Torsten
Goodmorning,
At this moment there is no functional compatibility between the PostgreSQL
and MonetDB with respect to the generate_series function. The range in
PostgreSQL is inclusive, in MonetDB exclusive. More importantly
generate_series can't be used with argument that is joined from a different
table, MonetDB just seems to support a subquery, hence joining that back
without a primary is impossible.
PostgreSQL;
select generate_series(a, b), a from (select 1 as a, 2 as b) as a;
generate_series | a
-----------------+---
1 | 1
2 | 1
(2 rows)
MonetDB;
select * from generate_series((select 1 as a, 2 as b));
+-------+
| value |
+=======+
| 1 |
+-------+
If I would like to functionally achieve the following;
I have list that states the speeds over partial segments, identified by
linear referencing. From 10 meter to 12 meter, the distance was 2 meter and
speed was 2s.
10 2 2
I would like to expand these values to segments of one meter, hence end up
with a table that would state:
10 2 2
11 2 2
12 2 2
Now I am aware of the Python bindings and I could achieve what I wanted by
the function below, but I would find generate_series still very practical.
DROP FUNCTION expand_per_meter;
CREATE FUNCTION expand_per_meter(trip_hash BIGINT, userstopcode STRING,
distancesincelastuserstop INTEGER, s INTEGER, t INTEGER)
RETURNS TABLE(trip_hash BIGINT, userstopcode STRING,
distancesincelastuserstop INTEGER, s INTEGER, t INTEGER)
LANGUAGE PYTHON {
result = dict()
result['trip_hash'] = []
result['userstopcode'] = []
result['distancesincelastuserstop'] = []
result['s'] = []
result['t'] = []
for i in range(0, len(trip_hash)):
expanded = range(distancesincelastuserstop[i],
distancesincelastuserstop[i] + s[i] + 1)
expand = len(expanded)
result['trip_hash'] += [trip_hash[i]] * expand
result['userstopcode'] += [userstopcode[i]] * expand
result['distancesincelastuserstop'] += expanded
result['s'] += [s[i]] * expand
result['t'] += [t[i]] * expand
return result
};
--
Stefan
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
--