SELECT DISTINCT ON (maxspeed, ap.assetid) ap.assetid, locationsname,
CASE WHEN startspeed > endspeed
THEN x(transform(startpoint(ap.geom), 99999))
ELSE x(transform(endpoint(ap.geom), 99999))
END AS xpoint,
CASE WHEN startspeed > endspeed
THEN y(transform(startpoint(ap.geom), 99999))
ELSE y(transform(endpoint(ap.geom), 99999))
END AS ypoint,
CASE WHEN startspeed > endspeed
THEN starttime
ELSE endtime
END AS ts,
maxspeed, fleet, tagid
FROM
( SELECT assetid,
CASE WHEN max(startspeed) > max(endspeed)
THEN max(startspeed) ELSE max(endspeed) END AS maxspeed , loi.name AS locationsname
FROM asset_position_lines , locations_of_interest loi
WHERE cube(cube(cube(EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2007-10-02T00:00:00-07:00'), EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2007-10-03T23:59:00-07:00')), xmin(loi.geom), xmax(loi.geom)), ymin(loi.geom), ymax(loi.geom)) && cubespacetime
AND Distance(loi.geom, asset_position_lines.geom) = 0
AND loi.id IN (456)
GROUP BY assetid , loi.name ) AS innertable
LEFT JOIN asset_position_lines ap ON innertable.assetid = ap.assetid AND (innertable.maxspeed = ap.startspeed OR innertable.maxspeed = ap.endspeed)
LEFT JOIN asset ON asset.id = ap.assetid
LEFT JOIN locations_of_interest loi ON
cube(cube(cube(EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2007-10-02T00:00:00-07:00'), EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2007-10-03T23:59:00-07:00')), xmin(loi.geom), xmax(loi.geom)), ymin(loi.geom), ymax(loi.geom)) && cubespacetime
AND Distance(loi.geom, ap.geom) = 0
WHERE cube(cube(cube(EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2007-10-02T00:00:00-07:00'), EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2007-10-03T23:59:00-07:00')), -180, 180), -90, 90) && cubespacetime
AND loi.id IN (456)
ORDER BY maxspeed desc, ap.assetid ASC LIMIT 20 OFFSET 0