MapInfo Pro

  • 1.  5 basic SQL Select statements

    Employee
    Posted 09-07-2018 08:27
    Edited by Peter Møller 02-21-2020 02:41

    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 analyse your data, find specific records and even to create derived data.

    Below you can find five basic SQL Select statements. I show them individually, but you can combine them in many ways.

    1. Finding 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 quotes. For numerical columns, you must not.

    In the example below, I am looking for all addresses on a specific road. The condition in the Where Condition field is: Streetname = "Rugårdsvej".

    See Attachment

    The query looks like this:

    Select *
      From Addresses

      Where StreetName = "Rugårdsvej"
      Into Selection


    If I were looking for specific road number, using numerical values, the condition would look like this instead: StreetCode = 6814.

    See Attachment

    The query looks like this:

    Select *
      From Addresses

      Where StreetCode = 6814
      Into Selection


    This is a very basic query that you also can do 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 on the column headers or using the Filter options on the TABLE tab.

    See Attachment

    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 addresses on three specific streets and can use this condition to find these: StreetName In ("Rugårdsvej", "Stadionvej", "Store Glasvej").

    See Attachment

    Notice how I list the street names inside the parentheses.

    The query looks like this:

    Select *
      From Addresses

      Where StreetName In ("Rugårdsvej", "Stadionvej", "Store Glasvej")
      Into Selection


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

    This is a very basic query that you also can do using the Simple Select dialog.

    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 addresses by three columns: the street name and the house number. You separate the column using a comma (,) and you can specify up to five columns.

    See Attachment

    The query looks like this:

    Select *
      From Addresses

      Order by StreetName, HouseNo
      Into Selection


    This is a very basic query that you also can do 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.

    See Attachment

    4. Grouping by values in a column

    If you have many records if 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 here 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 addresses on each street.

    See Attachment

    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.

    The query looks like this:

    Select StreetName, Count(*)
      From Addresses

      Group By StreetName
      Order By StreetName
      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

    See Attachment

    The resulting query will look like this in a floating browser window:

    See Attachment

    The query looks like this:

    Select StreetName, Count(*) "NumAddresses"
      From Addresses
      Group By StreetName
      Order By StreetName
      Into Selection

     

    Which basic SQL Select statement are you using the most?



  • 2.  RE: 5 basic SQL Select statements

    Posted 09-10-2018 02:07

    @Peter Horsbøll Møller?  not sure if it's the one I use the most but do feel that using geographic operators within SQL Select take this to another level. For example:

    "Select all objects from Table A that intersect objects in Table B"

    very powerful capabilities are unleashed with this type of query💪 ! The Geographic select operators can be combined with the basic statements you have listed above.



  • 3.  RE: 5 basic SQL Select statements

    Employee
    Posted 09-10-2018 02:25

    Very true, @Ashley Crane? - I have a couple of other articles lined up where I will try to cover these powerful spatial features of the SQL language in MapInfo Pro.

    This was just a basic article to get you started.