MVP Summit 2011

So, I spent last week in Seattle/Bellevue/Redmond (essentially all part of the same urban sprawl) attending the Microsoft MVP Global Summit 2011.

image

Microsoft’s MVP program has recently received some criticism (see, for example, here and some of the responses here), however, my personal experience of the Summit was brilliant. Whilst I mostly don’t agree with those criticisms, I can certainly agree that the benefit of attending Summit (and the benefit of the MVP program in general) is very much determined by the level of engagement with the product group in which you’re involved. What the MVP program provides is the opportunity to make those connections and involvement with the Microsoft product teams – it doesn’t set it up for you on a plate. The general “large group” sessions and keynote speeches I attended delivered little more than marketing spiels which, if you have any interest in following technology news, probably contained very little information that you didn’t already know.

image

However, where summit excelled was in providing the opportunity for small, intimate meetings with just a handful of MVPs and key Microsoft developers on a particular interest topic.

As far back as late November, all the Bing Maps MVPs including myself agreed that we would attend the Summit – travelling from the UK, France, Australia, Canada, and the US. We then started making contact ourselves with those people who we thought it would be beneficial to meet with. With the assistance of MS people within the Bing Maps team who set up meetings for us, we were able to create a personalised schedule covering two of the days – pretty much tailored just to the eight of us attending.

Not once were we told that somebody was too busy to meet us – senior program managers, MS staff who themselves had to travel from all around the country (not all MS staff are based in Redmond), and dev teams all spared time to chat with us, answer our questions, and share some of the exciting things that are coming up over the next 12 months and beyond (sorry – no details – NDA and all that). I can’t think how I could have had the opportunity to learn some of the things I did at summit any other way.

So, this is my personal thanks to my fellow MVPs and the Microsoft staff involved in the MVP program, and particularly those who took time out from their schedule to meet with us. I certainly valued your time, and I’m sure that the other attendees present did also. And I’m very excited about some of the developments we can look forward to seeing over the next 18 months! As soon as they become publicly-available I’ll be blogging about them here…

How to model Road Networks in SQL Server 2008

One of the most common example uses stated for a LineString geometry (or geography) in SQL Server is to model a road. In fact, a LineString geometry is ideally suited to almost any transit route – it is a simple, one-dimensional geometry, which is directed (i.e. it has a defined start and end point, which is useful for representing one-way routes), and it can be used with a range of useful methods such as STCrosses(), STLength(), STIntersects().

However, whilst it’s well known that a (single) LineString geometry can represent a (single) road, it is pretty unlikely that you’ll want to ever consider one road in isolation. I haven’t seen many discussions about different approaches to storing a connected network of roads in SQL Server, which is what I hope to briefly introduce in this post.

All of the following examples show different ways of modelling a few roads in Norwich, as shown on the following tile from Bing Maps:

image

Approach #1 Single Table – One LineString per Road.

If your application needs only to consider each road as a separate entity (say, for plotting roads on a map, or for identifying those roads that lie within a certain distance of a point), then network topology can be ignored. Each road can be stored as a separate LineString in a straightforward “Roads” table, as follows:

DECLARE @Roads TABLE (
 RoadId int,
 RoadName varchar(32),
 RoadGeometry geography
);

INSERT INTO @Roads VALUES
(1, 'Britannia Road', 'LINESTRING(1.313772 52.636871,1.316401 52.633518,1.316497 52.632869,1.316642 52.632542)'),
(2, 'Belsize Road', 'LINESTRING(1.317538 52.632697,1.317307 52.633448,1.317098 52.633749)'),
(3, 'Vincent Road', 'LINESTRING(1.31734 52.633818, 1.315982 52.635498,1.315038 52.635229)'),
(4, 'Plumstead Road', 'LINESTRING(1.314546 52.633479,1.31529 52.633298,1.315902 52.633363,1.318332 52.634119)');

And each row in the table accurately represents the shape of one individual road, shown as follows on the SSMS Spatial Results tab:

SELECT * FROM @Roads;

image

The problem is that, using this structure, there is no explicit relationship defined between any of the roads – each is treated as a distinct, separate entity, with no connection to each other. In other words, there is no network topology. The only way of defining a logical relationship between any of these roads is based on examining the spatial relationships between the LineStrings representing those roads.

We can see visually that Britannia Road crosses Plumstead Road, and that Vincent Road and Belsize Road are turnings off Plumstead Road. We can also use the inbuilt geometry methods STIntersects(), STTouches(), STCrosses() etc. to test these relationships programmatically. For example, the following code identifies all those roads that intersect Plumstead Road:

DECLARE @g geography;
SET @g = (SELECT RoadGeometry FROM @Roads WHERE RoadName = 'Plumstead Road');

SELECT RoadName FROM @Roads
WHERE RoadGeometry.STIntersects(@g) = 1
AND RoadName <> 'Plumstead Road';

