MapInfo Pro

Expand all | Collapse all

Improved SQL Syntax in MapInfo Pro v2019

  • 1.  Improved SQL Syntax in MapInfo Pro v2019

    Posted 11-30-2019 19:03
    Edited by Peter Horsbøll Møller 28 days ago
      |   view attached
    A lot of work has gone into improving the #SQL syntax in MapInfo Pro v2019.

    We have taken a good look a the many suggestions from the Ideas Portal and mixed that up with a few ideas of our own to come up with some powerful new features in the SQL Language of MapInfo Pro.

    These new features, of course, improve the Select statement, but we have also improved the Update as well as the Delete statement.

    For the Delete and Update statements, we have added support for expression in the Where clause.

    For the Select statement, we have made numerous improvements such as support for Table Alias, more flexible Join conditions, joins between base tables and temporary tables and support for a Limit clause.

    We have also added four new spatial aggregations that let you aggregate (or combine) the spatial objects if you group your records in a Select statement.

    I have included a small presentation that gives you an idea of these new improvements and how they could be used.

    Feel free to post any questions you may have.



    ------------------------------
    Peter Horsbøll Møller
    Pitney Bowes
    ------------------------------​

    Attachment(s)



  • 2.  RE: Improved SQL Syntax in MapInfo Pro v2019

    Pitney Bowes
    Posted 12-04-2019 12:45
    Hi Peter,

    There is a typo above. It is the delete and update statements that now support a where clause.

    ------------------------------
    Bob Fortin
    Software Architect and Distinguished Engineer
    ------------------------------



  • 3.  RE: Improved SQL Syntax in MapInfo Pro v2019

    Posted 12-04-2019 15:01
    Edited by Peter Horsbøll Møller 12-04-2019 15:03
    Thanks, Bob, I have changed the typo.
    Don't you just hate it when the Auto-Correction changes Delete into Insert :-)

    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------



  • 4.  RE: Improved SQL Syntax in MapInfo Pro v2019

    Pitney Bowes
    Posted 12-09-2019 19:35
    A few other related improvements

    • You can use the table.*  and table alias syntax in the Sql Select dialog.
    • After using the SqlSelect, SimpleSelect, UpdateColumn, or AppendRows dialogs, a script is logged into to sql window that you can open and edit. It will also show on the Sql dropdown in the Recent section so you can run them again.



    ------------------------------
    Bob Fortin
    Software Architect and Distinguished Engineer
    ------------------------------



  • 5.  RE: Improved SQL Syntax in MapInfo Pro v2019

    Posted 01-11-2020 10:43
    Hi,

    just played around a bit with the new SQL features and ran into some issues:

    I have a simple table containing objects and a column "Vertrieb", that can be used to group the records.

    When i now want to spatialy aggregate this into a new selection, typing:

    >  select Vertrieb, AggregateUnion(OBJ) from de_verkäufer group by Vertrieb

    into the mapbasic window, the selection i get is a simple, not mappable table, and the funny part: It contains ALL columns of the original table, not only the one, specified column, aggregated by vertrieb, with the contents of the first records of that group.

    When i now try to use naming the spatial column with this modified statement:

    >  select Vertrieb, AggregateUnion(obj) object from de_verkäufer group by Vertrieb

    as it was written in Peter's presentation, i get an error telling me, the column obj doesn't exist and object was found when searching for from.(double quotes around object or an additional AS doesn't make a difference).

    When i try to write the statement with aliases, - another new feature -, like this:

    >  select w.Vertrieb, AggregateUnion(w.obj) object from de_verkäufer as "w" group by w.Vertrieb

    i get the error that the expression doesn't evaluate to a string.

    What detail do i miss here, that makes it all work?
    or does spatial aggregation simply doesn't work with selections?
    ... even that wouldn't explain the strange results of the first query.

    Thanks and regards

    Stefan


  • 6.  RE: Improved SQL Syntax in MapInfo Pro v2019

    Posted 01-12-2020 16:14

    Sorry Stefan, my mistake.

    Apparently, I haven't updated the name of the aggregation in all places throughout the presentation. The aggregation is called AggregateCombine, not AggregateUnion.

    This query should work:

    Select Vertrieb, AggregateCombine(OBJ) From de_verkäufer Group By Vertrieb

    A bit weird that this doesn't raise an error but maybe we still have the former name somewhere behind the scenes. I'll check.

    Also note, that the Object keyword should only be used if the resulting query has multiple spatial object "columns". In this case, there is only one so you should not use the Object keyword.



    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------



  • 7.  RE: Improved SQL Syntax in MapInfo Pro v2019

    Posted 01-13-2020 01:40
    Good morning Peter,

    thanks for the reply and the solution, - you're right it works with the correct aggreate function, even with aliases, - great!

    Have a nice day
    and
    best regards

    Stefan