DAA-C01 Question 99
Single answerGeospatial functionsA retail analytics team stores customer delivery locations in a table as GEOGRAPHY points built from latitude and longitude. They also maintain sales territories as polygon boundaries in another table, also stored as GEOGRAPHY. The team needs a query that assigns each delivery to the territory that contains it and correctly handles locations on a global scale without manually converting coordinate systems. Which approach should the analyst use?
- A
Use ST_CONTAINS(territory_geog, delivery_geog) in a spatial join between the polygon and point columns.
- B
Convert both GEOGRAPHY columns to VARCHAR and compare their WKT text values to determine whether a point falls inside a polygon.
- C
Use ST_DISTANCE(territory_geog, delivery_geog) = 0 to identify all deliveries contained by a territory polygon.
- D
Cast both columns to GEOMETRY and use Euclidean comparisons because GEOMETRY automatically gives more accurate global results than GEOGRAPHY.
Show answer and explanation
Correct answer: A
Explanation
For Earth-based latitude/longitude analytics in Snowflake, GEOGRAPHY is generally the preferred data type because it supports geodesic calculations over the globe. When the task is to assign points to polygons, the correct spatial predicate is ST_CONTAINS, used in a join such as joining territory polygons to delivery points where ST_CONTAINS(territory_geog, delivery_geog) is true. This reflects Snowflake geospatial best practices: use GEOGRAPHY for global lon/lat data, and use topological predicates such as ST_CONTAINS, ST_WITHIN, ST_INTERSECTS, or ST_COVERS based on the business rule. Text representations like WKT are for interchange and readability, not analytics. Distance functions are useful for proximity analysis, but not as a substitute for containment logic. Snowflake documentation for geospatial data types and functions distinguishes GEOGRAPHY from GEOMETRY and documents ST_CONTAINS as the appropriate containment predicate.
- A. Correct.
Correct. ST_CONTAINS is the appropriate geospatial predicate for checking whether one spatial object contains another. In this scenario, the territory polygons and delivery points are both stored as GEOGRAPHY, which is designed for geodesic calculations on the Earth’s surface. This allows the analyst to perform containment testing at global scale without manually managing map projections or coordinate system transformations.
- B. Incorrect.
Incorrect. WKT is only a text representation of spatial objects. Comparing VARCHAR values does not perform any spatial computation and cannot determine whether a point is inside a polygon. This is a common misconception when users see geospatial objects rendered as text and assume string comparison is meaningful for spatial logic.
- C. Incorrect.
Incorrect. ST_DISTANCE measures the shortest distance between two spatial objects, but a distance of 0 does not reliably express polygon containment as the intended join condition here. While overlapping or touching objects can yield distance 0, this is not the correct semantic test for assigning points to containing polygons. The proper predicate is ST_CONTAINS or, depending on boundary requirements, a related topological function.
- D. Incorrect.
Incorrect. GEOMETRY does not automatically provide more accurate global results. In Snowflake, GEOGRAPHY is the appropriate type for Earth-based longitude/latitude use cases because it models geodesic relationships on a spheroid-like Earth surface. GEOMETRY uses planar mathematics and requires the analyst to manage coordinate systems appropriately. Casting longitude/latitude data to GEOMETRY without a projection strategy can produce misleading results for global analysis.