MapInfo Pro

 View Only
  • 1.  MapInfo Monday: Five Basic SQL Select Statements

    Employee
    Posted 06-20-2022 05:50

    Happy #MapInfoMonday!

    You should never underestimate the power of using SQL when working with data, be that alphanumerical data or spatial data. SQL can help you in many ways to analyze your data, find specific records, and even create derived data.

    Below you can find five basic SQL Select statements. I show them individually, but you can combine them in many ways. I have used the sample data from Washinton DC that comes with MapInfo Pro so that you can give this a try yourself.

    I have used the SQL Window to create the Select statements but you can also use the classic SQL Select dialog if you prefer.

    1. Finding a specific value

    Often you are looking for records with a specific value. You can very easily use SQL to find these records.

    The SQL Select statement allows you to specify conditions when selecting records. In this case, you want to say that the value in a certain column should be equal to a specific value.

    For text columns, you need to specify the value in double-quotes (""). For numerical columns, you must not.

    In the example below, I am looking for all road segments for a specific road. The Where condition looks like this: Name = "Madison Dr NW".


    The query looks like this:

    Select *
      From DC_Street_5
      Where Name = "Madison Dr NW"
      Into Selection


    If I were looking for a specific ID, using numerical values, the condition would look like this instead: ID = 14297905.


    The query looks like this:

    Select *
      From DC_Street_5
      Where ID = 14297905
      Into Selection


    You can also do this very basic query using the Simple Select dialog.

    Remember if you are viewing your result in a browser window, you can always filter the content of the browser by right-clicking inside a column or on the column headers or using the Filter control on the TABLE tab.


    If you have loaded the WindowHelper add-in, you can also select a specific cell in the browser, right-click on it, and then use Filter Using Cell Value to filter the browser to find all matching records.

    The new Select by Attribute dialog in MapInfo Pro v2021, also helps you find a specific value using the values lists that appear in the dialog.

    2. Finding specific values

    Let us take the first query to the next level. Often you are not looking for just a single value but multiple values – in my case multiple streets. You want to compare the values in a column to a list of values. To do so you need to use the In operator instead of the = operator.

    I am looking for segments for three specific streets and can use this condition to find these: Name In ("Madison Dr NW", "Jefferson Dr SW", "12th St NW").

    Notice how I list the street names inside the parentheses.

    The query looks like this:

    Select *
      From DC_Street_5
      Where Name In ("Madison Dr NW", "Jefferson Dr SW", "12th St NW")
      Into Selection

    If you are specifying numerical values, you do not need to use quotes around the values.

    You can also do this very basic query using the Simple Select dialog.

    And the new Select by Attribute dialog can also help you build such a query simply by letting you select the street names you are looking for from a list. Do note that you will have to change the operator from equals (=) to on of (In).

    3. Sorting by values in a column

    If you have many records, you need to scroll thru you often want these presented in a certain order. To do so you can request the result to be sorted. In my example below, I have chosen to sort my street segments by a single column: the street name. You can however sort by up to five columns. You separate the columns using a comma (,).


    The query looks like this:

    Select *
      From DC_Street_5
      Order by Name
      Into Selection


    You can also do this very basic query using the Simple Select dialog. The Simple Select dialog does however only allow you to sort using a single column.

    Remember if you are viewing your result in a browser window, you can always sort the content of the browser by right-clicking on the column headers or using the Sort options on the TABLE tab.

    4. Grouping by values in a column

    If you have many records it can be difficult to get an overview of the data. To understand your data better, it can be a good idea to group the records by for example a type or in my example using the street name.

    When you group your data, the resulting query will only hold one record for each unique value. In this way, you can go from thousands of records to a few hundred records.

    Often you will combine a group-by query with a sorting query and add some sort of aggregation to the result. In this example, we will sort by the street names and use the Count(*) aggregation to count the number of street segments for each street. I have also added a spatial aggregation that will combine the street segments into a single polyline for the entire street. I use the AggregateCombine aggregate that was added to MapInfo Pro v2019.


    Notice that I only include the column that I am grouping by in my result. If I were including other columns, the values in these would be random, typically from the first occurrence of the column, I am grouping by.

    By using a spatial aggregate, the resulting query table will stay mappable. Normally when you use Group By in a Select statement, the resulting query table will lose all the spatial data.

    The query looks like this:

    Select Name, Count(*), AggregateCombine(obj)
      From DC_Street_5
      Group By Name
      Order By Name
      Into Selection

    5. Using column aliases

    A column alias is used to rename an existing column name or an expression used in a SQL Select statement. As the query result is only temporary, the renaming is so too. If you save the query as a new base table, the alias name will however stick.

    To specify a column alias, you enter the new name in quotes right after the column or expression that you want to assign it to. Here we are using it on the Count(*) aggregation from the earlier example. You can also add the As keyword in front of the column alias if you are using MapInfo Pro v2019 or newer.


    The query looks like this:

    Select Name, Count(*) "NumSegments", AggregateCombine(obj)
      From DC_Street_5
      Group By Name
      Order By Name
      Into Selection

     

    Which basic SQL Select statement are you using the most? And have you started using the Select by Attribute dialog to build your queries?

    This is an updated version of this four-year-old article: 5 basic SQL Select statements.



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


  • 2.  RE: MapInfo Monday: Five Basic SQL Select Statements

    Employee
    Posted 06-23-2022 02:26
    Edited by Peter Møller 06-23-2022 02:27
    Here's a short video showing how to build a SQL Select statement looking for specific values through the SQL Window.

    We will also get a video showing how to do this through the Select by Attribute dialog posted.

    You can find more MapInfo Pro related videos in the MapInfo Pro Playlist on the Precisely YouTube channel.

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



  • 3.  RE: MapInfo Monday: Five Basic SQL Select Statements

    Employee
    Posted 06-24-2022 02:04
    And here's the video showing how to select records with specific values through the (new) Select by Attribute dialog.

    Enjoy!

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