Subscribe to this thread
Home - General / All posts - Create Geometry from SQL on Surface
hweather55 post(s)
#07-Nov-11 20:05

My task at hand is this:

Make an Area drawing that represents All of the (non null aka Visible (I)=True) grid cells of a surface raster. A fundamental attribute will be the Height (I) from the surface. My initial thought was a select query in a pseudo code as seen below.

-SQL

SELECT [Height (I)] as [Height],

MakeAreaGeom(

NewPoint( [Easting (I), [Northing (I)] ),

NewPoint( [Easting (I) + 2*ABS([Easting (I)]-Center Easting (I)]) , [Northing (I)]),

NewPoint( [Easting (I) + 2*ABS([Easting (I)]-Center Easting (I)]) , [Northing (I)])+2*ABS([Northing (I)]-Center Northing (I)]) ,

NewPoint([Easting (I), [Northing (I)]+2*ABS([Northing (I)]-Center Northing (I)]))

) as [Geom (I)]

from [Surface]

-

I am aware that the MakeAreaGeom is not a real SQL satement. Is there a way to generate an appropriate entry into geometry field with SQL from a query on a surface that could be later linked to in order to make a drawing of areas?

many thanks

volker

1,086 post(s)
#15-Nov-11 19:44

Something like in this thread ?:

http://www.georeference.org/forum/t107041.5

The query create a point at the center of the pixel and create an area with a boundingbox-buffer at the size of the pixel...but have a look at the attached map in this thread above.

Thanks to Art L. for the query.


http://www.thegisservicesector.de

hweather55 post(s)
#15-Nov-11 21:45

Bingo! Somehow my search missed this thread. Thanks for the pointer. this is exactly what I needed.

volker

1,086 post(s)
#14-Jul-16 20:15

Not the right title for my question, but i don't want to start a new thread....

What i have is a Drawing and a Surface and i run this query:

UPDATE [DRAWING] SET [Field] = Height([SURFACE], GEOM([Geom (I)]));

This should returns average height of pixels covered by my geom (the drawing). Is this right ?

But what Manifold use ? The center of the pixel or a edge (the lower left corner) ?

Cause i get difference between the calculation if i do this in Arc...


http://www.thegisservicesector.de

tjhb
10,094 post(s)
#15-Jul-16 04:30

That syntax is fine, although Geom([Geom (I)]) is equivalent to just [Geom (I)].

You can use any of these. If geoms are areas then all the results are the same.

Height([Surface], [Geom (I)])

HeightAvg([Surface], [Geom (I)])

Height([Surface], [ID])

HeightAvg([Surface], [ID])

In principle, Manifold uses the whole pixel, just as if it were itself a square area. If any part of a pixel falls within an area, then in principle its height is counted.

However, there are coding anomalies which can become significant when area size approaches pixel size. (I reported bugs in 2009/2010 and had confirmation from Tech explaining the cause. I can ask their permission to post the response in case of interest.)

Depending on your data, this could be enough to give differences compared to results in Arc. They would normally be minor, unless the size of the measurement areas (or detail in parts of the areas) is comparable to the size of surface pixels.

Do you want to have a second set of eyes look over your data, to see if that might apply?

volker

1,086 post(s)
#15-Jul-16 10:20

Hello Tim,

thank you for your fast answer.

I use the GUI-Function [Drawing->Transfer Height...] and i got the same result like in sql. Thats also similar.

I have 2 Surfaces with the same data, one surface with pixelsize 1x1m and one with pixelsize 20x20m.

The compare between Manifold and Arc with the 1x1m pixel Surface is no problem, this datas are more or less the same, but if i compare the results between Arc and Manifold with the 20x20m pixel Surface the result are in some cases far different (but i dont have a closer look to this).

But what i don`t understand: if i turn my mouse courser over a surface pixel i got difference values in the Z-Field on my screen.

If you want i upload my datas here...


http://www.thegisservicesector.de

volker

1,086 post(s)
#15-Jul-16 12:08

After looking deeper into my datas and what happens with it i can say Tim is right.

Every pixel that falls into the Drawings Area is used to calculate. In ArcGis it seems only the centroid of pixel it used.

What i have done: paste the surface as table and copy the table and paste it as drawing and use the View Bot to calculate the points that falls into the Drawings Area. So i come to my result describes above.

But something is curios: you must use Easting and Northing instead of Center Easting and Center Northing to paste the table as a drawing where the points are the centroid and not the left corner....


http://www.thegisservicesector.de

tjhb
10,094 post(s)
#16-Jul-16 00:20

It makes good sense that you get better results with a finer surface than with a coarser.

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