34

I'm trying to load a bunch of latitude/longitude pairs into a PostGIS geography type so as to be able to query by location.

In particular I have a table with float latitude and longitude columns and a geography(Point, 4326) column. I would like to do

update mytable set geography = ???

The documentation appears to suggest that the following should work:

update mytable set geography = ST_GeogFromText('POINT(' || latitude || ' ' ||
                                                           longitude || ')');

It doesn't. I don't know what it's interpreting this point as meaning, but it only allows the longitude to lie between -90 and 90, so it's clearly not a longitude.

So, what do I do?

sth
  • 222,467
  • 53
  • 283
  • 367
DRMacIver
  • 2,259
  • 1
  • 17
  • 17

3 Answers3

42

...sigh. Stupidity on my part. Apparently the correct order is longitude, latitude. I was fooled into thinking that both coordinates had the same range (-180 to 180) so thought something more subtle was going on.

DRMacIver
  • 2,259
  • 1
  • 17
  • 17
  • 8
    longitude, latitude ordering sure introduces a lot of bugs. – kbrock Jun 04 '13 at 14:04
  • This is still a horrible answer, and you should have accepted the advice of @MikeT with ST_MakePoint instead of string concatenation which introduces rounding errors. – Evan Carroll Nov 01 '16 at 15:47
  • 4
    You mean the ones made three and four years after I posted the question and long after I no longer had the problem? – DRMacIver Nov 01 '16 at 22:29
  • 2
    @DRMacIver maybe he thought you completely paused your life and went into a cryogenic slumber until an answer came up – Luffydude Nov 23 '16 at 10:07
  • ```Apparently the correct order is longitude, latitude``` thank you very much for this info @DRMacIver! Where can I find this info in the PostGIS official docs? Sorry I've been searching but can't find it in their docs :( It's should be something that they emphasize on their docs, because as @kbrock said, it sure introduces bugs not knowing it, because usually we use lat/lon order :( – Mon Oct 26 '22 at 22:30
12

Here are some different ways to make geography types:

  • Convert numeric long and lat columns to a geog geography type:

     UPDATE mytable SET geog = ST_SetSRID(ST_MakePoint(long, lat), 4326)::geography

  • Convert a geom geometry column (SRID=4326) to a geog geography type using a simple cast:

     UPDATE mytable SET geog = geom::geography

  • Transform a projected geom geometry column to a geog geography type:

     UPDATE mytable SET geog = ST_Transform(geom, 4326)::geography

Note that the last two examples work on any geometry type. Also, the conversion from geometry to geography is often implicit, and these examples work without ::geography, however explicit casts are usually a good practice for these things.

Mike T
  • 41,085
  • 18
  • 152
  • 203
5

To perform exchange between lat and lng you may use:

update mytable set geography = ST_GeographyFromText('SRID=4326;POINT(' || st_x(geom) || ' ' ||  st_y(geom) || ')');

with or without srid.

Dawid D
  • 1,057
  • 13
  • 28
  • This method will have some lossy round-off errors (i.e., `float8` → `text` → `float8`). Also, `ST_X` only works on `POINT` geometry types. – Mike T Oct 28 '14 at 04:36