September 2, 2016 at 6:53 am
I have two sets of data that have geographic data associated with them. One is a set of customers, one is a set of stores. The Idea is to look at the customer data set, and then match it against the stores data set to find the two closest stores. Right now, I am using a cursor to go through all of the customers data set and using STDistance to find the distance, putting the customer record, the store number and the distance into a temp table, then using a ROW_NUMBER() query to get the two with the shortest distance. Is there a better method to do this? I have been dealing with smaller data sets (about 1,000 rows) but now will be working with much larger data sets, and am concerned about performance.
September 2, 2016 at 7:12 am
Steven.Grzybowski (9/2/2016)
I have two sets of data that have geographic data associated with them. One is a set of customers, one is a set of stores. The Idea is to look at the customer data set, and then match it against the stores data set to find the two closest stores. Right now, I am using a cursor to go through all of the customers data set and using STDistance to find the distance, putting the customer record, the store number and the distance into a temp table, then using a ROW_NUMBER() query to get the two with the shortest distance. Is there a better method to do this? I have been dealing with smaller data sets (about 1,000 rows) but now will be working with much larger data sets, and am concerned about performance.
care to share what you have tried so far....along with some easy to follow scripts that create tables / insert some sample data
as per this article https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/%5B/url%5D%5B/b%5D
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 2, 2016 at 7:30 am
Maybe this could help.
CREATE TABLE Customers( id int, Point Geography);
INSERT INTO Customers
SELECT 1, geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
CREATE TABLE Stores( id int, Point Geography);
INSERT INTO Stores
SELECT 1, geography::STGeomFromText('POINT(-125.34900 47.65100)', 4326) UNION ALL
SELECT 2, geography::STGeomFromText('POINT(-135.34900 47.65100)', 4326) UNION ALL
SELECT 3, geography::STGeomFromText('POINT(-122.34900 48.65100)', 4326);
SELECT *
FROM Customers c
CROSS APPLY( SELECT TOP 2 *, c.Point.STDistance(s.Point) As distance
FROM Stores s
ORDER BY distance)s;
GO
DROP TABLE Customers, Stores;
September 2, 2016 at 7:40 am
If I am not following the guidelines exactly, I apologize, I am blocked from seeing that article at my workplace.
--Sample Tables
CREATE TABLE Customers
(CustomerID BIGINT, Customername varchar(50), Customeraddress Varchar(100), Latitude float , Longitude float )
Create TABLE Stores
(Storenumber INT, Latitude float, Longitude float, SPOINT Geography )
--Temp Table for Distances
CREATE Table #DISTTEMP
(CustomerID BIGINT, Storenumber INT, Distance float)
Declare @ID BIGINT
Declare @CustPoint geography
DECLARE Distancefinder cursor FOR SELECT CustomerID FROM Customers
Open DistanceFinder
FETCH NEXT FROM Distancefinder into @ID
WHILE @@FETCH_STATUS = 0
BEGIN
set @CustPoint = (SELECT geography::Point(Latitude, Longitude, 4326) FROM Customers WHERE CustomerID = @ID)
INSERT INTO #DISTTEMP
(CustomerID, Storenumber, Distance)
SELECT @ID CustomerID, Storenumber, @CustPoint.STDistance(SPOINT) As Distance FROM Stores
FETCH NEXT FROM DistanceFinder INTO @ID
END
Close DistanceFinder
Deallocate DistanceFinder
SELECT CustomerID, Storenumber, Distance FROM
(
SELECT CustomerID, Storenumber, Distance, ROW_NUMBER() OVER (PARTITION BY CUstomerID Order BY Distance) RN FROM #DISTTEMP
) A Where A.RN < 3
September 2, 2016 at 10:57 am
Luis Cazares (9/2/2016)
Maybe this could help.
CREATE TABLE Customers( id int, Point Geography);
INSERT INTO Customers
SELECT 1, geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
CREATE TABLE Stores( id int, Point Geography);
INSERT INTO Stores
SELECT 1, geography::STGeomFromText('POINT(-125.34900 47.65100)', 4326) UNION ALL
SELECT 2, geography::STGeomFromText('POINT(-135.34900 47.65100)', 4326) UNION ALL
SELECT 3, geography::STGeomFromText('POINT(-122.34900 48.65100)', 4326);
SELECT *
FROM Customers c
CROSS APPLY( SELECT TOP 2 *, c.Point.STDistance(s.Point) As distance
FROM Stores s
ORDER BY distance)s;
GO
DROP TABLE Customers, Stores;
It required a tiny bit of modification, but the cross JOIN results ended up matching up with my Cursors results. Thanks.
September 2, 2016 at 11:44 am
Can you share your solution?
I didn't use a cross join, I used a cross apply which is different.
September 2, 2016 at 12:47 pm
That was a typo, I meant cross apply, and as far as modifications, it just had to do with different table schemas than what I posted here- more fileds than I posted for confidentiality reasons.
September 2, 2016 at 4:35 pm
If you are going to be matching a large number of stores to a large number of customers, you are going to want to create spatial indexes for performance reasons.
To take advantage of a spatial index, Luis' query should be modified slightly:
https://msdn.microsoft.com/en-us/library/ff929109.aspx%5B/url%5D
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply