sql - PostGIS 按 ST_Distance 排序 - 不按距离排序记录

Question

我有一个规划应用程序表，我正在尝试将最近的 5 个应用程序返回到一个属性，通过 PHP 返回的 Postgres 查询按最近到最远的距离排序作为 JSON 数组。

结果已成功返回，但它们的排序似乎不正确。起初我以为是因为 ST_Distance 的结果值是 float ，所以将它们转换为整数，但它们仍然没有按距离返回记录。

这个 SQL 有什么明显的错误吗？

// Search the database for all similar items
    $sql = pg_query($conn, "SELECT DISTINCT b.reference, b.application_number, b.site_address, b.proposal, cast((st_distance(a.geom, b.geom)) as integer) as dist FROM addresses.llpg_standard a, planning.planning_applications_current b WHERE a.uprn = $query ORDER BY dist ASC LIMIT 5");
    $array = array();

我的查询结果如下，你可以看到'dist'值不排序......

[
  {
    "reference": "228028",
    "application_number": "RU.17\/1320",
    "site_address": "64 The Avenue\r\nEgham\r\nTW20 9AD",
    "proposal": "Application seeking approval of details pursuant to condiitions 2 (Materials), 3 (Surfacing Materials), 4 (Tree Portection), 10 (Construction Transport Management Plan), 12 (Travel Plan), 13 (Construction Management Plan), 21 (Gas Prroof Membrane), 23 (Archaeology), (24( Flood Risk Management Plan), 28 (CEMP) of planning permission RU.16\/1453 (80 bed Care Home, Ancillary facilities and retention of 64 The Avenue).",
    "dist": "120"
  },
  {
    "reference": "228568",
    "application_number": "RU.17\/1303",
    "site_address": "64 The Avenue\r\nEGHAM\r\nTW20 9AD",
    "proposal": "Proposed three storey 80 bed care home with ancillary facilities in the roof space.",
    "dist": "120"
  },
  {
    "reference": "233449",
    "application_number": "RU.17\/1820",
    "site_address": "Egham Leisure Centre\r\nVicarage Road\r\nEGHAM\r\nTW20 8NL",
    "proposal": "Variation of conditions 2 (approved plans), 5 ( Sustainable Drainage Plan) and 29 ( Flood Risk Assessment) of RU.17\/0488",
    "dist": "280"
  },
  {
    "reference": "236908",
    "application_number": "RU.18\/0089",
    "site_address": "Egham Leisure Centre\r\nVicarage Road\r\nEGHAM\r\nTW20 8NL",
    "proposal": "Details pursuant to Condition 28 (Replacement Temporary Bin Storage) of planning permission RU.17\/0488 (Demolition of existing leisure centre and erection of replacement leisure centre (Use Class D2); with 1no. outdoor synthetic sports pitch with associated fencing and lighting columns; new service access off Vicarage Road; alterations to the existing car park including overflow area; landscaping and public realm works; installation of a substation; and associated works). ",
    "dist": "280"
  },
  {
    "reference": "239748",
    "application_number": "RU.18\/0388",
    "site_address": "1 Vicarage Crescent\r\nEGHAM\r\nTW20 9JP",
    "proposal": "Proposed lawful development Certificate to establish whether planning permission is required for a rear extension",
    "dist": "18"
  }
]

Answer 1

如果您负担得起直接从数据库中检索 JSON:

基于如下表结构...

CREATE TEMPORARY TABLE tmp_geo (id SERIAL, geom GEOMETRY);

...包含以下记录...

id |            geom            
----+----------------------------
  1 | POINT(14.0025 51.056388)
  2 | POINT(14.57249 52.04263)
  3 | POINT(10.7809 51.983199)
  4 | POINT(14.114722 51.067777)
  5 | POINT(14.14779 51.28735)
  6 | POINT(10.546666 51.506111)
  7 | POINT(14.650439 51.88293)
  8 | POINT(9.82623 48.215339)
  9 | POINT(14.408049 52.700519)
 10 | POINT(8.47574 51.023049)

... 使用以下 SQL 查询 ...

SELECT row_to_json(j)
FROM (
   SELECT id,CAST(ST_Distance(ST_GeomFromText('POINT(50.12 8.69)',4326),geom) AS INTEGER) AS dist
   FROM tmp_geo
   ORDER BY dist ASC
) j;

...您可以获得完美有序的 JSON 输出:

     row_to_json     
---------------------
 {"id":4,"dist":56}
 {"id":7,"dist":56}
 {"id":2,"dist":56}
 {"id":8,"dist":56}
 {"id":5,"dist":56}
 {"id":1,"dist":56}
 {"id":9,"dist":57}
 {"id":6,"dist":58}
 {"id":3,"dist":58}
 {"id":10,"dist":59}