(Note that, by definition, every geometry intersects itself. Therefore we need to add the additional condition to the SELECT statement to avoid including Plumstead Road in the results).

However, there’s a problem with this approach. The results only give two rows: Britannia Road and Belsize Road. Why isn’t Vincent Road being returned? The answer may not be apparent from the Spatial Results tab in SQL Server Management Studio as shown above, but the LineStrings representing Vincent Road and Plumstead Road don’t quite touch. In fact, if you run the following query:

DECLARE @g geometry;
SET @g = (SELECT RoadGeometry FROM @Roads WHERE RoadName = 'Plumstead Road');

DECLARE @h geometry;
SET @h = (SELECT RoadGeometry FROM @Roads WHERE RoadName = 'Vincent Road');

SELECT @g.STDistance(@h);

You’ll see that the result is 7.27813053755075E-06 – in other words there is a gap of 7 micrometres between them. While this is unlikely to matter if all you want to do is display the roads (unless you’re using an insane map zoom level!), it clearly makes a great deal of difference if you’re using intersection to infer connection between these roads. Also note that although there are two turnings from Plumstead Road onto Britannia Road (depending on whether you turn left or right onto Britannia Road), this only counts as one intersection, so it is only returned once in the results.

To model the topological structure of a road network for routing or analysis purposes, we clearly need an alternative model.

Approach #2 Single Table – Multiple Segmented LineString Edges

The next approach might be, rather than describe each road as a single LineString, to split each road up into a number of connected LineString segments. The end point of each LineString segment implicitly defines an intersection at which that road connects with another road, or to the next segment of the current road. This should avoid the problems with the previous model, since any two roads that intersect will both share exactly the same start/end point – there should be no ambiguity caused by one road running close to (but not quite intersecting) another, and there can be an infinite number of LineStrings that share a common start/end point. In fact, this model provides far more flexibility than the previous model, because it is also possible to define roads that intersect but are not connected (that is, they intersect somewhere mid-LineString and not at the start/end point of a segment).

To keep track of which segment(s) are part of which road, we can relate each SegmentId to it’s corresponding RoadId and, as each Segment belongs to one and only one Road, we can add the RoadId directly to the Segments table without affecting normalisation.

The following code demonstrates this approach to define the same example roads as used previously:

DECLARE @Roads TABLE (
 RoadId int,
 RoadName varchar(32)
);

INSERT INTO @Roads VALUES
(1, 'Britannia Road'),
(2, 'Belsize Road'),
(3, 'Vincent Road'),
(4, 'Plumstead Road');

DECLARE @RoadSegments TABLE (
 SegmentId int,
 RoadId int,
 SegmentGeometry geography
);

INSERT INTO @RoadSegments VALUES
(1, 1, 'LINESTRING(1.313772 52.636871, 1.315038 52.635229)'),
(2, 1, 'LINESTRING(1.315038 52.635229,1.316052 52.63399,1.316401 52.633518)'),
(3, 1, 'LINESTRING(1.316401 52.633518,1.316497 52.632869,1.316642 52.632542)'),
(4, 2, 'LINESTRING(1.317538 52.632697,1.317307 52.633448,1.317098 52.633749)'),
(5, 3, 'LINESTRING(1.31734 52.633818,1.315982 52.635498,1.315038 52.635229)'),
(6, 4, 'LINESTRING(1.314546 52.633479,1.31529 52.633298,1.315902 52.633363,1.316401 52.633518)'),
(7, 4, 'LINESTRING(1.316401 52.633518,1.317097 52.633749)'),
(8, 4, 'LINESTRING(1.317098 52.633749,1.31734 52.633818)'),
(9, 4, 'LINESTRING(1.31734 52.633818,1.318332 52.634119)');

To view the complete road network, we can now run the following query:

SELECT *
FROM @RoadSegments rs JOIN @Roads r ON rs.RoadId = r.RoadId

image

(Note that, in order to buffer the roads a bit and make both the roads names and segment IDs visible, the actual query used to produce the above image was:

SELECT RoadName, SegmentGeometry
FROM @RoadSegments rs JOIN @Roads r ON rs.RoadId = r.RoadId
UNION ALL
SELECT '    ' + CAST(SegmentID AS varchar(32)) AS RoadName, SegmentGeometry.STBuffer(2) AS SegmentGeometry
FROM @RoadSegments rs JOIN @Roads r ON rs.RoadId = r.RoadId)

We can now find all those roads that are accessible from Plumstead Road using the following query:

DECLARE @RoadID int;
SET @RoadID = (SELECT RoadID FROM @Roads WHERE RoadName = 'Plumstead Road');

