Home > other >  How to correctly store lat/long values with postgis extension
How to correctly store lat/long values with postgis extension

Time:06-26

Description

I'm storing lat/long from google geocoding results into a geography column. But depending on the lat/long, the query returns different values than the one inserted. What am I doing wrong or missing?

Repro

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE "public"."test_location" (
  "name" text,
  "location" GEOGRAPHY (point)
);

INSERT INTO "public"."test_location" ("name", "location")
  VALUES
  ('test 1', ST_POINT (32.714476, -117.151735)),
  ('test 2', ST_POINT (36.1450444, -85.4788305));

SELECT
  name,
  ST_AsText (location)
FROM
  "public"."test_location";

-- output:
-- |-----------------------------------------|
-- | name    | ST_AsText                     |
-- |-----------------------------------------|
-- | test 1  | POINT(32.714476 -62.848265)   | --> expected POINT(32.714476 -117.151735)
-- | test 2  | POINT(36.1450444 -85.4788305) |
-- |-----------------------------------------|

DROP TABLE "public"."test_location";

CodePudding user response:

It looks like longitude and latitude are exchanged

ST_POINT(longitude, latitude)

Longitude has range -180 to 180

Latitude has range -90 to 90

  • Related