Jun 122018
 

A quick review of the D435 with pictures of real-life situations; I wish I’d had this when I was considering buying a D435.

Unboxing is a breeze. The D435 is small enough to hide in your hand, in a sleek aluminium case weighing in at 71.8g. It comes with a 1m USB3 cable and a cute, flimsy little plastic tripod. Amusingly, the 28.6g tripod isn’t up to the task of supporting the camera once the 41.2g cable is plugged in and it falls over; I’ve given it to my grand-daughter for her Lego. The standard camera thread mounting underneath works perfectly for a real tripod; for an aligned mounting there are 2 M3 tapped holes on the back, 45mm apart but they are only 2.5mm deep.

All the software is available on GitHub, quick-start with the Intel.RealSense.Viewer.exe to get this, depth at 1280×720:

The toy tripod is in the foreground. Notice that I switched on the hole-filling filter, which is off by default. Switching to 3D with depth colours and quads:

and using the camera colours:

The red light meter to the right of the tripod reads 28 lux: I was astonished at the depthmap accuracy in such poor light.

On of the major failings of depth cameras is reflections, so my next tests were with glass. Here, an interior, again the light meter in the foreground reads 15 lux:

The right-hand side of the table is glass and thus appears further away, which is normal. The wine glass next to the light meter is captured perfectly. The model on the front-left of the table is a tiny Sterling engine. The Viewer has a nice zoom function:

The stereo matching doesn’t get the inner details of the wheel, but the outline is well delimited.

Next, looking outside from behind a window of 6mm + 15mm glass at 260 lux. My cursor was on the middle of the small hedge to the right of the wall, the 32 metres measured through 2 layers of glass in the depth stream seem quite reasonable:

Stepping outside to 1’800 lux (and light rain), there are a few artefacts due to the reflection in the water in front of the chair and the window on the right appears to be at the same distance as the trees, which makes perfect sense as that is what is reflected. Notice that the depth images cover a greater area than the colour images. This scene is extremely unfavourable for a depth camera: poor light, reflections, textureless walls and rain:

Zooming in on the flower pot in the foreground, it isn’t immediately clear if the flowers themselves are distinguished:

but a little image enhancement shows that they indeed are:

Panning left, there’s a buddha statue about 8 metres away. I positioned the cursor on the roof of the building above the Catalpa tree; the reading was 65.54 metres, which seems right (and is quite astonishing):

The buddha is clearly rendered in the zoomed depthmap, with only a handfull of dead pixels:

The camera button on the depthmap outputs the PNG, the RAW and the metadata:

Frame Info:
Type,Depth
Format,Z16
Frame Number,64305
Timestamp (ms),1528815686864.40
Resolution x,1280
Resolution y,720
Bytes per pixel,2

Intrinsic:,
Fx,639.315613
Fy,639.315613
PPx,637.479370
PPy,362.691040
Distorsion,Brown Conrady

which is a nice touch.

The SDK, librealsense-2-12.0 has the CPP sources for everything and wrappers for C#, Unity, OpenCV, PCL, Python, NodeJS and LabView. I tried the C# example SLN; after adding the reference to Intel.Realsense.DLL, they ran on first compile. The Depth tutorial generates a cute 70’s-style image made with characters (it’s my hand):

and the 2nd example – 100 lines of code –  with depth and colour:

Conclusion

Over the years I have studied many depth cameras, particularly for outdoor use: Kinect, Stereolabs, SwissRanger, PrimeSense, Bumblebee, to name but a few. None were satisfactory, either because they were blinded by sunlight or cripplingly expensive. The D435 works perfectly both inside (with IR) and outside (with stereo matching); it is cheap, resilient and accurate. I think it is going to be a revolutionary game-changer in computer vision.

Aug 282017
 

TLDR: Results . Lessons learned . Summary & Conclusions . Quick-start guide

I am building an autonomous lawnmower which needs to know its position precisely – in the order of centimetres. Standard GPS is an order of magnitude too coarse and until recently, all flavours of differential GPS were very expensive. This changed last year when UBlox released the C94-M8P with a price tag of €359 for a complete solution. The announcement said:

The NEO-M8P module series introduces the concept of a “Rover” and a “Base Station”. By using a correction data stream from the Base Station, the Rover can output its relative position with stunning cm-level accuracy in good environments.

