PostGIS算法示例

最新推荐文章于 2024-07-23 15:42:09 发布

GIS开发者

最新推荐文章于 2024-07-23 15:42:09 发布

阅读量1.7k

点赞数 1

分类专栏： postGIS 文章标签：算法

本文链接：https://blog.csdn.net/GISuuser/article/details/79102591

版权

postGIS 专栏收录该内容

36 篇文章 21 订阅

订阅专栏

1 点的最近点查询

select smoke, ST_AsGeoJSON(geom) as geom, company, address from power t order by ST_Distance(t.geom,ST_GeometryFromText('POINT(113.77990722656251 34.63320791137959)',4326))  limit 1

4326为坐标系编号；原理是根据距离找最近的一个记录

2.根据点在数据库里查询在哪个多边形中，11万条记录只需要94ms

SELECT * from dt_cy where ST_Contains(geom, st_geometryfromtext('POINT(113.458729 34.816974)',4326));

3.使用knn(最近邻法)，计算距离多边形最近的点，测试结果174ms

select * from teatcyd ORDER BY teatcyd.geom <-> (SELECT geom from dt_cy where gid =74833) limit 1

4.创建索引（通用索引），添加后执行3直接变为100ms

CREATE INDEX teatcyd_geom_idx ON teatcyd USING GIST (shape);

5. 给表添加空间字段

SELECT AddGeometryColumn ('dt_yzebdb', 'shape', 4326, 'POINT', 2);

6.计算多个点的中心点

SELECT ST_Centroid(ST_Collect(ARRAY(select shape from dt_yzebdb)))

7.将查询结果输出为geojson

SELECT
        row_to_json ( fc )
        FROM
        (
        SELECT
        'FeatureCollection' AS TYPE,
        array_to_json ( ARRAY_AGG ( f ) ) AS features
        FROM
        (
        SELECT
        'feature' AS TYPE,
        ST_AsGeoJSON ( shape ) :: json AS geometry,
        ( SELECT row_to_json ( T ) FROM ( SELECT area_type,objectid ) AS T ) AS properties
        FROM
        dtwx_farms
        WHERE  shape IS NOT NULL
        ) AS f
        ) AS fc