DAA-C01 Question 100
Single answerGeospatial functionsA retail analytics team stores customer home locations in a Snowflake table as latitude and longitude columns in WGS84 decimal degrees. They also have a table of store trade areas provided as GeoJSON polygons. The team needs to identify customers who live inside a store's trade area and avoid incorrect matches caused by coordinate-system issues. Which approach should the data analyst use?
- A
Convert the customer latitude/longitude into a GEOGRAPHY point with ST_MAKEPOINT(longitude, latitude), parse the GeoJSON polygons into GEOGRAPHY, and use ST_CONTAINS(trade_area, customer_point).
- B
Convert the customer latitude/longitude into a GEOMETRY point with ST_MAKEPOINT(latitude, longitude), parse the GeoJSON polygons into GEOMETRY, and use ST_DISTANCE in degrees to determine whether the point is inside the polygon.
- C
Store the customer coordinates as VARCHAR and compare them directly to the polygon bounding box values extracted from the GeoJSON to avoid geospatial conversion overhead.
- D
Convert the customer latitude/longitude into a GEOGRAPHY point with ST_MAKEPOINT(latitude, longitude), parse the GeoJSON polygons into GEOGRAPHY, and use ST_INTERSECTS(customer_point, trade_area) only after casting both objects to VARIANT.
Show answer and explanation
Correct answer: A
Explanation
This scenario tests practical use of Snowflake geospatial data types and predicates. For Earth-based coordinates stored as longitude and latitude in WGS84, GEOGRAPHY is the correct type because it models geodetic coordinates on the globe. A common implementation pattern is to create points with ST_MAKEPOINT(longitude, latitude) and convert GeoJSON polygons into GEOGRAPHY using Snowflake's geospatial conversion functions. To identify customers inside a trade area, use a topological predicate such as ST_CONTAINS(polygon, point) or, in some cases, ST_INTERSECTS. The key applied concept is that coordinate order matters and must be longitude first, latitude second. Another important best practice is to avoid ad hoc string or bounding-box logic when exact polygon containment is required. Snowflake documentation for geospatial data types and functions emphasizes using GEOGRAPHY for WGS84 coordinates, correct point construction with ST_MAKEPOINT, and spatial relationship functions such as ST_CONTAINS and ST_INTERSECTS for accurate geospatial analysis.
- A. Correct.
Correct. For WGS84 longitude/latitude coordinates, Snowflake geospatial best practice is to use GEOGRAPHY when working with geodetic coordinates on the Earth. ST_MAKEPOINT expects arguments in the order (longitude, latitude), which is a common source of mistakes. GeoJSON can be converted to GEOGRAPHY using Snowflake geospatial parsing functions, and ST_CONTAINS(polygon, point) is an appropriate predicate to test whether a customer point falls within a trade area polygon.
- B. Incorrect.
Incorrect. There are two issues here. First, ST_MAKEPOINT expects (longitude, latitude), not (latitude, longitude), so the point would likely be created in the wrong place. Second, ST_DISTANCE does not test point-in-polygon membership; distance in degrees is not a valid substitute for containment logic. While GEOMETRY can be used for planar data, this scenario explicitly uses WGS84 lat/long coordinates, making GEOGRAPHY the appropriate type in most cases.
- C. Incorrect.
Incorrect. Treating coordinates as VARCHAR bypasses Snowflake's geospatial types and functions and prevents proper spatial calculations. Bounding-box comparisons can create false positives because a point can be inside a polygon's envelope but outside the polygon itself. This approach also increases the risk of string-format inconsistencies and does not solve coordinate-system correctness.
- D. Incorrect.
Incorrect. ST_MAKEPOINT argument order is wrong here because it should be (longitude, latitude). Also, casting geospatial objects to VARIANT is not required for spatial predicates and would not help ST_INTERSECTS work correctly. Although ST_INTERSECTS can return true when a point lies in a polygon, the proposed approach introduces an unnecessary and incorrect cast and still mishandles coordinate order.