I am looking for an efficient way to get geometries sorted by their latitude (northmost in case of polygons) when querying a postGis database

Something that could work in an order by clause which possibly works independent of the geometry type (as long as the CRS is lat/lon based)

I think the following would be the ideal writing for it

order by ST_North(geometry)

but I am not aware of such an operator


So far I could think of computing the bounding box and getting its maximum Y value like in:

order by ST_YMax(Box2D(geom))

PS: Actually the conversion to a box is not necessary and to sort from north to south desc works like a charm, hence the final query part is

order by ST_YMax(geom) desc
  • 4
    You can use ST_YMax directly without building Box2D ST_YMax(geom) from table – user30184 Apr 26 '16 at 13:34
  • You are right, I did not notice it does not require converting the geometry. Then I guess it is exactly the operator I am looking for to sort results. – a1an Apr 26 '16 at 15:17
  • PostgreSQL calls this an implicit cast, where the type is automatically converted to match another type used by the function. You can see that the geometry->box2d cast is marked IMPLICIT to enable this handy feature. – Mike T Apr 27 '16 at 1:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.