You are pushing it, Håkan :-)
Unfortunately, MapInfo Pro doesn't come with a spatial aggregate function that can merge/combine objects - at least not out of the box.
But with v17.0.1 you are able to create your own functions using MapBasic and publish these to MapInfo Pro.
Here is the scenario, you talked about:
A municipality is trying to compare the school district coverage. Is this case they have both primary and secondary schools (the blue circles) inside their admin boundary (the blue polygon).
They want to know the total coverage of school districts compared to the admin boundary but are also looking for a detailed area based on school type.
The problem is that the school districts overlap and some of the district is also located outside the admin boundary. That means we can't just calculate the area of these.
MapBasic to the rescue
I created a small function that can combine objects from a table and return the combined object:
Function OBJCombineFromTable( ByVal sTab As String, ByVal sCol As String , ByVal sID As String ) As ObjectDim oCombined As Object, sCmd As StringOnError GoTo ErrorOccured sCmd = "Select * From " & sTab
If sCol Then
If ColumnInfo(sTab, sCol, COL_INFO_TYPE) = COL_TYPE_CHAR Then
sCmd = sCmd & " Where " & sCol & " = " & Chr$(34) & sID & Chr$(34)
Else
sCmd = sCmd & " Where " & sCol & " = " & Chr$(34) & sID & Chr$(34)
End If
End If If SystemInfo(SYS_INFO_MIVERSION) >= 1700 Then
sCmd = sCmd & " Into __TO__COMBINE NoSelect Hide"
Else
sCmd = sCmd & " Into __TO__COMBINE NoSelect"
End If ' Print sCmd Run Command sCmd Set Progressbars Off
Create Object As Union
From __TO__COMBINE
Into Variable oCombined
Set Progressbars On Close Table __TO__COMBINE OBJCombineFromTable = oCombined Exit Function
'-------------------------
ErrorOccured:
Call ERRCreate(Err(), Error$(), "OBJCombineFromTable")
Call ERRPrint()End FunctionNext, I published this function so that it will appear in the function lists in the SQL Select dialog:
nResult = RBNRegisterFunctionAsPublic("OBJCombineFromTable", "OBJCombineFromTable", "Combines objects from a table, optionally specify a column and a value") And now I can use it from with in the SQL Select dialog:
Here is the basic query that calculates the total area of the admin boundary, the area of the school districts and the area where there is no school district:
The query itself looks like this:
Select Admin.NAME
, CartesianArea(obj, "sq m") "AdminArea"
, CartesianArea(OBJCombineFromTable("Schools", "", ""), "sq m") "SchoolArea"
, CartesianArea(Erase(OBJ, OBJCombineFromTable("Schools", "", "")), "sq m") "NoSchoolArea"
From Admin
Into SelectionThe query where I group the result by the level of the school looks like this:
Select Admin.NAME, Schools.NAME
, CartesianArea(Admin.obj, "sq m") "AdminArea"
, CartesianArea(OBJCombineFromTable("Schools", "NAME", Schools.NAME), "sq m") "SchoolArea"
, CartesianArea(Erase(Admin.OBJ, OBJCombineFromTable("Schools", "NAME", Schools.NAME)), "sq m") "NoSchoolArea"
From Admin, Schools
Where Admin.Obj Intersects Schools.Obj
Group By Admin.NAME, Schools.NAME
Into Selection
The query result looks like this:
By default my tool is using a lat/long coordinate system but I can force it to use a cartesian/projected coordinate system by running this statement in the MapBasic window. If I don't the area calculations seem to fail to return a cartesian area:
Print WINHSetCoordsysUsingTable("Schools")------------------------------
Peter Horsbøll Møller
Pitney Bowes
------------------------------
Original Message:
Sent: 11-22-2018 03:21
From: Hakan Karlsson
Subject: Quick-Fire Webinar: Spatial SQL
Sounds like a great session Peter!
I remember your post in the old MapInfo journal where you created a buffer and made a spatial join on the fly in SQL (slurry tanks near water stream, was it?). Very nice example, but I would not be able to write it without some repetition. :-)
I do have one other idea, a question that came at an earlier Quick Fire seminar. Not sure if we can accomplish this using spatial SQL? Imagine you have a boundary, for example, an admin area. Inside you have a few region objects, maybe circular buffers, or catchments around schools. Can we somehow calculate the proportion of the larger area that is covered by 0, 1, 2, etc. of these buffers/catchments? Or for starters, can we at least calculate the proportion that is not covered by any of them?
------------------------------
Hakan Karlsson
Presales, Pitney Bowes Sweden
Original Message:
Sent: 11-21-2018 02:48
From: Peter Horsbøll Møller
Subject: Quick-Fire Webinar: Spatial SQL
Next week I'm presenting at another Quick-Fire webinar. This time the focus will be around Spatial SQL.
Any suggestions to what I should talk about or what you want to hear?
I'm certainly open to suggestions and input.
My initial idea was to start with some basic queries to calculate the size of object and extract coordinates from objects. We can then move on to converting objects or creating new objects based on the input objects and finally do some sub select and spatial joins and show how you can work with derived spatial objects.
But as I said, I'm more than happy to cover specific request too.
Find the webinar here and make sure you are signed up: Quick-Fire Webinear: Spatial SQL
------------------------------
Peter Horsbøll Møller
Pitney Bowes
------------------------------