Hey,
1 sec seems really good in this case,
and I'm assuming you tuned postgres so the main index fits into ram (work_mem and all other stuff).
You could avoid a CTE by mixing both cte.
WITH pts AS (
SELECT (pt).geom, (pt).path[1] as vert
FROM
ST_DumpPoints(
ST_Segmentize(
ST_GeographyFromText('SRID=4326;LINESTRING(-150.008056 61.179167,-156.77 71.285833)'),
600
)::geometry
) as pt
)
SELECT elevation
FROM data
INNER JOIN (SELECT
ST_MakeLine(ARRAY[a.geom, b.geom]) as short_line
FROM pts a
INNER JOIN pts b
ON a.vert=b.vert-1 AND b.vert>1) segments
ON ST_DWithin(location, segments.short_line, 600)
ORDER BY elevation DESC limit 1;
Then you could remove the useless and (potentially explosive if you have large number of dump points) inner join on points :
"FROM pts a
INNER JOIN pts b "
You could simply use a window function to generate the segments, like in
here.
The idea is to dump points, order them by path, and then link each point with the previous one (function lag).
Assuming you don't want to use the available function,
this would be something like :
WITH segments AS (
SELECT ST_MakeLine( lag((pt).geom , 1, NULL) OVER (ORDER BY (pt).path)
,(pt).geom) AS short_line
FROM ST_DumpPoints(
ST_Segmentize(
ST_GeographyFromText('SRID=4326;LINESTRING(-150.008056 61.179167,-156.77 71.285833)'),
600
)::geometry
) as pt
)
SELECT elevation
FROM data ,segments
WHERE segments.short_line IS NOT NULL --the first segment is null by design (lag function)
AND ST_DWithin(location, segments.short_line, 600) = TRUE
ORDER BY elevation DESC
limit 1;
I don't know if you can further improve this query after that,
but I'll guess it would reduce your time and be more secure regarding scaling.
if you want to further improve your result,
you'll have to reduce the number of row in your index,
that is partition your table into several tables !
This is not easy to do with current postgres partitionning methods as far as I know
(partitionning is easy, automatic efficient query is hard).
Another way would be to reduce you requirement, and consider that in some case you may want less details in the altimetry, which would allow you to use a Level Of Detail approach.
Congrats for the well explained query/problem anyway !
Cheers,
Rémi-C
