[postgis-users] coordinate order of geometry vs coordinate order of gml

[Paul Ramsey]

Since everything is wrong except for GML, which has to reverse things
that are correct in order to do the "right" thing vis-a-vis GML, my
guess is that the data were loaded in the wrong order to start with.
That is, if you run ST_AsText() you'll find the coordinates in Lat-Lon
order, when the expectation is that they are in Lon-Lat order.

To fix,

update mytable set geom = st_flipcoordinates(geom)

http://postgis.net/docs/ST_FlipCoordinates.html


P.

On Mon, May 25, 2015 at 1:59 PM, Rushforth, Peter
<Peter.Rushforth at nrcan-rncan.gc.ca> wrote:
> Hi,
>
> I am confused by coordinate order issues that I noticed when using qgis for the first time today.
>
> I have a table with a geometry column that stores different shapes - points, lines and polygons.
>
> The projection of the column is EPSG:4326 ->
>
> "(f_table_catalog)";"(f_table_schema)";"(f_table_name)entrystore";"(f_geometry_column)georss_where";(coord_dimension)2;(srid)4326;"(type)GEOMETRY"
>
> When I access the column using qgis, and apparently with pgsql2shp, the coordinate order of the geometry column is reversed / incorrect.
>
> When I access the column with ST_AsGML, it is correct.  When I access with ST_AsKML, it reverses the coordinate order:
>
> select ST_AsKML(georss_where), ST_AsGML(georss_where) from entrystore where updatetimestamp = 914103->
> "<Polygon><outerBoundaryIs><LinearRing><coordinates>46,-76 45,-76 45,-74 46,-74 46,-76</coordinates></LinearRing></outerBoundaryIs></Polygon>";"<gml:Polygon srsName="EPSG:4326"><gml:outerBoundaryIs><gml:LinearRing><gml:coordinates>46,-76 45,-76 45,-74 46,-74 46,-76</gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygon>"
>
> Am I wrong in thinking that either ST_AsGML or ST_AsKML has got something wrong?  I would have expected that PostGIS, knowing the geometry is EPSG:4326,
> would return the KML in lon,lat order.
>
> When I take the centroid of the geometry and report it with ST_AsLatLonText it reports longitude and latitude reversed, when compared with the GML:
>
> select ST_AsLatLonText(ST_centroid(georss_where)), ST_AsGML(ST_Centroid(georss_where)) from entrystore where updatetimestamp = 914103
> "75°0'0.000"S 45°30'0.000"E";"<gml:Point srsName="EPSG:4326"><gml:coordinates>45.5,-75</gml:coordinates></gml:Point>"
>
>
> Thank you.
> Peter Rushforth
>
> P.S.
> I noticed that on this documentation page there are two coordinate orders for apparently a similar area in North America (?) - EPSG:4269, but
> perhaps this is unrelated.
>
> http://postgis.net/docs/ST_GeomFromGML.html
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users