Find values in one table that are missing in another table MapInfo

Took me a little while to find an example of this, so I thought I would add one to the "interweb" to help other people.

One part of MapInfo that is extremely powerful is the SQL functionality. It isn't comprehensive, but there are a number of things you can do with it. Where I'm currently living/working (The Northern Territory) has a number of communities that could have several different names for the same location. This comes from communities named by European settlers in the area, and Indigenous names. Basically, you can have a spreadsheet of data that you want to tie to a geographic location via the name of an organisation, but the spreadsheet names might not necessarily match the database names that have the latitude and longitude. So I want to find out which values in the spreadsheet are "missing" from the database. In other words, which values did not join.

Two Tables: sdss_geography and tutorial_sample_2
Two Columns: sdss_geography.Organisation_Name and tutorial_sample_2.School

The first step is to perform a Join on the two tables. Using Query --> SQL Select

Select Columns: *
From Tables: sdss_geography, Tutorial_Sample_2
Where: sdss_geography.Organisation_Name = Tutorial_Sample_2.School
Ordered By: School
into Table Named: InitialJoin

Next go to File --> Save Query and save the InitialJoin query as a table. Then close the query table and load the InitialJoin.tab table.

Once the table is loaded, go back to Query --> SQL Select.

Select Columns: *
From Tables: Tutorial_Sample_2
Where: not School in (select School from InitialJoin)
Ordered By: School
into Table Named:MissingJoin

Click OK.

This produces a table of missing values. I take this table and make sure the name and spelling matches the central database.

Hopefully that helps someone else.

MapInfo MapCad 9.5

I thought I should post a little more about MapCAD than just "I Couldn't get it to install." If you are using 9.5 you should definitely download MapCAD. It will give you more data editing control than previously, as well as speed up some general data editing tasks.

To add MapInfo MapCad. Go To --> Tool Manager. Click on Add Tool, and navigate to C:\Program Files\AGIS\MapCAD. Then add the MapCAD.mbx. Two new toolbars should be added.

Highlights:
Basic CAD stuff like mirroring and filleting. The Copy Style tool is cool, and can be useful at times. This is a common feature in Word (AutoCAD has one as well).

There are some more sophisticated options such as splitting a polyline (exploding) to different line segments. There is a tool to add nodes at intervals on a segment. The Calculate angle and direction tools will be useful for more precise drawing.

I think the Create Lines and Create Polylines from database are interesting. Seems to be a way to store geographic data without using a spatial object...but with SQL server 2008 express this might not be necessary...

Basically they've added some nice little features that are part of a CAD application.

I found the tools a little cumbersome initially, but it didn't take long to figure them out.
-------------

I've complained about Named Views on here a few times, so with that in mind here is a feeble attempt to produce something "different." This is my first MapBasic Application, so feel free to improve upon it. Basically it adds to options to the Map Menu - Load Saved View and Save View. All it does is write the centre coordinates and zoom to a text file. Nothing fancy, but it might work for you.

For the source code.

For a compiled file (mbx, version 9.5).

Good to know

Just wanted to thank Hampden GIS/IT for a very valuable MapInfo tip. As I've said a few times, I'm not a fan of workspaces, but there is something you can do with a workspace that you can't with any other proprietary program file I've worked with (e.g. *.dwg, *.mxd, *.map). You can open it up in Notepad or WordPad. This is excellent for fixing broken paths (which I'm needing to do right now). It's actually neat to read through it, because it is like reading a program...a LISP or FORTRAN program. Sort of takes me back to working with AutoCAD commandline. Personally, I think commandline is more efficient than searching for icons...This is probably a good workaround to the Named Views problem. Pick a view that works at then use map basic to set it...even copy and paste it into the workspace in Notepad. That's much better than saving it at the application level. In fact to rebuild this workspace, I'm just copying the Map Basic code because it wouldn't load anymore. That's definitely a mark in the plus column for MapInfo.

Still I couldn't resist an experiment. I started a new workspace with one Map Window and one Layout Window. Saved the workspace, then added a piece of text to the layout, closed the layout window and saved as a new workspace. I compared the two in Notepad, and the layout was gone in the second workspace. I was secretly hoping that it just retained all the commands, but of course that really isn't efficient to save all the commands. Oh well, nothing is perfect. In the end, I shouldn't be trying to fit MapInfo into an AutoCAD ArcGIS mold, all three are different programs. It's useful to know the quirks so that I can compensate when using any of the programs.

MapInfo Tutorial

I can't help but laugh at this. I'm back in government land putting together a "manual"/"tutorial" for working with MapInfo. There was a copy of version 4.5 floating around with the manuals, so I was flipping through it. It looks exactly the same! And, this was for Windows 95! So I went online and searched for MapInfo tutorials. I didn't see any of the initial hits that were for greater than version 7.5...

On the one hand, MapInfo is like a rock and doesn't change. On the other hand, they haven't fixed fatal flaws with their program (IMHO), like workspaces.

I'll post the tutorial (edited to remove gov' related info) when it is done.

