4

I'm using PostGIS version 2.3. I want to get the SW and NE corners of a bounding box, corrected to 4 decimal places.

Right now I have this to retrieve the SW and NE corners from my geometry column:

select
st_astext(st_transform(st_makepoint(st_xmin(st_extent(geom)), st_ymin(st_extent(geom))), '+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +datum=OSGB36 +units=m +no_defs ',4326)) as sw,
st_astext(st_transform(st_makepoint(st_xmax(st_extent(geom)), st_ymax(st_extent(geom))), '+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +datum=OSGB36 +units=m +no_defs ',4326)) as ne
FROM HOLDINGS
WHERE custname='XXX';

Which returns results like this:

                     sw                     |                     ne
--------------------------------------------+--------------------------------------------
 POINT(-0.685239554498251 51.4940418030595) | POINT(-0.680132974825013 51.4962802022361)

But how can I amend this to get the SW and NE corners corrected to 4 decimal places?

The results can be four numbers rather than two WKT POINTs (in fact that would be preferable).

5

The simplest way to round geometry coordinates is to use ST_SnapToGrid:

SELECT ST_AsText(ST_SnapToGrid('POINT(-0.685239554498251 51.4940418030595)', 0.0001));
       st_astext
-----------------------
 POINT(-0.6852 51.494)

But if you just need the 4 bounding coordinates, then ST_Extent returns a simple box2d type, which sort-of looks like WKT:

SELECT ST_Extent(
  ST_SnapToGrid(
    ST_Transform(geom, '+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +datum=OSGB36 +units=m +no_defs', 4326),
    0.0001))
FROM (
  -- whatever geometry table goes here, e.g. two points
  SELECT 'POINT(123 4567)'::geometry geom
  UNION SELECT 'POINT(8910 11121314)'
) s;
               st_extent
---------------------------------------
 BOX(-15.9517 49.8078,-7.5602 89.9958)

(You may also want to assign an SRID for that projection in your spatial_ref_sys table, as ST_Transform performs faster using geometries with these set)

2

And if Mike T's answer isn't what you want, you can get the X and Y coordinates out with something like

SELECT 
    round(ST_X('POINT(-0.685239554498251 51.4940418030595)'), 4),
    round(ST_Y('POINT(-0.685239554498251 51.4940418030595)'), 4);
0

I combined both answers from @AlexLeith and @MikeT.
Here is my question with an answer

ST_GeomFromText(concat(
'POINT (', 
round(ST_X(ST_SnapToGrid(centroid, 0.0000001))::numeric, 7), 
' ', 
round(ST_Y(ST_SnapToGrid(centroid, 0.0000001))::numeric, 7),
')'
), 4326);

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.