For storing latitude and longitude in the database, I use the following query:

WITH cte_posttype AS (
 SELECT posttype_id, code AS posttypeCode FROM "post"."posttype" WHERE code = $6;
INSERT INTO "post"."post"(post_id, user_id, fb_username, place_name, geom, posttype_id, status_text, status_img) 
VALUES ($1, $2, $3,
   ST_SetSRID(ST_MakePoint($5, $4), 4326),
   SELECT posttype_id FROM cte_posttype,
   $7, $8)
RETURNING post_id, user_id, fb_name, place_name, geom, cte_posttype.posttypeCode, status_text, status_img, updated_at, created_at;

The relevant part is:

ST_SetSRID(ST_MakePoint($5, $4), 4326)

Now how do I revert the geometry type back to get the original lat and lng to return it?
The same question applies on making a SELECT: I want to retrieve the lat and lng value to return it to the client as such.


In fact provided you always store your points as lon/lat (4326) then just

st_x(geom) as lng, st_y(geom) as lat 

should work.

  • Can you answer this question? Its about converting raw geometry hex values to coordinates Mar 29 '20 at 4:59
  • this answer will work with any geometry
    – Ian Turton
    Mar 29 '20 at 9:36

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.