2

I am having some trouble in inserting long/lat in the form of point. Currently, I have array of long/lat value and which I want to store into database in the form of point. However, I am unable to store it in the form of point and when I query as ST_AsText(geom), I simply get long/lat value not a point.

In the picture below, the bottom one is my output where point and long/lat are same but what I want to achieve is the top query output. enter image description here

Insert code:

INSERT INTO point_route(ID, ROUTE_NAME, geom)
VALUES (1, 'point 1', ST_SetSRID(ST_makepoint(27.71595,  85.28364),900913));
  • Please do not include text as graphics -- not everyone is using a device that would make that legible (and I'm using a 23" monitor). Coordinates are specified in X,Y,Z,M order; Latitude is a Y value, so may need to flip your order to lon,lat. – Vince Sep 13 '15 at 17:42
4

If you want your points to be in web mercator like the query above, you need to change your point_route table as follows. You have a couple of issues:

1) Your coordinates are flipped as Vince pointed out X is longitude, and y is latitude but you have the other way around.

2) Your projection is wgs84, but looks like you tagged it as web mercator which is wrong and why transform does nothing for you.

The below should fix all these issues:

ALTER TABLE point_route 
   ALTER COLUMN geom type geometry(POINT,900913) 
      USING ST_Transform(ST_SetSRID(ST_FlipCoordinates(geom),4326), 900913);

Refer to manual for details:

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

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

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

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

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.