--SQL9 FUNCTION Converter(source TABLE, target TABLE) -- arguments must be display components (e.g. drawing, image) -- not source tables TABLE AS CALL CoordConverterMake( ComponentCoordSystem(target), ComponentCoordSystem(source) ) END; FUNCTION TileSizeXY(metadata TABLE, table_name NVARCHAR, field_name NVARCHAR) -- dimensions of tiles in named field and table INT32X2 AS ( SELECT CAST([Value] AS INT32X2) -- source string in form '[ x, y ]' FROM metadata WHERE StringToLowerCase([Name]) = StringToLowerCase(table_name) AND StringToLowerCase([Property]) = StringToLowerCase('FieldTileSize.' + field_name) ) END; FUNCTION PixelCoordXY -- coordinates of pixel in image space (tileX INT32, tileY INT32, pixelX INT32, pixelY INT32, tilesizeXY INT32X2 ) INT32X2 AS VectorMakeX2( tileX * VectorValue(tileSizeXY, 0) + pixelX, tileY * VectorValue(tileSizeXY, 1) + pixelY ) END; FUNCTION VectorFloorX2(v FLOAT64X2) -- round vector values downwards INT64X2 AS VectorMakeX2( Floor(VectorValue(v, 0)), Floor(VectorValue(v, 1)) ) END; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -- temp database and reference to project CREATE ROOT [Scratch]; USE ROOT [Scratch]; CREATE DATASOURCE [Project] AS ROOT; -------------------------------------------------------------------------------- -- temp tables CREATE TABLE [image points] ( [point id] INT64, [Geom] GEOM, [imageXY] FLOAT64X2, [pixelXY] INT32X2, INDEX [pixelXY_x] BTREEDUP ([pixelXY]) ); CREATE TABLE [pixel values] ( [pixelXY] INT32X2, [Value] INT32, INDEX [pixelXY_x] BTREE ([pixelXY]) ); -------------------------------------------------------------------------------- -- output table and drawing CREATE TABLE [Project]::[Points with image values Table] ( [mfd_id] INT64, [source id] INT64, [Geom] GEOM, [Value] INT32, INDEX [mfd_id_x] BTREE ([mfd_id]), INDEX [Geom_x] RTREE ([Geom]), PROPERTY 'FieldCoordSystem.Geom' ComponentCoordSystem([Project]::[p]) ); CREATE DRAWING [Project]::[Points with image values] ( PROPERTY 'Table' '[Points with image values Table]', PROPERTY 'FieldGeom' 'Geom' ); -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -- populate temp tables INSERT INTO [image points] ([point id], [Geom], [imageXY], [pixelXY]) SELECT [mfd_id], [Geom], [imageXY], -- exact location in image space CAST(VectorFloorX2([imageXY]) AS INT32X2) AS [pixelXY] -- index of containing pixel -- reprojected point may be outside image bounds -- (with positive or negative coordinates) FROM ( SELECT [mfd_id], [Geom], CoordConvertPoint( CALL Converter([Project]::[p], [Project]::[t]), GeomCoordXY([Geom], 0) ) AS [imageXY] FROM [Project]::[p] ) ; -------------------------------------------------------------------------------- INSERT INTO [pixel values] ([pixelXY], [Value]) SELECT --[tileX], [tileY], [X], [Y], --[tileXY], PixelCoordXY( [tileX], [tileY], [X], [Y], -- pixel in tile [tileXY] ) AS [pixelXY], -- pixel in image --[Value] -- FLOAT64 regardless of source type CAST([Value] AS INT32) -- source type FROM ( SELECT [X] AS [tileX], [Y] AS [tileY], TileSizeXY( [Project]::[mfd_meta], StringTrim(ComponentProperty([Project]::[t], 'Table'), '[]'), ComponentProperty([Project]::[t], 'FieldTile') ) AS [tileXY], -- calculate once before SPLIT -- (with copy after) SPLIT CALL TileToValues([Tile]) FROM [Project]::[t] ) -- prefilter may be worthwhile for a very large image --WHERE PixelCoordXY( -- [tileX], [tileY], -- [X], [Y], -- pixel in tile -- [tileXY] -- ) IN -- (SELECT [pixelXY] FROM [image points]) ; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -- write output table INSERT INTO [Project]::[Points with image values Table] ([source id], [Geom], [Value]) SELECT t.[point id], [t].[Geom], --t.[imageXY], --t.[pixelXY], --u.[pixelXY], u.[Value] FROM [image points] AS t LEFT JOIN [pixel values] AS u ON t.[pixelXY] = u.[pixelXY] ; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -- clean up DROP TABLE [image points]; DROP TABLE [pixel values]; DROP ROOT [Scratch]; Attachments: Pixel values to points b.txt pts_val-9 e.map
|