SELECT r.RoadName, rs.SegmentGeometry.STAsText()
FROM @RoadSegments rs
JOIN @Roads r ON rs.RoadId = r.RoadId
JOIN (SELECT SegmentGeometry.STEndPoint() AS Node
 FROM @RoadSegments rs
 WHERE rs.RoadID = @RoadID) RoadIntersections
 ON rs.SegmentGeometry.STEndPoint().STEquals(RoadIntersections.Node) = 1
WHERE rs.RoadID <> @RoadID;

The subselect statement in this query first selects the endpoints of each LineString segment that are part of ‘Plumstead Road’. Then, the outer SELECT statement finds all those segments of other roads that start at any of these endpoints.

This approach assumes that all the LineStrings are directed – it only finds those that roads that start at the specified endpoint(s). To find all LineStrings that either start or end at any of the supplied nodes, we can modify the query to become:

DECLARE @RoadID int;
SET @RoadID = (SELECT RoadID FROM @Roads WHERE RoadName = 'Plumstead Road');

SELECT r.RoadName, rs.SegmentGeometry.STAsText()
FROM @RoadSegments rs
JOIN @Roads r ON rs.RoadId = r.RoadId
JOIN (SELECT SegmentGeometry.STStartPoint() AS Node
 FROM @RoadSegments rs
 WHERE rs.RoadID = @RoadID) RoadIntersections
 ON rs.SegmentGeometry.STEndPoint().STEquals(RoadIntersections.Node) = 1
 OR rs.SegmentGeometry.STStartPoint().STEquals(RoadIntersections.Node) = 1
WHERE rs.RoadID <> @RoadID;

This query now assumes all roads are two-way. Note that I’ve included the SegmentGeometry column in the results to demonstrate that the results now include the two different segments of Britannia Road that intersect Plumstead Road:

Britannia Road    LINESTRING (1.315038 52.635229, 1.316052 52.63399, 1.316401 52.633518)
Britannia Road    LINESTRING (1.316401 52.633518, 1.316497 52.632869, 1.316642 52.632542)
Belsize Road    LINESTRING (1.317538 52.632697, 1.317307 52.633448, 1.317098 52.633749)
Vincent Road    LINESTRING (1.31734 52.633818, 1.315982 52.635498, 1.315038 52.635229)

Approach #3 Two Tables – Segmented LineString Edges and Intersection Point Nodes

There are still problems with the previous model. From a theoretical point-of-view, it is not necessarily a good idea to tie the spatial relationship between two features to their logical relationship so closely. Just because two roads “touch” doesn’t necessarily mean that one is accessible from the other. Also, it cannot model more complex connections and restrictions between objects that are geographically superimposed but not logically connected (think, for example, of roads that are “no right turn” when approached from a certain direction, but accessible when approached from the other).

Another fundamental problem with the previous approach comes from its practical implementation – since it relies on using the STEquals() method to compare and join the endpoints of road segments together, it is likely to be slow performing when compared to, say a direct join between integer ID fields representing those endpoints in two tables.

Suppose instead, that we added a new table, RoadIntersections, to explicitly define all those points at which two or more roads connected. The RoadIntersections table will contain one row for every road segment that joins a particular intersection. A T-Junction will therefore have three rows inserted into the RoadIntersections table, representing the three road segments that meet at the intersection. The example road network can be modelled using this approach as follows:

DECLARE @Roads TABLE (
 RoadId int,
 RoadName varchar(32)
);

INSERT INTO @Roads VALUES
(1, 'Britannia Road'),
(2, 'Belsize Road'),
(3, 'Vincent Road'),
(4, 'Plumstead Road');

DECLARE @RoadSegments TABLE (
 SegmentId int,
 RoadId int,
 SegmentGeometry geography
);

INSERT INTO @RoadSegments VALUES
(1, 1, 'LINESTRING(1.313772 52.636871, 1.315038 52.635229)'),
(2, 1, 'LINESTRING(1.315038 52.635229,1.316052 52.63399,1.316401 52.633518)'),
(3, 1, 'LINESTRING(1.316401 52.633518,1.316497 52.632869,1.316642 52.632542)'),
(4, 2, 'LINESTRING(1.317538 52.632697,1.317307 52.633448,1.317098 52.633749)'),
(5, 3, 'LINESTRING(1.31734 52.633818,1.315982 52.635498,1.315038 52.635229)'),
(6, 4, 'LINESTRING(1.314546 52.633479,1.31529 52.633298,1.315902 52.633363,1.316401 52.633518)'),
(7, 4, 'LINESTRING(1.316401 52.633518,1.317097 52.633749)'),
(8, 4, 'LINESTRING(1.317098 52.633749,1.31734 52.633818)'),
(9, 4, 'LINESTRING(1.31734 52.633818,1.318332 52.634119)');

DECLARE @RoadIntersections TABLE (
 IntersectionId varchar(32),
 IntersectionLocation geography
);

