Original query:
select
(
select array
(
-- << — Returns TRUE if A's bounding box is strictly to the left of B's.
-- <<| — Returns TRUE if A's bounding box is strictly below B's.
-- >> — Returns TRUE if A's bounding box is strictly to the right of B's.
-- |>> — Returns TRUE if A's bounding box is strictly above B's.
-- &< — Returns TRUE if A's bounding box overlaps or is to the left of B's.
-- &<| — Returns TRUE if A's bounding box overlaps or is below B's.
-- &> — Returns TRUE if A' bounding box overlaps or is to the right of B's.
-- |&> — Returns TRUE if A's bounding box overlaps or is above B's.
--south east; includes north middle line
(
select g_sector.geonameid from geo.geonamegeoindexed_5 as g_sector
where
1=1
and g_sector.fcode in ('PPL', 'PPLA', 'PPLA2', 'PPLA3', 'PPLA4', 'PPLC')
and g_sector.country = g.country
and g_sector.coordinates &< g.coordinates -- overlaps or is to the left
and g_sector.coordinates <<| g.coordinates -- strictly below
order by g_sector.coordinates <-> g.coordinates
-- order by ST_Distance(g_sector.coordinates, g.coordinates, false)
limit 5
)
union
--north east; includes east middle line
(
select g_sector.geonameid from geo.geonamegeoindexed_5 as g_sector
where
1=1
and g_sector.fcode in ('PPL', 'PPLA', 'PPLA2', 'PPLA3', 'PPLA4', 'PPLC')
and g_sector.country = g.country
and g_sector.coordinates << g.coordinates -- strictly left
and g_sector.coordinates |&> g.coordinates -- overlaps or is above
order by g_sector.coordinates <-> g.coordinates
limit 5
)
union
--north west; includes north middle line
(
select g_sector.geonameid from geo.geonamegeoindexed_5 as g_sector
where
1=1
and g_sector.fcode in ('PPL', 'PPLA', 'PPLA2', 'PPLA3', 'PPLA4', 'PPLC')
and g_sector.country = g.country
and g_sector.coordinates &> g.coordinates -- overlaps or is to the right
and g_sector.coordinates |>> g.coordinates -- strictly above
order by g_sector.coordinates <-> g.coordinates
limit 5
)
union
--south-west; includes south middle line
(
select g_sector.geonameid from geo.geonamegeoindexed_5 as g_sector
where
1=1
and g_sector.fcode in ('PPL', 'PPLA', 'PPLA2', 'PPLA3', 'PPLA4', 'PPLC')
and g_sector.country = g.country
and g_sector.coordinates >> g.coordinates -- strictly right
and g_sector.coordinates &<| g.coordinates -- overlaps or is below
order by g_sector.coordinates <-> g.coordinates
limit 5
)
)
)
from geo.geonamegeoindexed_5 as g
where
g.fcode in ('PPL', 'PPLA', 'PPLA2', 'PPLA3', 'PPLA4', 'PPLC')
limit 1000;