How do I convert a latitude/longitude pair into a PostGIS geography type?

database postgresql gis postgis
DRMacIver picture DRMacIver · Mar 26, 2010 · Viewed 24.2k times · Source

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?

Answer

DRMacIver picture DRMacIver · Mar 26, 2010

...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.

Related questions

How to choose between MongoDB and PostgreSQL for GIS?

I'm trying to find some information on this topic but most articles I'm finding are 3+ years old. I am also fairly new to this side of things and not sure who to ask. For my particular use case, I'm not …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
What is this Javascript "require"?

I'm trying to get Javascript to read/write to a PostgreSQL database. I found this project on github. I was able to get the following sample code to run in node. var pg = require('pg'); //native libpq bindings = `var pg = …

Read More