An old dog, I was wary of “in good environments”. That RTK is capable of centimeter accuracy when stationary and with a horizon-to-horizon view of the sky, I have no doubt; but what about in a less GPS-friendly environments? This article is the result of real-life testing in my garden, cluttered by surrounding buildings and vegetation..

Objectives

My aim was to establish whether or not the UBlox solution would be sufficiently accurate for my mower,
in particular how would it perform in less-than optimal conditions, so I devised a series of experiments, which I have named for the purposes of this discussion.

  1. Open. Stationary for 24 hours in the middle of the lawn, with a decent view of the sky. A base-line test to determine the system performance.
  2. Sky270. The receiver is next to the corner of a building, 90° of sky are obscured.
  3. Sky180. The receiver is close to a wall of a building, 180° of sky are obscured.
  4. Sky090. The receiver is in a corner of a building, 270° of sky are obscured.
  5. Canyon. The receiver is between two buildings with a heavy tree canopy, most of the sky is obscured.
  6. Mobile. The receiver is moved around the garden, stopping for 10 seconds at a known location in each of the previous tests.

Tests 2-6 are performed for an hour. As described later, I discovered that the receiver needs 25-30 minutes to settle on a solution and these data were removed.

All the tests are made with the antenna 54cm from the ground.

Test setup

I built a rooftop base station

powered by a solar cell with an MPP charger, the DC1621A evaluation board based on the LTM8062 chip from Linear Technology. As I had no idea how much power the C94-M8P would need – after all it has a UHF transmitter – I opted for a 10Ah 2S Lipo. On the left we have the DC1621A, on the right the UBlox board and the battery below. There’s a cell-balancer and packets of silica gel tucked behind the DC1621A. After verifying that every was working correctly, I mounted it on my roof:

My rover was simply a low table in the garden, with the antenna 54cm off the ground.

The rover’s signature is

In what follows, I am focussed solely on horizontal position – the X, Y values; I ignore altitude as it is only relevant for surveying and aviation.

All my calculations were performed in 64-bit floating point which gives ~15 decimal digits of precision.

The raw GPS data from the receiver contains latitude/longitude values. To convert these into distances I use Vincenty’s formula. I validated my implementation against 500’000 known distances, which are precise to 0.1µ metre.

Outputs

Each test produces 5 results.

1. Summary statistics

Quality (the NMEA term) is the type of Fix that the GPS currently has. Possible values are:

  1. Autonomous GNSS
  2. Differental GNSS
  3. 3D fix
  4. RTK Fixed
  5. RTK Float

It is worth noting that the ‘best’ fix is 4.

SVs is the number of satellites in view. I configured the rover UBX-CFG-NMEA to high precision mode (to output 9 digits of precision on latitude and longitude) and as a result considering mode is disabled. It is unclear whether satellites-in-view is those seen or those actually used.

hDop is the horizontal dilution of precision created by the constellation geometry.

ErrorLat is the distance in millimetres from the average centre of all the readings. Similarly ErrorLon.

ErrorLatLon is Euclidean distance from the measured point and the averaged centre of all the readings.

The ErrorX values are calculated using Vincenty’s Formulæ.

2. Error statistics

Limit is simply a reminder of the percentages associated with the number of standard deviations.

σ is the standard sigma calculation, the square root of the variance.

Out is the actual number of measurements that exceed the CEP below.

CEP is obtained by finding the number of samples ‘Out’ which exceed the limit %.

The difference between σ (a statistical calculation) and CEP (the true value for the data set in question) is readily explained: The statistical σ is valid only when the data are normally distributed (a Gaussian). Even over 24 hours, GPS readings are not Gaussian because the average is not stationary. Disraeli’s quip remains as true as ever.

3. Deviation map

An X-Y plot of ErrorLat horizontally against ErrorLon vertically:

A point’s colour indicates its age as hue corrected for the human eye. First blue, then green, yellow, orange and finally red.

4. ErrorLatLon over time

The Euclidean distance from the average centre to the measured point, in mm, over time:

5. Histogram

The number of errors at each bucket of the Euclidean distance from the average centre:

 

Test results

Open test

Sky visibility is typical for a residential area. To the north and west a single-storey building

The receiver, antenna and PC are on a low table in the foreground.

To the east, a 4-storey building (actually 3-storey but on higher ground)

To the south and west, mature oak trees and 2-storey houses

The receiver is in Fixed mode 71% of the time.

The 3σ CEP of 712 mm is surprisingly high and this is visible both in the deviation map:

and in the histogram (I have added log(Count) overlayed on Count to show the long tail more clearly):

