postrges如何查询当前位置是否在gis的围栏中

zhangfanglin

1、安装postgres扩展postgis

$ sudo apt-get install postgis

2、进入数据库

$ sudo -u postgres psql

3、数据库添加扩展

# 添加空间插件
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

4、sql方法使用

SELECT  ST_Contains( ST_MakePolygon(ST_GeomFromText('LINESTRING ( 121.312350 30.971457 , 121.156783 31.092221 , 121.353250 31.278195 , 121.509125 31.157431 , 121.312350 30.971457 ) ')) ,st_point(121.632378,31.07106) );

5、例子

select * from	table where 
ST_Contains( ST_MakePolygon(ST_GeomFromText('LINESTRING (121.312350 30.971457 , 121.156783 31.092221 , 121.353250 31.278195 , 121.509125 31.157431 , 121.312350 30.971457) ')),st_point(CAST(mobile_message_intro.position::json->>0 as DECIMAL(10,2)),CAST(mobile_message_intro.position::json->>1 as DECIMAL(10,2))) )=true