Remove ordering in landcover-lowzoom?

[gravitystorm]

We currently return lowzoom landcover ordered by layer. Is this actually necessary? They are only shown to zoom level 9, and I would be surprised to find landuses that are a) shown at that zoom and b) overlapping and c) have different layer tags and d) cover more than a pixel or two.

Would removing the ordering speed up the query?

[pnorman]

Would removing the ordering speed up the query?

We order by layernotnull, way_area. If this is quick depends largely on two things:

• If the sort fits into work_mem. This depends on work_mem and what's being sorted
• The complexity of the sort. A 30-key sort can be substantially slower

I am not sure if the size of the sort key influences sort memory usage significantly.

I'd be fine with removing it as I don't think we should order by layer on low zoom for cartographic reasons.

[matthijsmelissen]

This is very similar to the question asked in #53.

I agree with removing the order.

[imagico]

I think ordering in a specific way in addition to way_area is probably pointless for the low zoom landcovers because if there was need for it, it would have to include water areas in that sort order as well, which is neither easily possible nor desirable.

[kocio-pl]

Since there was an agreement, we can make it. Does it mean just removing these 5 lines or something else is needed too?

            WHERE (landuse IN ('forest', 'military', 'farmland')
              OR "natural" IN ('wood', 'wetland', 'mud', 'sand', 'scree', 'shingle', 'bare_rock'))
              AND way_area > 0.01*!pixel_width!::real*!pixel_height!::real
              AND building IS NULL
            ORDER BY COALESCE(layer,0), way_area DESC

[matthijsmelissen]

No, we should only remove COALESCE(layer,0), from the last line. We probably still want to order by area size (way_area).

The first four lines are about filtering only the objects we want so the result from the query does not get too large (which would take Mapnik a lot of time). We want only landuse=forest/landuse=military/landuse=farmland and some natural tags, we don't want objects that are smaller than 0.01 pixel, and we don't want things with a building tag.

[kocio-pl]

Thanks. BTW - is there a reason why "natural" is written like this (with quotes), unlike other keys?

[matthijsmelissen]

Yes, NATURAL is also an SQL keyword.

[mboeringa]

"natural" is a reserved keyword in PostgreSQL, by quoting it, PostgreSQL should ignore it and tread it like any other database identifier. PostgreSQL is the only RDBMS having made the mildly unfortunate decision to make "natural" a keyword... (at least in the context of OSM)

https://www.postgresql.org/docs/current/static/sql-keywords-appendix.html

[HolgerJeromin]

We could have all values in quotes. At least this is the way phpmyadmin generates queries which i find a quite nice style