MapInfo 9.5 MapCad

There have been a few comments on the MapInfo 9.5 semi-review I wrote in regards to MapCad. Sounds like I had an installation problem, but I let the trial version expire so I couldn't resolve it. But it sounds like people are using it without problems. I found this page which gives a better description of the MapCad tools. It seems hard to find independent reviews of the new version though. I suppose if you are happy with MapInfo then you will continue to be happy with 9.5, although you might have to break habits to use the new righ-click editing features. If you are unhappy with MapInfo for some of the reasons I am unhappy with it (named views, and workspaces), then MapInfo 9.5 will suffer from the same problems. I am impressed to see the MapCad direction, and have always disliked the GIS software editing environments. This bias comes from my AutoCAD origins. I have never worked in a better editing environment with better snapping tools. Commandline makes it so much easier and faster to call up a particular snapping tool or feature type (polyline, polygon).

MapInfo 9.5 Update

Downloaded and installed the evaluation version. I also downloaded and installed MapCad. Workspaces are still there, so that alone is enough to turn me off. There is some handy right-click context menu functionality in the map browser. Especially for editing. The interface looks essentially the same, and I can't see if they changed anything. On the one hand this is nice, because everything looks the same and makes it easy to learn from one version to the next (can't say this about Autodesk).

I tried to use the MapCad tools but, honestly, couldn't find how to load them. I ran the installer, twice, but nothing happened. I looked in the helpfile for MapCad and it said to go to the Help dropdown and go to MapCad Help. This, of course, did not exist. So again I ask the question, why spend over a 1000$ when you can get the same functionality out of Manifold (plus both the extensions for that amount)? It will supposedly also work with MSSQL 2008 spatial as well.

I didn't bother playing with the .NET features...

MapInfo 9.5

The geoblogosphere seems to be all a twitter (no relation to twitter) with the release of ArcGIS 9.3. I made the mistake of getting my knickers in a twist when 9.2 came out. I installed it wanting to try the new features, but unfortunately I used it to work on some project work when the rest of the office hadn't upgraded from 9.1 (or 9.0 in some cases). That was dumb on my part, so now if I happen to receive a copy, I don't think I'll upgrade till SP1 comes out. Though, sometimes service packs can lead to more headaches as I found with 9.2 sp3 and the loss of some of my python tools in the toolbox...weeeeee!

