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

Use coordinates to search for buildings within a specified radius

When processing geographic data, you usually need to find objects within a certain range of a specific location. This is particularly useful for tasks such as finding buildings within a certain radius of a specific point. In this article, we'll focus on finding all buildings within 5 miles of a given set of coordinates.

Data structure

Our database table is named "Building" and contains the following necessary information:

• name: building name
• lat: latitude coordinate
• lng: longitude coordinates

Challenge

We want to retrieve all buildings that fall within a 5 mile radius of a specific point, which we will represent using a given set of coordinates:

• -84.38653999999998 (longitude)
• 33.72024 (latitude)

Initial attempt

To search for buildings within the desired range, we might initially consider using the ST_Contains function. However, this approach only works if we store the coordinates as a single geometry. Since we are using separate latitude and longitude columns, ST_Contains cannot be applied directly.

Solution

Instead, we can leverage two other functions to perform point-based geospatial calculations: ST_DWithin and ST_Distance. Let's explore them individually.

1. ST_DWithin

ST_DWithin allows us to check if a geometry is within a specified distance of another geometry. Here's an example of how we use it in this situation:

<code class="language-sql">SELECT name,
       long,
       lat,
       ST_Distance('POINT(-84.38653999999998, 33.72024)'::geography,
                   ST_MakePoint(long, lat)) * 0.000621371 AS distance
FROM building
WHERE
  ST_DWithin('POINT(-84.38653999999998, 33.72024)'::geography,
              ST_MakePoint(long, lat), 8046.72); -- 8046.72 米 = 5 英里</code>

2. ST_Distance

ST_Distance Calculates the distance (in meters) between two geometries. We can use this to find all buildings within 5 miles and then convert the distance to miles:

<code class="language-sql">SELECT name,
       long,
       lat,
       ST_Distance('POINT(-84.38653999999998, 33.72024)'::geography,
                   ST_MakePoint(long, lat)) * 0.000621371 AS distance
FROM building
WHERE
  ST_Distance('POINT(-84.38653999999998, 33.72024)'::geography,
              ST_MakePoint(long, lat)) * 0.000621371 < 5;</code>

Note that the order of arguments is critical when constructing ST_Point geometries. The first parameter represents longitude and the second parameter represents latitude.

When executing either of these two queries, you should get the results you want: a list of building names, longitude, latitude and distance from the specified coordinates, filtered to include only those buildings within a 5 mile radius .

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