Subscribe to this thread
Home - General / All posts - Aggregate from external table joined to one of two drawings based on distance from a selected point SQ! 8 problem
Graeme

927 post(s)
#09-Mar-18 04:38

This is destined for an IMS implementation, so still working in 8.

I'm struggling with the syntax, still. Two points drawings D1 and D2. One stand alone table T containing data I wish to average based on a join field in D1, but restricted to points in D1 within a specified distance of a selection in D2. I've got all the bits working as separate queries but am struggling to nest them all together. The map contains a comments on objective and the query. Any help to drop the penny much appreciated..

Attachments:
Distance and external join SQL 8.map

adamw

8,061 post(s)
#09-Mar-18 09:42

Try this:

--SQL

SELECT Avg([T].[Income]AS [Avg_Income][T].[Income Class][D1].[Postcode]

  --, [D1].[Unique_ID] -- cannot use field due to GROUP BY

FROM [D2][D1] -- order should be D2, D1, because D1 is then used in JOIN

 

Left JOIN

[T]

ON [D1].[Unique_ID] = [T].[Unique_ID]

 

WHERE [D2].[Selection (I)] 

AND DistanceEarth([D2].[ID],[D1].[ID],"km") <=15

Group by [D1].[Postcode],[T].[Income Class];

There are two changes from the original query:

1. [D1].[Unique_ID] is excluded from the SELECT list. You are using GROUP BY and groups might contain records with different values of that field - either specify which one of them you want using an aggregate, or group by it, or exclude it from the list (which is what I did).

2. The order of drawings in the FROM section cannot be D1, D2. The FROM section then reads as D1, (D2 JOIN T). The comma operator does translate into a join of its own, but it has a lower priority than an explicit JOIN. (D2 JOIN T) then fails because its join criteria tries to use fields from D1. Just swap D1 and D2 around to make the FROM section read D2, (D1 JOIN T). Or use parens: (D1, D2) JOIN T.

Hope this helps.

Graeme

927 post(s)
#10-Mar-18 00:09

It does help a lot, thank you.

The overall objective was to select the D1 objects with aggregate values from T, but revisiting the manual I think I'm running into a hard limit:

Queries Joining Columns from Different Tables

In current editions of Manifold, queries that join columns from tables not associated with drawings cannot be used to select objects in drawings. For example, a query formed with "Union D1, D2, T3" where D1 and D2 are drawings and T3 is a table could not be used to select objects in the drawings.

Is my option then to aggregate T into a separate summary table and relate that to directly to D1 to allow D1.ID to be selected? Is this different in 9?

Graeme

927 post(s)
#10-Mar-18 04:22

The answer is to include an aggregating spatial extension in the select statement - AllBranches works for this example:

SELECT Avg([T].[Income]AS [Avg_Income][T].[Income Class][D1].[Postcode],AllBranches([D1].[Geom (I)]

FROM [D2][D1] 

    INNER JOIN

    [T]

    ON [D1].[Unique_ID] = [T].[Unique_ID]

WHERE [D2].[Selection (I)] 

AND DistanceEarth([D2].[ID],[D1].[ID],"km") <=15

Group by [D1].[Postcode],[T].[Income Class];

And link a drawing from the query.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.