advanced sql selects
DESCRIPTION
Practice calculating distances, object lengths and other geographic operations in SQL. Learn how to use Sub-selects and other advanced featuresTRANSCRIPT
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Welcome
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Hands on Lab 13D Advanced SQL Selects
Peter Horsbøll MøllerGIS/LI Pre-Sales SpecialistJune 13, 2014
Every connection is a new opportunity™
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Advanced SQL Selects
Practice calculating distances, object lengths and other geographic operations in SQL.
Learn how to use Sub-selects and other advanced features.
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Geographic Measurements
MapBasic functions
• Area(OBJ, ”units”)
• ObjectLen(OBJ, ”units”)
• Perimeter(OBJ, ”units”)
• They also exist as Cartesian- & Spherical- versions
• Units: ”m”
”km”
”in”
”ft”
”mi”
...
”sq m”
”sq km”
”hectare”
”sq ft”
”sq mi”
...
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Exercise
Data sets
•US\States.tab
•US\US_HIWay.tab
•Calculate the area of each state
•Calculate the perimeter of each state
•Calculate the length of each highway
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Aggregations and Grouping
With the aggregations you can extract statistical information from a number of records, such as Sum, Average and Minimum or Maximum values
The aggregations often take a column as parameter. Count(*), however, just takes a *
If you use an aggregation you’ll get just one row in the result, unless you specify a column to group the result by
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Exercise
Data sets
•US\States.tab
•US\US_HIWay.tab
•Calculate the total area of all states
•Calculate the total area of the selected states
•Calculate the total length of the selected highways
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Joining Tables
When joining two tables thru a Select statement you need to specify a join condition
This can be a alfanumerical join:
• TABLE1.ID = TABLE2.ID
or a spatial join:
• TABLE1.OBJ Intersects TABLE2.OBJ
The spatial object is taken from the first table in the join statement
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Exercise
Data sets
•US\States.tab
•US\US_HIWay.tab
•Find the spatial join between highways and states, that is which states does the highways intersect?
•How much of each highway (in miles) is within the individual states?
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Sub Selections
A Sub Selection returns a value or a list of values that can compared to bojects or alfanumerical values
A sub select has this structure:
Select * From TABLE1 Where COLUMN1
In (Select COLUMN1 From TABLE2)
Or
Select * From TABLE1 Where OBJ Intersects Any (Select OBJ From TABLE2)
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Exercise
Data sets
•US\States.tab
•US\US_HIWay.tab
•Find the states that has one or more highways crossing
•Find the states that has no highways crossing
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Measuring Distance
MapBasic functions
• Distance(xFrom, yFrom, xTo, yTo, ”units”)
• Exists also as Cartesian- & Spherical- versions
• Format$()
• Can be used to forse a larger number of decimals:
Format$(numeric_value, ”#.######”)
• CentroidX(object) / CentroidY(object)
• Extracts coordinates from the centroid of an object
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Exercise
Data sets
•US\States.tab
•US\US_HIWay.tab
•US\STATECAP.tab
•Find the distance from every state capital to Washington
•Find the distance from every state capital to Nashville
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Solutions
1 Select State_Name
, Area(obj, "sq mi") "Area sq miles"
from STATES into Selection
2 Select State_Name
, Area(obj, "sq mi") "Area sq miles"
, Perimeter(obj, "mi") "Perimeter miles"
from STATES into Selection
3 Select Highway
, ObjectLen(obj, "mi") “Length miles”
from US_HIWAY into Selection
4 Select Sum(Area(obj, "sq mi")) "Area sq miles"
from STATES into Selection
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Solutions
5 Select Sum(Area(obj, "sq mi")) "Area sq miles"
from Selection into __SUM
6 Select Sum(ObjectLen(obj, "mi")) “Length miles”
from Selection into __SUM
7 Select US_HIWAY.Highway, STATES.State_Name
from US_HIWAY, STATES
where US_HIWAY.Obj Intersects STATES.Obj
order by US_HIWAY.Highway, STATES.State_Name
into Selection
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Solutions
8 Select US_HIWAY.Highway, STATES.State_Name
, ObjectLen(Overlap(US_HIWAY.Obj, STATES.Obj), "mi") "Length Miles"
from US_HIWAY, STATES
where US_HIWAY.Obj Intersects STATES.Obj
order by US_HIWAY.Highway, STATES.State_Name
into Selection
9 Select State_Name
from STATES
where OBJ Intersects Any (Select OBJ From US_HIWAY)
order by State_Name
into Selection
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Solutions
10 Select State_Name
from STATES
where not OBJ Intersects Any (Select OBJ From US_HIWAY)
order by State_Name
into Selection
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Solutions
11 Set CoordSys Table STATECAP
‘Select Washinton
Fetch First From Selection
Print Format$(CentroidX(Selection.obj), "#.######")
+ ", " + Format$(CentroidY(Selection.obj) , "#.######")
Select Capital, State
, Distance(-77.016167, 38.90505, CentroidX(OBJ), CentroidY(OBJ), "mi")
"Distance To Washington"
from STATECAP into Selection
MapInfo User Conference 2014: GIS Gets Personal #MapInfoUC
Get the latestMapInfo User Conference news:Twitter via #MapInfoUCand follow us @MapInfo