3

Being totally new with PostGIS, I am trying to UPDATE one of my column on my table.

The column itself is called 'center_point' with type of GEOGRAPHY(Point,4326)

UPDATE cities
SET center_point = st_setsrid(st_makepoint(-6.28381815,106.804863491948),4326) WHERE id = 1;

Upon verifying the result:

SELECT id, st_asgeojson(shape) AS "geojson", st_asgeojson(center_point)
FROM cities WHERE id = 1;

I am getting a different output: The coord does not match with what i entered

  • It says that the coordinate is -6.283... , 73.1951...

  • Where i am expecting it to be -6.283.... , 106.80486

Can anyone help me why the result is different?

  • 2
    did you recieve an error? ST_MakePoint expects it's parameters in (X, Y) order, i.e. (Lon, Lat); you pass them in wrong order (there can't be Lat > 90°). – ThingumaBob Oct 19 '18 at 6:57
  • 1
    @ThingumaBob clumsy me. Thanks for pointing that out. It works now. Can you please put this an answer so that i can accept it. Thanks alot. – Jeremy Oct 19 '18 at 7:17
3

The simple answer:

ST_MakePoint, as most functions in PostGIS (and OGC conform software), expects the order of coordinates passed in to be ST_MakePoint(X, Y), i.e. ST_MakePoint(lon, lat).

Your second paramter exceeded 90°, so I assumed you mixed them.


Interesting note:

While PostGIS' geometry type doesn´t care for the input values of a given coordinate pair in a given CRS (it will happily accept INSERT/UPDATE ST_SetSRID('POINT(0 100)::geometry), 4326) into/on a table with specified geometry(POINT, 4326) column), the geography implicitly denies out-of-bounds coordinates and will simply substract the difference from the bound.

Example:

  • WGS84 (and quite universal) bounds of latitude is 90°
  • you passed in 106.80486°

  • you got 90 - (106.80486 - 90) = 73.19514

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.