postgresql 纬度和经度的数据类型是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8150721/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Which data type for latitude and longitude?
提问by user1008404
I am newbie to PostgreSQL and PostGIS. I want to store latitude and longitude values in PostgreSQL 9.1.1 database table. I will calculate distance between two points, find nearer points by using this location values.
我是 PostgreSQL 和 PostGIS 的新手。我想在 PostgreSQL 9.1.1 数据库表中存储纬度和经度值。我将计算两点之间的距离，使用此位置值找到更近的点。
Which data type should I use for latitude and longitude?
我应该为纬度和经度使用哪种数据类型？
回答by Erwin Brandstetter
You can use the data type point- combines (x,y)which can be your lat / long. Occupies 16 bytes: 2 float8numbers internally.
您可以使用数据类型point- 组合(x,y)可以是您的经纬度。占用16个字节：float8内部2个数字。
Or make it two columns of type float(= float8or double precision). 8 bytes each.
Or real(= float4) if additional precision is not needed. 4 bytes each.
Or even numericif you need absolute precision. 2 bytes for each group of 4 digits, plus 3 - 8 bytes overhead.
或者将其设为两列float（=float8或double precision）。每个 8 个字节。
或者real(= float4) 如果不需要额外的精度。每个 4 个字节。
或者即使numeric您需要绝对精度。每组 4 位数字 2 个字节，加上 3 - 8 个字节的开销。
Read the fine manual about numeric typesand geometric types.
The geometryand geographydata types are provided by the additional module PostGISand occupy onecolumn in your table. Each occupies 32 bytes for a point. There is some additional overhead like an SRID in there. These types store (long/lat), not (lat/long).
该geometry和geography数据类型由附加模块提供了PostGIS并占据一个在你的表列。每个点占 32 个字节。那里有一些额外的开销，例如 SRID。这些类型存储 (long/lat)，而不是 (lat/long)。
Start reading the PostGIS manual here.
从这里开始阅读PostGIS 手册。
回答by Darafei Praliaskouski
In PostGIS, for points with latitude and longitude there is geography datatype.
在 PostGIS 中，对于具有纬度和经度的点，有地理数据类型。
To add a column:
要添加列：
alter table your_table add column geog geography;
To insert data:
插入数据：
insert into your_table (geog) values ('SRID=4326;POINT(longitude latitude)');
4326 is Spatial Reference ID that says it's data in degrees longitude and latitude, same as in GPS. More about it: http://epsg.io/4326
4326 是空间参考 ID，表示它是以经度和纬度为单位的数据，与 GPS 相同。更多相关信息：http: //epsg.io/4326
Order is Longitude, Latitude - so if you plot it as the map, it is (x, y).
顺序是经度，纬度 - 所以如果你把它绘制成地图，它是（x，y）。
To find closest point you need first to create spatial index:
要找到最近点，您首先需要创建空间索引：
create index on your_table using gist (geog);
and then request, say, 5 closest to a given point:
然后请求，比如说，最接近给定点的 5 个：
select *
from your_table
order by geog <-> 'SRID=4326;POINT(lon lat)'
limit 5;
回答by tvieira
回答by Hans Bouwmeester
If you do not need all the functionality PostGIS offers, Postgres (nowadays) offers an extension module called earthdistance. It uses the pointor cubedata type depending on your accuracy needs for distance calculations.
如果您不需要 PostGIS 提供的所有功能，Postgres（现在）提供了一个名为earthdistance的扩展模块。它根据距离计算的精度需求使用点或立方体数据类型。
You can now use the earth_boxfunction to -for example- query for points within a certain distance of a location.
您现在可以使用earth_box函数来（例如）查询某个位置特定距离内的点。
回答by golfalot
In PostGIS Geometry is preferred over Geography (round earth model) because the computations are much simpler therefore faster. It also has MANY more available functions but is less accurate over very long distances.
在 PostGIS 中，几何优于地理（圆形地球模型），因为计算更简单，因此速度更快。它还具有更多可用功能，但在很长的距离内精度较低。
Import your CSV to long lat fields to DECIMAL(10,6)columns. 6 digits is 10cm precision, should be plenty for most use cases.
将您的 CSV 导入到长经纬度字段到DECIMAL(10,6)列。6 位数是 10 厘米的精度，对于大多数用例来说应该足够了。
Then cast your your imported data
然后投射您导入的数据
SELECT
--ST_SetSRID(ST_Point(long, lat),4326) geom -- the wrong way because SRID not set in geometry_columns table
ST_Point(long, lat)::geometry(Geometry, 4326) geom
INTO target_table
FROM source_table;
Verify SRID is not zero!
验证 SRID 不为零！
SELECT * FROM public.geometry_columns WHERE f_table_name = 'target_table';
Validate the order of your long lat parameter using a WKT viewer and ST_AsEWKT(target_table.geom).
使用 WKT 查看器和ST_AsEWKT(target_table.geom).
Then index it for best performance
然后索引它以获得最佳性能
CREATE INDEX idx_target_table_geom_gist
ON target_table USING gist(geom);
