MapInfo Pro

 View Only
  • 1.  SQL - loops?

    Posted 11-17-2020 06:54
    Hi,

    not sure if there's been a discussion about this before but I was trying to find a Pro solution for this issue.

    I want to turn the results of a spatial join like this:
    into a result table that looks like this:

    can it be done in the new SQL window?  At the moment I have to join > group by > update column > delete points > repeat until I've got them all.

    I'm assuming Pivot table in excel will probably do it.  Any pointers?

    Thanks

    Nick





    ------------------------------
    Nick Hall
    Mapchester LTD
    nick.hall@mapchester.co.uk
    ------------------------------


  • 2.  RE: SQL - loops?

    Posted 11-17-2020 09:10
    Hi Nick,

    You can do it this way: first, create a field in the Polygon table that will contain the point IDs (e.g. point_list).  Then, after you carry out your join (to create a temp table called joinedTable), issue the following update statement:

    Update joinedTable Set point_list = IIf(point_list="", point, point_list + ";" + point)

    This assumes your polygon ids (1 to 4) are in a field called "polygon" and your point ids (a,b,c etc) are in a field called "point", as in your example above.  The update statement will update the point_list field within the Polygon table.  Where there is a one to many link, point ids will be concatenated.  To avoid inserting a ";" at the beginning, the IIf statement is used so that concatenation of the ";" separator only occurs if the field has already been populated with at least one point id.  I hope this explanation makes sense!

    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------



  • 3.  RE: SQL - loops?

    Posted 11-17-2020 09:27
    Perfect!  

    Thanks very much.

    Nick

    ------------------------------
    Nick Hall
    Mapchester LTD
    nick.hall@mapchester.co.uk
    ------------------------------



  • 4.  RE: SQL - loops?

    Employee
    Posted 11-18-2020 04:30
    Nice workaround, James.
    I would have suggested writing a custom function to do it but that's not even necessary.

    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------



  • 5.  RE: SQL - loops?

    Posted 11-18-2020 06:38
    Thanks Peter, and I'm glad it helped you Nick.  The IIf function helps nicely.  In previous versions of MapInfo a second update would be required to remove the ";" from the beginning of the field.

    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------



  • 6.  RE: SQL - loops?

    Posted 11-18-2020 06:55
    Peter,

    Another little bonus for me was finally using (understanding) the little drop down option for the new SQL window.  Usually I would have run the update from the mapbasic window but it worked perfectly in the new SQL using the update statement.

    One thing I have requested on the wish list is for the new sql window to be updated with the syntax from the old SQL select window like it does to the mapbasic window.  Certainly would help to get my clients using it more!!

    Nick


    ------------------------------
    Nick Hall
    Mapchester LTD
    nick.hall@mapchester.co.uk
    ------------------------------



  • 7.  RE: SQL - loops?

    Employee
    Posted 11-18-2020 07:03
    Hi Nick

    Are you aware that you can right-click on a query in the Table List and open the Select statement for this query in the SQL window?

    That's one way to build from a query you created somewhere else in MapInfo Pro. This also works with the queries you create with the new Select by Location dialog.

    You can also find the query statements in the Script dropdown. Typically they will be named something like "select sometablename"

    I don't think we will let the SQL Window record the queries and show them immediately.

    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------



  • 8.  RE: SQL - loops?

    Posted 11-18-2020 07:23
    Peter,

    Nope I hadn't noticed either of those options and both do exactly what I want!!!!

    Would it be helpful for me just to delete my wish list addition?

    Nick



    ------------------------------
    Nick Hall
    Mapchester LTD
    nick.hall@mapchester.co.uk
    ------------------------------



  • 9.  RE: SQL - loops?

    Employee
    Posted 11-18-2020 09:24
    Maybe you can just add a comment that the capabilities I have described above solve the issue for you.

    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------



  • 10.  RE: SQL - loops?

    Employee
    Posted 11-18-2020 10:07
    Hi Nick,

    When you use any of the dialogs on the sql ribbon dropdown including the sql select, a script with that statement is created and added to the list of scripts in the sql window. You can then select it and modify it.

    In this example I just did a select on World via the sql select dialog. you can see the script in the dropdown at the top of the sql window:


    -Bob

    ps. Peter answered first

    ------------------------------
    Bob Fortin
    Software Architect and Distinguished Engineer
    MapInfo Pro Development Team
    ------------------------------