How to get longitude and latitude for a place within certain distance from a known longitude and lat-CodePudding

I want to find a place (longitude and latitude) with distance less than 10 km from a known longitude and latitude using BigQuery SQL. Is there any possible query for this?

CodePudding user response：

I read your request as saying that given a geospatial point, you wish to query for anything within less than a 10km radius of that point. Here's two ways to solve this:

Using ST_BUFFER

You could use the ST_BUFFER function which similarly takes an argument of the radius to use around a point, but instead uses a segmented circle with 8 segments by default.

SELECT *
FROM `table`
WHERE ST_CONTAINS(
  ST_BUFFER(
    ST_GEOPOINT(longitude, latitude),
    10 * 1000), -- Radius argument is expressed in meters
  YourGeoPointColumn)

Using ST_BUFFERWITHTOLERANCE

You might use ST_BUFFERWITHTOLERANCE that replaces the segmented circle with tolerance instead of circle segments.

SELECT *
FROM `table`
WHERE ST_CONTAINS(
  ST_BUFFERWITHTOLERANCE(
    ST_GEOPOINT(longitude, latitude),
    10 * 1000, -- Radius argument is expressed in meters
    1), -- Tolerance of 1% of the buffer radius, expressed in meters
  YourGeoPointColumn)

CodePudding user response：

ST_Distance function should work here, like this:

with data as (
  select 1 id, st_geogpoint(-122, 47) as geo
  union all 
  select 2 id, st_geogpoint(-121, 47) as geo
)
select * from data
where st_distance(geo, st_geogpoint(-122.1, 47)) < 10000

id  geo
------------------
1   POINT(-122 47)

Another way to write the distance condition is

ST_DWithin(geo, st_geogpoint(-122.1, 47), 10000)

If something does not work, please provide sample data and what data you expect in the results but is missing.