October 19, 2009 at 6:00 pm
[font="Courier New"]
--> need to integrate with the 1st "zip code radius query" with the main 2nd query so I can get "all providers within a certain zip code radius"
SELECT t1.ZipCode, t1.City, t1.State, DistanceInMiles = dbo.fnDistance(t1.Latitude, t1.Longitude, t2.Latitude, t2.Longitude)
FROM dbo.ZipCodes t1
JOIN dbo.ZipCodes t2
ON (dbo.fnDistance(t1.Latitude, t1.Longitude, t2.Latitude, t2.Longitude) < 60)
WHERE t2.ZipCode='10023'
ORDER BY DistanceInMiles
==== HOW TO INTEGRATE WITH QUERY BELOW =====
SELECT * FROM
(
SELECT
TotalRows = COUNT(*) OVER(),
RowNum = ROW_NUMBER()OVER (ORDER BY P.ProviderID),
P.ProviderID, P.OccupationID, P.LastName, P.FirstName, O.OccupationName
FROM dbo.Providers P WITH (NOLOCK)
INNER JOIN dbo.Occupations O ON O.OccupationID = P.OccupationID
WHERE (P.OccupationID = 1)
AND P.Zip = '10023'
)
AS XYZ
WHERE RowNum BETWEEN 1 AND 8
ORDER BY RowNum ASC[/font]
October 19, 2009 at 11:37 pm
SELECT * FROM
(
SELECT
TotalRows = COUNT(*) OVER(),
RowNum = ROW_NUMBER()OVER (ORDER BY P.ProviderID),
P.ProviderID, P.OccupationID, P.LastName, P.FirstName, O.OccupationName
FROM dbo.Providers P WITH (NOLOCK)
INNER JOIN dbo.Occupations O ON O.OccupationID = P.OccupationID
WHERE (P.OccupationID = 1)
AND P.Zip IN
(
SELECT
t1.ZipCode
FROM
dbo.ZipCodes t1
JOIN
dbo.ZipCodes t2
ON (dbo.fnDistance(t1.Latitude, t1.Longitude, t2.Latitude, t2.Longitude) < 60)
WHERE
t2.ZipCode='10023'
)
)
AS XYZ
WHERE RowNum BETWEEN 1 AND 8
..
October 20, 2009 at 12:01 am
Thank you very much! matt
October 20, 2009 at 1:23 am
You still seem to be missing the main performance point , you need to set a limit on the long / lat.
Looking at your query plan in one of your other posts, im guessing that you have a very small data load on this table right now. How many rows are in your ZipCode table ? What percentage of a production load is that ?
October 21, 2009 at 3:58 pm
Thanks Dave - My zip code table has about 40,000 records. I now understand and implemented a "latitude limiter"
DECLARE @start_lat float
DECLARE @MaxNorthLat float
DECLARE @MaxSouthLat float
SELECT @start_lat = (SELECT Z.Latitude FROM dbo.ZipCodes Z WHERE Z.ZipCode = @zip)
SET @MaxNorthLat = @start_lat + (@radius*360/24859.82)
SET @MaxSouthLat = @start_lat - (@radius*360/24859.82)
Then I use the MaxNorthLat and MaxSouthLat in my WHERE clause. The query seems pretty fast (50ms) , so I didn't limit longitude (yet) because it seems more complicated than latitude. So finally i think understand what you mean! thanks, matt, NYC
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply