Geography_Distance.sql

--> Jalal Hejazi STDistance using The Great Circle Formular with SQL Spatial GeoType

-- OpenSource GEO DB
-- http://workshops.opengeo.org/postgis-intro/geometries_exercises.html
-- http://workshops.opengeo.org/postgis-intro/geography.html

--> SQLServer Spatial DB
--> http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.types.sqlgeography.stdistance.aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-1

/*
  SQL Server 2008/2012
	Using the spacial function STDistance return distance in meters

	geography::Point(@lat1, @lon1, 4326).STDistance(geography::Point(@lat2, @lon2, 4326))
*/



--> SQLSERVER STDistance using Great Circle Formular

CREATE FUNCTION dbo.DictanceKM(@lat1 FLOAT, @lat2 FLOAT, @lon1 FLOAT, @lon2 FLOAT)
	RETURNS FLOAT 
AS
	BEGIN
	    RETURN ACOS(SIN(PI()*@lat1/180.0)*SIN(PI()*@lat2/180.0)+COS(PI()*@lat1/180.0)*COS(PI()*@lat2/180.0)*COS(PI()*@lon2/180.0-PI()*@lon1/180.0))*6371
	END
GO



SELECT
  Name,
  Address,
  City,
  State,
  Latitude,
  Longitude,
  (
    ACOS(
      COS(@center_latitude 	* (PI()/180)) *
      COS(@center_longitude * (PI()/180)) *
      COS(Latitude * (PI()/180)) *
      COS(Longitude * (PI()/180)) +
      COS(@center_latitude * (PI()/180)) *
      SIN(@center_longitude * (PI()/180)) *
      COS(Latitude 	* (PI()/180)) *
      SIN(Longitude * (PI()/180)) +
      SIN(@center_latitude * (PI()/180)) *
      SIN(Latitude * (PI()/180))
    ) *
    (
      (@equatorial_radius * @polar_radius) /
      (
        SQRT(
          (@equatorial_radius * @equatorial_radius) -
          (
            (
              (@equatorial_radius * @equatorial_radius) -
              (@polar_radius * @polar_radius)
            ) *
            (
              COS(@center_latitude) *
              COS(@center_latitude)
            )
          )
        )
      )
    )
  ) AS Miles
FROM
  Places
WHERE
  Miles <= @search_radius

 GO


--> Fing Point of Interessts around a radius of 1KM of a location at any given POINT(longitude latitude)
--> STDistance or UDF using the great circle formular

Declare @CodeGEOG geography = geography::STGeomFromText('Point(longitude latitude)',4326);

Select top(5) 
	addr.AddressID as id ,
	addr.addressline1,
	addr.city,
	addr.SpatialLocation, 				-- Will show the raw data (WKB)
	addr.SpatialLocation.AsGml() 			as SpationalGML,
	addr.SpatialLocation.STAsText() 		as SpatialText, --> WKT
	addr.SpatialLocation.STGeometryType() 		as GeoType,
	addr.SpatialLocation.STNumPoints() 		as Points,
	addr.SpatialLocation.STDistance(@CodeGEOG) 	as distance_in_meters,
	
	from dbo.Address addr
	Where addr.SpatialLocation.STDistance(@CodeGEOG) < 1000 --> find places around 1KM radius

GO



--*********************************************************************************
-- Optimal Performance Query of K-Search algorithm
--> http://en.wikipedia.org/wiki/K-nearest_neighbor_algorithm
-- Ref. http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx
-- http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor_generic
--*********************************************************************************

DECLARE @start FLOAT = 1000; 
 
WITH NearestPoints AS
(
   SELECT TOP(1) WITH TIES *,  T.g.STDistance(@x) AS dist
   FROM Numbers JOIN T WITH(INDEX(spatial_index)) 
   ON T.g.STDistance(@x) < @start*POWER(2,Numbers.n)
   ORDER BY n
)
SELECT TOP(1) * FROM NearestPoints
ORDER BY n, dist
GO