Wikipedia:Reference desk/Archives/Computing/2012 February 28

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is an archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

February 28

missed an email - how do I forward the whole email account in Gmail's new look?

I have an account I rarely used and missed an email. I used to be able to forward ALL incoming mail (by going under settings) in gmail, and I would like to turn that on again, but can't find it under the new look. Can anyone tell me where it is (exactly)/how to get to that window again? Thanks. --80.99.254.208 (talk) 11:06, 28 February 2012 (UTC)

At the top right, click on the gear/sprocket icon. Go down to Settings. Click on the Forwarding tab. Change the top item. Dismas|^(talk) 11:40, 28 February 2012 (UTC)

Hey, thanks! Perfect. I don't know how I missed that gear/sprocket, I was looking around at that area, mostly by clicking my username at top-right and clicking "account settings". oh well. Thanks again. --80.99.254.208 (talk) 09:11, 29 February 2012 (UTC)

You're welcome! Dismas|^(talk) 14:05, 29 February 2012 (UTC)

Icon maker

I've been playing with a resource hacker (A program that has the ability to change bitmaps on a executable file, it is what the exe displays, for example calc.exe displays a calculator). It seems the program needs different versions of the same icon..

256 colors	256x256px	48x48px	32x32px	16x16px
32 colors	256x256px	48x48px	32x32px	16x16px
16 colors	256x256px	48x48px	32x32px	16x16px