The problem occured mid-morning and early afternoon:

The cause appeared to be due to receiver occasionally losing the RTK solution. To analyse this I zoomed in on the first occurrence and plotted the absolute error along with the Fix. 3D=2, Fixed=4 and Float=5. To highlight the differences, Fixed errors are green, Float errors are blue and 3D errors are red. Notice that the vertical scale is logarithmic:

At 10:28:18 and 10:32:52 the receiver switches for one measurement from Float to 3D. The same thing happens at 11:21:18:

and in both cases takes some 20 minutes before settling down.

The replay in ucentre shows nothing untoward at the instant of the loss:

I note that 20 minutes is the time that it takes the receiver to lock on cold start (details in Lessons Learned below) and postulate that momentary interference can cause a complete loss of the solution.

There were 4 such events in the 24-hour test. 4 in 86’400 = 1 in 21’600; a frequency less than 4σ, so I sought an explanation.

On the weekend of the tests there was an airshow nearby with several passes of military aircraft in formation. One can reasonably assume that 6 fighters at low altitude produce vast quantities of electromagnetic radiation and I imagine that military RF applications care little about the interference that they generate. Furthermore, the dropouts only occurred during the day, when the aircraft were flying, so I decided that they were the most likely cause. Consequently I decided to use a subset of the data with these events excluded. I also excluded the noisier data as of 04:32 in the morning, on ‘benefit of the doubt’ reasoning, for want of an explanation. The results then become:

   

Which is what I expected and much more reasonable.

Sky270 test

The receiver is against the NW corner of the house, 90° of obscured sky.

The receiver is slightly more in Float rather than Fixed (4.63 average quality). The 3σ CEP is 54 mm.

There is significantly more longitude error than latitude.

Sky180 test

The receiver is near the NW wall of the house, half the sky is obscured.

The receiver is continuously in Fixed mode. The 3σ CEP is 43 mm.

The histogram has longer tails due to the less favourable constellation geometry (practically no SVs to the SE):

The CEP is better than the Sky270 test, where only 90° of sky were obstructed. I attribute this to the fact that due to the lay of the land, the angle of the apex of the house is much larger when viewed from a corner. Additionally the sky view away from the house in this position is a little more cluttered.

Sky90 test

The receiver is in a corner of a building, facing SW, 270° of sky are obscured.

The receiver is in Fixed mode for the whole hour. The 3σ CEP is 185 mm.

  

The deviation shape clearly reflects the SW direction of the corner.

Canyon test

The receiver is in a NE-SW canyon between two houses with heavy canopy:

The wide-angle lens gives the impression that there is a wide view towards the camera. This is not the case, the two houses’ walls are almost parallel, some 12 metres apart and the canopy almost reaches to above the camera.

The receiver never achieves Fixed mode. The 3σ CEP is 135 mm.

The error and histogram are noisier and the canyon effect is clear in the deviation map, where the longitude errors are double the latitude errors, again due the the constellation geometry (few SVs NE and SW):

Mobile test

I walked around the garden, pausing 10 seconds at each of the SKYNNN points to hold the GPS at a marked position.

The red points are the 9 nearest points to the average of each location.

The receiver was in Fixed mode 7% of the time.

The average position error when moving is 19 mm.

Lessons learned

Here are some tips which might be useful to those envisaging the C98-M8P solution.

Status

The status bar at the bottom-right of U-Centre is very helpful:

From left to right:

  • Version of connected GPS
  • Port, if a GPS is connected
  • File currently open (recording or replay)
  • The last message-type received. This is particularly useful to confirm that you are receiving RTCM messages, where you will see the field display UBlox-NMEA-RTCM in quick succession.
  • Elapsed time
  • TOD

Antennæ

UBlox stress repeatedly that the solution’s accuracy depends on the antenna quality. I verified this and was unable to get the receiver to converge to ‘fixed’ mode (the highest accuracy) with the supplied patch antennas, so I bought a pair of Garmin GA38.

Startup accuracy

I was disappointed with my initial tests, until it dawned on me that the receiver needs quite a while to converge on the optimal solution. Here is an error plot on power-up:

After 52 seconds the error is momentarily down to 24 mm, but it quickly rises to 1’400 mm, thereafter gradually getting close to zero over the next 13 minutes. It again wanders up to 200 mm over 5 minutes and achieves first Fix at 25:54. It finally alternates between Fix and Float for a couple of minutes before permanently settling to Fixed at 26:47. All this is perfectly reasonable, given the calculations involved.