INSERT INTO @RoadIntersections VALUES
('A', 'POINT(1.315038 52.635229)'),
('B', 'POINT(1.316401 52.633518)'),
('C', 'POINT(1.317097 52.633749)'),
('D', 'POINT(1.31734 52.633818)');

DECLARE @RoadIntersection_Segments TABLE (
 IntersectionId varchar(32),
 SegmentId int
);

INSERT INTO @RoadIntersection_Segments VALUES
('A',1),
('A',2),
('A',5),
('B',2),
('B',6),
('B',3),
('B',7),
('C',7),
('C',4),
('C',8),
('D',5),
('D',8),
('D',9);

To view all the road segments and intersections in the model, we can now run the following query:

SELECT IntersectionId, IntersectionLocation.STBuffer(7.5) FROM @RoadIntersections
UNION ALL
SELECT CAST(SegmentId AS varchar(32)), segmentgeometry.STBuffer(2)
FROM @RoadSegments rs JOIN @Roads r ON rs.RoadId = r.RoadId

image

The database structure may seem a lot more complicated than the original single-table approach, but it’s actually more efficient, and a lot more useful. This approach models the road network as a graph (as in graph theory, not as in Excel…), in which each road intersection is a node, and each road segment segment is a distinct edge, connecting exactly two nodes. The spatial relationship between two LineStrings has been separated from their logical relationship – even if two LineStrings touch, the roads represented by those LineStrings are only defined to be topologically connected if they share a common intersection as defined in the RoadIntersections table.

Why does this matter? Well, if your road network conforms to a graph model as above, not only can you perform spatial functions such as STDistance() or STLength() on the LineString geometries that represent individual roads, but you can also apply graph algorithms such as Djikstra or A* to perform routefinding across an entire table of spatial data, traversing across the edges (road segments) of the graph from a chosen start node to another end node, according to the defined network structure. And you can do this right from within SQL Server using a SQLCLR function – but that’s for another post….

As a side-note, you may think that this subject is only relevant if you’re collecting your own spatial data – surely if you’ve imported your road geometry data from an existing source, it will come supplied with the associated links between roads, right? However, this is generally not the case – the most commonly used format for spatial data interchange is the ESRI Shapefile, which represents a single layer of data, containing geometric “features” with certain “attributes”. However, it it does not explicitly define structured relations between those features – these must be created yourself, inferred from the associated attribute data.

Fun with SQL Server Management Studio

Next time you’re waiting for that long-running SSIS job to finish (as I was this morning), why not pass the time by doing some doodling using the geometry datatype and the SQL Server Management Studio Spatial Results tab?

The sum total of my efforts this morning as follows:

DECLARE @SqlBitsLogo table (
  geom geometry
);

