[GIS] How to insert long/lat value in postgis database in the form of Point(x,y)

latitude longitudepointpostgis

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));

Best Answer

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

Related Question