X893

Find near building address from GPS position

Click on map to find nearest building

How it work. Two tables on MS SQL 2005 and two functions.


CREATE TABLE [dbo].[street]
(
[name] [nvarchar](32) NULL,
[street_id] [int] NOT NULL,
CONSTRAINT [PK_street] PRIMARY KEY CLUSTERED
(
[street_id] ASC
)
)

CREATE TABLE [dbo].[house]
(
[house_id] [int] IDENTITY(1,1) NOT NULL,
[street_id] [int] NULL,
[name] [nvarchar](20) NULL,
[longitude] [real] NULL,
[latitude] [real] NULL,
CONSTRAINT [PK_house] PRIMARY KEY CLUSTERED
(
[house_id] ASC
)
)


and two functions


-- LAT and LON in degrees
CREATE FUNCTION [dbo].[GeoDistance]
(
@DLAT1 REAL,
@DLON1 REAL,
@DLAT2 REAL,
@DLON2 REAL
)
RETURNS REAL
AS
BEGIN
DECLARE @LAT1 REAL, @LAT2 REAL, @LON1 REAL, @LON2 REAL
DECLARE @PI REAL, @RADIUS REAL, @DLON REAL, @DLAT REAL, @AX REAL, @ANGLE REAL, @D REAL

SET @PI = 3.141592653589793
SET @RADIUS = 6378.137
SET @LAT1 = @DLAT1 * @PI / 180.0
SET @LAT2 = @DLAT2 * @PI / 180.0
SET @LON1 = @DLON1 * @PI / 180.0
SET @LON2 = @DLON2 * @PI / 180.0

SET @DLON = @LON2 - @LON1
SET @DLAT = @LAT2 - @LAT1
SET @AX = SIN(@DLAT / 2) * SIN(@DLAT / 2) + COS(@LAT1) * COS(@LAT2) * (SIN(@DLON / 2) * SIN(@DLON / 2))
SET @ANGLE = 2000.0 * ATN2(SQRT(@AX), SQRT(1.0 - @AX))
SET @D = @ANGLE * @RADIUS
RETURN @D
END

-- LAT, LON in degrees
-- RADIUS_METERS in meters
CREATE FUNCTION [dbo].[NearBuilding]
(
@LAT REAL,
@LON REAL,
@RADIUS_METERS REAL
)
RETURNS TABLE
AS
RETURN
(
SELECT TOP 1
house_id, latitude, longitude,
ROUND(dbo.GeoDistance(@LAT, @LON, latitude, longitude), 2) [distance],
(street.name + ', ' + house.name) [address]
FROM
house, street
WHERE
ABS(latitude - @LAT) <= (0.001 * @RADIUS_METERS / 111.3888)
AND ABS(longitude - @LON) <= (0.001 * @RADIUS_METERS / 63.67195)
AND house.street_id = street.street_id
ORDER BY
dbo.GeoDistance(@LAT, @LON, latitude, longitude)
)


Use next SQL to fetch near building from front-end


SELECT * FROM dbo.NearBuilding(55.779125213623, 37.6269378662109, 100)

0 comment(s) so far

Post your comment

Thanks for your comments

  • Comment

Github
Bitbucket
Codeplex
SF.net

Status

Subscribe to x893 blog Subscribe to x893 blog