MapInfo Pro

 View Only

  • 1.  Thematic bins / sql "count" within ranges for >16 bins

    Posted 07-20-2023 07:19

    Hi

    I have a table of points (50k of them), each with an ID number. Many of the ID numbers are repeated, as they're on different sampling lines.

    I need to count the number of points which fall between certain ranges.

    This is simple to do in "custom ranged thematics", giving me a nice list of point counts, but only for up to 16 bin ranges. I need about 500 bin ranges (which I have in a table: MinID; MaxID).

    I don't need the colours, just the count.

    I tried to do this in sql and can't work out how to "join" 2 tables - the one with the bin ranges (minID/maxID columns) and the one with the points.

    It seems like it should be simple. Something like "where [point_table]pointID>[range_table]minID_value AND [point_table]pointID<=[range_table]maxID_value"
    But MapInfo sql doesn't like this as there's no "join" specified.

    Thematics does it so quickly, so I'm sure MapInfo is capable, but I don't know how.

    Any ideas? If you can even follow what I'm trying to do?

    Sample tables below

    PointID
    3
    4
    23
    456
    4
    24
    25
    300
    8
    etc

    MinID   MaxID  Count
    1          12         ?
    13        24         ?
    25        36
    etc

    Thanks muchly in advance



    ------------------------------
    Caroline Hilton
    Pelican GeoGraphics Ltd (VAR)
    Petersfield
    ------------------------------


  • 2.  RE: Thematic bins / sql "count" within ranges for >16 bins

    Posted 07-21-2023 02:53

    You need a dummy field for joining, it can be just 0's in both tables.

    select ranges.MinValue, ranges.MaxValue, Count(*)

    from ranges, InputValues

    where ranges.dummy=InputValues.dummy and ranges.MinValue<=InputValues.Value and ranges.MaxValue>=InputValues.Value

    group by ranges.MinValue

    order by ranges.MinValue



    ------------------------------
    Uffe Kousgaard
    ROUTEWARE
    Roskilde
    ------------------------------

    Original Message


  • 3.  RE: Thematic bins / sql "count" within ranges for >16 bins

    Posted 07-21-2023 05:54
    Edited by Caroline Hilton 07-21-2023 06:06

    Uffe thanks a million, that worked perfectly (albeit slowly).

    I am intrigued by the dummy field. You would think that a whole column of zeros would send it into a spin.

    You are a MapInfo Jedi Master!



    ------------------------------
    Caroline Hilton
    Pelican GeoGraphics Ltd (VAR)
    Petersfield
    ------------------------------

    Original Message


  • 4.  RE: Thematic bins / sql "count" within ranges for >16 bins

    Posted 07-21-2023 06:13

    I all fairness: Someone else than me came up with this solution. Many years ago.



    ------------------------------
    Uffe Kousgaard
    ROUTEWARE
    Roskilde
    ------------------------------

    Original Message


  • 5.  RE: Thematic bins / sql "count" within ranges for >16 bins

    Employee
    Posted 07-22-2023 05:11

    That is a very useful "hack".

    It basically creates a Cross Join where each record in the first table is joined with all records in the second table.

    With MapInfo Pro v2019.1 and later, you can do this by adding the "Cross Join" keyword after the tables names you are joining - without adding the dummy columns.

    There are a few operators that aren't yet supported in a Cross Join for example Between.



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

    Original Message


  • 6.  RE: Thematic bins / sql "count" within ranges for >16 bins

    Posted 07-22-2023 10:05

    Thanks Peter, I've just found that on the "new" sql build box. I usually use the original sql dialogue, otherwise I find I'm putting tables/column names in the wrong place. But very handy to know about, cheers!



    ------------------------------
    Caroline Hilton
    Pelican GeoGraphics Ltd (VAR)
    Petersfield
    ------------------------------

    Original Message


  • 7.  RE: Thematic bins / sql "count" within ranges for >16 bins

    Posted 08-07-2023 13:55

    Hi Caroline Hilton,
    You can try this for faster query in case you have min 0 to MAX range,
    let say MAX - MIN = 100 and you will split into 5  ranges, then SPLIT_RANGE = 100/ 5 = 20 
    select  int(COLNAME\SPLIT_RANGE )*SPLIT_RANGE "Min1", (1+int(COLNAME\SPLIT_RANGE ))*SPLIT_RANGE "Max1" ,  Count(*) from TABLENAME Group by col1 Order by col1 

    For min <> 0 cases you can use this mapbasic

    dim tname as string
    dim cname as string
    dim mn as  float 
    dim mx as float
    dim rng as float
    dim rng2 as float
    dim nrange as integer

    tname = "TABLE1"
    cname = "COLNAME"
    Nrange = 5
     
    run command "select min("+cname+"), max("+cname+") from "+Tname+" into selRange"
    run command "select "+cname +" from "+ Tname + " into selData"
     
    fetch first from selRange  
    mn = selRange.col1 
    mx = selrange.col2 
    rng=mx-mn 
    rng2=rng/Nrange
    select int(col1\rng2)*rng2 +mn "Min1", ( (1+int(col1\rng2))* rng2 +mn -1) "Max1" ,  Count(*) from selData Group by col1 Order by col1 into sel_Range_N_Counts 
    Browse * From Selection

     



    ------------------------------
    Ahmet Dabanli
    Başarsoft
    Balgat, Cankaya
    ------------------------------

    Original Message


  • 8.  RE: Thematic bins / sql "count" within ranges for >16 bins

    Posted 08-09-2023 10:56

    Thanks Ahmet, that looks complex, especially as I'm not a whizz with mapbasic, but I'll give it a try!



    ------------------------------
    Caroline Hilton
    Pelican GeoGraphics Ltd (VAR)
    Petersfield
    ------------------------------

    Original Message