问题描述
我安装了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 nogeometry
orgeography
column I used the functionST_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 typegeography
this value is interpreted as meters.'quad_segs='
: number of segments used to approximate a quarter circle (text from thedocumentation
)
之后,您可以使用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;
进一步阅读:
这篇关于地理信息系统PostGIS(地图点(在美国地图上带有raduis的经纬度)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!