calculate all latitude and longitudes in a metric area

Question

I know there are many threads about calculating from lat/long to metric systems. This solves the first part of my problem.

The second (main part) of my problem is this:

• I have google maps as map app.
• I have a lat&long pair of a point
• I have a a metric distance, lets say 30meters.
• I need some point to do a sql query.

Why?

I have many records of POIs which can be located with this lat&long pair. I don't want to get all the results (reason: better performance).

Is there a way to do something like:

select * from myTable where lat> .... and lat < ... and long > ... and long < ...?

Is this possible? And if its, how do I get these values in ... ?

Regards!

Answer 1

Take a look at the article: Selecting points within a bounding circle

and the SO question: SQL Query for Performing Radius Search based on Latitude Longitude

These should get you started.

Answer 2

here is a sample query that returns the distances between a supplier lat/lng and a database of lat/lng pairs:

DECLARE
@Latitude float,
@Longitude float

SET @Latitude = 46.51257000000000
SET @Longitude = -84.33660900000000

DECLARE @SampleData TABLE
(
Name varchar(100),
Lat float,
Lng float
)

INSERT INTO @SampleData
(Name, Lat, Lng)
SELECT 'Point1', 46.52450048415351,-84.35223018530274
UNION
SELECT 'Point2', 46.51835838382206,-84.30279170874024
UNION
SELECT 'Point3', 46.522138220045285,-84.2622796237793
UNION
SELECT 'Point4', 46.54056115608927,-84.223140829834

SELECT 
    Name,
    Lat,
    Lng,
    -- using spherical law of cosines, where 6371 is the earth's radius in km. Returns km.
    Distance = ACOS(SIN(RADIANS(@Latitude))*SIN(RADIANS(Lat))+COS(RADIANS(@Latitude))*COS(RADIANS(Lat))*COS(RADIANS(Lng-@Longitude)))*6371  
FROM 
    @SampleData