Subscribe to this thread
Home - General / All posts - Query Question - Cross Tab Totals on Touching Drawings
tpg-land
142 post(s)
#12-Sep-17 18:43

Greetings!

I am trying to break down the distribution of values for various property categories within/touching defined areas.

Drawings: Parcels which contains "Categories" and "Value" & Grid which contains the "GridId"

Desired Result: Grid Drawing with new Columns for each "Category" and a sum of the "Category's Value" for each GridId Row.

See the attached example which may clarify the question.

Thanks for your suggestions in advance!

Scott

Attachments:
CrossTab_by_Touching_Drawings.map

tjhb

7,364 post(s)
#12-Sep-17 22:24

[No that's still not it. You need it pivoted.]

tjhb

7,364 post(s)
#12-Sep-17 22:43

OK finally. Sorry for being hasty.

OPTIONS CoordSys("Grid" AS COMPONENT);

TRANSFORM Coalesce(SUM([P].[MarketProp]), 0)

SELECT [G].[GridId][G].[Geom (I)]

FROM

    [Grid] AS [G]

    LEFT JOIN

    [Parcels] AS [P]

    ON Touches([G].[ID][P].[ID])

GROUP BY [G].[GridId][G].[Geom (I)]

PIVOT [P].[Category]

;

Link a new drawing from geometry [Geom (I)] in the query result.

tpg-land
142 post(s)
#13-Sep-17 20:16

Works Great!!!

Thanks!

tjhb

7,364 post(s)
#13-Sep-17 01:11

If you want to avoid the overcounting and double counting, here is a version that tabulates [MarketProp] values pro rata according to the area inside each grid cell.

OPTIONS CoordSys("Parcels" AS COMPONENT); -- reprojected

TRANSFORM Coalesce(SUM([MarketProp pro rata]), 0)

SELECT [GridId][Cell]

FROM

    (SELECT

        [G].[GridId][G].[Cell],

        [P].[Category],

        [P].[MarketProp] *

            (Area(ClipIntersect([P].[ID][G].[Cell])) / Area([P].[ID]))

            -- requires metrics in same projection

            AS [MarketProp pro rata]

    FROM

        (SELECT

            [GridId]

            Project([ID]CoordSys("Parcels" AS COMPONENT)) AS [Cell]

            -- match projections to divide pro rata by area

        FROM [Grid]

        ) AS [G]

        LEFT JOIN

        [Parcels] AS [P]

        ON Touches([P].[ID][G].[Cell])

    )

GROUP BY [GridId][Cell]

PIVOT [Category]

;

Attachments:
Aggregate MarketProp by category pro rata a.txt
Aggregate MarketProp by category pro rata b.txt
Aggregate MarketProp by category.txt

tjhb

7,364 post(s)
#13-Sep-17 01:55

Scott, do you mind if I use your sample map for submitting a possible bug report? I still have to check whether it is just me. Regardless, it doesn't affect any of the above. Tim

tpg-land
142 post(s)
#13-Sep-17 20:30

Just curious, does it have to do with the Projection of the Parcel Drawing?

tpg-land
142 post(s)
#13-Sep-17 20:18

Absolutely...you may use the map to submit the potential bug report.

Thanks again!

Scott

tjhb

7,364 post(s)
#15-Sep-17 02:24

Thanks Scott.

I don't think the different projections are the issue, but I'll match them before submitting a report. The issue I thought I saw was with Topology Overlay and in particular the Proportional subdivision rule, which I used as a check on the second query above. It took me quite a while to be sure that my answers were right and the built-in results unexplained. I still have to re-check.

tpg-land
142 post(s)
#15-Sep-17 19:16

Sloppy on my part, but the Parcels are received in State Plane SC NAD83 feet which for some reason are imported as Lambert Conformal Conic (see Assign Projection on sample map).

Prior to "Assigning" the correct projection, the layer always is visible and projected correctly, but I am swiftly reminded of the iffy projection status during some calculations at which point I "Assign" the projection as I should have immediately upon importing.

There are no complaints/errors/warnings when "Assigning" to the correct projection so Manifold must have some idea of "where" the drawing resides in space for rendering.

Not sure if this might explain anything, but it is another reminder that setting projections is important!

Thanks again for your solutions!

Scott

tpg-land
142 post(s)
#13-Sep-17 20:19

I had not thought about splitting the value up based upon the amount of the parcel that was 'contained' within the grid.

Awesome suggestion and solutions!

Thanks!

Scott

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