Subscribe to this thread
Home - General / All posts - Surface virtual table row "ID"
mdsumner


4,260 post(s)
#30-Mar-15 00:44

Hello, is it possible to generate an ID for the virtual table of a surface? I have many surfaces that match exactly in extent, dimension, scale - and I want to write a query to build a single table with multiple columns from the several [Height (I)] columns of my surfaces.

The "row ID" in the virtual table is implicit - is there a better way to join than like this on X and Y?

SELECT [S1].[Height (I)][S2].[Height (I)] FROM 

   [S1] 

JOIN [S2] ON

[S1].[X (I)] = [S2].[X (I)] AND [S1].[Y (I)] = [S2].[Y (I)]


https://github.com/mdsumner

tjhb
10,094 post(s)
#30-Mar-15 02:16

Mike,

It's a while since I checked how much faster this is than using two conditions, but if you want a unique ID to use in a single join condition you can do this:

SELECT [S1].[Height (I)][S2].[Height (I)]

FROM 

    [S1] JOIN [S2] 

    ON [S1].[Y (I)] * PixelsByX("S1") + [S1].[X (I)]

    = [S2].[Y (I)] * PixelsByX("S2") + [S2].[X (I)]

;

Or, with pre-preparation (more RAM usage I think, but possibly faster to join):

SELECT [T1].[Height (I)][T2].[Height (I)]

FROM

    (SELECT

        [Y (I)] * PixelsByX("S1") + [X (I)] AS [ID],

        [Height (I)]

    FROM [S1]

    ) AS [T1]

    INNER JOIN

    (SELECT

        [Y (I)] * PixelsByX("S2") + [X (I)] AS [ID],

        [Height (I)]

    FROM [S2]

    ) AS [T2]

    ON [T1].[ID] = [T2].[ID]

;

mdsumner


4,260 post(s)
#30-Mar-15 09:37

Thanks Tim.


https://github.com/mdsumner

danb

2,064 post(s)
#31-Mar-15 00:48

Am I right in saying that your queries assume that the two surfaces/images match exactly in extent, dimension, scale as per Mike's original question?

The speedup is pretty significant from some quick tests. Thanks for sharing.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

tjhb
10,094 post(s)
#31-Mar-15 02:02

Thanks for testing that Dan. Great.

Am I right in saying that your queries assume that the two surfaces/images match exactly in extent, dimension, scale as per Mike's original question?

Yes. It would be easy to make allowances where scales, extents or dimensions were different though (as long as the differences were known); sometimes this would mean access only to the overlap of course. Different projections would be hard (better in that case to use a completely different means).

danb

2,064 post(s)
#31-Mar-15 02:11

How would you adjust the query such that you retain the speed of the join but only return the overlapping portion, if [S1] and [S2] have the same dimensions but only partially overlap? I had a quick go, but destroyed the performance.

BTW in my very un-rigorous tests on a 25 million pixel surface, Q1 took ~450 seconds, Q2 took ~470 seconds and I gave up on the conventional join after nearly 3 hours with the join 88% complete.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

tjhb
10,094 post(s)
#31-Mar-15 04:09

I had a quick go, but destroyed the performance.

Same here. Confusing, and I hit something I don't understand at first glance--almost certainly my fault. Putting it aside for now.

Forest
625 post(s)
#31-Mar-15 10:49

Quick guess, joins work fast because the join keys are indexed and when you calculate join keys, they are not indexed?

tjhb
10,094 post(s)
#31-Mar-15 12:05

Good guess but not an answer here. See Dan's times fir Q1 and Q2 above.

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