Ensure ordering for distance queries.

[rossjones]

For queries where we are asking for 'things' within X miles of a point, we should order the results with the nearest first.

[morty]

The existing query is:

select * from naptan_ferry_ports where ST_DISTANCE((select latlong from codepoint where postcode = normalise_postcode($1)), latlong) < $2;

Which do you think is nicest? Repeating the distance calculation:

select * from naptan_ferry_ports where ST_DISTANCE((select latlong from codepoint where postcode = normalise_postcode($1)), latlong) < $2 order by ST_DISTANCE((select latlong from codepoint where postcode = normalise_postcode($1)), latlong);

or using a with?

with ferry_ports as (select *, ST_DISTANCE((select latlong from codepoint where postcode = normalise_postcode($1)), latlong) distance from naptan_ferry_ports)
select * from ferry_ports where distance < $2 order by distance;

[rossjones]

Which is fastest? ;) I'd prefer to read the WITH query

[morty]

I think the with is quickest. Looking at the EXPLAIN output from the two queries the total cost estimate for the first one is 212.75 and for the second is 173.16. It looks like this is because it has to do the postcode lookup twice.

I like the with more, but it's not a commonly used SQL statement.

[morty]

If I change the manifest do I have to update the JSON files too?

[rossjones]

Will be removing those and the YAML at some point in favour of db entries - for now, only the JSON needs to be updated.