The takeaway is that you should give the receiver half an hour to warm-up before you can expect accurate results. Note also that seeing it switch to Fixed is not a guarantee that it has completely settled.

Fixed .vs. Float

It is apparent from the Sky090 test results that Fixed mode is an indicator of better quality but not a guarantee. All that can be said is that if the quality is 4 or 5, it’s RTK; lower values are standard GPS.

Power

On a 7.45 V battery, the base station consumes ~0.077 A = 0.573 W.

At 12.58 V, the rover consumes 0.066 A = 0.83 W.

So the evaluation board seems to use a linear regulator and I imagine that a non-negligible amount of that power goes to the blinking LEDs.

At closer to 3.5 V, you can assume a power consumption base or rover << 0.5 W.

Interference

As I discovered in the Sky360 test, unexpected interference can cause the unit to lose the RTK solution. The application can detect when this occurs by observing a Quality < 4; the consequence is that the position accuracy will be significantly degraded for the following 20 minutes.

Baseline & Radio range

Although not relevant for my use-case, I nonetheless tested the maximum baseline distance by driving away from the base station with the unit on my dashboard.

The receiver was never in Fixed mode in the car and lost Float mode (falling back to a 3D fix) between 576 and 745 metres away from the base. The distances to the loss points are in red:

(The terrain in my area is almost perfectly flat.)

This limitation is certainly not due to the (amazing) UHF radio, I drove 13 kilometres away from the base and the RTCM stream arrived faultlessly during the whole journey.

Survey-In

There seems to be some confusion about precision and accuracy of the base station. Given the non-Gaussian nature of GPS measurements, acquiring a TRUE position (whatever that means) by averaging would take weeks (months?) simply because the precision of the average depends on the ‘Gausian-ness’ of the data. The base station’s absolute coordinates, insofar as they are within metres of the truth, are irrelevant because the base transmits only satellite relative errors to the rover. The rover uses these relative numbers to adjust it’s solution; the accuracy of the result is not contingent on the precision of the base. Given that the satellites are ~20’000 Km away, a rover-base distance < 100 KM means that they both see identical distortions and this is why RTK baselines are generally recommended to be less than 50-100 Km.
If follows that it is perfectly acceptable to perform a Survey-In in 1 minute with an error of 5 metres, your accuracy will be no better than surveying for an hour with a 30cm error. That said, expect a survey-in of 1 metre to take about 10 minutes.

Deviation Map

You can scroll in and out with the mouse-wheel, but the minimum is 1 metre. To scroll in more, hold the Shift key, whence you can scroll in to a diameter to 20 cm.

Radio baud rate

The default baud rate is 19’200. Initially, I couldn’t get the RTCM messages to flow and finally discovered an error message: “GNTXT More than 100 frame errors, UART RX was disabled”. Reducing the baud rate to 9’600 fixed this, with no apparent consequences.

Summary

What does all this mean in layman’s terms?

Assuming the following conditions:

  • Urban environment
  • Reasonable antenna
  • 50 cm off the ground
  • During one hour, from a known truth position

I can make the following assertions about the accuracy and precision:

Accuracy. The error of the reported position will be less than 28 mm from the truth 99.73% of the time. In an urban canyon the error rises to 68 mm and with only 25% of the sky visible 93mm:

On average, you will get one reading worse than these values every 6 minutes.

Precision. Will never be worse than the precision of your base station ±356 mm (the radius of the 24-hour CEP).

Conclusions

The promised accuracy is indeed ‘stunning’.

When combined with a TOF camera, the accuracy of a few centimetres is more than adequate for the mower I am building, so I’m perfectly satisfied with it.

That said, the limitations I discovered preclude this solution in some other use-cases that come to mind. Both UAV and farming applications require a much longer baseline and the inability to maintain Fixed mode accuracy is a handicap for those needing consistent high accuracy.

If UBlox can fix the baseline limitation and significantly improve the unit’s ability to stay in Fixed mode, this GPS will become a truly amazing feat of engineering.

In the spirit of openness, you are welcome to use the data that gathered, for whatever purpose you see fit. Download here.

I am indebted to my local surveyors, JC Wasser, for explaining the finer points of their art and their advice on how to present my results.

sorry, you'll have to type this yourself

Appendix: Quick Start

If you’d like to try this solution, here’s a summary of the user guide, along with a few tips.

