Get count of near points based on 2 unrelated tables

Question

I have 2 tables, cities and listings and there is not FK relationship or any other field that I can create a relationship based on. They both have a geography field.

I would like to get list of all cities that have at least 1 listing (using Location property of both tables) When I run the following query, I get cities with count of 1, multiple times. I simply want to get cities with number of listings near it as the count.

DECLARE @distance FLOAT = 100000;
SELECT c.name, count(l.ListingID) FROM listings l, Cities c
WHERE l.Location.STDistance(c.Location) <= @distance
GROUP BY c.name, l.listingid

I also have a 16x16x16x16 and 256x256x256x256 indexes on both tables for Location field if that matters...

I am using SQL Server 2019

EDIT: indexes on both tables

CREATE SPATIAL INDEX [256_HHHH] ON [dbo].[Listings]
(
    [Location]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
CELLS_PER_OBJECT = 256, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE SPATIAL INDEX [16_HHHH] ON [dbo].[Listings]
(
    [Location]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Actual Execution Plan https://www.brentozar.com/pastetheplan/?id=rJ2HftM4v

Answer 1

simply want to get cities with number of listings near it as the count.

Then don't GROUP BY listingid eg

DECLARE @distance FLOAT = 100000;
SELECT c.name CityName, count(l.ListingID) Listings 
FROM listings l, Cities c
WHERE l.Location.STDistance(c.Location) <= @distance
GROUP BY c.name

Or you might try something like:

DECLARE @distance FLOAT = 100000;
SELECT c.name CityName, 
(
  select count(*) 
  FROM listings l
  WHERE c.Location.STDistance(l.Location) <= @distance
) Listings
FROM Cities c