0

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

1 Answer 1

0

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
4
  • This seem to be working but its very slow(10 min and still going) . I have about 250k records in cities and about 200k listings. Anything else I can do to speed this query up?
    – Zoinky
    Sep 6, 2020 at 14:51
  • Add the execution plan to your question. brentozar.com/pastetheplan Sep 6, 2020 at 15:08
  • Or try the alternative in the updated answer. Sep 6, 2020 at 15:24
  • I have provided the execution plan, it actually takes 14:35, the alternative query you provided takes 35min and counting (going now)
    – Zoinky
    Sep 6, 2020 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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