The user guide is clear and setting-up the two stations is easy once you get the hang of u-centre (a nice piece of software, by engineers, for engineers). Assuming a classic setup with a fixed base station, here’s a quick-start guide:

  • Download the latest firmware. When you install it (Tools-Firmware Update), it’ll ask for the FIS file which nobody talks about; it’s is hidden away in the u-centre program files, called flash.xml.
  • Download the latest version of u-center

Setting up the Base

  • The base station needs power. It’ll take if from USB but you’re going to need a source of external power once you’ve finished configuring it. It needs to be between 3.7 and 20 VDC, the + and – signs are on each side of the power connector, you’ll need a magnifying glass to read them.
  • Fire up u-centre and connect the card you’ve chosen as base station to a USB port (the cards are identical, it’s your parameters that make them base or rover). Don’t try connecting more than one station to your PC at the same time!
  • In what follows, you need to click ‘Send’ in the toolbar at the bottom to transmit you settings over USB to the base station.
  • In Message view-UBX-CFG-GNSS, make sure GPS and GLOSSNAS are enabled.
  • The base station also needs to know its own location, which it can figure out itself. Expand UBX-CFG-TMODE3, select Mode=1-Survey-In; this is what makes it a base station. The Maximum Observation Time and Required Position Accuracy are actually quite unimportant, because it doesn’t matter what the true latitude and longitude are, so 300 seconds and 3 metres are fine. Assuming you’ve got a good sky view it’ll finish the survey easily in the 5 minutes. If you really insist on better precision, it takes about 10 minutes for an RPA of 1 metre.
  • UBX-CFG-MSG and enable F5-05 RTCM3.2 1005, 1077, 1087 and 1230 for UART1. They should all be set to 1 Hz except 1230 where 10 seconds is enough for the GLOSSNAS Code-phase biases.
  • UBX-CFG –> PRT set the target=1-UART1, Protocol-in=none, Protocol-out=5-RTCM3 and the baud rate to 9’600. This is saying: Send all RTCM messages to UART1, where they’ll get transmitted on the UHF radio link. I couldn’t get the recommended 19’600 baud rate to work, I kept getting “GNTXT More than 100 frame errors, UART RX was disabled” messages.
  • UBX-CFG-CFG and save the configuration. That way, when you power-cycle the base, it will resume operation normally. If you forget this and lose power, go to step 2.
  • After a while, perhaps up to half an hour, in the Data View, you’ll see the base station switch to TIME. If it doesn’t, don’t worry, it’ll get there eventually and FLOAT mode already gives good results.

Setting up the Rover

  • Unplug the base station and connect the other, rover, station.
  • UBX-CFG-PRT set the target=1-UART1, Protocol-in=5-RTCM3, Protocol-out=none and the baud rate to 9’600. This is saying: RTCM messages will coming in on UART1, the UHF radio link.
  • After a moment, the rover should start receiving RTCM messages. You won’t see them by default, UBX-CFG-Messages right-click on RTCM and ‘Enable child messages’. Then open the packet console to see them (you won’t see anything in the Text Console).
  • Once they’re flowing in, time for a beer, the rover has to converge on the solution and this can also take a while, for me 20 minutes at minimum. You can monitor the convergence progress with UBX-NAV-RELPOSNED, click the ‘Poll’ button bottom-left to update the display if it isn’t enabled. The N and E Relative accuracies will fall gradually and eventually reach 0.01 = 10 centimetres.
  • After some time it should switch to Fixed mode. If it doesn’t, don’t worry, as I mentioned in Lessons Learned, Fixed mode is better but not a pre-requisite for decent results.
  • Now, and only now, can you make meaningful observations. Restart u-centre (the only way I found to reset the Deviation Map) and View-Deviation Map (F12). Yellow dots = last value, green dots = previous values. To zoom in closer than 1 metre, press Shift whilst scrolling the mouse wheel, the minimum is 20 centimetres.
 Posted by at 1:54 pm  Tagged with:
Apr 262017
 

[mass noun] combination of computer hardware, software and telecommunications equipment that allow individuals to disseminate vacuous guff to a wide audience.

The ultimate DrivelWare©™ is Twitter. As it’s name and logo clearly indicate, it allows hundreds of millions to parrot sparrows by creating digital noise. It is a fact that sparrows’ tweets have important Darwinian functions: to congregate, warn of danger and attract mates for reproduction. In contrast, human tweets fulfill none of these functions; there is no congregation or dangers in cyberspace and reproduction requires a physical encounter. Twitter has become the de facto leader in DrivelWare©™ due to its limit to 140 characters, which curtails – wisely – the amount of information that can be transmitted. In practice this limit is not problematic as the average tweet length is 28 symbols, a good proxy for the authors’ IQ.

