MapInfo Query Workshop - April 14, 2005

      SQL - Structured Query Language

      Relation database terms:

            Database - collection of tables

            Tables - rows and columns, similar to a spreadsheet

            Row - record or tupple

            Column - field or attribute

Select Queries

      Field Types

  Select * from Join where area_tax < 35      # numeric field, not quoted

      Select * from Join where map < “00500"              # character field, requires quotes

      Select * from Retired where retired_date > "1/1/2004"        # date field, quotes

      Use Table > Maintenance > Table Structure to determine field types, etc.

Sorting results

single or multiple fields separated by comma

sort order is ascending (lowest to highest) append DESC to sort descending

Results set are a selection from the base table, not a copy.

Naming a query’s result set

Saving a copy to your hard disk - remember that you must then open it.

Operators

            Like versus Equals (=) 

        select * from join 

        where map like “%640”            # wildcards: % (multiple) and _ (single)

                  where map = “00640"                # equal does not accept wildcards

                  where map not like “%640”    # like uses not

                  where map <> “00640"                  # not equal 

Between

select *

from Retired

where retired_date between "1/1/2004" and "12/31/2004"

            In 

Select * from Join where map in ("00640", "00860", "00819" )

See “Find Unmatched” query below for another example using the In operator.

Divide and Conqueror

     Select * from Retired where retired_date > "1/1/2004" in R1

     Select * from R1 where retired_date < "1/1/2004"

Grouping results

Select *

from Weeds

group by Scientific

Aggregates

      Query > Calculate Statistics...

Functions

      In Select

Select Area(obj, "sq ft")

     from Zoning

     where zoning = "RU"

     Select zoning, Sum(Area(obj, "acre")) “Acres”   # notice use of alias

     from Zoning

     where zoning = "RU"

     group by zoning

      In Where

     from Zoning

     where Left$(zoning, 1) = "P"

      In Labels

     Mid$(Name1, InStr(1, Name1, ", ")+2, 35)

     +Chr$(10)+

     Left$(Name1, InStr(1, Name1, ", ")-1)

Joins - Multiple Tables (inner join)

Select *

from Assessor, Parcel

where Assessor.PIDN=Parcel.PIDN

Data Modification Queries

      Update Queries

      Append Queries

            field order must be the same between tables

Handy Queries

      Find duplicates

     Select PIDN, count(*) "cnt"

from parcel

group by PIDN

order by cnt desc

  Find unmatched

     Select *

     from TOJ_Zoning

     where zone in

        (select zoning from Zoning)

     group by zoning

     order by zoning

Returns all Town zoning district codes that do not exist in the County. Note that Zoning (with capital Z) is the name of the County Zoning table and that zoning (lower case) is the name of the field in each table. Not the handiest names for this example...

Spatial Operators

      GIS rows have geometry, we see it on the map, and we can query it:

Select obj from Parcel

Select * from Parcel where str$(obj) = "POINT"

Select objectinfo(obj, 21) from Parcel

      MapInfo references the geometry via the obj field. Obj is null for rows having no geometry.

      Example; points in polygons, finding all weed infestations on public lands.

      Get just polygons (not condominium points):

Select *

from Join

where str$(Join.obj) = "REGION"

into join_region

      Save the query to disk to create a “base table”.

      Close the query.

      Open the table.

Select *

from Weeds, join_region

where tax_id like "%nontax"

and Weeds.obj within join_region.obj

The WITHINoperator treats polygons as points, using their centriod. This makes for very fast queries. Example; parcels within a given precinct.

Select *

from parcel, precinct

where parcel.obj within precinct.obj

and combine = "4-1"

This is good if you only want to select something once, e.g. above example we only want to count each parcel in the county one time. Another example would be where you want to update or delete matching polygons but NOT adjoining polygons.

This is also the way that the multi-select tools work (Marquee Select, Radius Select, Polygon Select, and Boundary Select).

      The ENTIRELY WITHIN examines the polygon’s edges. Modifying the above query:

Select *

from parcel, precinct

where parcel.obj entirely within precinct.obj

and combine = "4-1"

            (this excludes polygons that are in more than one precinct)

      The INTERSECTS operator also examines edges

Select *

from parcel, precinct

where parcel.obj intersects precinct.obj

and combine = "4-1"

      This is what we use for the adjoiner notification query.

Reference info: G:\tools\mapbasic.hlp