POSTGIS 常用查询

--随机抽样语句, 抽样比 10%, 并且限制 1000条
SELECT * FROM xxx_table tablesample bernoulli(0.1) limit 1000;
 
 
--按 geometry + 半径100米查询
SELECT name, address,geometry FROM xxx_table WHERE name = '广发汽车美容装饰' and
ST_DWithin(
    geometry::geography,
    '0101000020E6100000C72C7B12D88F5B40BA6587F887A14340'::geography,
    100)
 
 
--按经纬度点+500米半径范围查询
SELECT * FROM xxx_table WHERE
ST_DWithin(geometry::geography, ST_SetSRID(ST_MakePoint(121.809008,31.142393),4326)::geography, 500);

 
--geometry 转成经纬度
select st_astext('0101000020E61000003DD68C0C728A5C4044A67C08AAB23640'::geography)
 
 
--查询两个geometry之间的距离
select ST_DISTANCE(
'0101000020E61000003DD68C0C728A5C4044A67C08AAB23640'::geography,
'0101000020E61000009E5C5320B38A5C403620425C39B33640'::geography)