创建表存储多边形，并查询这个多边形是否包含点(PostgreSQL + PostGIS + QGIS)

需求：创建一张表，存储下面的多边形，并查询这个多边形是否包含了（16.45，113.0）位置点。

解决思路：使用PostgreSQL中的函数ST_GeomFromText()进行geometry对象的构建，用ST_Contains()函数进行geomertry对象关系的判断，最后可以在QIS中添加PostGIS layer看一下具体的位置。

PostgreSQL + pgAdmin + PostGIS安装请见下文

安装教程：PostgreSQL + PostGIS + pgAdminhttps://blog.csdn.net/weixin_58587245/article/details/123742189?spm=1001.2014.3001.5501参考文献：《Guide to Maritime Informatics》

目录

步骤一：建表

步骤二：空间查询

步骤三：QIS呈现

---

步骤一：建表

-- 建表
CREATE TABLE data_analysis.polygen_test (
	id SERIAL PRIMARY KEY,
	name VARCHAR(128),
	geom GEOMETRY(POLYGON, 3035));

-- 添加GIST索引
CREATE INDEX idx_polygen_test_geom ON data_analysis.polygen_test USING GIST(geom);

-- 插入数据
INSERT INTO data_analysis.polygen_test(name,geom) VALUES('p1',
	ST_GeomFromText('POLYGON((16.6633300 113.1602400 ,
16.6384100 113.1602400 ,
16.5896900 113.1539000 ,
16.5846400 113.1531800 ,
16.5796000 113.1523200 ,
16.5733500 113.1510700 ,
16.5671400 113.1496000 ,
16.4828500 113.1283300 ,
16.3985500 113.1070400 ,
16.3142400 113.0857600 ,
16.2299400 113.0644800 ,
16.1456300 113.0432000 ,
16.0613300 113.0219200 ,
15.9770400 113.0006500 ,
15.9716600 112.9992100 ,
15.9663200 112.9976100 ,
15.9610200 112.9958700 ,
15.9557700 112.9939700 ,
15.9484900 112.9910600 ,
15.8973000 112.9693900 ,
15.8910400 112.9666000 ,
15.8848700 112.9636000 ,
15.8788000 112.9603900 ,
15.8728400 112.9569700 ,
15.8690700 112.9546600 ,
15.8653400 112.9522600 ,
15.8616700 112.9497800 ,
15.8580600 112.9472200 ,
15.8545000 112.9445800 ,
15.8509900 112.9418600 ,
15.8475400 112.9390600 ,
15.8441600 112.9361800 ,
15.8419800 112.9342700 ,
15.7863600 112.8845800 ,
15.7803600 112.8790100 ,
15.7745900 112.8731900 ,
15.7690500 112.8671400 ,
15.7637400 112.8608700 ,
15.7632800 112.8602900 ,
15.7216500 112.8086300 ,
15.7185400 112.8046900 ,
15.7155300 112.8006600 ,
15.7126200 112.7965600 ,
15.7098100 112.7923900 ,
15.6923300 112.7658000 ,
15.6883000 112.7594300 ,
15.6845100 112.7529200 ,
15.6817500 112.7478200 ,
15.6791300 112.7426400 ,
15.6696000 112.7231200 ,
15.6664000 112.7162500 ,
15.6597400 112.7012600 ,
15.6564700 112.6935000 ,
15.6535100 112.6856100 ,
15.6521500 112.6816300 ,
15.6408900 112.6475200 ,
15.6385500 112.6399800 ,
15.6364900 112.6323600 ,
15.6281700 112.5990600 ,
15.6263400 112.5911000 ,
15.6238200 112.5790500 ,
15.6229300 112.5746000 ,
15.6068600 112.4890200 ,
15.5907800 112.4034000 ,
15.5747000 112.3177900 ,
15.5586200 112.2321700 ,
15.5425300 112.1465500 ,
15.5264500 112.0609300 ,
15.5103700 111.9753100 ,
15.4942900 111.8897000 ,
15.4782100 111.8040800 ,
15.4621300 111.7184600 ,
15.4460500 111.6328400 ,
15.4299700 111.5472200 ,
15.4138800 111.4616100 ,
15.3978000 111.3759900 ,
15.3819600 111.2916500 ,
15.3805500 111.2845900 ,
15.3794400 111.2786000 ,
15.3782400 111.2707500 ,
15.3773400 111.2628600 ,
15.3734500 111.2219300 ,
15.3730300 111.2168800 ,
15.3727300 111.2118100 ,
15.3725500 111.2067400 ,
15.3724900 111.2016700 ,
15.3724900 111.1966700 ,
15.3725300 111.1921500 ,
15.3726800 111.1876300 ,
15.3729200 111.1831200 ,
15.3732500 111.1786200 ,
15.3736800 111.1741200 ,
15.3742000 111.1696300 ,
15.3748200 111.1651600 ,
15.3755400 111.1607000 ,
15.3765700 111.1551000 ,
15.3777500 111.1495300 ,
15.3883000 111.1029500 ,
15.3988500 111.0563700 ,
15.4003000 111.0503600 ,
15.4019300 111.0443900 ,
15.4037300 111.0384700 ,
15.4057000 111.0326200 ,
15.4079800 111.0264600 ,
15.4104400 111.0203800 ,
15.4131000 111.0143900 ,
15.4159300 111.0084900 ,
15.4176000 111.0051500 ,
15.4188200 111.0027600 ,
15.4244900 110.9917600 ,
15.4278400 110.9855400 ,
15.4310800 110.9799400 ,
15.4345000 110.9744400 ,
15.4380900 110.9690600 ,
15.4418300 110.9638000 ,
15.4735700 110.9206600 ,
15.4765800 110.9166700 ,
15.4796800 110.9127600 ,
15.4830500 110.9087100 ,
15.4865300 110.9047600 ,
15.4901000 110.9009000 ,
15.4937700 110.8971300 ,
15.4970900 110.8938000 ,
15.5023400 110.8887300 ,
15.5077600 110.8838600 ,
15.5138200 110.8788100 ,
15.5551900 110.8456800 ,
15.5606600 110.8414600 ,
15.5662600 110.8374300 ,
15.5719800 110.8335900 ,
15.5778400 110.8299600 ,
15.5823600 110.8273400 ,
15.5929000 110.8214200 ,
15.5970500 110.8191500 ,
15.6012600 110.8169800 ,
15.6046000 110.8153100 ,
15.6092400 110.8130600 ,
15.6139300 110.8109300 ,
15.6186700 110.8089300 ,
15.6234600 110.8070600 ,
15.6289200 110.8050900 ,
15.6344300 110.8032900 ,
15.6399900 110.8016500 ,
15.6455900 110.8001800 ,
15.6902800 110.7891400 ,
15.7349700 110.7780900 ,
15.7408100 110.7767400 ,
15.7466800 110.7755800 ,
15.7510000 110.7748400 ,
15.7553400 110.7741900 ,
15.7596900 110.7736400 ,
15.7640600 110.7732000 ,
15.7684300 110.7728500 ,
15.7728000 110.7726000 ,
15.7771900 110.7724400 ,
15.7815700 110.7723900 ,
15.7878200 110.7724800 ,
15.7940600 110.7727800 ,
15.8003000 110.7732700 ,
15.8065100 110.7739700 ,
15.8506100 110.7796200 ,
15.8556000 110.7803200 ,
15.8605700 110.7811600 ,
15.8637600 110.7817600 ,
15.9501000 110.7989000 ,
16.0364100 110.8160300 ,
16.1227300 110.8331600 ,
16.2090400 110.8502900 ,
16.2953600 110.8674200 ,
16.3816800 110.8845500 ,
16.4679900 110.9016800 ,
16.5543100 110.9188100 ,
16.6406100 110.9359300 ,
16.7269400 110.9530600 ,
16.8132600 110.9701900 ,
16.8995800 110.9873100 ,
16.9858900 111.0044400 ,
17.0722000 111.0215600 ,
17.1585400 111.0386900 ,
17.1649800 111.0400800 ,
17.1713700 111.0416900 ,
17.1777200 111.0435200 ,
17.1840000 111.0455700 ,
17.2531800 111.0693900 ,
17.2585600 111.0713400 ,
17.2626700 111.0729400 ,
17.2667400 111.0746400 ,
17.2707700 111.0764200 ,
17.2747700 111.0783000 ,
17.2787200 111.0802700 ,
17.2826300 111.0823300 ,
17.2865000 111.0844800 ,
17.2903200 111.0867100 ,
17.2960100 111.0902500 ,
17.3015700 111.0939800 ,
17.3070200 111.0979100 ,
17.3123400 111.1020100 ,
17.3537600 111.1351100 ,
17.3571200 111.1378600 ,
17.3604300 111.1406800 ,
17.3655400 111.1453000 ,
17.3705000 111.1500900 ,
17.3755000 111.1550900 ,
17.3793400 111.1590200 ,
17.3830700 111.1630600 ,
17.3867000 111.1672100 ,
17.3902100 111.1714500 ,
17.3930500 111.1750400 ,
17.3958000 111.1787000 ,
17.4275900 111.2218700 ,
17.4314800 111.2273300 ,
17.4351900 111.2329200 ,
17.4387300 111.2386300 ,
17.4420800 111.2444600 ,
17.4450800 111.2500700 ,
17.4507700 111.2610800 ,
17.4519900 111.2634800 ,
17.4569900 111.2734900 ,
17.4604300 111.2807000 ,
17.4636000 111.2880400 ,
17.4730500 111.3111300 ,
17.4748400 111.3156400 ,
17.4765200 111.3201800 ,
17.4848700 111.3435400 ,
17.4853600 111.3449200 ,
17.4867800 111.3491200 ,
17.4881100 111.3533500 ,
17.4925000 111.3677800 ,
17.4939700 111.3728200 ,
17.4991600 111.3915600 ,
17.5015600 111.4002300 ,
17.5022200 111.4026400 ,
17.5037200 111.4082700 ,
17.5040600 111.4096700 ,
17.5054300 111.4157500 ,
17.5055700 111.4163900 ,
17.5056600 111.4168100 ,
17.5099600 111.4369500 ,
17.5100200 111.4372000 ,
17.5101100 111.4377000 ,
17.5116600 111.4458700 ,
17.5117200 111.4461800 ,
17.5117400 111.4463100 ,
17.5118000 111.4466300 ,
17.5118900 111.4471700 ,
17.5134100 111.4562700 ,
17.5135000 111.4568400 ,
17.5135200 111.4569800 ,
17.5141300 111.4609100 ,
17.5142200 111.4615500 ,
17.5147600 111.4655600 ,
17.5148300 111.4661400 ,
17.5152900 111.4702300 ,
17.5153400 111.4707400 ,
17.5157100 111.4749100 ,
17.5157300 111.4750700 ,
17.5157600 111.4753500 ,
17.5171300 111.4926400 ,
17.5184600 111.5093500 ,
17.5189800 111.5180000 ,
17.5191500 111.5266700 ,
17.5190000 111.5346500 ,
17.5185600 111.5426100 ,
17.5178400 111.5505500 ,
17.5168200 111.5584600 ,
17.5108500 111.5988800 ,
17.5103300 111.6022400 ,
17.4973600 111.6813500 ,
17.4843900 111.7604200 ,
17.4714300 111.8394900 ,
17.4584600 111.9185500 ,
17.4455000 111.9976300 ,
17.4325300 112.0767000 ,
17.4195700 112.1557600 ,
17.4066100 112.2348300 ,
17.3936300 112.3139500 ,
17.3934500 112.3150600 ,
17.3928800 112.3182600 ,
17.3907600 112.3297100 ,
17.3892300 112.3372500 ,
17.3859000 112.3522700 ,
17.3845900 112.3578200 ,
17.3809100 112.3725400 ,
17.3796400 112.3773900 ,
17.3782500 112.3822000 ,
17.3732500 112.3988800 ,
17.3718000 112.4035100 ,
17.3702500 112.4081100 ,
17.3682000 112.4137300 ,
17.3659900 112.4192900 ,
17.3476200 112.4636500 ,
17.3452200 112.4692200 ,
17.3426600 112.4747200 ,
17.3399500 112.4801400 ,
17.3370800 112.4854800 ,
17.3367900 112.4860100 ,
17.3359500 112.4875000 ,
17.3337300 112.4913900 ,
17.3150200 112.5232400 ,
17.3128100 112.5269100 ,
17.2994700 112.5486000 ,
17.2954700 112.5548400 ,
17.2829700 112.5736200 ,
17.2785600 112.5799900 ,
17.2739300 112.5861800 ,
17.2668600 112.5952600 ,
17.2255700 112.6549000 ,
17.1809600 112.7193400 ,
17.1363500 112.7837900 ,
17.0917300 112.8482300 ,
17.0471200 112.9126800 ,
17.0024800 112.9771500 ,
16.9979800 112.9834100 ,
16.9932500 112.9894900 ,
16.9888900 112.9946900 ,
16.9843800 112.9997500 ,
16.9398700 113.0479100 ,
16.9360500 113.0519400 ,
16.9321300 113.0558500 ,
16.9281100 113.0596500 ,
16.9239900 113.0633300 ,
16.9183000 113.0681000 ,
16.9124600 113.0726500 ,
16.9057800 113.0776500 ,
16.9044900 113.0786100 ,
16.8953900 113.0853000 ,
16.8915100 113.0880900 ,
16.8875600 113.0907900 ,
16.8825600 113.0941100 ,
16.8803800 113.0955400 ,
16.8695200 113.1025600 ,
16.8636600 113.1062100 ,
16.8598400 113.1084400 ,
16.8559700 113.1105800 ,
16.8520600 113.1126300 ,
16.8481000 113.1146000 ,
16.8441100 113.1164700 ,
16.8400700 113.1182600 ,
16.8360000 113.1199500 ,
16.8319000 113.1215500 ,
16.8278200 113.1230300 ,
16.8237100 113.1244300 ,
16.8195700 113.1257300 ,
16.8154100 113.1269400 ,
16.7654400 113.1408500 ,
16.7154700 113.1547500 ,
16.6633300 113.1602400
))',3035)													
);

建表结果如下：

在postgresql通过Geometry Viewer可以直接查看一下这个Polygen

步骤二：空间查询

--执行空间查询 最终返回值为True
SELECT ST_Contains( (SELECT geom FROM data_analysis.polygen_test 
					 WHERE name = 'p1'),ST_GeomFromText('POINT(16.45 113.0)',3035));

返回值为true，说明该点在多边形内。

步骤三：QIS呈现

Layer - Add Layer - Add PostGIS Layer (快捷键 Ctrl  + Shift + D)添加对应图层，得到下图，可以清除看到点在多边形内。