PostGIS按ST_Distance排序-不按距离排序记录 [英] PostGIS Order by ST_Distance - not ordering records by distance

问题描述

我有一张规划应用程序的表，我试图将最近的5个应用程序返回到一个属性，该属性通过最近一次相距最近的距离(通过以JSON数组形式返回的PHP)通过Postgres查询进行排序.

I have a table of planning applications and I am trying to return the closest 5 applications to a property, ordered by the closest to farthest distance via a Postgres query through PHP returned as a JSON array.

结果已成功返回，但似乎未正确排序.起初我以为是因为ST_Distance的结果值是浮点型的，所以将它们强制转换为整数，但是它们仍然不按距离返回记录.

The results are successfully returned but they do not appear to be ordered correctly. At first I thought it was because the resulting values from the ST_Distance are float so casted them to an integer, but they still do not return the records by distance.

此SQL中有明显的错误吗?

Is anything obviously wrong in this 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'值不排序...

My resulting query result is below, you can see the 'dist' values do not order...

[
  {
    "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"
  }
]

推荐答案

如果您有能力直接从数据库中检索JSON:

If you can afford retrieving JSON directly from the database:

基于以下表结构...

Based on the following table structure ...

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

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

... containing the following records ...

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查询...

... with the following SQL Query ...

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输出:

... you can get a perfectly ordered JSON output:

     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}

这篇关于PostGIS按ST_Distance排序-不按距离排序记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！