The most pervasive DrivelWare©™ is Facebook, where the gerbil-like publish self-important, whimsical information created by random synapse firings: location, bowel movements, olfactory sensations and so forth. The behaviour is rewarded with ‘likes’ from correspondents, sustaining a Pavlovian feedback mechanism that encourages cyclic eructations.

Finally, the epitomy of DrivelWare©™ is SnapChat, where the mindless content is automatically deleted a few seconds after it is created, thus reinforcing the correlation between the quality and the lifetime of the message.

Apr 292016
 

Disclaimer: I have never flown a model aircraft in my life. I unboxed RF7.5 at 23:30 and crashed my first trainer at 23:50.

They were an instructive 20 minutes and I’ll detail them, hopefully you might find it useful.

RF is a trivial install; pop in the DVD and run setup.exe. The only thing you might like to change is the install directory; the RF install is 6.5GB, if you’re tight on space on your C: drive you might like to put it on another disk. Otherwise, just be patient, it takes several minutes but couldn’t be easier.

If you’re in America, skip this paragraph. We Europeans will launch RF and get “Invalid or corrupt version of WMVCORE.DLL”. This is par for the course, ‘N’ versions of Windows don’t have the correct components of Windows Media Player. With a little sleight of hand you’ll find the reason for your OS and from there (for me Windows 7 N or KN editions) get the Media Feature Pack. Once you’ve installed that, re-booted your PC and uttered a spray of profanities, you’re ready to start flying. Double-click RF on the desktop and you should see something like this:

airfield1

This might not seem very impressive. Well, take off and you’ll see this:

ScreenShot1461882329

I played with my first flight simulator on an IBM PC with 2 5.5″ disks back in 1988 and I’ve seen many in-between. Here I was gob-stopped by the simulation; the specular lighting and level of detail is truly astonishing and the aircraft behaviour is incredibly realistic.

Now, a purist 3D-modeller might argue that the perspective of the fence-posts doesn’t change with the viewing angle, nonetheless when you drive the plane gently into the fence, you hear the propeller tapping the wires:

ScreenShot1461883311

The simulation could hardly be more perfect.

I’ll be back with more when I’ve got a bit more experience with what appears to be a very polished piece of software.

Mar 192013
 

Word 2007 recently balked when re-opening a file I had spent quite some time on, with “The file cannot be opened because there are problems with the contents”. Not to worry, I said to myself, the Details>>> button will tell me what’s wrong. I couldn’t have been more wrong, it said “No error detail available”. This is bad programming at its worst. If Word has figured out that the file is unreadable, it must by definition know why it can’t read it; either it can read it or it can’t and it has to know why. If one of my programmers did something like that they’d be enjoying a very long weekend, apparently not the case at Microsoft.

Some Googling later I’d discovered a raft of morons trying either to make me download some virus-riddled software or promising to repair my file for 22$. SavvyCorrupt is a prime example, he has the balls to post on SourceForge, but without publishing the source. TechRepublic has the usual collection of spam links and if you’re really have a soft spot for viruses, you might like to try wordrepairrecovery DOT com, repairmyword DOT com and all the other spammers.

The solution is remarkably simple and little-advertised: Open Office. I installed it and opened my ‘damaged’ docx file with only a slight loss in fidelity; a “Save As .DOC” and I was a happy man.

As our American friends would say, “your mileage may vary”, but it worked perfectly for me.

Aug 132012
 
Imports System.Data
' Refer to these DLLs:
'   microsoft.sqlserver.connectioninfo
'   Microsoft.SqlServer.Management.Sdk.Sfc
'   Microsoft.SqlServer.smo
'   Microsoft.SqlServer.sqlenum
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Sdk.Sfc
Imports Microsoft.SqlServer.Management.Common

