地理信息系统PostGIS(地图点(在美国地图上带有raduis的经纬度) [英] QGIS & PostGIS (map points(lat and long with raduis on USA map)

问题描述

我安装了QGIS和PostGIS.我有200个要在美国图表上显示的半径为100英里的点.

I installed QGIS and PostGIS. I have 200 points that I want to display with a radius of 100 miles on a graph of the US.

我已将我的纬度和经度导入到PostGIS数据库中.所以我有三个字段:[地址]，[lat]，[lng].

I have imported my latitude and longitude in the PostGIS database. So I have three fields: [address], [lat], [lng].

1)我需要将lat和lng字段转换为点或geom字段吗?如果可以，怎么办? (st_buffer?) 2)我使用什么命令/SQL来显示半径点?

1) Do I need to convert the lat and lng fields into a point or geom field? If so how? (st_buffer?) 2) What command/SQL do I use to display the points with radius?

我可以这样查询自己的观点.

I can query my points like so..

SELECT * FROM postgis_test

我只是不明白如何在半径范围内显示所有点.

I just don't understand how to display all the points on a map with the radius.

示例要点:

city        lat          lng
New York    40.7127753  -74.0059728
Los Angeles 34.0522342  -118.2436849
Chicago     41.8781136  -87.6297982

推荐答案

首先创建geometry或geography列，例如与 AddGeometryColumn ..

First create a geometry or geography column, e.g. with AddGeometryColumn ..

SELECT AddGeometryColumn ('public','postgis_test','geom',4326,'POINT',2);

..然后使用 ST_Buffer

.. and then update it with the output of ST_Buffer

UPDATE postgis_test 
SET geom = ST_Buffer(ST_SetSRID(ST_MakePoint(lng,lat),4326),50, 'quad_segs=8');

ST_Buffer的详细参数:

• ST_SetSRID(ST_MakePoint(lng,lat),4326):由于您的表没有geometry或geography列，所以我使用了函数 ST_MakePoint 创建一个.值4326对应于SRS WGS84-检查哪一个适合您的坐标.
• 5:缓冲区的半径，以度为单位.如果第一个参数的类型为geography，则该值将解释为米.
• 'quad_segs=':用于近似四分之一圆的段数(来自 documentation )

• ST_SetSRID(ST_MakePoint(lng,lat),4326) : As you table had no geometry or geography column I used the function ST_MakePoint to create one. The value 4326 corresponds to the SRS WGS84 - check which one suits your coordinates.
• 5 : radius of the buffer in degrees. If the first parameter is of type geography this value is interpreted as meters.
• 'quad_segs=' : number of segments used to approximate a quarter circle (text from the documentation)

之后，您可以使用Add PostGIS Layer选项将其导入QGIS.

After that you'll be able to import it into QGIS using the Add PostGIS Layer option.

创建半径为5度的缓冲区

Creating buffers with a radius of 5 degrees

CREATE TABLE public.postgis_test (city TEXT, lng NUMERIC, lat NUMERIC);
SELECT AddGeometryColumn ('public','postgis_test','geom',4326,'polygon',2);

INSERT INTO postgis_test 
VALUES ('New York',-74.00,40.71),
       ('Los Angeles',-118.24,34.05),
       ('Chicago',-87,41.87);

UPDATE postgis_test 
SET geom = ST_Buffer(ST_SetSRID(ST_MakePoint(lng,lat),4326),5, 'quad_segs=8');

在QGIS中可视化缓冲区

Visualizing buffers in QGIS

如果要同时显示点和缓冲区，则必须创建一个额外的层(表):

If you want to display both points and buffers at the same time, you have to create an extra layer (table):

CREATE TABLE public.postgis_test (city TEXT, lng NUMERIC, lat NUMERIC);
SELECT AddGeometryColumn ('public','postgis_test','geom',4326,'point',2);

INSERT INTO postgis_test 
VALUES ('New York',-74.00,40.71),
       ('Los Angeles',-118.24,34.05),
       ('Chicago',-87,41.87);

UPDATE postgis_test SET geom = ST_SetSRID(ST_MakePoint(lng,lat),4326);

CREATE TABLE buffers AS
SELECT city, ST_Buffer(geom,5, 'quad_segs=8') 
FROM postgis_test;

如果您更喜欢使用米，只需将几何列转换为geography并以米为单位传递参数.

If you prefer to work with meters just cast the geometry column to geography and pass the parameter in meters.

创建100英里(〜160934米)的缓冲区

Creating buffers of 100 miles (~160934 meters)

CREATE TABLE public.postgis_test (city TEXT, lng NUMERIC, lat NUMERIC);
SELECT AddGeometryColumn ('public','postgis_test','geom',4326,'point',2);

INSERT INTO postgis_test 
VALUES ('New York',-74.00,40.71),
       ('Los Angeles',-118.24,34.05),
       ('Chicago',-87,41.87);

UPDATE postgis_test SET geom = ST_SetSRID(ST_MakePoint(lng,lat),4326);

CREATE TABLE buffers AS
SELECT city, ST_Buffer(geom::geography,160934, 'quad_segs=8')::geometry 
FROM postgis_test;

进一步阅读:

• ST_SetSRID
• World Borders Dataset
• ST_MakePoint

这篇关于地理信息系统PostGIS(地图点(在美国地图上带有raduis的经纬度)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！