INSERT INTO @SqlBitsLogo(geom) VALUES
  ('POLYGON ((1.5197357088327408 0.45103654265403748, 1.7697358727455139 0.45103654265403748, 1.7697358727455139 1.5482594966888428, 1.5197357088327408 1.5482594966888428, 1.5180402845144272 1.5360659956932068, 1.5217091739177704 1.4768347442150116, 1.51995949447155 1.4646955132484436, 1.5150224268436432 1.4557437002658844, 1.5058467984199524 1.45103719830513, 1.4811682403087616 1.4896928369998932, 1.4522375613451004 1.5198442041873932, 1.4200041890144348 1.5412336587905884, 1.3853972405195236 1.553549200296402, 1.3493593484163284 1.55653315782547, 1.3128128200769424 1.549887090921402, 1.2767071127891541 1.5333397686481476, 1.2419577389955521 1.5065927803516388, 1.2030444145202637 1.4375416338443756, 1.1787049323320389 1.3284378349781036, 1.1669522672891617 1.1933059096336365, 1.1658264994621277 1.0461568236351013, 1.1733541786670685 0.90100157260894775, 1.1875482499599457 0.77185109257698059, 1.2064488232135773 0.67275705933570862, 1.2280688434839249 0.61770331859588623, 1.2661480307579041 0.58193668723106384, 1.3070212602615356 0.56404659152030945, 1.3490541726350784 0.56046587228775024, 1.390625923871994 0.56758663058280945, 1.430108904838562 0.58184173703193665, 1.4658687561750412 0.59965047240257263, 1.5197357088327408 0.63159221410751343, 1.5197357088327408 0.45103654265403748), (1.4919579029083252 0.7288145124912262, 1.4592837244272232 0.732449471950531, 1.4385521113872528 0.746419757604599, 1.4274708330631256 0.76875865459442139, 1.423734113574028 0.79749944806098938, 1.4250565469264984 0.83067545294761658, 1.4364023208618164 0.93714797496795654, 1.4371686428785324 0.99443966150283813, 1.4344898760318756 1.1754564940929413, 1.4347001165151596 1.2337926030158997, 1.436849907040596 1.288602203130722, 1.4417734146118164 1.3383798003196716, 1.4502912163734436 1.381592720746994, 1.478069007396698 1.381592720746994, 1.5010318011045456 1.3590503633022308, 1.5156938135623932 1.3285463452339172, 1.5236622989177704 1.2915590107440948, 1.5265648663043976 1.2495939135551453, 1.5260019898414612 1.2041701674461365, 1.5197357088327408 1.0621480643749237, 1.5239267796278 0.921604335308075, 1.5243065655231476 0.87578725814819336, 1.5223805606365204 0.83243870735168457, 1.5170705020427704 0.79271155595779419, 1.5072845220565796 0.75778588652610779, 1.4919579029083252 0.7288145124912262))'),
  ('POLYGON ((3.9264891371130943 0.65814930200576782, 3.9641817584633827 0.65937000513076782, 3.9894503280520439 0.722561776638031, 4.0106634497642517 0.81659667193889618, 4.0255153477191925 0.92991869151592255, 4.0316459983587265 1.0509853959083557, 4.0267360508441925 1.1682407855987549, 4.0084661841392517 1.2701288759708405, 3.9745034873485565 1.3450936675071716, 3.9225150644779205 1.3815927058458328, 3.8932588621973991 1.3841155022382736, 3.8680445402860641 1.3793818801641464, 3.8460583090782166 1.3693992346525192, 3.8264727890491486 1.3562291860580444, 3.7912487015128136 1.3283971399068832, 3.7739825248718262 1.3178177028894424, 3.7558482885360718 1.3121482133865356, 3.7540036663413048 1.3346091657876968, 3.7580455541610718 1.4358597844839096, 3.7563501298427582 1.45848348736763, 3.7513588070869446 1.4777977466583252, 3.7419593930244446 1.4927038997411728, 3.72126168012619 1.480415478348732, 3.699031300842762 1.4696597158908844, 3.6047387048602104 1.4319942146539688, 3.561403714120388 1.4093704968690872, 3.561403714120388 0.77048119902610779, 3.5887746140360832 0.76486594974994659, 3.614124558866024 0.75722977519035339, 3.7067624256014824 0.717923104763031, 3.7558482885360718 0.70103670656681061, 3.7576793432235718 0.74259489774703979, 3.7599444314837456 0.749010369181633, 3.7638235539197922 0.75381180644035339, 3.769737184047699 0.7565922886133194, 3.7813338711857796 0.7317306250333786, 3.7963756620883942 0.71031405031681061, 3.8149574846029282 0.69242395460605621, 3.837160736322403 0.678168848156929, 3.8630939126014709 0.667643666267395, 3.8928383961319923 0.660929799079895, 3.9264891371130943 0.65814930200576782), (3.8114038780331612 0.79825899004936218, 3.777454748749733 0.82671494781970978, 3.7575572729110718 0.87358999252319336, 3.7487817704677582 0.93381138145923615, 3.748212106525898 1.0022657364606857, 3.7529321610927582 1.07388037443161, 3.7666176110506058 1.2061368674039841, 3.769737184047699 1.2565926313400269, 3.8114038780331612 1.2565926313400269, 3.8187823593616486 1.2070320397615433, 3.8241534531116486 1.1508254110813141, 3.827476479113102 1.0901835560798645, 3.8286700621247292 1.0273444205522537, 3.827720619738102 0.96450529992580414, 3.824560359120369 0.903904139995575, 3.8191350027918816 0.84775175154209137, 3.8114038780331612 0.79825899004936218))'),
  ('MULTIPOLYGON (((4.0752929449081421 1.4232594072818756, 4.0961127281188965 1.4302174150943756, 4.1195095516741276 1.4345848262310028, 4.1449544467031956 1.4369177222251892, 4.283626414835453 1.4371483027935028, 4.283626414835453 1.5899261832237244, 4.2537056058645248 1.5903330743312836, 4.1988959982991219 1.5861148685216904, 4.1484944894909859 1.5774749964475632, 4.0752929449081421 1.5621483772993088, 4.0752929449081421 1.4232594072818756)), ((4.2697375155985355 0.61770331859588623, 4.2697375155985355 1.3399260193109512, 4.2419190295040607 1.3417028188705444, 4.2171116136014462 1.3404549807310104, 4.1279188469052315 1.3254945874214172, 4.1031114310026169 1.3242603093385696, 4.0752929449081421 1.3260371088981628, 4.0752929449081421 0.63159221410751343, 4.1284478195011616 0.632650151848793, 4.1788086369633675 0.630941167473793, 4.2260227538645267 0.62607191503047943, 4.2697375155985355 0.61770331859588623)))'),
  ('POLYGON ((1.8947359499939289 1.74270405720262, 2.1586250129029949 1.74270405720262, 2.1586250129029949 0.56214772313574857, 1.8947359499939289 0.56214772313574857, 1.8947359499939289 1.74270405720262))'),
  ('POLYGON ((2.1586250129029949 1.74270405720262, 2.1586250129029949 0.56214772313574857, 1.8947359499939289 0.56214772313574857, 1.8947359499939289 1.74270405720262, 2.1586250129029949 1.74270405720262))'),
  ('POLYGON ((0.53362394130506574 1.256592625528026, 0.54649558607792259 1.167115027460021, 0.59734468645035721 1.0975755935817939, 0.6703088730210458 1.04135539591742, 0.74951221301166493 0.99186263324496993, 0.81909233702089124 0.942478377588519, 0.86318009395890094 0.88658370097214068, 0.86590633273587059 0.81757323879690813, 0.81140190226197673 0.7288144997098952, 0.78751001367674245 0.73442973778783271, 0.771450975308921 0.747898171148683, 0.761739597377029 0.76768713819146284, 0.75687034503408313 0.79233179420018884, 0.75534446512160014 0.82031304095087765, 0.75584631007059455 0.90937017433188727, 0.54751283935291128 0.90937017433188727, 0.56005896307777181 0.74035693353676679, 0.62850654510751058 0.6291779322690032, 0.73253764669635379 0.57230669250863564, 0.85183432909852708 0.56620317285870347, 0.96607187187975674 0.607340895299246, 1.054939117982769 0.69217981843330256, 1.098118128661788 0.81717990086391246, 1.0752909651710421 0.978814664571115, 1.0425082829624071 1.03503486223549, 0.99362587225495025 1.07995676685899, 0.87767934397173963 1.155111438815154, 0.8256570114888182 1.1949877671947109, 0.78762530238124118 1.2428122344961789, 0.77110510919542485 1.303413402931505, 0.78362410616628564 1.3815927079586361, 0.80066648936659612 1.387845424755566, 0.81592528849142643 1.3848614818156, 0.82910210924677985 1.3741328506087189, 0.83988499396165994 1.3571515026049079, 0.84797554834207 1.33540940927415, 0.85307537809401335 1.310412105463427, 0.85487252554649329 1.283637999265725, 0.85306859640551336 1.256592625528026, 1.0752909651710421 1.256592625528026, 1.077142366131522 1.39021901573054, 1.0283616807515641 1.4861663446274731, 0.94432977854899847 1.5456688795258109, 0.840427529041654 1.569974451109541, 0.73202902005885939 1.5603037633086489, 0.63450833942994389 1.51791821018412, 0.56324635667273637 1.4440520590429411, 0.53362394130506574 1.339926013815099, 0.53362394130506574 1.256592625528026))'),
  ('POLYGON ((4.3808487005582828 1.5343705864849371, 4.3881593607612013 1.4875769358354569, 4.3823135452742656 1.44418769281294, 4.3688451119134157 1.4039722800083869, 4.3532879184945887 1.366727246766801, 4.3411758228337236 1.3322084523021851, 4.3380426827467584 1.3001717558285411, 4.3494223560496321 1.2704137066908721, 4.3808487005582828 1.24270372748018, 4.3844565588402427 1.1647143097310479, 4.3833036717952547 1.0819641466549681, 4.3771458986373233 0.9126118214348512, 4.3773629126693212 0.83123155943575622, 4.3832494182872557 0.75552078902259812, 4.3974367106290977 0.68809724195634792, 4.4225153947018194 0.63159221337497629, 4.4559626823834471 0.62939494630100079, 4.4869278720741024 0.62967977721799762, 4.5160755692467784 0.63178210065297424, 4.59950390117285 0.64209026717285955, 4.6586266615151937 0.64548111142282183, 4.6586266615151937 0.78437009190127738, 4.6455380027103388 0.78696069690824855, 4.633005442362478 0.78479055658827268, 4.5998022954668478 0.76978946162643946, 4.5905249455989514 0.7686772647124519, 4.5823191025140417 0.77286834820540529, 4.57529327322812 0.78437009190127738, 4.57529327322812 1.24270372748018, 4.6447377634673481 1.24270372748018, 4.6447377634673481 1.353814911862945, 4.62116461424161 1.359755670988879, 4.6028133651608139 1.370904766882755, 4.5891821712759659 1.386787481349578, 4.5798234411460692 1.4069426595713539, 4.5742353298221321 1.4308684565990879, 4.5719295557321571 1.4580901542377851, 4.5724449640581506 1.488119470915451, 4.57529327322812 1.5204816884370911, 4.5559112074953356 1.5271548699210169, 4.5341691141645777 1.5314408970529689, 4.51052814805384 1.5338416147819429, 4.3808487005582828 1.5343705864849371))'),
  ('POLYGON ((5.1169602970940966 0.9510368684754239, 5.0957743022203319 0.99469737903793842, 5.0587327196337437 1.030436877432541, 5.0130512658982518 1.061442757254196, 4.9659863477087756 1.090875285343869, 4.9247672450062341 1.1219354186735231, 4.8966503644855459 1.1577834240841249, 4.8888514227106334 1.201620258547637, 4.9086268263764126 1.256592625528026, 4.9263541601152161 1.253486612195061, 4.93946994567407 1.2457690506821459, 4.9487337321649667 1.2341994901012749, 4.9549186320768994 1.219537479564438, 4.9587841945218551 1.202583258314627, 4.9641824185677956 1.1454814411452621, 5.0033805780973593 1.14648513104325, 5.0400423861279524 1.144952469442267, 5.0735032371865794 1.14020528749232, 5.1030713990462511 1.1315925430974161, 5.0897114727013992 1.2302932375253179, 5.0404221606839474 1.3039559380114989, 4.9686447696007461 1.3507495886609791, 4.8877799159276449 1.3688566969557781, 4.811255342894496 1.356432643623916, 4.7524987937311494 1.311646372770414, 4.724924448290456 1.2326803918772919, 4.7419600498022669 1.117703645049571, 4.7661842411239972 1.075806373497036, 4.8039582460685768 1.0408942410994251, 4.893096759711586 0.98118825554608857, 4.9309385815411648 0.95095548821342479, 4.9552848432558942 0.91681646830480445, 4.9593538563558486 0.87608564717425741, 4.9364046224721054 0.826036786044814, 4.9150016135663419 0.82373101195483966, 4.8996885609335123 0.82751519413779751, 4.8894889014296261 0.83641276944969856, 4.8834260719106926 0.84944717474655362, 4.8805099458557262 0.86562828350737375, 4.8797639601207354 0.88397953258816964, 4.8808490302807224 0.92325907237973281, 4.7558489478501116 0.92325907237973281, 4.7449575561192336 0.90173399308097213, 4.7397085292202918 0.87695370330224776, 4.7390710505012983 0.85016603372754562, 4.7420549934412657 0.82259168828685225, 4.7476159780112033 0.7954378075331543, 4.754777441067124 0.76995222215043768, 4.769737845897958 0.7288144997098952, 4.8295387750902927 0.7085236877181208, 4.8869525499306539 0.70513284346815852, 4.9408127199970551 0.71629550273903442, 4.9899935249985079 0.73969232806377416, 5.0333420778900262 0.77301754535240363, 5.069705491626622 0.8139518171379484, 5.0979715692943079 0.86017580595343435, 5.1169602970940966 0.90937017433188727, 5.1169602970940966 0.9510368684754239))'),
  ('POLYGON ((2.8591598570346832 0.3754885196685791, 3.1508267223834991 0.3754885196685791, 3.2155782878398895 0.38712590932846069, 3.4238168299198151 0.40805420279502869, 3.4879716038703918 0.42027479410171509, 3.5445579886436462 0.44010445475578308, 3.5905649662017822 0.47045928239822388, 3.623049259185791 0.51437750458717346, 3.5910261273384094 0.52054885029792786, 3.5636823773384094 0.53139954805374146, 3.54100438952446 0.54690247774124146, 3.5230057835578918 0.56709834933280945, 3.5097001194953918 0.59198716282844543, 3.5011009275913239 0.62158244848251343, 3.4972082376480103 0.65589779615402222, 3.4980491697788239 0.69493317604064941, 3.4486920535564423 0.7033696174621582, 3.4130882024765015 0.726888507604599, 3.3901931941509247 0.76169213652610779, 3.3789084851741791 0.80400985479354858, 3.3781760632991791 0.85005751252174377, 3.3869380056858063 0.89605093002319336, 3.4040956795215607 0.93819233775138855, 3.4286046922206879 0.97271114587783813, 3.3928787708282471 0.99838662147521973, 3.3689800798892975 1.0328104794025421, 3.3558507263660431 1.0734056532382965, 3.3524327576160431 1.1175951361656189, 3.3576682209968567 1.1627611815929413, 3.3704991936683655 1.2063267529010773, 3.3898676931858063 1.2456876635551453, 3.4147157967090607 1.2782669067382813, 3.3866938650608063 1.3136537671089172, 3.3713129758834839 1.3545202016830444, 3.3677458167076111 1.3980315327644348, 3.3751649856567383 1.4413529336452484, 3.3927431106567383 1.4816497266292572, 3.4196257293224335 1.516087144613266, 3.4550125896930695 1.541830450296402, 3.4980491697788239 1.55604487657547, 3.4980491697788239 1.6671560406684875, 3.509252518415451 1.6906613707542419, 3.5280784964561462 1.7065712213516235, 3.5758351385593414 1.7282590568065643, 3.59843173623085 1.7403847277164459, 3.6159420609474182 1.7575830817222595, 3.6252058446407318 1.7830415368080139, 3.623049259185791 1.8199339210987091, 3.5871334373950958 1.8517264723777771, 3.5420487821102142 1.8743501901626587, 3.4898297786712646 1.8898395597934723, 3.4324838221073151 1.9002019762992859, 3.2500428259372711 1.9208861291408539, 3.1924934387207031 1.9310451149940491, 2.7619443535804749 1.9310451149940491, 2.7079282104969025 1.9190822243690491, 2.6486494839191437 1.9123954772949219, 2.5259280502796173 1.9031723737716675, 2.4683040380477905 1.8948308825492859, 2.4170480370521545 1.8801146149635315, 2.3750693798065186 1.8561210036277771, 2.3452774286270142 1.8199339210987091, 2.3968182504177094 1.8037663698196411, 2.4516278505325317 1.7908675968647003, 2.5088992118835449 1.7804238200187683, 2.7462583184242249 1.7469629645347595, 2.8036042749881744 1.7366005480289459, 2.7423927485942841 1.7278657257556915, 2.6787669658660889 1.7254785597324371, 2.6140086054801941 1.7284896373748779, 2.5493791103363037 1.7359766364097595, 2.4861398637294769 1.7470172047615051, 2.4255726039409637 1.7606755197048187, 2.3689319491386414 1.7760292887687683, 2.3174996376037598 1.7921561300754547, 2.3174996376037598 1.5004892647266388, 2.3500992059707642 1.4827347993850708, 2.3853775560855865 1.4676659107208252, 2.4230430424213409 1.4549841284751892, 2.4628040790557861 1.4444047212600708, 2.5043690502643585 1.4356291890144348, 2.5917307734489441 1.4222828447818756, 2.63694429397583 1.4171558916568756, 2.6647220849990845 1.4171558916568756, 2.7220476865768433 1.408502459526062, 2.7850224673748016 1.4055049419403076, 2.984512597322464 1.4070918560028076, 3.0467549264431 1.4033619165420532, 3.1029751300811768 1.3935962915420532, 3.1508267223834991 1.3754891753196716, 3.0976718664169312 1.3765606880187988, 3.0473110377788544 1.3748381435871124, 3.0000969171524048 1.3699688911437988, 2.956382155418396 1.3616002798080444, 2.956382155418396 1.0838223397731781, 2.9039190113544464 1.07961767911911, 2.8520526587963104 1.0806485116481781, 2.7025571167469025 1.0949714183807373, 2.6556006968021393 1.0967617928981781, 2.6104956865310669 1.0937235951423645, 2.5674997866153717 1.0838223397731781, 2.6542850732803345 1.0837951898574829, 2.6693607568740845 1.0815165638923645, 2.6822934448719025 1.0770948827266693, 2.6924998760223389 1.0699334442615509, 2.6924998760223389 1.0560445189476013, 2.6658003628253937 1.0503479242324829, 2.6248050630092621 1.0499003231525421, 2.5737728476524353 1.0538744032382965, 2.5169762074947357 1.0614698827266693, 2.4586808085441589 1.0718458592891693, 2.4031591415405273 1.0842021107673645, 2.3546768426895142 1.0977247953414917, 2.3174996376037598 1.1116001307964325, 2.3174996376037598 0.80604436993598938, 2.4135893881320953 0.771918922662735, 2.4800160229206085 0.751546710729599, 2.5157555043697357 0.7438834011554718, 2.5539771020412445 0.7387022078037262, 2.6230553984642029 0.73659989237785339, 2.6288944184780121 0.7250710129737854, 2.6420576870441437 0.72087991237640381, 2.6979048848152161 0.72465053200721741, 2.7127635478973389 0.7221413254737854, 2.7211389541625977 0.713162362575531, 2.7202776670455933 0.69493317604064941, 2.5094553232192993 0.7202153205871582, 2.4580162167549133 0.7277972400188446, 2.4084285199642181 0.7372509241104126, 2.3613703846931458 0.7492138147354126, 2.3174996376037598 0.76437768340110779, 2.3174996376037598 0.47271081805229187, 2.3517539501190186 0.45315241813659668, 2.3894329965114594 0.43701201677322388, 2.4301299154758453 0.42388266324996948, 2.4734445512294769 0.41338461637496948, 2.5189632475376129 0.40508380532264709, 2.5662926435470581 0.39858695864677429, 2.6647220849990845 0.3893774151802063, 2.7202776670455933 0.3893774151802063, 2.7375777661800385 0.38758707046508789, 2.8178865015506744 0.39150688052177429, 2.8352747559547424 0.38979789614677429, 2.8493942320346832 0.38482013344764709, 2.8591598570346832 0.3754885196685791))')

SELECT geom FROM @SqlBitsLogo;

I’m quite pleased with how it came out…

image