Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using Sql Spatial Data (C#) to find the "visual" center of irregular polygons

I'm drawing regions (using SqlGeometry/SqlGeography and translating them to the WPF LocationCollection equivalent) on the Bing Maps WPF Control and needed to label them. I got the labels drawn on the regions and attached them to the point found by STCentroid(). Of course as you imagine this is a problem with the 'U' or 'C' shaped regions where the centroid ends up outside the region, which makes the label incorrect.

Is there a way using SqlGeometry/SqlGeography to find the "visual" center or perhaps find the largest circle that can fit in the shape and use that center? I've tried various methods using STPointOnSurface() but it seems that STPointOnSurface() always picks a point on the edge like so DCREHA (the label for the bottom dark green region always ends up on the edges:

enter image description here

like image 944
Hershizer33 Avatar asked Sep 01 '16 16:09

Hershizer33


People also ask

What is spatial data SQL?

Spatial data represents information about the physical location and shape of geometric objects. These objects can be point locations or more complex objects such as countries, roads, or lakes. SQL Server supports two spatial data types: the geometry data type and the geography data type.

What is CT in SQL?

SQL Server Change Tracking, also known as CT, is a lightweight tracking mechanism, introduced the first time in SQL Server 2008, that can be used to track the DML changes performed in SQL Server database tables. SQL Change Tracking can be configured in all SQL Server editions, including the free Express edition.

What is geometry in SQL?

The planar spatial data type, geometry, is implemented as a common language runtime (CLR) data type in SQL Server. This type represents data in a Euclidean (flat) coordinate system. SQL Server supports a set of methods for the geometry spatial data type.

Is MySQL a spatial database?

MySQL has spatial data types that correspond to OpenGIS classes. The basis for these types is described in Section 11.4. 2, “The OpenGIS Geometry Model”.


1 Answers

An almost identical question has already been asked here. In your case, the most relevant answer is probably this one. The hard part of that answer is determining where to draw the line that splits the polygon into two equal areas. Therefore, I propose a slightly modified version below for SQL Server:

  1. Use STCentroid to find the centre of mass
  2. If this is inside the polygon (STWithin), then no need to process further; otherwise:
  3. Determine the centroid of the polygon's bounding box (e.g. use STEnvelope to get the polygon's envelope and pass that as the argument to STCentroid)
  4. If this centroid is within polygon (STWithin), it may be good enough (would need testing); otherwise:
  5. Extend the line that connects the polygon's centroid and the envelope's centroid so that it extends to just beyond the polygon's envelope in each direction (may be easier in C# code, or see here for a PostGIS solution)
  6. Determine the intersection points between the extended line and the polygon (STIntersection)
  7. Find the nearest intersection point to the polygon's centroid point from step 1 (see here). This point is an approximation for the "1st cut point" detailed in the linked answer.
  8. Find the nearest intersection point to the intersection point found above in step 7. This point is an approximation for the "2nd cut point" detailed in the linked answer.
  9. The mid-point of the line that connects the two intersection points will be inside the polygon, and should be a reasonable approximation for the "visual centre" of the polygon.

For instance, given the polygon for the DCREHA example above, the following GIF animates the procedure listed above (judged by eye - actual result will differ):

enter image description here

like image 136
Peet Whittaker Avatar answered Sep 18 '22 12:09

Peet Whittaker