database 如何将纬度/经度对转换为 PostGIS 地理类型？

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

提问by DRMacIver

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.

我正在尝试将一堆纬度/经度对加载到 PostGIS 地理类型中，以便能够按位置查询。

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

特别是我有一个带有浮动纬度和经度列和一geography(Point, 4326)列的表格。我想要做

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.

它没有。我不知道它把这一点解释为什么意思，但它只允许经度介于 -90 和 90 之间，所以它显然不是经度。

So, what do I do?

那么，我该怎么办？

回答by DRMacIver

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

...叹。我的愚蠢。显然正确的顺序是经度，纬度。我被愚弄了，认为两个坐标都有相同的范围（-180 到 180），所以认为发生了一些更微妙的事情。

回答by Mike T

Here are some ways to make geography types:

以下是一些制作地理类型的方法：

1. Convert numeric longand latcolumns to a geoggeography type:

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

2. Convert a geomgeometry column (SRID=4326) to a geoggeography type using a simple cast:

   UPDATE mytable SET geog = geom::geography
   

3. Transform a projected geomgeometry column to a geoggeography type:

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

1. 将数字long和lat列转换为geog地理类型：

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

2. 使用简单的强制转换将geom几何列 ( SRID=4326) 转换为geog地理类型：

   UPDATE mytable SET geog = geom::geography
   

3. 将投影geom几何列转换为geog地理类型：

   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.

请注意，最后两个示例适用于任何几何类型。此外，从几何到地理的转换通常是隐式的，并且这些示例没有::geography，但是显式转换通常是这些事情的好习惯。

回答by Dawid D

To perform exchange between lat and lng you may use:

要在 lat 和 lng 之间执行交换，您可以使用：

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

with or without srid.

有或没有 srid。