I'm not able to run a very simple query to return the bounding box of a table in another SRID. I'm doing something stupid, because it is a really simple query. When I run the query with the composition ST_Transform(ST_SetSRID(ST_Extent())) the conversion gives me wrong results. Maybe the problem is related with ST_Extent, since it is an aggregate operator.
1) If I don't convert the coordinates, if runs fine (the result is in my local EPSG:3763):
SELECT ST_AsGeoJSON(ST_Extent(geom), 0) as bbox FROM ppgis_pu.c_nivel_pu;"{"type":"Polygon","coordinates":[[[-27367,98989],[-27367,100889],[-25468,100889],[-25468,98989],[-27367,98989]]]}"2) If I get the coordinates returned by ST_Extent with ST_AsText and then transform them, if runs fine:
SELECT ST_AsGeoJSON(ST_Transform(st_geometryfromtext('POLYGON((-27367.2586796198 98988.7091137031,-27367.2586796198 100888.647066528,-25467.5369489055 100888.647066528,-25467.5369489055 98988.7091137031,-27367.2586796198 98988.7091137031))', 3763), 4326), 5);"{"type":"Polygon","coordinates":[[[-8.45624,40.5593],[-8.45632,40.57641],[-8.43389,40.57647],[-8.43381,40.55936],[-8.45624,40.5593]]]}"The transformation is working as expected.
3) If I do everything in one query, the result is completely wrong:
SELECT ST_AsGeoJSON(ST_Transform(ST_SetSRID(ST_Extent(geom),3673), 4326), 5) as bbox FROM ppgis_pu.c_nivel_pu;"{"type":"Polygon","coordinates":[[[-103.75417,-5.40583],[-103.75476,-5.39141],[-103.74037,-5.39083],[-103.73979,-5.40524],[-103.75417,-5.40583]]]}"The correct result should be like the previous:
"{"type":"Polygon","coordinates":[[[-8.45624,40.5593],[-8.45632,40.57641],[-8.43389,40.57647],[-8.43381,40.55936],[-8.45624,40.5593]]]}"What am I doing wrong?
My environment:
select postgis_full_version()"POSTGIS="2.1.2 r12389" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="UNKNOWN" RASTER"
أكثر...
1) If I don't convert the coordinates, if runs fine (the result is in my local EPSG:3763):
SELECT ST_AsGeoJSON(ST_Extent(geom), 0) as bbox FROM ppgis_pu.c_nivel_pu;"{"type":"Polygon","coordinates":[[[-27367,98989],[-27367,100889],[-25468,100889],[-25468,98989],[-27367,98989]]]}"2) If I get the coordinates returned by ST_Extent with ST_AsText and then transform them, if runs fine:
SELECT ST_AsGeoJSON(ST_Transform(st_geometryfromtext('POLYGON((-27367.2586796198 98988.7091137031,-27367.2586796198 100888.647066528,-25467.5369489055 100888.647066528,-25467.5369489055 98988.7091137031,-27367.2586796198 98988.7091137031))', 3763), 4326), 5);"{"type":"Polygon","coordinates":[[[-8.45624,40.5593],[-8.45632,40.57641],[-8.43389,40.57647],[-8.43381,40.55936],[-8.45624,40.5593]]]}"The transformation is working as expected.
3) If I do everything in one query, the result is completely wrong:
SELECT ST_AsGeoJSON(ST_Transform(ST_SetSRID(ST_Extent(geom),3673), 4326), 5) as bbox FROM ppgis_pu.c_nivel_pu;"{"type":"Polygon","coordinates":[[[-103.75417,-5.40583],[-103.75476,-5.39141],[-103.74037,-5.39083],[-103.73979,-5.40524],[-103.75417,-5.40583]]]}"The correct result should be like the previous:
"{"type":"Polygon","coordinates":[[[-8.45624,40.5593],[-8.45632,40.57641],[-8.43389,40.57647],[-8.43381,40.55936],[-8.45624,40.5593]]]}"What am I doing wrong?
My environment:
select postgis_full_version()"POSTGIS="2.1.2 r12389" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="UNKNOWN" RASTER"
أكثر...