https://community.databricks.com/t5/technical-blog/dynamic-segmentation-in-geospatial-analytics-on-databricks-part/ba-p/91802 <P><I><SPAN>This is the second part of a two-part series blog on geospatial data processing on Databricks. In the </SPAN></I><A href="https://community.databricks.com/t5/technical-blog/from-raw-to-refined-processing-overture-maps-geospatial-data-on/ba-p/90838" target="_blank" rel="noopener"><I><SPAN>first part</SPAN></I></A><I><SPAN>, we covered ingesting and processing Overture Maps data on Databricks.&nbsp; In this second part, we will delve into a practical use case on dynamic segmentation.</SPAN></I></P> <P><SPAN>Imagine driving down a winding road where the speed limit changes every few hundred metres, or navigating a city where the pavement conditions shift from smooth asphalt to bumpy gravel. This dynamic nature of our road networks presents a fascinating challenge for location intelligence. Dynamic segmentation is a powerful technique that allows us to slice and dice linear features based on varying attributes.&nbsp;</SPAN></P> <P><SPAN>For instance, city planners might use dynamic segmentation to determine whether sections with higher curbs experience fewer pedestrian-related accidents compared to those with lower curbs, or how accident rates change in areas where the speed limit fluctuates. This granular approach to road network analysis enables city planners and traffic engineers to identify high-risk zones and implement targeted safety measures, potentially saving lives and reducing injuries.</SPAN><SPAN><BR /></SPAN></P> <P><SPAN>While this method can be applied in a multitude of scenarios, from environmental monitoring to urban planning, this blog post will zoom in on road networks as a captivating use case.&nbsp;<BR /><BR /></SPAN></P> <H1><SPAN>Understanding Dynamic Segmentation</SPAN></H1> <P><SPAN>Dynamic segmentation is the process of dividing linear features into segments based on changing attributes along their length. This technique is particularly useful for analysing and visualising how properties like speed limits, pavement conditions, or traffic volumes vary along a road network.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="dannywong_4-1727308411198.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/11499iF5E2A3C1F8D6B02A/image-size/large?v=v2&amp;px=999" role="button" title="dannywong_4-1727308411198.png" alt="dannywong_4-1727308411198.png" /></span></P> <P><SPAN>Dynamic segmentation creates variable-length segments that accurately represent changes in attributes. This approach provides a more precise representation of real-world conditions and enables more nuanced analysis.</SPAN></P> <P>&nbsp;</P> <H1><SPAN>Apache Sedona and Databricks: A Powerful Combination</SPAN></H1> <P><SPAN>The Databricks Lakehouse platform offers a powerful and flexible environment for processing geospatial data at scale, through built-in product features as well as by using various 3rd party libraries. One popular library, among many, is </SPAN><A href="https://sedona.apache.org/" target="_blank" rel="noopener"><SPAN>Apache Sedona</SPAN></A><SPAN>, a geospatial data processing Apache Spark-based framework. Sedona has some useful functions for the focus of this dynamic segmentation use case, which can be applied to augment our built-in capabilities.</SPAN></P> <P><SPAN>Databricks enhances geospatial workloads with innovative features like </SPAN><A href="https://docs.databricks.com/en/delta/clustering.html" target="_blank" rel="noopener"><SPAN>Liquid Clustering</SPAN></A><SPAN>, which simplifies data layout and improves query performance; </SPAN><A href="https://docs.databricks.com/en/sql/language-manual/sql-ref-h3-geospatial-functions.html" target="_blank" rel="noopener"><SPAN>30+ native H3</SPAN></A><SPAN> global gridding functions, enabling highly scalable discrete spatial analytics; and 60+ Spatial SQL functions, currently in private preview for DBR 14.3+ (reach out to your Databricks sales team to join the preview).</SPAN></P> <P><SPAN>Users can easily install Apache Sedona on their Databricks clusters by following straightforward</SPAN><A href="https://sedona.apache.org/1.6.0/setup/databricks/" target="_blank" rel="noopener"><SPAN> instructions</SPAN></A><SPAN>. This extensibility, combined with the platform's distributed processing power and performance optimizations, positions Databricks as an ideal choice for organizations dealing with large-scale geospatial analytics and dynamic segmentation tasks.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="dannywong_5-1727308490158.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/11500i7F2E18A50682A6EE/image-size/large?v=v2&amp;px=999" role="button" title="dannywong_5-1727308490158.png" alt="dannywong_5-1727308490158.png" /></span><BR /><BR /></P> <H1><SPAN>Implementing Dynamic Segmentation</SPAN></H1> <P><SPAN>Let's explore how to perform dynamic segmentation using Apache Sedona on Databricks. We'll use a road network dataset and segment it based on different attributes.</SPAN></P> <H2><SPAN>Example 1: Segmenting Roads by Pavement Condition</SPAN></H2> <P><SPAN>In this example, we'll segment a road network (Rte) based on pavement condition (PC):</SPAN></P> <TABLE style="border-style: hidden; width: 100%;" border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="javascript">SELECT PC.Classified_Road_Number , PC.Direction , PC.route , PC.Surface_Type , PC.Roughness_Category , PC.Start_Chainage_m , PC.End_Chainage_m , ST_LineSubstring( Rte.geometry , PC.Start_Chainage_m/Rte.ARCLENGTH , PC.End_Chainage_m/Rte.ARCLENGTH ) as geometry FROM pavement_condition PC, routes Rte WHERE PC.route = Rte.ROUTE_ID AND Classified_Road_Number = 1234 </LI-CODE></TD> </TR> </TBODY> </TABLE> <P><SPAN>In transportation planning or traffic analysis, Sedona’s </SPAN><A href="https://sedona.apache.org/1.6.0/api/sql/Function/?h=st_linesubstring#st_linesubstring" target="_blank" rel="noopener"><SPAN>ST_LineSubstring</SPAN></A><SPAN> can be used to extract a particular segment of a road or path for detailed study, such as a stretch of road where frequent accidents occur.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><FONT face="courier new,courier"><SPAN>Rte.geometry</SPAN></FONT><SPAN> is the full geometry of the route.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><FONT face="courier new,courier"><SPAN>PC.Start_Chainage_m/Rte.ARCLENGTH</SPAN></FONT><SPAN> calculates the start point of the substring as a fraction of the total route length.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><FONT face="courier new,courier"><SPAN>PC.End_Chainage_m/Rte.ARCLENGTH</SPAN></FONT><SPAN> calculates the end point of the substring as a fraction of the total route length.</SPAN></LI> </UL> <P><SPAN>For large LINESTRING geometries (e.g., routes), ST_LineSubstring can be used to create smaller, more manageable segments (e.g., Pavement conditions) for analysis. This can be particularly useful when working with large datasets or when only a specific section of the data is relevant.</SPAN></P> <H2><SPAN>Example 2: Finding measure value along a route</SPAN></H2> <P><SPAN>This example is to calculate the distance along a specific bus route (RTE) to each bus stop (BS) on that route.</SPAN></P> <TABLE style="border-style: hidden; width: 100%;" border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="javascript">SELECT RTE.ROUTE_ID , BS.Location_Description , ST_LineLocatePoint(RTE.geometry, BS.geometry) * ARCLENGTH as measure FROM route_bus_stops BS, routes RTE WHERE RTE.ROUTE_ID = 1234 ORDER BY measure</LI-CODE></TD> </TR> </TBODY> </TABLE> <P><SPAN>For managing transportation assets such as bus stops, signage, and maintenance points, Sedona’s </SPAN><A href="https://sedona.apache.org/1.6.0/api/sql/Function/?h=st_linelocatepoint#st_linelocatepoint" target="_blank" rel="noopener"><SPAN>ST_LineLocatePoint</SPAN></A><SPAN> can help pinpoint their exact locations on the road network. This aids in asset inventory management, maintenance scheduling, and optimising the placement of new assets.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><FONT face="courier new,courier"><SPAN>ST_LineLocatePoint(RTE.geometry, BS.geometry) </SPAN></FONT><SPAN>returns a fraction between 0 and 1, representing where the bus stop point is located along the route line.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>This fraction is then multiplied by </SPAN><FONT face="courier new,courier"><SPAN>ARCLENGTH</SPAN></FONT><SPAN> (the total length of the route) to convert it into an actual distance measure.</SPAN></LI> </UL> <P><SPAN>This is a useful application of spatial analysis in transportation planning. It can help in:</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Visualising the distribution of bus stops along a route</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Calculating distances between consecutive stops</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Analysing the coverage and accessibility of public transport along the route<BR /><BR /></SPAN></LI> </UL> <H1><SPAN>Benefits of Dynamic Segmentation on Databricks</SPAN></H1> <OL> <LI><SPAN>Scalability: Efficiently process large road networks with the distributed computing environment, while Delta Lake ensures high performance, transactional reliability, and governance.</SPAN></LI> <LI><SPAN>Flexibility: Easily adapt dynamic segmentation to various attributes or conditions, with Liquid Clustering enabling data layout evolution without rewrites.</SPAN></LI> <LI><SPAN>Integration: Results can be seamlessly integrated with other data analysis workflows on the Databricks Platform to combine the power of spatial and aspatial data on a single unified platform.</SPAN><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="dannywong_6-1727308785610.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/11501iC8309A6C3384B873/image-size/large?v=v2&amp;px=999" role="button" title="dannywong_6-1727308785610.png" alt="dannywong_6-1727308785610.png" /></span></LI> <LI><SPAN>Visualisation: Visualize segmented data using popular GIS tools or directly within Databricks Notebooks using open source libraries like </SPAN><A href="http://kepler.gl" target="_blank" rel="noopener"><SPAN>kepler.gl</SPAN></A><SPAN>.</SPAN></LI> </OL> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="dannywong_8-1727308855863.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/11503i0AD8EA083F826E0C/image-size/large?v=v2&amp;px=999" role="button" title="dannywong_8-1727308855863.png" alt="dannywong_8-1727308855863.png" /></span></P> <P>&nbsp;</P> <H1><SPAN>Conclusion</SPAN></H1> <P><SPAN>Dynamic segmentation opens up a world of possibilities in location intelligence, transforming how we understand and interact with our road networks. By leveraging Apache Sedona on Databricks, we can slice through complex data to reveal insights about speed limits, pavement conditions, and bus stop locations.&nbsp; Buckle up and get ready to uncover the hidden narratives in your datasets!</SPAN></P> <P><SPAN>If you haven't already, make sure to check out the </SPAN><A href="https://community.databricks.com/t5/technical-blog/from-raw-to-refined-processing-overture-maps-geospatial-data-on/ba-p/90838" target="_blank" rel="noopener"><SPAN>first part of our series</SPAN></A><SPAN>, where we discussed the foundational steps of processing Overture Maps data on Databricks. Together, these two-part series give you a couple of practical examples of running geospatial workloads on Databricks.</SPAN></P> Mon, 21 Oct 2024 22:34:37 GMT dannywong 2024-10-21T22:34:37Z https://community.databricks.com/t5/technical-blog/dynamic-segmentation-in-geospatial-analytics-on-databricks-part/ba-p/91802 <P><I><SPAN>This is the second part of a two-part series blog on geospatial data processing on Databricks. In the </SPAN></I><A href="https://community.databricks.com/t5/technical-blog/from-raw-to-refined-processing-overture-maps-geospatial-data-on/ba-p/90838"><I><SPAN>first part</SPAN></I></A><I><SPAN>, we covered ingesting and processing Overture Maps data on Databricks.&nbsp; In this second part, we will delve into a practical use case on dynamic segmentation.</SPAN></I></P> Mon, 21 Oct 2024 22:34:37 GMT https://community.databricks.com/t5/technical-blog/dynamic-segmentation-in-geospatial-analytics-on-databricks-part/ba-p/91802 dannywong 2024-10-21T22:34:37Z https://community.databricks.com/t5/technical-blog/dynamic-segmentation-in-geospatial-analytics-on-databricks-part/bc-p/92804#M299 <P>Nice work Danny, just curious where the speed zone stuff is, example 1 appears to be pavement condition data?</P> Fri, 04 Oct 2024 22:17:53 GMT https://community.databricks.com/t5/technical-blog/dynamic-segmentation-in-geospatial-analytics-on-databricks-part/bc-p/92804#M299 robert_potter 2024-10-04T22:17:53Z https://community.databricks.com/t5/technical-blog/dynamic-segmentation-in-geospatial-analytics-on-databricks-part/bc-p/92875#M301 <P>My bad, thanks for spotting that Rob and I will make the change!</P> Mon, 07 Oct 2024 02:05:14 GMT https://community.databricks.com/t5/technical-blog/dynamic-segmentation-in-geospatial-analytics-on-databricks-part/bc-p/92875#M301 dannywong 2024-10-07T02:05:14Z