Subscribe to this thread
Home - General / All posts - M9 GeomClip
ColinD


1,918 post(s)
#26-Apr-18 08:09

I've been following the steps in help Clip Areas with a Transform Dialog Expression and am lost on syntax for when there is a clip area already available (in this case a rectangle) in the project. This is what I have so far:

PRAGMA ('progress.percentnext' = '100');

UPDATE (

  SELECT [mfd_id],

    [Geom],

    GeomClip([Geom], ([ClipArea], True, 0) AS [n_Geom]

  FROM [Drawing]

  THREADS SystemCpuCount()

SET [Geom] = [n_Geom];

Invalid object reference.


Aussie Nature Shots

tjhb

8,760 post(s)
#26-Apr-18 08:25

So [ClipArea] is a separate drawing (or table)?

In that case you will need a join, splicing the two sources together (and forming a virtual table inside the query).

For each row in the source table/drawing, there must be a clipping area in the same row. (It is a beauty of SQL joins that the clipping area can be either the same for each source object, or adaptive and different.)

Then you can perform one clipping operation per row.

ColinD


1,918 post(s)
#26-Apr-18 09:15

Tim, if that's the case why is it that the help examples using GeomMakeRect do not use a join?

rk maybe not, there are two pairs of brackets.


Aussie Nature Shots

tjhb

8,760 post(s)
#26-Apr-18 09:27

You are not using GeomMakeRect.

What is crucial is what and where [ClipArea] is.

If it is a column also in [Drawing] then Riivo's correct observation handles it.

Otherwise you need a join.

rk
308 post(s)
#26-Apr-18 09:28

then one ) is missing. you have 4 )-s and 5 (-s

If you want to clip one drawing A with all areas from other drawing B you can combine B to one area with function. (in my example A and B are in same table)

--SQL9

function aoi_holes() GEOM

AS

(

   SELECT  GeomUnionAreas([AreaGeom]as [AreaGeom] from [AOI] WHERE [IsHole]

)

END

;

UPDATE 

(

SELECT 

 [AOI].[mfd_id]

 ,

 GeomClip([AOI].[AreaGeom], aoi_holes(), false, 0) as [n_AreaGeom]

 ,

 [AOI].[AreaGeom]

FROM 

 [AOI]

WHERE

 NOT [IsHole]

 AND aoi_holes() IS NOT NULL

 

)

   SET [AreaGeom] = [n_AreaGeom]

;

ddd

tjhb

8,760 post(s)
#26-Apr-18 09:34

I think we should go back to Colin's original post. He puts his puzzle clearly.

I wish I could explain equally clearly!

I think example data, however trivial, is needed to do that. (Colin can you provide any?)

ColinD


1,918 post(s)
#26-Apr-18 10:46

Here's an example. The aim is to clip Bot_divs_LatLon with NSW_rect in the M8 manner of Clip With Intersect.

Thanks

Attachments:
Map.mml


Aussie Nature Shots

rk
308 post(s)
#26-Apr-18 11:20

Like this?

Note that Bot_divs outside are not deleted but [Geom] are set to NULL.

Attachments:
clip_area.PNG
m9_ClipArea.mxb

ColinD


1,918 post(s)
#26-Apr-18 12:06

Thanks but that hasn't quite worked Rivo. The partial areas at the edges of the clip are named unknown instead of the relevant Division name.


Aussie Nature Shots

adamw


8,579 post(s)
#26-Apr-18 16:03

But they have DIVISION set to 'unknown' in the original data, no?

Overall, two small notes:

I think Riivo's query is correct. I'd get rid of the WHERE check for the clip area not being NULL and do this manually, but that's minor. There are ways to improve performance for the general case - first do UnionAreas into a different table, and then just do First on that table instead of UnionAreas, because there's nothing to union. But that's for the general case, in this case the rect drawing is already just one record.

We are going to re-add the clip transforms to the Transform pane. We are missing a bit of the syntax currently (we want queries like this to be a little smarter than they can be so now, basically), this will be corrected.

Attachments:
unknown.png

ColinD


1,918 post(s)
#26-Apr-18 23:08

Ha! I didn't know my own data. The unknowns are indeed in the original. Odd. Thanks again for this.


Aussie Nature Shots

steveFitz

230 post(s)
#26-Apr-18 13:19

l tried to import Colin's mml file into the latest Viewer 9.0.166.5:

• file menu - nothing

• right click > Create data source - system data tables only

. . . and all on a windows tablet with a digitizer pen!

Even managed to freeze pen input pad writing this

(I'm not usually such a masochist)

adamw


8,579 post(s)
#26-Apr-18 15:48

(I cannot reproduce this. Launched Viewer 9.0.166.5, the 64-bit EXE. Invoked File - Import. Selected MAP.MML. It imported and created a map, two drawings and two tables.)

steveFitz

230 post(s)
#27-Apr-18 07:49

Apologies for hijacking Colin's thread - I should have started a new post.

I confim that mml doesn't load drawings (using methods Adam describes above) on 64 bit Win 10 tablet but does work on 64 bit Win 7 laptop. Even tried unblocking it to no avail.

adamw


8,579 post(s)
#27-Apr-18 09:30

I suspect this affects more than just MML.

Can you read KML or, say, GPX? There is a chance you cannot and if so, that's likely related to .NET on the tablet not being a full version.

steveFitz

230 post(s)
#27-Apr-18 23:10

No, can't read kml either. GPX seems to have drawings and tables but I can't see anything in them (poss my lack of knowledge on working with GPX).

Given SSD on tablet is only 128GB is it best not to have full .Net?

adamw


8,579 post(s)
#28-Apr-18 10:46

Full .NET is not that big so if you work with any of the XML-based formats, you should be able to install it just fine. But regardless, it looks like it might make sense for us to reduce dependencies on .NET.

Thanks for the test!

ColinD


1,918 post(s)
#18-Mar-19 09:45

Back to this. I have imported a GDB with a table of state-wide cadastral boundaries, about 3 mil objects. I am wanting to clip this to an AOI as per this query that worked fine on the sample data I posted here. I have created an mfd_id and btree index on a field OBJECTID. I get the following result with the query:

> --SQL9

function cliparea() GEOM

AS

(

   SELECT  GeomUnionAreas([Geom]) as [Geom] from [ClipTo] 

)

END

;

UPDATE (

SELECT 

 [mfd_id]

 ,

 [Geom]

 ,

 GeomClip([Geom], cliparea(), True, 0) as [n_Geom]

FROM 

 [CADASTRE]

WHERE

    cliparea() IS NOT NULL

 

SET [Geom] = [n_Geom]

;

'Geom': Unknown name.

Using 9.0.168.10-x6

Both tables have a Geom field <geom, area>


Aussie Nature Shots

adamw


8,579 post(s)
#18-Mar-19 12:52

I'll check what's up with the syntax error, but right off the bat, try using this - don't compute union more often than is necessary:

--SQL9

 

VALUE @cliparea GEOM = (SELECT GeomUnionAreas(...) ...);

UPDATE (

  SELECT ... GeomClip([geom], @cliparea, TRUE, 0) AS ...

) ...;

tjhb

8,760 post(s)
#18-Mar-19 14:29

In Colin's query there is a collision between the [Geom] from the cliparea() function used in WHERE and the [Geom] from [CADASTRE] which the query wants to update.

(In any case solved by your optimization Adam.)

tjhb

8,760 post(s)
#18-Mar-19 18:50

Thinking about it some more, I'm pretty sure Colin's intention in using the WHERE clause is very different from what the query actually does. The same would apply for Adam's modification.

I suspect what Colin actually wants is to retain only the parts of the geometry data in [CADASTRE] that lie within the area(s) in [ClipTo]?

That can be done with GeomClip using an UPDATE query followed by a DELETE query, or more simply using one SELECT INTO or INSERT INTO query.

But the fastest tool for that job is likely to be GeomOverlayTopologyIntersectPar.

ColinD


1,918 post(s)
#18-Mar-19 19:31

I suspect what Colin actually wants is to retain only the parts of the geometry data in [CADASTRE] that lie within the area(s) in [ClipTo]

Correct, and that is what the same query does on the smaller drawing in the previously attached mxb.


Aussie Nature Shots

tjhb

8,760 post(s)
#18-Mar-19 20:11

You mean the .mxb attached by Riivo, Colin? (There is no other .mxb in the thread that I can see.)

I'll look at that.

tjhb

8,760 post(s)
#18-Mar-19 20:52

OK, Riivo's .mxb project includes the data you posted as .mml.

His query works without error on that data. If there are objects in [Bot_divs_LatLon] which do not intersect the area in [NSW_rect], then the original objects are replaced by NULL. That may not be ideal but is OK.

I was wrong that there is a (material) collision between the two [Geom] fields. The engine just handles it.

So, now, you have changed the data, and adjusted the query to suit, substituting [CADASTRE] for [Bot_divs_LatLon], and [ClipTo] for [NSW_rect]. (So it is not "the same query".)

Now you get an error 'Geom' : Unknown name.

Well, do both new tables, [CADASTRE] and [ClipTo], have a field named [Geom]?

If they do, it would be best to post a sample of the new data (both tables), along with the exact query you are using if different from the above.

ColinD


1,918 post(s)
#18-Mar-19 21:19

Well, do both new tables, [CADASTRE] and [ClipTo], have a field named [Geom]?

Yes, as per the OP.

So it is not "the same query"

Why is it not the same query if only the tables have changed? I was unaware that syntax might need to be different for different tables.


Aussie Nature Shots

tjhb

8,760 post(s)
#18-Mar-19 21:25

It is not the same query because you have changed it. Simple. Always say what you have changed, however trivial you think it may be. (And changing tables is not trivial.)

You really will have to post current data for anyone to address this properly.

Preferably a project, with both tables and the query, where you know you get the error. That would be ideal.

Just saying "I did the same thing, and now I get this error" is no good, especially when you are not doing the same thing.

ColinD


1,918 post(s)
#18-Mar-19 20:12

Thanks, but I still get 'Geom' : Unknown name.


Aussie Nature Shots

tjhb

8,760 post(s)
#19-Mar-19 02:13

Colin, since you haven't been able to post data, can you confirm what result you get with each of these queries.

(a) SELECT only.

--SQL9

VALUE @cliparea GEOM = 

    (

    SELECT GeomUnionAreas([Geom])

    FROM [ClipTo]

    )

;

SELECT 

    [mfd_id],

    [Geom],

    GeomClip([Geom], @cliparea, TRUE, 0) AS [n_Geom]

FROM [CADASTRE]

;

(b) The same with UPDATE ... SET.

--SQL9

VALUE @cliparea GEOM = 

    (

    SELECT GeomUnionAreas([Geom])

    FROM [ClipTo]

    )

;

UPDATE 

    (

    SELECT 

        [mfd_id],

        [Geom],

        GeomClip([Geom], @cliparea, TRUE, 0) AS [n_Geom]

    FROM [CADASTRE]

    )

SET [Geom] = [n_Geom]

;

(You don't need the WHERE clause in either case.)

[Corrected.]

ColinD


1,918 post(s)
#19-Mar-19 03:19

Oh dear here I go again, how embarrassment totally out of my depth. CADASTRE has a column full of <geom, area> but it is not named Geom. Trying to change that column name with the following script from

http://www.georeference.org/forum/t145627.7#145857

'VBScript

Sub Main

 Set table = Document.ComponentSet("Table")

 Set columns = table.ColumnSet

 If columns.ItemByName("Height") < 0 Then

 Exit Sub ' no column with this name

 End If

 Set column = table.ColumnSet("Height")

  column.Name = "Renamed"

End Sub

I get error [5:3] Object required: 'Document'


Aussie Nature Shots

tjhb

8,760 post(s)
#19-Mar-19 05:26

That's a script for 8, which can't help in 9, among other reasons because you can't rename a column in 9. (Really can't. There are reasons why.)

What you need to do instead is make a new column, copy the old data, then optionally delete the old column.

...Or just rename the column in the query. Much simpler.

What is the geometry field called now? Use that name.

ColinD


1,918 post(s)
#19-Mar-19 08:07

Thanks Tim I have tried that and still get the same 'Geom': Unknown name error. The GDB can be downloaded from

http://qldspatial.information.qld.gov.au/catalogue/custom/detail.page?fid={4091CAF1-50E6-4BC3-B3D4-229AA318231A}

Cadastral data weekly - whole of State Queensland

About 350 MB

The ClipArea drawing is attached. I am needing to clip QLD_CADASTRE_DCDB

Attachments:
ClipArea.zip


Aussie Nature Shots

tjhb

8,760 post(s)
#19-Mar-19 21:12

Thanks Colin.

Cadastral data weekly - whole of State Queensland

DP_QLD_DCDB_WOS_CUR.zip

About 772 MB so far, unknown time left. Finished now. 1 GB total.

ColinD


1,918 post(s)
#19-Mar-19 22:09

Sorry to put you to all that Tim, but many thanks for taking the trouble. I have made some progress. In my previous mucking around I somehow managed to delete both the Cadastre and ClipTo drawing contents which explains the unknown geom error. I have run Adam's query as follows:

--SQL9

VALUE @cliparea GEOM = 

    (

    SELECT GeomUnionAreas([Geom])

    FROM [ClipTo Drawing]

    )

;

UPDATE 

    (

    SELECT 

        [mfd_id],

        [O_SHAPE],

        GeomClip([O_SHAPE], @cliparea, TRUE, 0) AS [n_Geom]

    FROM [QLD_CADASTRE_DCDB_BAK Drawing]

'a backup drawing created in case of an accident

    )

SET [O_SHAPE] = [n_Geom]

;

This gave a result but nothing was clipped.


Aussie Nature Shots

tjhb

8,760 post(s)
#19-Mar-19 22:32

For SQL, a comment like

'a backup drawing created in case of an accident

should be

--a backup drawing created in case of an accident

Using ' for a comment is correct for vbscript and VB.NET, and was usually tolerated in Manifold 8 SQL as well, but in Manifold 9 SQL it is problematic, since single quote marks are used to define strings.

In Manifold 9, a simple query like

'comment

SELECT * FROM [mfd_meta]

;

gives no error, but no result, while reversing the lines

SELECT * FROM [mfd_meta]

'comment

;

is tolerated and does give the expected result.

I don't know if this is your current problem, but to avoid uncertainty use -- not ' for SQL comments.

--comment

SELECT * FROM [mfd_meta]

--comment

;

I have the data loaded. I'll let you know what the query gives for me.

ColinD


1,918 post(s)
#19-Mar-19 22:54

Noted Tim, thanks. That was just for the benefit of the post, I didn't run the query with that line included.


Aussie Nature Shots

tjhb

8,760 post(s)
#19-Mar-19 22:58

Cool. I thought maybe that was the case.

tjhb

8,760 post(s)
#19-Mar-19 22:40

One slight problem is that [ClipArea Table] uses WGS84, while [QLD_CADASTRE_DCDB] uses GDA_1994 (GRS80). That should be fixed since they have slightly different flattening. (There is no implicit reprojection in SQL9.)

Actually no it is a major problem, because of how the SPHEROID parameters are defined.

For [QLD_CADASTRE_DCDB], FieldCoordSystem.Geom is (with line breaks added)

GEOGCS[

"GCS_GDA_1994",

DATUM["D_GDA_1994",

SPHEROID["GRS_1980",6378137.0,298.257222101]],

PRIMEM["Greenwich",0.0],

UNIT["Degree",0.0174532925199433]]

For [ClipArea Table] it is

GEOGCS[

"GCS_WGS_1984",

DATUM["D_WGS_1984", SPHEROID["WGS_1984",6.378137e6,2.9825722356300156e2]],

PRIMEM["Greenwich",0.0],

UNIT["Degree",0.0174532925199433]]

If you create a map for just one of the drawings, then open the map and drag the other drawing into it, you'll imediately see the problem (massive scale difference).

So we need reprojection.

tjhb

8,760 post(s)
#19-Mar-19 22:53

Map images attached.

Attachments:
Map GDA_1994.png
Map WGS84.png

ColinD


1,918 post(s)
#19-Mar-19 22:59

Oh, projection issues were in the back of my mind but had assumed all would be handled on the fly.


Aussie Nature Shots

tjhb

8,760 post(s)
#19-Mar-19 23:11

I'm not sure that it should be such a large problem.

After all, 6378137.0 and 6.378137e6 are exactly the same number, differently expressed, while 298.257222101 and 2.9825722356300156e2 are only different from the 6th decimal place.

I am surprised that this causes a massive scale difference, rather than a subtle shift. It could be a bug. Or one or both drawings might be incorrectly encoded.

How was the ClipArea drawing created?

Anyway, for now, we can work around it...

tjhb

8,760 post(s)
#19-Mar-19 23:19

...Let me guess. [ClipArea] was created in Manifold 8, in the coordinate system of an image (or of a map created from an image)?

If so, it had local offsets > 0 and scales ≠ 1, and should have been reprojected to remove those before export.

After import from the shapefile you attached above, [ClipArea Table] has FieldBounds.Geom property

[ -591.0138888888889, -342.3333333333334, 735.7083333333334, 370.22222222222223 ]

with those figures expressed in degrees. That's not helping!

tjhb

8,760 post(s)
#20-Mar-19 00:14

(Can't do much now until we have a revised ClipArea that is correctly projected in Manifold 9.)

ColinD


1,918 post(s)
#20-Mar-19 00:16

That's not helping!

Indeed! Now I have the expected clip! So I have been my own worst enemy in this saga and thanks for sticking with me Tim, I owe you yet again.

A couple of observations along the way:

1. I note that if I delete a component in the project pane that component's tab remains in any map that contained it.

2. Following the help instructions and opening a drawing component or clicking on its tab in a map, edit>style is not available.

3. Somehow the drawing contents can been deleted but the table remains. I can re-create the drawing from that table.

4. I miss not seeing at a glance whether a component has contents or is empty, as per M8.


Aussie Nature Shots

tjhb

8,760 post(s)
#20-Mar-19 00:51

Cool, great.

1. Yes. Adam has explained (can't find where) why the current behaviour suits maps that may contain layers from embedded sources (outside the current project). I hope this is a rough paraphrase: say I have projects A and B, with B embedded in A, and one of B's drawings shown in a map in A. A is currently closed. I open B and edit it, e.g. deleting a layer. Or I just move project B, or it becomes unavailable. Later I (or perhaps someone else) opens project A and then opens the map in question. Should the now unconnected map layer be automatically deleted? In some cases this would mean that my carefully constructed map would be "automatically" ruined--especially bad if there are many such layers. Leaving the orphaned layer(s) present but empty seems more pragmatic.

But the behaviour could be changed for layers within the same project. Perhaps it will be, I don't know.

I can see how it could bite you in the bum. E.g. if you delete a particular drawing or image, then come back some days later, open a map that contains the (now) phantom component, and assume that you must not have deleted the component after all. There it is, the map layer. Much scratching of the head ensues.

2. Can you point to the topics/pages you are looking at? They are outdated. (I can see one at Edit Menu, at the bottom.) A job for you know who.

3. Not sure of the problem here. Remember that the table is primary in 9--it can have any number of drawings and/or images derived from it, including none. Deleting a drawing or image in the project pane does not affect the source table at all. If objects are deleted in a drawing window, they are removed from the table. If all objects are deleted in the drawing then this empties the table (and any other derived drawings likewise become empty). If a table is empty, all derived drawings or images show nothing.

4. So do I and we are not alone. The status bar readout in the Project pane in 8 is really useful.

ColinD


1,918 post(s)
#20-Mar-19 01:11

2. I had an old link in bookmarks. Found it now.

3. I will try and replicate this, it is a mystery. As noted, that geom unknown error was entirely down to both drawings being empty.


Aussie Nature Shots

tjhb

8,760 post(s)
#20-Mar-19 01:36

3.

I think I've got it.

Starting with a new project:

File > Create... New drawing. This gives you a drawing and its table. The table is primary, the drawing takes data from it. There is currently no data in the table, so the drawing shows nothing, that's fine.

Open a Command window, or make a new Query component, and run a simple statement like

SELECT [Geom] FROM [Drawing];

We get an empty result table, because the table is empty, but no error. That's good.

Now delete the table. The drawing remains, but is orphaned.

Rerun the same query.

SELECT [Geom] FROM [Drawing];

[Drawing] still exists, since we did not delete it, but it has no data source.

We now get the error

'Geom': Unknown name.

That makes sense, right?

But the error message could be made more obvious. It could say, for example

'Drawing': No table.

BTW we get the same error if both drawing and "its" table exist, but they are not correctly linked. That is, if the drawing's 'Table' property is missing, or if it names a table that does not exist.

ColinD


1,918 post(s)
#20-Mar-19 02:02

3.

That would be one source of the error but in my case I didn't delete any components. Somehow in trying variations of the queries I managed to empty both drawings and break the connection to the tables. So then had empty drawings and their tables still present in the project pane with the original data intact. That's what I want to try and find, how I emptied the drawing.

Agree that the error could be more informative under these circumstances.


Aussie Nature Shots

tjhb

8,760 post(s)
#20-Mar-19 02:08

How about this, very easy to do.

You renamed both a drawing and its table, but did not update the drawing's 'Table' property to reflect the change.

That has the same effect.

The property is not adjusted automatically when a source table is renamed (and probably it should not be--unsure). You have to make the adjustment manually.

ColinD


1,918 post(s)
#20-Mar-19 04:16

Yep, that's what I did to both. Will need to throw old M8 habits and assumptions.


Aussie Nature Shots

tjhb

8,760 post(s)
#20-Mar-19 02:06

Regarding point 1 again:

How would it be if orphaned map layers were systematically shown with greyed-out tabs? Differently from layers that have been disabled (turned off). So maybe both in italic/oblique font and also greyed out.

ColinD


1,918 post(s)
#20-Mar-19 04:19

Yes, much like greyed out tables that can't be edited.


Aussie Nature Shots

ColinD


1,918 post(s)
#20-Mar-19 10:06

Still wrestling this demon . The drawing is clipped to the ClipTo area so in a map they overlie as expected. However the query result still shows the full 3.2 mil components; still tied to the original table. I exported the supposed clipped drawing as shp which turned out to contain a massive many GB dbf. Then tried to import back into M9 which resulted in an empty drawing with the expected original table. I have gone through the help but so far no luck but not sure what to look for either.


Aussie Nature Shots

tjhb

8,760 post(s)
#20-Mar-19 14:45

Pretty sure this is because of the NULL metrics. Ideally these should be removed (UPDATE then DELETE).

Or, better, a different query, which will only return the clipped metrics, into a new table and drawing. Much more efficient.

And for speed I would use GeomOverlayTopologyIntersectPar, in preference to GeomClip.

rk
308 post(s)
#26-Apr-18 09:00

You have one extra (

([ClipArea]

ColinD


1,918 post(s)
#26-Apr-18 23:54

Supplementary questions:

1. In Rivo's query, where does the function cliparea () come from? I don't see that in the functions list.

2. Can the query be adapted to clip a drawing of lines? Or a drawing containing both lines and areas?

Thanks


Aussie Nature Shots

tjhb

8,760 post(s)
#27-Apr-18 00:35

1.

He wrote it!

See

FUNCTION ClipArea() GEOM AS

...

END;

in Riivo's code.

In 9 we can write custom functions in SQL, C#, IronPython, VB.NET, vbscript, JavaScript ... basically anything.

Riivo has used a custom function rather than a join in response to your question about GeomMakeRect (a built-in function).

Using a custom function rather than a join might be significantly faster in this case. It would be interesting to see a comparison.

2.

Yes.

The query can be simplified a bit, as Adam says, but it should work as it is for points, lines or areas, or any combination of object types.

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