在 Postgis 中为 Polygon 值创建表并插入 [英] Creating a table for Polygon values in Postgis and inserting

问题描述

我有以下 10 个不同区域的区域名称"和多边形"值('A',50.6373 3.0750,50.6374 3.0750,50.6374 3.0749,50.63 3.07491,50.6373 3.0750)

I have the following area "name" and "polygon" values for 10 different areas ('A',50.6373 3.0750,50.6374 3.0750,50.6374 3.0749,50.63 3.07491,50.6373 3.0750)

我想使用 POSTGIS 在 postgres DB 中创建一个表

I want to create a table in postgres DB using POSTGIS

稍后，我将在表中列出 lan 和 lat 值(例如 50.5465 3.0121)与上表进行比较并提取区域名称

Later, I will have lan and lat values (e.g. 50.5465 3.0121) in a table to compare with the above table and pull out the area name

你能帮我编写创建和插入多边形坐标的代码吗?

Can you help me with the code for both creating and inserting the polygon coordinates?

推荐答案

我没有足够的声誉来评论您的问题，有一个链接您可能会觉得有用:使用 PostgreSQL 的点多边形 SQL 查询

I don't have enough reputation to comment you question, there is a link you might find useful: SQL query for point-in-polygon using PostgreSQL

为您的数据库添加扩展

CREATE EXTENSION postgis;

创建表格

CREATE TABLE areas (
    id SERIAL PRIMARY KEY,
    name VARCHAR(64),
    polygon GEOMETRY
);

在多边形字段上创建索引

Creating index over polygon field

CREATE INDEX areas_polygon_idx ON areas USING GIST (polygon);

插入记录

INSERT INTO areas (name, polygon) VALUES (
    'A',
    ST_GeometryFromText('POLYGON((50.6373 3.0750,50.6374 3.0750,50.6374 3.0749,50.63 3.07491,50.6373 3.0750))')
);

查询

SELECT name FROM areas WHERE ST_Contains(polygon, ST_GeomFromText('POINT(50.637 3.074)'));
 name 
------
(0 rows)

SELECT name FROM areas WHERE ST_Contains(polygon, ST_GeomFromText('POINT(50.63735 3.07495)'));
 name 
------
 A
(1 row)

这篇关于在 Postgis 中为 Polygon 值创建表并插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！