Spatial queries in Fabric Data Warehouse
Spatial data has become increasingly important in various fields, from urban planning and environmental monitoring to transportation and logistics. Fabric Data Warehouse offers spatial functionalities that enable you to query and analyze spatial data efficiently.
In this blog post, we will delve into the spatial capabilities in the Fabric Data Warehouse and demonstrate how to use the spatial functions in your queries.
Introduction to Spatial data
Fabric Data Warehouse supports two primary spatial data types:
- The geometry data type is used for planar (Euclidean) spatial data. The geometry data type is suitable for applications that operate on flat surfaces. It supports a wide range of spatial operations, including calculating distances, intersections, and spatial relationships.
- The geography data type is designed for round-earth spatial data. It is ideal for applications dealing with geodetic data, such as GPS coordinates. It considers the Earth’s curvature and provides accurate spatial operations over large distances.
These data types encapsulate spatial information and provide many functions for calculation relationships and properties of spatial data. Note that these types cannot be used as the column types in tables. You can use them as variables, expressions, and parameters of functions. If you need to physically store spatial data in the tables, you should use either pairs of float (lat,lon) columns to persist the points, or VARBINARY(MAX)/VARCHAR(MAX) there you store content in well-known binary or well-known text format, and then convert them to spatial objects using geometry::Point(), geometry::STGeomFromWKB() or geometry::STGeomFromText() functions.
Spatial functions in Fabric Data Warehouse
Fabric Data Warehouse offers many functions that operate on spatial objects to perform various calculations and transformations. Some of the most used functions include:
- STDistance(): calculates the shortest distance between two spatial instances.
- STIntersection(): returns the geometric intersection of two spatial instances.
- STIntersects()/STWithin(): checks whether an object is within another object.
- STUnion(): combines two spatial instances into a single instance.
- STBuffer(): creates a buffer area around a spatial instance.
Fabric Data Warehouse doesn’t support spatial indexes and aggregate functions (for example STUnionAggregate() or STEnvelopeAggregate()), but the functions that are available in Data Warehouse enable you to implement a lot of scenarios.
Demonstrating spatial capabilities with sample warehouse
To showcase the spatial functionalities in Fabric Data Warehouse, we will use the sample data warehouse in Fabric. A sample warehouse enables you to quickly start with you warehouse experinece by providing a data sample that you can query immediatelly without a need to load your data.
The sample data warehouse includes information about the taxi trips with the pickup and drop-off locations expressed as (longitude, latitude) pairs. Let’s look at a few examples of how to leverage this data effectively.
Example 1: Trips longer than 50km in November 2013
Suppose we need to find the trips in November 2013 that are longer than 50km. We can filter rows by date, convert pickup/drop-off locations to geography points, and calculate the distance between pickup and drop-off locations:
SELECT *
FROM dbo.Trip
WHERE DateID BETWEEN 20131101 AND 20131130
AND geography::Point(PickupLatitude, PickupLongitude, 4326)
.STDistance(geography::Point(DropoffLatitude, DropoffLongitude, 4326)) >= 50000;Note that we need to know the unit of measure that is used to measure the distance between pickup and drop-off locations. For this purpose, we need to provide the SRID parameter. The SRID=4326 defines that a unit of measure that is returned by a distance should be meters, and therefore we can compare the calculated distance with 50.000, which represents 50 km. You can check the unit of measures for SRIDs using the following query (note that most of the SRIDs are using meter):
SELECT unit_of_measure
FROM sys.spatial_reference_systems
WHERE spatial_reference_id = 4326Example 2: The trips starting near Empire State Building
You can easily find the trips starting near some specific location such as Empire State Building, New York, USA by measuring a distance from that point (40.748817,-73.985428) to the pickup location:
SELECT *
FROM dbo.Trip
where geography::Point(PickupLatitude, PickupLongitude, 4326)
.STDistance(geography::Point(40.748817, -73.985428, 4326)) < 10;This query selects all records from the dbo.Trip table where the starting point of the trip (PickupLongitude and PickupLatitude) is within 10 meters from the coordinates of the Empire State Building.
You can easily change this query and provide any other location to find the trips starting or ending at that location. Simply replace the coordinates of the Empire State Building with the desired location’s longitude and latitude values. This makes it a versatile tool for analyzing travel patterns and understanding the geographic distribution of trips.
Example 3: Trips starting from Central Park
Instead of a single point, we can define an area (for example Central Park boundaries) and find all trips starting within this area:
declare @CentralPark geometry = geometry::STGeomFromText('POLYGON((
-73.98145304343566 40.768091803976354,-73.97321329734169 40.76484149306375,
-73.94949541883834 40.796860957240355,-73.95833597975167 40.80075943144777,
-73.98145304343566 40.768091803976354))', 4326)
SELECT COUNT(*)
FROM dbo.Trip
where 1 = geometry::Point(PickupLongitude, PickupLatitude, 4326)
.STIntersects(@CentralPark)Note that here we are using geometry data instead of geography, because we are not using units of measures. Geometry functions are more performant than the geography functions and since we just need to check whether the location intersects with the area, we can use geometry.
Conclusion
The spatial functionalities in Fabric Data Warehouse provide a robust API for querying and analyzing spatial data. By leveraging these capabilities, you can gain valuable insights and make informed decisions based on spatial relationships and patterns. We encourage you to explore the sample data warehouse and experiment with the spatial functions to unlock the full potential of your spatial data.
Next steps
We can’t wait for you to try out spatial queries on your own data and let us know what you think. Submit your feedback on Fabric Ideas and join the conversation on the Fabric Community. To get into the technical details, head over to the Fabric documentation.
