CODEWITHSUNDEEP
sqlpostgresqlcastingsql-order-bypostgis
Chris
Chris

Reputation: 677

PostGIS Order by ST_Distance - not ordering records by distance

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.

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.

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();

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

Upvotes: 0

Views: 2486

Answers (2)

Jim Jones
Jim Jones

Reputation: 19653

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)

... 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;

... 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}

Upvotes: 2

Chris
Chris

Reputation: 677

I realised that the issue was with a subsequent bit of code where I was sorting the array after fetching it.

As it was already being sorted, I just removed this and now it sorts correctly.

Upvotes: 0

Related Questions