Anyway, all the talk around 9.3 has overshadowed the pending release of MapInfo 9.5. Its fairly obvious from my posts that I am primarily an ESRI user, and have given a fair share of MapInfo criticism. They must have heard the call (I'm certain I wield such influence), because it sounds like they have made some substantial changes. They are a little behind the times on some things, like the .NET MapBasic features. ESRI has had that for over a year now. I'm not too impressed by having support for MSSQL, because it sounds like everyone will. Although, I bet MapInfo won't require any extra extension to handle it. I am hopeful but very much doubt that there will be direct support in ArcGIS 9.3 without the need for ArcSDE. MapInfo should also add PostGIS support in my opinion. It seems to be gaining some commercial acceptance through Manifold, ArcSDE, zigGIS (also ArcGIS), etc...

I'm downloading an evaluation copy of 9.5. I can't test everything, but I read that they designed the interface with .NET, so I'm hoping it doesn't look like crayon on sandpaper anymore. I also hope they replaced workspaces with something more functional. I'll post my findings here.

The Anarchist's Cookbook to MapInfo

*Edit*
There have been a number of posts about working with MapInfo. You may also be interested in them.
*End Edit*

Let's get one thing straight - I hate working with MapInfo. I know a lot of people love it. At least one person has said, "I used to work with ArcView, and then I tried MapInfo and never switched back." I can understand this if maybe you worked with ArcView 3.2 (which I hate working with too), but the newer version of ArcGIS is a different program. At some point, as a software company, you have to wake up and stop building on top of your legacy programs. AutoCAD has done this(at least twice), ESRI did this, Microsoft does this annoyingly (office 2007). Yes this creates bugs and in some cases a slightly less stable program, but the capabilities and improvements, in my opinion, outway the negatives. Now, there are good ways and bad ways to handle this. Autodesk handled it well, simultaneously releasing versions of their old land desktop and new civil 3d. Increasing the stability of civil 3D with each release, and eventually phasing out the old product. The temptation is not to learn the new product because it is easier to work in the old product. Microsoft handles this badly, because they just give you something new and different. I've heard it estimated it takes at least a full month of using the new version of office regularly in order to get back to your level. If you are in a workplace, when do you have time? I'm sure ESRI didn't phase well, but this was before my time. My main question is why would anyone pay 3000$ for MapInfo when you get essentially the same product for 300$ with Manifold GIS (complete with its own annoyances)?

I will add this caveat though - I come from an AutoCAD ArcGIS background. Obviously I am biased, and I'm probably trying to force Arc methods on MapInfo tools. So if my tips point to something I'm doing wrong, hopefully I will be corrected. I appreciate any additions, corrections, complaints. I've spent enough time ranting, time to move onto some tips. My focus is on Layouts, Maps, and Labels.

1. Use workspaces. You are forced to use workspaces, so might as well use what you are given. Save often, too. This is a no brainer, but it was a struggle for me to get over how crappy workspaces are. Be sure to close all tables before opening another workspace, or you'll merge two workspaces together. Workspaces are not like a drawing in AutoCAD, or a Map in Manifold, or an Mxd in Arc; these are static saves of your "project". A workspace is the closest thing to a project MapInfo offers. If you happen to close a layout, or a Map Browser without saving a workspace, then you have lost this information. That doesn't happen in other "normal" software. If you couldn't tell, I've made this mistake several times.

2. Add the named views tool to your program (Tools --> Tool Manager --> Named Views), and use it. Let me explain what I mean by views: a view on a layout is a frame that looks out onto your world. In most cases the view looks out onto a Map Browser. If you don't use named views, then it is difficult to maintain the same view in a frame on a layout. As soon as you zoom or pan in a map browser, this changes the associated views in your layout. To get back to where you were there is previous zoom, but that is lost if you move 2X. A named view is really your only way (as far as I can tell) to retrieve a view. Named Views would be similar to a bookmark in ArcGIS, or a view in Manifold. There is no need for views in AutoCAD because it is intelligent enough to remember your view regardless of what you do in modelspace (data view, map browser). I wish GIS systems would do this as well, but they don't.
The main problem with named views is a view is saved at the program level. This means it is not saved in the workspace. Also, it is only saved at the program level if you shutdown properly. If your MapInfo crashes, all the named views are lost since your last shutdown, even if you've saved your workspace. If you have two versions of MapInfo open, named views from the first program you shut down are lost. If someone opens up a workspace, they will need to create their own views. Please Please someone tell me I'm an idiot, and there actually is something better than this! (or just tell me I'm an idiot if you prefer, but I'd prefer something more constructive).

3. Layouts and views. This isn't a tip, but a warning. You can't double click on a view and access the map browser. This is just an irritation if you've used Arc or CAD. You just need to switch back to the map browser. It's a slight loss of productivity, but doesn't matter to much.

4. Labeling is pretty good in MapInfo. It's sort of a combination of automatic labeling and manual labeling. That really makes labeling fairly easy, because you can turn the labels on, and then move them around. The nice thing is they don't loose their associativity with the layer, so you can turn them off. You can also change the style en masse too, which is handy. When moving labels, it behoves you to first set up your layout and views(frames) with a chosen scale. This, of course, changes your map browser scale. Create a named view in this map browser view, so you can get back to it later. Then change the map browser scale to the same as the layout view scale. Now move the labels. Go back to the named view when done, and check out the results in the layout. If you don't do this, and edit the labels with the named view scale, they will suffer from "5 year old needing to pee" syndrome and will bounce all over the place. This syndrome also happens in Arc on occasion (in particular with the dynamic scale bar in layout view). Here is an example. Add a frame to a layout and select a map browser. This is your view of the map browser. When you double-click on the frame/view, you can set the scale (1 cm = 20 km). When you go to your map browser, the scale will be set as 1cm = 11km (or something like that). Save the named view, then change the scale to 1cm = 20km. Move the labels. Go back to the saved view (this changes the scale back to 1cm = 11km). The labels should look correct on the layout view.

If you're not confused, then I haven't done my job.