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

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?

Answers


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


Here are some ways to make geography types:

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

    UPDATE mytable SET geog = ST_SetSRID(ST_MakePoint(long, lat), 4326)::geography
    
  2. Convert a geom geometry column (SRID=4326) to a geog geography type using a simple cast:

    UPDATE mytable SET geog = geom::geography
    
  3. 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.


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.


Need Your Help

Mail go to spam intead of inbox using smtp mail method in php

php smtp

I want to send mail with smtp mail method using PHP.Now mail go to spam instead of inbox,i am using smtp mail method.I had set the hostname,username and password.

What is difference between method `toHaveBeenCalled()` and `andCalledThrough()`

angularjs unit-testing jasmine karma-jasmine

While using the Jasmine Spies, how different is andCalledThrough() method from toHaveBeenCalled, does it actually run the original method completely? Any ideal scenarios when I should use it?