SELECT c.CustomerID, c.PersonID AS BusinessEntityID, p.FirstName AS Name1, p.LastName AS Name2, ea.EmailAddress, ad.City, ad.PostalCode,
sp.Name AS StateProvince, sp.StateProvinceCode, ad.SpatialLocation.STDistance(@GeoLocation) / 1609.344 AS DistanceinMiles, ad.SpatialLocation,
p.Demographics.value('declare namespace awns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; (awns:IndividualSurvey/awns:NumberCarsOwned) [1]',
'int') AS NumberCarsOwned,
p.Demographics.value('declare namespace awns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; (awns:IndividualSurvey/awns:CommuteDistance) [1]',
'varchar(30)') AS CommuteDistance
FROM Sales.Customer AS c INNER JOIN
Person.Person AS p ON p.BusinessEntityID = c.PersonID INNER JOIN
Person.BusinessEntityAddress AS a ON a.BusinessEntityID = p.BusinessEntityID INNER JOIN
Person.AddressType AS t ON a.AddressTypeID = t.AddressTypeID INNER JOIN
Person.Address AS ad ON ad.AddressID = a.AddressID INNER JOIN
Person.EmailAddress AS ea ON ea.BusinessEntityID = p.BusinessEntityID INNER JOIN
Person.StateProvince AS sp ON sp.StateProvinceID = ad.StateProvinceID
WHERE (c.StoreID IS NULL) AND (t.Name = N'Home') AND (sp.CountryRegionCode = N'US') AND (ad.SpatialLocation.STDistance(@GeoLocation) / 1609.344 < @Radius)

SELECT
[StoreID] as BusinessEntityID
, s.Name as Store
-- , t.Name as AddressType -- Main Office or Shipping
, ad.PostalCode
-- , sp.CountryRegionCode
--, sp.Name as StateProvince
--, sp.StateProvinceCode
, ad.SpatialLocation.ToString() as GeoLocation
FROM [Sales].[Customer] c
INNER JOIN Sales.Store s ON s.BusinessEntityID = c.StoreID
INNER JOIN Person.BusinessEntityAddress a ON a.BusinessEntityID = s.BusinessEntityID
INNER JOIN Person.AddressType t ON a.AddressTypeID=t.AddressTypeID
INNER JOIN Person.[Address] ad ON ad.AddressID = a.AddressID
INNER JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID
WHERE -- PersonID IS NULL means Customer is a store
PersonID IS NULL
AND t.Name = N'Main Office'
AND sp.CountryRegionCode = N'US'