1

Is there a way I can remove the center point from my results in a query like the following?

(select * from cities 
where l = true
and ST_DWithin(geography(cities.geog), (SELECT geog FROM cities WHERE cities.i = 'HNL'), 120000)
LIMIT 25)
UNION
(select * from cities 
where l = false
and ST_DWithin(geography(cities.geog), (SELECT geog FROM cities WHERE cities.i = 'HNL'), 200000)
LIMIT 5);

EDIT: ah. this seems to work:

(select * from cities 
where l = true
and ST_DWithin(geography(cities.geog), (SELECT geog FROM cities WHERE cities.i = 'HNL'), 120000)
and geog != (SELECT geog FROM cities WHERE cities.i = 'HNL')
LIMIT 25)
UNION
(select * from cities 
where l = false
and ST_DWithin(geography(cities.geog), (SELECT geog FROM cities WHERE cities.i = 'HNL'), 200000)
and geog != (SELECT geog FROM cities WHERE cities.i = 'HNL')
LIMIT 5);

but is this the fastest way? I can probably swap out the sub-queries when I call this function from within express.js

  • 2
    Yes, try using a CTE, ie move the (SELECT geog FROM cities WHERE cities.i = 'HNL') to an initial WITH query. There may be other optimizations, it's a bit hard to tell what your query is trying to do, with the different LIMITS and search paramters to WITHIN. – John Powell Feb 7 at 7:26
2

So you want to get all cities in the proximity of 'HNL' (with your conditions), but not 'HNL' itself I take it?

Try (with a CTE, as @JohnPowell mentioned)

WITH
  hnl AS (
    SELECT i,
           geog
    FROM   cities
    WHERE  i = 'HNL'
  )

SELECT a.*
FROM   cities AS a
JOIN   hnl AS b
  ON   ST_DWithin(a.geog, b.geog, 120000)
 AND   a.i != b.i
WHERE  l = true
LIMIT  25
UNION ALL
SELECT a.*
FROM   cities AS a
JOIN   hnl AS b
  ON   ST_DWithin(a.geog, b.geog, 200000)
 AND   a.i != b.i
WHERE  l = false
LIMIT  5;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.