Mapbasic connection to a live database and Live thematic maps

[David Rhodes]

Questions to the community:

I know it is possible to have a live connection to a database table however I have never done this before.  When you have a live connection does it update the MapInfo table when new records are added ODBC source?  And my next question is, is it possible to update a thematic map automatically (say with a mapbasic program) as the new records are added?  How exactly does it "refresh" with the new  additional data?  Do you possibly have a document I can read if this is a task that MI can perform?

Thanks so much for your help, David

[Peter Horne]

Hi David,
I am using V15 32 bit and can't speak to Live tables, but I have a system that maps numerous linked tables/views from an SQL database.
I have a workspace file that contains numerous thematic layers from these linked tables/views and I simply run a small mbx in the workspace that contains refresh statements -
EG.
Server Refresh vw_fieldTrap
Server Refresh vw_Trap_Pickup
Server Refresh vw_Access_Denied
Server Refresh vw_Internal_Spray
Server Refresh vw_VisitAll
Server Refresh vw_RefusedSpray
Server Refresh vw_Inspect

and when the workspace file is opened the thematic layers update with the new data.

There is also a Table refresh button on the toolbar, which the user can hit at any time to bring in the new data into the map

Also, you may consider if you really do need live tables. I find linked tables are adequate in most circumstances, unless of course you absolutely have to see data that is "live".

Thanks
Peter

[David Rhodes]

Thank you for the reply Peter.  Do you have to click a button for the refresh to occur or is there some kind of counting loop that refreshes the table after a certain period of time?

Thanks for the suggestions,

[Peter Horsbøll Møller]

Out of the box, you’ll have to click a button and select the linked tables to refresh.

You could write a MapBasic tool that does a refresh once in a while.

 

Besides using Linked tables, you can also decide to use Live tables, with or without cache.

With cache they work a bit like linked tables once you have opened them – MapInfo Pro will fetch records from the database based on the map extents you are viewing.

MapInfo Pro will not automatically refresh any of the records already fetched from the database.

 

Without cache, MapInfo Pro will always read the data from the database.

Even though this might sound like a good thing, you might also quickly see that it slows down everything.

 

From page 48 in these slides, you can read a bit about the up and downsides of Linked, Live with cache and Live without cache:

https://www.slideshare.net/peterhorsbollmoller/mapinfo-professional-120-and-sql-server-2008

 

Peter Horsbøll Møller

Pitney Bowes

--
--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

---
You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Eric Blasenheim]

Most of these questions I think can be answered.

First of all, a connection is not live or linked. It is a table that is "live or linked". The connection (information to connect to the database and the actual doing of that) is the same. Of course, the connection is used a bit differently and far more frequently with live than linked.  

Second, live is not really live. At least that is how I try to explain it. That is, it is not automatically updated like you might expect a sports score on a live game page. What you see might be stale. You might look at a value of 12.0 and decide that this should be 11.5 and by the time you go to edit it, it will be 11.0 because it changed on the database.  So it is live at access time.

Of course, live with cache makes that a bit of a contradiction because we have some of the data locally.

And linked is just a full cache with some other behaviors. (below).  

Live without cache, in my view can be a bit strange even though it might sound good because it means that the program has to access the data many times for the same information.  Or as I have often said, imagine you accessed the web and got some information and as you scrolled the browser, it was going back and getting the data as you scrolled?  That would be like our live access. 

A  drawing of a map polygon, its label, the values in the browser or info tool or even the data used for a theme are all possibly accessed separately. What if two maps of the same table rendered two different looking themes because the data had changed between the draw of the first and the draw of the second? What if you stared at the map and clicked on something and it did not select? You might think that was odd.  But what if it were live and it was no longer there? If you redrew the map you might see! (oops).

Now you might say well, yes, my data is not changing that much and you would be right. But that begs the question of why you are forcing "live" in the first place. If you have some idea of when the data is changing , then optimize how your data is cached. 

For me, it is like this:

1) Live no cache - up to date at the moment of access. If you look in the query of a live access tab file, you will see that it usually says "select *" unless you force it. That means that even your table schema could change. Use case is for data that will be optimally accessed in a local area and no one really knows when data is changed. Imagine tables that span a very large area but each user only uses a small part. Could work just fine. Used to be needed for very large tables but now we have NativeX.

Note that live has some tricky issues. When you put your data in a map, we query it. If the live table is the first table in a map, we may not know where the data is.  We may end up asking for all the data which maybe you were trying to avoid. The MapCatalog has an optional starting bounding box. Much better to define your map area with another table and then add the live data.

For the browser it gets a bit odder. We ask for the data by the number of rows in the browser and the primary key order.  But again we may not know how many records are in the table. Count (*) on some dbs can be very slow.  What order the rows show up in might not be obvious. Using MapInfo Rowids is dangerous.

Mixing map and browser data is even a bit trickier because we have to make sure we don't get things twice or for each map/browser.

2) Live with cache - If the workspace and the users can be assumed to optimally access the map and not wildly zoom out and download all the data, this can be good because the repeated access of the same data for browsing, themes, labels, layouts, etc, will be optimized and be faster. However, the MapInfo session is important because each time you open the table, you start the caching over again.

Many of the same tricky issue exist with cache.

3) Linked - local native tab copy for optimal access as long as you can find a time to get it. Off hours is perfect for large data.  Access is the same as native tables and the data can be used offline.  You do not need to be connected to use the data, even with edited data.  

Note that in all cases, data edits are stored locally. Up. on commit, a check is made to ensure that you locally edited records have not been changed since you started. This happens in all cases.

So now your questions:

1) When you have a live connection does it update the MapInfo table when new records are added ODBC source? No. You don't really have a MapInfo Table if by that you mean a native table. However, when what you do in MapInfo causes data to be searched,  the new data just appears (and the old disappears).

2) is it possible to update a thematic map automatically (say with a mapbasic program) as the new records are added. Not sure what you mean. A thematic map updates itself all the time without any processing. If new records show up, they will be styled according to the rules in your theme. No special processing.  If you want the thematic bins or individual values to be recalculated due to the new data, the themes will have to be recreated with the shade statement in MapBasic. But you don't need to do anything to get pre-defined rules applied to new data. Many people use themes with fixes bins because they need to display based on those rules. 

3) How exactly does it "refresh" with the new  additional data? I think that refers to the earlier question and I think I answered that. 

I did not get into ODBC tables that use queries. A slightly more complicated topic.

Regards and I hope I helped,

Eric Blasenheim

Pitney Bowes Software.

[David Rhodes]

Eric, Even with my vague questions you were exactly able to answer my questions.  I very much appreciate your answer(s).