Postgis query to sort geometries by latitude

Question

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

a1an · Accepted Answer · 2016-04-26 15:19:36Z

4

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

answered Apr 26, 2016 at 13:28

a1an

5812 gold badges9 silver badges20 bronze badges

5

You can use ST_YMax directly without building Box2D ST_YMax(geom) from table
– user30184
Commented Apr 26, 2016 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
Commented Apr 26, 2016 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
Commented Apr 27, 2016 at 1:03

Add a comment |

1 Answer 1