Module Module1

    Sub Main()
        GetServers()
        Console.ReadLine()
    End Sub

    Private Function GetServers() As List(Of String)

        Dim servers As New List(Of String)
        Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers(False)

        If dt.Rows.Count > 0 Then

            For Each dr As DataRow In dt.Rows

                Dim servername As String = dr("Name")

                If Not servers.Contains(servername) Then ' Only once per server

                    servers.Add(servername)
                    Console.WriteLine(servername)

                    Dim server As New Server(servername)
                    Try
                        ' Enumerate databases 
                        For Each db In server.Databases

                            If Not db.issystemobject Then
                                Console.WriteLine("  " & db.name)

                                ' Enumerate user tables
                                For Each t As Table In db.tables

                                    If Not t.IsSystemObject Then
                                        Console.WriteLine("    " & t.Name)
                                    End If

                                Next

                            End If

                        Next

                    Catch ex As Exception
                        Console.WriteLine("  " & ex.Message)
                        Console.WriteLine("  Maybe the SQL Server Browser Service isn't started on " & servername & "?")
                    End Try
                End If
            Next
        End If

        Return servers

    End Function


End Module

 Posted by at 10:17 pm  Tagged with:
Jan 132012
 

I’ve wasted hours trying to figure out why my embedded geocoding application doesn’t work with IE9.

This is not the first bad experience I’ve had with IE9, which frankly is one of the worst browser implementations I’ve ever seen.

If you’ve arrived here, you’re probably looking for a solution. Here’s what worked for me:

<meta http-equiv="X-UA-Compatible" content="IE=EmulateIE7" >

That we have to resort to this kind of shit beggars belief.

 Posted by at 11:42 pm  Tagged with:
Jan 132012
 

This spreadsheet contains the VBA code to write an Excel range as a clean, formatted HTML page, with optional auto-refresh.
NOTE If you click on the ‘This spreadsheet’ link above and it opens a ZIP file or a set of HTML files, right-click on the link and choose Save As.

Features:

  • Correctly renders all Excel formatting, including conditional formats.
  • Renders embedded charts.
  • Generates an HTML page with tabs identical to those in the Excel file.
  • Creates clean, formatted HTML that validates to HTML 4.01 strict.
  • Optimised stylesheet to ensure minimal page weight.
  • Supports merged cells with ROWSPAN and COLSPAN.
  • Updates the webpage automatically when cells’ values are changed.
  • The webpage automatically refreshes itself at a user-supplied interval.

The result is almost pixel-perfect, compare this Excel screenshot with the HTML page it generated.

14 March 2013. Update, improved lower tab bar to stop over-spilling (thanks to Paul Palmer) and added support for 64-bit Office.

21 August 2014. Many people have asked how to insert an image in the HTML page. It is not possible to get the contents of an embedded Excel image, but it is possible to get an embedded chart. Here’s how to perform this trick:

  1. In the sheet to be published, make sure no cells are selected
  2. Excel Menu->Insert->Chart and choose any chart type. This will insert an empty chart.
  3. Move and size the empty chart as required
  4. Select the chart by clicking its border
  5. Excel Menu->Layout and click the Picture icon
  6. Select an image file (JPG, BMP, etc) and Insert

28 September 2014. Now handles Unicode text correctly.

Dec 232011
 

I though this would be trivial; turns out it’s not.

  • When you copy a formula from the clipboard, relative references are adjusted, so Range(x).Formula=Range(y).Formula doesn’t produce the expected results.
    The correct way is Range(x).Formula=Range(y).FormulaR1C1. Easy once you’ve been there.
  • Copying border formats with Range(x).Borders=Range(y).Borders crashes Excel. You have to copy each individual border, left, right, top, etc.
  • Similarly for fonts, you have to copy the font attributes one by one.
  • Good practice: check that the areas have compatible sizes and don’t overlap.

Nothing inordinately difficult, but time-consuming to get right. Here’s the code:

Option Explicit

' Copy cells without using the clipboard.
'
' Source is a range from which to copy values/formulas/formats.
' Dest is the destination range. Must be either
'     a single cell, the top-left of the target range. The source size (rows x columns) is copied.
'   or
'     a range exactly the same size as source. We throw an error if the shapes don't match.
'
' If 'what' is omitted, copies the values.
' what=CopyFormulas copies the formulas instead of the values.
' what=CopyFormats copies the formats.
' what=CopyFormulas+CopyFormats copies both.
'
' Examples:
'   CopyCells Range("b2:c6"), Range("h10")                                   ' Copies 8 cells (6x2) from B2 to H10
'   CopyCells Range(cells(2,2),cells(6,3)), Range("h10")                     ' Idem
'   CopyCells Range(cells(2,2),cells(6,3)), Range(cells(10,10),cells(14,12)) ' Fails, source is 6x2, dest is 6x3
'   CopyCells Range("b2:c6"), Range("b3")                                    ' Fails, source and dest intersect

