How to Find Buildings Within a 5-Mile Radius Using Coordinates?

Locating Buildings Within a 5-Mile Radius via Coordinates

This guide details how to efficiently retrieve all buildings within a 5-mile radius of given coordinates.

Step 1: Optimize Data Storage

Store building location data using a geometry or geography data type. This significantly improves spatial query performance and prevents data type conversion issues.

Step 2: Employ ST_DWithin or ST_Distance

Two functions facilitate distance calculations:

1. ST_DWithin: This function efficiently determines if geometries fall within a specified distance. To find buildings within 5 miles of (-4.6314, 54.0887):

   <code class="language-sql">SELECT name, longitude, latitude
   FROM building
   WHERE ST_DWithin('POINT(-4.6314 54.0887)'::geography, ST_MakePoint(longitude, latitude), 8046.72);
   -- 8046.72 meters = 5 miles</code>

2. ST_Distance: This function calculates the distance in meters. Convert meters to miles using: distance * 0.000621371. For buildings within 5 miles of (-4.6314, 54.0887):

   <code class="language-sql">SELECT name, longitude, latitude,
   ST_Distance('POINT(-4.6314 54.0887)'::geography, ST_MakePoint(longitude, latitude)) * 0.000621371 AS distance_miles
   FROM building
   WHERE distance_miles <= 5;</code>

Step 3: Key Considerations

• Maintain the correct order in ST_MakePoint: longitude, latitude.
• ST_Distance calculations bypass spatial indexes. Use it in the SELECT clause to avoid performance bottlenecks.
• Amazon Athena uses degrees for distance calculations in ST_Distance.

The above is the detailed content of How to Find Buildings Within a 5-Mile Radius Using Coordinates?. For more information, please follow other related articles on the PHP Chinese website!