References

Geospatial data types | Snowflake Documentation

Overview

For Addresses

Find the lat/long for the address

Get back a list of lat/longs
From that, create a set of OR query with a radius objects to find which records are within the radiuses.

Create a lat/long radius search object for Snowflake
Should find all records that are within the lat/long radius

Need to figure out what the column shape to search the records

Is it a geolocation point data type?

select * from people_view

where

(

lat_long_point in the radius_object1

OR lat_long_point in the radius_object2

OR lat_long_point in the radius_object3

)

From Google

To find records within a specified radius around a given latitude and longitude in Snowflake, particularly when considering a "zip code radius," you would typically perform the following steps:
Geocode Zip Codes:
.
If your data contains only zip codes and you need to define a radius around them, you must first obtain the central latitude and longitude coordinates for those zip codes. This can be achieved using a geocoding service or a dataset that maps zip codes to their corresponding geographic coordinates (e.g., from the Snowflake Marketplace).
Store Geospatial Data:
.
Ensure your location data (for both the points you are searching around and the records you want to find) is stored in a GEOGRAPHY data type column in Snowflake. This optimizes geospatial queries. If your data is in separate latitude and longitude columns, you can convert them into a GEOGRAPHY point using ST_GEOGRAPHYFROMWKT or ST_MAKEPOINT.
Calculate Distance:
.
Use the ST_DISTANCE function to calculate the distance between the central point of your target zip code (or the given latitude/longitude) and the GEOGRAPHY points of your records.
Filter by Radius:
.
Filter the results based on the calculated distance, keeping only those records where the distance is less than or equal to your defined radius.
Example SQL Query (conceptual):
Code

SELECT
    your_record_id,
    your_record_name,
    ST_DISTANCE(
        ST_GEOGRAPHYFROMWKT('POINT(-122.4194 37.7749)'), -- Replace with your target lat/lon
        your_location_column -- Your GEOGRAPHY column
    ) AS distance_meters
FROM
    your_table
WHERE
    distance_meters <= (your_radius_miles * 1609.34); -- Convert miles to meters for ST_DISTANCE
Explanation:
ST_GEOGRAPHYFROMWKT('POINT(-122.4194 37.7749)'): Creates a GEOGRAPHY point from a Well-Known Text (WKT) representation of your target latitude and longitude (e.g., the center of a zip code).
your_location_column: This should be the column in your table that stores the GEOGRAPHY points of your records.
ST_DISTANCE(): Calculates the distance in meters between the two GEOGRAPHY points.
your_radius_miles * 1609.34: Converts your desired radius in miles to meters, as ST_DISTANCE returns results in meters.

City

Will this be a similar method. We need to find a lat/long radius as well and do a similar query as above

Example

To find records within a geographic radius in Snowflake using latitude and longitude, the

ST_DWITHIN

function is typically used. This function checks if two

GEOGRAPHY

objects are within a specified distance of each other.

Steps to search for records within a geographic radius:

Ensure GEOGRAPHY Data Type: The latitude and longitude columns in the table should be converted or stored as GEOGRAPHY data types. This is achieved using the ST_POINT function.

Code

ALTER TABLE your_table ADD COLUMN geom GEOGRAPHY; UPDATE your_table SET geom = ST_POINT(longitude, latitude);

Use ST_DWITHIN in the WHERE Clause.

Code

SELECT * FROM your_table WHERE ST_DWITHIN(geom, ST_POINT(target_longitude, target_latitude), radius_in_meters);

geom: This is the GEOGRAPHY column in your table representing the location of your records.
ST_POINT(target_longitude, target_latitude): This creates a GEOGRAPHY object for the central point of your search radius.
radius_in_meters: This is the desired radius in meters. ST_DWITHIN calculates distances in meters by default when operating on GEOGRAPHY objects.

Example:

To find all records within a 5-kilometer radius of a specific point (e.g., latitude 34.0522, longitude -118.2437):

Code

SELECT *FROM your_tableWHERE ST_DWITHIN(geom, ST_POINT(-118.2437, 34.0522), 5000); -- 5000 meters = 5 kilometers

Performance Considerations:

Search Optimization Service:

For large tables and frequent geospatial queries, enable the Search Optimization Service on the GEOGRAPHY column to significantly improve query performance.

Filtering:

Pre-filter data using other non-geospatial criteria (e.g., date ranges, categories) before applying the ST_DWITHIN function to reduce the dataset size that needs geospatial processing.

Example from ADS Direct View Query

SELECT * 
from BETTRDATA.production.PEOPLE_VW
WHERE 1=1
AND (
        ST_DWITHIN(GEO_POINT, ST_POINT(36.813701, -76.084809), 10000)
        or 
        ST_DWITHIN(GEO_POINT, ST_POINT(36.813701, -76.084809), 10000)
        or 
        ST_DWITHIN(GEO_POINT, ST_POINT(36.813701, -105.084809), 10000)
    )
LIMIT 100
;

Finding Record By Address of location lat/long point

References

Overview

For Addresses

From Google

City

Example

Example from ADS Direct View Query