Public Const CopyFormulas = 1
Public Const CopyFormats = 2
Public Sub CopyCells(source As Range, dest As Range, Optional what As Long)

    ' Turn off screen updating, wastes (a lot of) time
    Dim updating As Boolean
    updating = Application.ScreenUpdating
    Application.ScreenUpdating = False

    If IsMissing(what) Then
        what = 0
    End If

    Dim r As Long
    Dim c As Long

    ' If destination is not a singe (r,c) top-left cell, ensure that the ranges are the same shape and size
    If dest.Rows.Count > 1 Or dest.Columns.Count > 1 Then

        If dest.Rows.Count <> source.Rows.Count Or _
           dest.Columns.Count <> source.Columns.Count Then

            Err.Raise 1000, "CopyCells", "Destination area " & dest.Rows.Count & "x" & dest.Columns.Count & _
                " is not the same shape as the source area " & source.Rows.Count & "x" & source.Columns.Count

        End If
    End If

    If Not (Intersect(source, dest) Is Nothing) Then
            Err.Raise 1000, "CopyCells", "Source area " & Replace(source.Address, "$", "") & " " & source.Rows.Count & "x" & source.Columns.Count & _
                " intersects destination area " & Replace(dest.Address, "$", "") & " " & dest.Rows.Count & "x" & dest.Columns.Count
    End If

    For r = 1 To source.Rows.Count

        For c = 1 To source.Columns.Count

            If what And CopyFormulas Then
                dest.Cells(r, c).Formula = source.Cells(r, c).FormulaR1C1
            Else
                dest.Cells(r, c).Value = source.Cells(r, c).Value
            End If

            If what And CopyFormats Then

                Dim b As Long
                For b = xlEdgeLeft To xlInsideHorizontal
                    With source.Cells(r, c).Borders(b)
                        dest.Cells(r, c).Borders(b).Weight = .Weight ' You must do this *before* linestyle
                        dest.Cells(r, c).Borders(b).LineStyle = .LineStyle
                        dest.Cells(r, c).Borders(b).ColorIndex = .ColorIndex
                        dest.Cells(r, c).Borders(b).TintAndShade = .TintAndShade
                    End With
                Next
                dest.Cells(r, c).ColumnWidth = source.Cells(r, c).ColumnWidth
                dest.Cells(r, c).Interior.Color = source.Cells(r, c).Interior.Color
                dest.Cells(r, c).Interior.Pattern = source.Cells(r, c).Interior.Pattern
                dest.Cells(r, c).HorizontalAlignment = source.Cells(r, c).HorizontalAlignment
                dest.Cells(r, c).IndentLevel = source.Cells(r, c).IndentLevel
                dest.Cells(r, c).NumberFormat = source.Cells(r, c).NumberFormat
                dest.Cells(r, c).Orientation = source.Cells(r, c).Orientation
                dest.Cells(r, c).RowHeight = source.Cells(r, c).RowHeight
                dest.Cells(r, c).UseStandardHeight = source.Cells(r, c).UseStandardHeight
                dest.Cells(r, c).UseStandardWidth = source.Cells(r, c).UseStandardWidth
                dest.Cells(r, c).VerticalAlignment = source.Cells(r, c).VerticalAlignment
                dest.Cells(r, c).WrapText = source.Cells(r, c).WrapText

                With source.Cells(r, c).Font
                    dest.Cells(r, c).Font.Background = .Background
                    dest.Cells(r, c).Font.Bold = .Bold
                    dest.Cells(r, c).Font.Color = .Color
                    dest.Cells(r, c).Font.ColorIndex = .ColorIndex
                    dest.Cells(r, c).Font.FontStyle = .FontStyle
                    dest.Cells(r, c).Font.Italic = .Italic
                    dest.Cells(r, c).Font.Shadow = .Shadow
                    dest.Cells(r, c).Font.Size = .Size
                    dest.Cells(r, c).Font.Strikethrough = .Strikethrough
                    dest.Cells(r, c).Font.Subscript = .Subscript
                    dest.Cells(r, c).Font.Superscript = .Superscript
                    dest.Cells(r, c).Font.Underline = .Underline
                End With
                On Error GoTo 0
            End If

        Next
    Next

    Application.ScreenUpdating = updating

End Sub

Sub test()
    CopyCells Range("b2:c6"), Range("h10"), CopyFormulas + CopyFormats
End Sub