(Ok there isn't really a 16 colors bitmap with 256x256 resolution, it's ridiculous, it's an example) Is there anyway automated way of getting all those versions of a bitmap? It seems that it will take a really long time to do it manually. Thanks! --190.60.93.218 (talk) 12:52, 28 February 2012 (UTC)

If you are just editing icons, there are icon editors out there that will auto-generate the different sizes. Otherwise you could use something like Imagemagick to do it automatically. --Mr.98 (talk) 21:48, 28 February 2012 (UTC)

Do you know where I can get an Imamagick GUI? 190.158.184.192 (talk) 22:49, 28 February 2012 (UTC) > Nevermind, seems that the gui doesn't work for it, and I'm not really good with commands... well do you know any icon editor that do that automatically? I'm using IcoFX Portable 190.158.184.192 (talk) 22:56, 28 February 2012 (UTC)

8 x 4 foot IR pass filter

I need an 8 foot by 4 foot sheet of semi transparent material that allows most/all IR light to pass though. I intend to use this sheet(maybe glued to a sheet of acrylic) as a projector screen. This screen needs to let IR pass though and it needs to show the projected image from the opposite side. Anyone know where i could get a sheet or a lining or paint that will allow me to build this screen? 98.238.132.145 (talk) 19:24, 28 February 2012 (UTC)

You won't be able to get so much material for cheap, at least not if it's got great optical properties. As a cheap workaround, you can use blue plastic - which will block blue light and pass red light. Let's speak quantitatively, though: how strong of a wavelength discrimination do you need? How much do you want to attenuate non-infrared light? How much parasitic attenuation can you afford in the infrared band? What non-visible-light bands do you care (and not-care) about blocking? Nimur (talk) 20:18, 28 February 2012 (UTC)

For example, McMaster sells 4x8 foot blue acrylic (8505K189, 8505K889, and 8505K989, ranging from ~ $130 to ~$230 per sheet). Catalog page 3569. This will pass infrared and block blue visible light (and other visible light, but not very well). You can stack as many sheets as deep as you need to progressively cut off more visible light. Nimur (talk) 20:22, 28 February 2012 (UTC)

My goal is to make a table that can ID and track objects. The way i was thinking that i was going to achieve this was to have a camera under the table that only sees IR and a IR flood light. To ID and track objects i was going to place a bar code on the bottom of the objects and read it via IR(if you have a better idea please tell me). The table it's self would have an image project on to it from underneath. I understand that this is not a cheap project so i am not only exploring cheap options. The sheets of blue acrylic might work if i am able to off set the blue from the computer/projector (make the projected image full color). As a side note i have discovered that the polarized layer on the front of a computer screen contains all the properties needed for my project - its just that i dont know of any 8 foot by 4 foot screen that i could take apart. 98.238.132.145 (talk) 20:55, 28 February 2012 (UTC)

A camera that only sees IR? Do you mean like FLIR? or Thermographic camera? Those are considerably different to typical "near infrared" night vision cameras which also "see" considerable parts of the visible spectrum. Vespine (talk) 00:08, 29 February 2012 (UTC)

Interesting and challenging idea! A couple of other factors spring to mind. Any surface that scatters visible light enough to make it a useful projecting surface will also probably scatter "near infrared light", unless you employ some sort of exotic narrow band filtering, which is probably not cost effective or practical. Anything outside of the "near infrared" range will probably NOT be able to read a barcode unless you print your barcodes out of exotic IR absorbing materials. Also, IR flood lights are also "near infared" so they'll be useless if you use anything but a near infrared camera. So immediately I wonder if you are just over engineering this. Have you ever seen someone in a steamy shower? The closer something is to a "diffuse" surface the less diffuse it appears, to the point where if something is resting directly on it, the frosting might be barely noticeable all together. The projector provides a fairly bright light source, maybe if you find just the right level of frosted perspex or glass, you can just use a regular camera? Vespine (talk) 00:17, 29 February 2012 (UTC)

The camera i had in mind was either going to be a wii controller or a modified FinePix S700. Your point about the diffused surface has already been considered, i was planning on playing around with that and seeing if i could simply get away with a 8x4 foot sheet of acrylic with a diffuser on it - but i don't think the light from the projector would work as it's light changes depending on whats being displayed. — Preceding unsigned comment added by 98.238.132.145 (talk) 18:09, 29 February 2012 (UTC)

Have you seen Johnny Lee's website? he even mentions rear projection and tracking and stuff.. I haven't gone through all of it but I'm sure you'll find some useful info there. Vespine (talk) 03:06, 1 March 2012 (UTC)

In my experiments with near IR photography, which you can see some of on commons, most colours and dyes used by humans become colourless but not all. So you could experiment with black food colouring, dyes and inks to see which ones look grey or white in IR. Graeme Bartlett (talk) 10:03, 2 March 2012 (UTC)

Effective way to quickly reverse geocode

I have a table (table1) full of latitude and longitude coordinates stored as doubles.

I have another table (table2) (a very large one) full of places in countries (e.g. cities) that also have latitude and longitude coordinates, along with a country code.

What's an efficient SQL query that allows me to take the lat/lon coordinates in table1, run them through table2 to find the nearest coordinates, and then grab the country code from table2?

It does not have to be precise. Speed matters more than accuracy here; table2 has several million entries in it, and table1 is pretty large as well, and I'd like this to finish in my lifetime... --Mr.98 (talk) 21:54, 28 February 2012 (UTC)

One caution, the method you outlined above may sometimes return the wrong country. That is, a location in nation X may be closer to a city in your DB in nation Y, than to any city in your DB in nation X. For example, let's say your coords are in Niagara-on-the-Lake, Ontario but that city isn't in table 2, and the nearest city in table 2 is actually Buffalo, New York. StuRat (talk) 01:24, 29 February 2012 (UTC)

Also, does this have to be all SQL ? I'm thinking a mixture of embedded SQL and program logic would be in order. That is, use SQL to find the 10 closest possibilities with some very approximate calculation (like the sum of the differences in longitude and latitude), then use the program to sift out the closest, using exact calculations accounting for the distortion of the grid at different locations on the Earth (using some basic trig). StuRat (talk) 01:36, 29 February 2012 (UTC)

One other point, how do you deal with East and West and North and South longitudes and latitudes ? Have you set up a convention where one hemisphere is positive and another is negative ? As long as you've used the same convention in both tables, that should work, except for when -180 becomes +180. That is, your program would have to know that -179 is right by +179, it would be difficult to get an SQL query to handle that. StuRat (talk) 01:41, 29 February 2012 (UTC)

For reference, a relevant article is Spatial index. We also have Reverse geocoding, but it does not discuss methods or algorithms. -- ToE 02:13, 29 February 2012 (UTC)

I don't really care TOO much about the boundary areas. Again, I'm crunching a lot of data here. As for SQL — well, I just want the dang thing to be pretty fast. It's a ton of data. If it takes more than a second per entry, it's going to take a week to do the calculations. That's a bit much. As for the coordinates, it's just standard decimal lat/lng, so it uses negatives, positives, what have you. Both of the databases use the same standardized approach. --Mr.98 (talk) 03:27, 29 February 2012 (UTC)

Can we assume that your lat and lng columns in table 2 are indexed? -- ToE 04:17, 29 February 2012 (UTC)

Sure! I mean, they're not at the moment, but I can probably change that, assuming Access doesn't explode under the strain (which it has done a few times already). --Mr.98 (talk) 13:11, 29 February 2012 (UTC)

OK — all lat/lng fields in all tables are indexed. --Mr.98 (talk) 18:43, 29 February 2012 (UTC)

OK, so let's go with your concept of doing it all in a single SQL statement. The exact way to find distances is as follows:

DIST = R * acos[sin(lat1)*sin(lat2) + cos(lat1)*cos(lat2)*cos(long1-long2)]

R is the radius of the Earth, so could be omitted, since we don't need the actual distance, just which is greater. However, all those trig functions might be rather CPU intensive. We could probably come up with a faster approximate formula without trig functions, using Euclidean distance. This will find the straight-line distance (through the Earth), rather than the great circle distance (along the surface):

DIST = sqrt[(lat1-lat2)²+(long1-long2)²]

Again, since we don't want the actual distance, but only to know which is greater, we can omit the CPU intensive square root function. (You could also reduce the CPU time a bit more by skipping the squaring, decreasing accuracy further. However, I don't think the trade-off here is worth it, especially since you'd need to replace the squaring with taking the absolute value of each difference.)

This formula doesn't account for the distortion near the poles (where longitude lines get closer together) and the problem near the International Date Line, where the -179/+179 problem comes in. But, since presumably most test points won't be at the poles or mid-Pacific, this should be a reasonable approximation for most cases. Do you need any help formulating it into an SQL statement ? StuRat (talk) 22:30, 29 February 2012 (UTC)

StuRat had taken us to the point of comparing

DIST² = (lat1-lat2)²+(long1-long2)²

Note that you definitely do not want to omit the squaring unless you replace it with an absolute value, as otherwise negative Δlat would cancel positive Δlng, and vice versa, making the points (0°,45°) and (45°,0°) coincidence. Note that

DIST = |lat1-lat2| + |long1-long2|

is the taxicab metric as opposed to the Euclidean metric with the squares. Also note that you could make a pretty good correction for higher latitude cos(lat) distortion by

DIST² = (lat1-lat2)²+( cos(lat1) * (long1-long2) )²

where cos(lat1) needs to be computed only once per query, and not for every row examined of table 2. (This correction will be pretty good as long as lat2 is not too far from lat1.)

I assume, however, that your question was not about the formula, but about a tactic for keeping the database engine from hitting up every row of table 2 for each new query generated from a row in table 1. You can try querying for the row with the min value of either of the two expressions above (squared or absolute values), but I have my doubts about the power of the query optimizer. (My understanding of min here is incorrect; see below.) Might it possibly be smart enough to recognize that these expressions are minimized when the two lats and the two lngs are close together, and thus use table 2's indexes? (Anybody here know?) If not, then I see three ways to force index use, which I will follow up with shortly. -- ToE 23:51, 29 February 2012 (UTC)

• Method one is to use a spatially enabled database. I invite anyone here more familiar with the various spatial database systems, such as PostGIS for PostgreSQL, to comment on their applicability. -- ToE 00:05, 1 March 2012 (UTC)

• Method two is firing off multiple queries that will use the simple indexes you placed on table 2's lat and lng columns, until you determine the smallest box centered on (lat1,lng1) which contains one (or only a few) points from table 2. Start with an initial size of your box. (It should probably be pretty small, so that it is unlikely to contain many points in table 2.) I assume that you know how to compute delta_lat and delta_lng so that (lat1 - delta_lat, lng1 - delta_lng) and (lat1 + delta_lat, lng1 + delta_lng) represent catty-corners of the square centered on (lat1,lng1) with size size. Note that applying cos(lat1) here would be quite cheap, as it need only be computed once per row of table 1. Now query the database to determine the count of rows from table 2 which fall in that box, that is, where:

lat1 - delta_lat < lat2 < lat1 + delta_lat 
lng1 - delta_lng < lng2 < lng1 + delta_lng

If none, then repeat after doubling size. If, on the other hand, the count is quite large, then halve size and repeat. Keep doubling or halving size until you have determined a value of size whose box contains no rows from table 2, yet the box with sides twice as large contains some (possibly lots) of rows from table 2. Then half-split until you have a size whose box contains only one or just a few rows from from table 2. These rows will be amongst the closest to (lat1,lng1). Note that this is using the taxicab metric. If you are concerned about the Euclidean metric, then you will now have to compute actual distances, and if the closest row in your box is outside of the circle inscribed in the box, you will have to check for new points in a slightly larger box, to ensure that there are not any closer ones than those "sneaking in the corners" of your box.

It is possible that, upon doubling size, your box might go from containing no rows of table 2 to lots and lots of such rows. I don't think that this would cause too much of a performance hit, because since you are querying for a count, all the work should be in the index, and no retrievals from the table itself should be required. (What sayeth a Database Guru?) You only actually retrieve a row once you've determined a box size with only one or just a few rows. -- ToE 01:05, 1 March 2012 (UTC)

• Method three involves creating your own proper spatial index for table 2. For simple point data, a point quadtree index should do. You would create a new column in table 2 which is formed by interleaving the bits of the appropriately represented latitude and longitude values. You then place an index on that column. Database Gurus: If I ask for a min(abs(col_1 - constant_value)), will the query optimizer use col_1's index? If so, then you can directly query for a closest point. (My understanding of min here is incorrect; see below.) If not, they you will need to play with boxes again, but the queries will be more efficient. This is pretty much what a spatially enabled database extension will do automatically for you. -- ToE 01:29, 1 March 2012 (UTC)

min() function assumption error

In the discussion above I assumed that

select col_1, min(expression) from table_1;

would return the value of col_1 from a row for which expression is minimized, but this does not appear to be the case. As an aggregate function, it appears that any other columns selected along with a min must be used in a group by. If you want a single query that identifies the closest point (not simply tells you how far away it is), you would presumable need an inner join on that minimum distance value, which is even more to optimize. So it appears to me that unless you use a spatially enabled database (and do they even offer a "closest to" function?), you will want to do something along the lines of the multiple queries described in method 2. The proper spatial index of method 3 could still be used to speed things up if method 2 is not fast enough. -- ToE 14:05, 1 March 2012 (UTC)

Thanks for all the effort, guys. I ended up doing something a bit dirtier and simpler, in the end — I rigged up Google Maps to let me just define a lot of arbitrary rectangular boundaries, and then just had it grab any points within those boundaries. After about an hour's work of drawing the boundaries, I had clustered about 95% of the points into big categories that were good enough for me, and that's fine for the task at hand. Pretty lame, I know! But it finally got everything to work and only took a few minutes to put the points into the bounds "buckets" once I had defined them. --Mr.98 (talk) 21:34, 1 March 2012 (UTC)

OK, sounds like we can mark this resolved, then. StuRat (talk) 05:36, 2 March 2012 (UTC)

Resolved