PostGIS 查询点在线上

1、缓冲区法:查询数据库fm表里,与坐标(12989691.512 4798962.444)相距0.0001米的数据(3857坐标系)

SELECT id FROM fm where st_intersects(st_transform(st_buffer(st_transform(st_geomfromtext('point(12989691.512 4798962.444)',3857),3857),1),3857),geom) ;
--如果坐标系统一,不用transform也可以
SELECT id FROM fm where st_intersects(st_buffer(st_geomfromtext('point(12989691.512 4798962.444)'),0.0001),geom) ;

2、缓冲区法:查询fm表里,与点要素geometry相距0.0001米的要素

--geometry(例:0101000000D34D62709FC66841FA7E6A9C7C4E5241)
SELECT id FROM fm where st_intersects(st_buffer('0101000000D34D62709FC66841FA7E6A9C7C4E5241',0.0001),geom) ;

3、空间分析法:ST_Intersects查看相交

--带业务逻辑
select t.gid,t.geom from fm t where t.gid  in (
          select a.gid from fm a,(select c.* from zy c where c.gid = up_temprow.gid) b where ST_intersects(a.geom,b.geom) 
        )
--简化
select t.gid,t.geom from fm t where t.gid  in (
          select a.gid from fm a,zy b where ST_intersects(a.geom,b.geom) 
        )

 

posted @ 2019-10-11 17:54  苍龙de链  阅读(1568)  评论(0编辑  收藏  举报