I have written, tested and used (extensively) the following query in Manifold 8.

' set Infr in Prprty Table to true where there is a turbine (Trbn-WFL001 Table) on the property.

UPDATE

( SELECT [Prprty].[Infr]

FROM [Prprty Table] AS [Prprty], [Trbn-WFL001 Table] AS [Trbn]

WHERE (Touches([Prprty].[Geom (I)],[Trbn].[Geom (I)]) AND ([Trbn].[Prjct/Othr]="P")) )

SET [Prprty].[Infr] = True

I believe that the conversion in Manifold 9 would be

-- set Infr in Prprty Table to true where there is a turbine (Trbn-WFL001 Table) on the property.

UPDATE

( SELECT [Prprty].[Infr]

FROM [Prprty Table] AS [Prprty], [Trbn-WFL001 Table] AS [Trbn]

WHERE (GeomTouches([Prprty].[Geom],[Trbn].[Geom (I)],0) AND ([Trbn].[Prjct/Othr]='P')) )

SET [Prprty].[Infr] = True

(Note that the geom in the 'Prprty Table' in Manifold 9 is Geom, whereas in Manifold 8 it was 'Geom (I)'.)

When I execute this query, I get a "Cannot parse query." message. I have tried dozens of variations on this query but simply cannot make it work. Note that the "SELECT" portion of the query works as expected in Manifold 9. When I add the "UPDATE", it fails.

Is there something obvious that I'm doing wrong? Could it be that UPDATE in Manifold 9 will not implicitly consider more than a single Table in defining the WHERE condition whereas is would in Manifold 8?

Any help with this would be very gratefully accepted.

Thanks and cheers, Jim

I won't have time at the moment to explore this, but hopefully someone else will. If not, I will try to look at it this weekend. But, my first guess is that with an update statement, you need to get the unique id for the table you are looking for. I would search for UPDATE on the forum, and look at what others have written.

Another way to test it is to use the Join command, and then write out the SQL from the tool - that is really helpful.

But, this brings up a broader point, something many of us have written about: better error messages.

Cannot parse query could mean so many things. Maybe a column is misspelled. Maybe a comma is missing, or the parentheses aren't balanced. But, the error messages are really limited, and that is going to have to get fixed at some point because complex queries are very difficult to debug at the moment.

Hi Art,

Thanks for your quick response. (For your interest, I was a post-doc in Civil and Environmental Engineering in Hollister Hall at Cornell in the early 1980's - yes, I'm that old and still trying to figure out Manifold 9.)

I'm pretty sure that the syntax is (at least close to) correct. As noted, the SELECT portion of the query returns the expected result. I just don't know how to pipe that (correctly) into the UPDATE portion of the query. As also noted, it worked fine in Manifold 8, but the conversion to Manifold 9 seems to have broken something.

Would it be possible to expand a bit on using the JOIN command? (SQL is not my native language - most comfortable in FORTAN!)

I agree fully with your comment with respect to error messages. It is definitely a challenge when nearly every failure simply generates the same message.

Thanks and cheers, Jim

Hi Jim,

I’m guessing you must have worked with or taken classes with Tom O’Rourke. He’s a great guy, and he just retired.

The join command is actually a GUI command that sets up a join and update structure. You can then press the button and see the SQL that would make it happen.

Here is the link to the command: https://manifold.net/doc/mfd9/join.htm

Hi Art,

I don't recall Tom O'Rourke but that doesn't mean he wasn't at Cornell at the time.

Thanks for the link. I will study it to see what insight I might obtain.

Cheers, Jim

Hi Art, Yves,

I have looked at your suggestions.

I used the Join GUI window and (sort of) achieved want I wanted. The resulting SQL code generated by Manifold is as follows.

-- $manifold$

--

-- Auto-generated

-- Join

--

UPDATE (

SELECT

t.[mfd_id] AS tkey0,

t.[Infr] AS t0,

s.s0

FROM [Prprty] AS t LEFT JOIN (

SELECT

[s_mfd_id] AS skey0,

FirstNonNull([o_AllTrue]) AS s0

FROM CALL GeomOverlayTouchingPar([Prprty], [Trbn-WFL001], 0, ThreadConfig(SystemCpuCount()))

GROUP BY [s_mfd_id]

) AS s ON t.[mfd_id] = s.skey0

) SET

t0 = s0;

There are a couple of issues with this although perhaps they arise simply from my shallow depth of understanding of SQL.

1. I don't fully understand what is happening here, but I think I have an inkling. (I do find the syntax to be non-intuitive (unlike the Manifold 8 version, which I could understand several years after writing it), but this may well be due to my lack of experience here.)

2. Because this code uses CALL GeomOverlayTouchingPar(... to generate a table from which "touching" geoms can be extracted, it is not clear to me how I could incorporate my second criterion (AND ([Trbn-WFL001 Table].[Prjct/Othr]='P') from the Trbn-WFL001 Table.

Again, any guidance on this would be very helpful.

Many thanks and cheers, Jim

Glad that worked. And yes, sometimes it can be a little difficult to understand exactly what is going on. but honestly, I think if you spent no less than 20 minutes looking line by line and trying to see what each column that was renamed was originally associated with, you will understand how the query works.

Once you understand how this works you are going to really see the power of those queries. But yes, it does take a little bit of time to wrap your head around them. But, fortunately, it doesn’t take that much time.

To help understand what is happening in the query:

The query wants to use GeomOverlayTouchingPar because that's a nice bulk task that can be parallelized. (In 8 we were writing SELECT ... WHERE Touches(...), but with big changes to the architecture in 9 calls like GeomOverlayTouchingPar scale significantly better.) So, the query calls GeomOverlayTouchingPar, this then returns pairs of records from the two tables, I will call these tables L and R. Since we are interested in updating data in L and for each record in L there might be multiple matching records in R and so multiple records in the result table of the overlay, the query then takes the result table of the overlay and groups it on a key field from L. After that, we have a table where each record from L occurs at most once. This table is read-only because its records do not correspond to any physical records in any table anymore, any links to the original tables have been erased by the overlay operation and by grouping. (A quick illustration of what I mean: if we have a table T -- SELECT Sum(pop) FROM T -- is read-only, because what should an attempt to edit the sum even do? such an attempt makes no sense. It's the same here, grouping, as well as the overlay operation itself, remove links to the original table and make data read-only.) To link the result back to the original L, the query uses LEFT JOIN. We then finally update the result of that JOIN.

(Erm, the above still seems too technical, but I guess it might help a little.)

From what I see, the table returned by the SELECT only includes a single field that you want to modify and is thus read-only in 9. This was different in 8.

In 8 tables are either stored in MAP files or are fetched in full into memory if they are linked. So, in 8 each record can remember which source record it came from automatically. Because of this, the result table of the SELECT in 8 is writable automatically.

In 9 tables can be stored in data sources other than MAP files and they are not fetched in full into memory. So, in 9 a record cannot generally automatically track its source record, it needs additional data to do this. This additional data comes in the form of key fields. Because of this, the result table of the SELECT in 9 is writable only if the SELECT includes the key fields for one of the unique indexes in the source table.

Alter the SELECT to include MFD_ID or something similar and the UPDATE should work. (If it still doesn't, send example MAP file and we will take a look at what else might be missing.)

Hi adamw,

Many thanks for your reply.

I altered my query based on your suggestion ("Alter the SELECT to include MFD_ID or something similar and the UPDATE should work.") The resulting query is as follows:

UPDATE

( SELECT [Prprty].[mfd_id], [Prprty].[Infr], [Trbn].[mfd_id], [Trbn].[Prjct/Othr]

FROM [Prprty Table] AS [Prprty], [Trbn-WFL001 Table] AS [Trbn]

WHERE (GeomTouches([Prprty].[Geom],[Trbn].[Geom],0) AND ([Trbn].[Prjct/Othr]='P')) )

SET [Prprty].[Infr] = True

It still doesn't work for me. Note that the SELECT portion of the query works fine and provides the results that I expect. Note, also, for clarification that I have changed the naming of the "Trbn-WFL001 Table" geom from "GEOM (I)" in my original post to "Geom" and this is reflected in the query shown above.

I believe that I have attached a .map file (JimSalmon-M8vM9UPDATEQuestion-20220916.map), but I'm not certain how this should work for this forum. It it's not there, please let me know how to make it available to you.

Assuming that I have actually attached it, the .map contains the two tables in question (Prprty Table, Trbn-WFL001 Table) and two queries. The "Test-SELECTonly Query" is the SELECT portion of the UPDATE query and, as noted above, works fine. The "Test-UPDATE Query" is the query shown above and does not work for me.

Again, many thanks for your (and everyone's) assistance and I would be pleased with any further clarification that might be provided.

Thanks and cheers, Jim

Attachments:
JimSalmon-M8vM9UPDATEQuestion-20220916.map

You can’t UPDATE a table (virtual or otherwise) unless it has a unique index.

A virtual table which is the result of an inner join, like yours, can’t retain a unique index from any of the source table(s), because in principle (which is what matters) there might have been multiple matches, whether N:1, 1:M, or N:M.

So to UPDATE a joined table, you must force the target table to have uniquely indexed rows.

The easiest and usual way is by grouping. In that case, indexing can in principle be preserved—the SQL compiler can see this and behaves accordingly.

But first, check your groups! Multiple matches can occur when you least expect them. Especially with derived data. Usually they matter.

This was also an issue in Manifold 8, but 8 simply ignored it, allowing you to shoot yourself (or your best friend) in the foot.

Manifold 9 is more careful.

Tim is right, including key fields into the SELECT from the first post is not enough, because the result table of that SELECT may contain multiple records for each record in the table you want to update. I overlooked it, apologies.

There are multiple ways of dealing with this:

(1) You can force the result table of the SELECT to contain at most one record for the table you want to update using GROUP BY. Then join the result of that operation back into the table you want to update using INNER JOIN or LEFT JOIN. This is what the query in the Join dialog does.

(2) Convert a query that joins two tables and then filters the resulting pairs of records using WHERE GeomTouches(...) into a query that traverses the table you want to update and only keeps records that return something in that WHERE. Eg:

--SQL9

SELECT [mfd_id], [Infr] FROM [Prprty Table] AS [Prprty] WHERE EXISTS (

  SELECT * FROM [Trbn-WFL001 Table] AS [Trbn]

    WHERE GeomTouches([Prprty].[Geom], [Trbn].[Geom], 0)

      AND ([Trbn].[Prjct/Othr]='P')

);

The result is writable, you can update it as follows:

--SQL9

UPDATE (

SELECT [mfd_id], [Infr] FROM [Prprty Table] AS [Prprty] WHERE EXISTS (

  SELECT * FROM [Trbn-WFL001 Table] AS [Trbn]

    WHERE GeomTouches([Prprty].[Geom], [Trbn].[Geom], 0)

      AND ([Trbn].[Prjct/Othr]='P')

)

) SET [Infr] = TRUE;

...or, simpler:

--SQL9

UPDATE [Prprty Table] SET [Infr] = TRUE WHERE EXISTS (

  SELECT * FROM [Trbn-WFL001 Table] AS [Trbn]

   WHERE GeomTouches([Prprty Table].[Geom], [Trbn].[Geom], 0)

     AND ([Trbn].[Prjct/Othr]='P')

);

You can also wrap the test in the inner SELECT into a function:

--SQL9

FUNCTION TouchesTrbnP(@g GEOM) BOOLEAN AS (

  EXISTS (

    SELECT * FROM [Trbn-WFL001 Table]

    WHERE GeomTouches(@g, [Geom], 0) AND [Prjct/Othr]='P'

  )

) END;

UPDATE [Prprty Table] SET [Infr] = TRUE WHERE TouchesTrbnP([Geom]);

Hope this helps.

wow. OK, I have a new request for the forum: can we have some capability to pin a post? This is one I'd like to come back to. Though admittedly, I'd probably have about 1,000 pins at this point!!

This is a very useful reference, thanks Adam. +1 for pinning or adding things like this to the manual.

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

Thanks to you both. :-)

Actually, there is one more way to write the query, which might be superior to the two I mentioned above:

(3) Use GeomOverlayTouchingFilter (or its Par variant). GeomOverlayXxxFilter functions take two drawings, overlay them, then return records from the first drawing which are in the specified spatial relation with any record in the second drawing. (GeomOverlayXxx functions just return pairs of records, like the SELECT in the first post.) The result table of GeomOverlayXxxFilter is writable = directly updatable.

Now, there are two obstacles to using GeomOverlayTouchingFilter on the MAP file posted above. First, the MAP file doesn't have drawings. That's solvable, we can just CREATE DRAWING using SQL, or we can use ComponentFieldDrawing to create virtual drawings to pass to GeomOverlayTouchingFilter. Second and more difficult, you are interested in touches not just with any record from [Trbn-WFL001 Table], but only with those that have [Prjct/Othr]='P'. That's solvable, we can create a query that would perform this filtering, create a drawing on that query, then pass that drawing to GeomOverlayTouchingFilter.

Here's an example solution:

--SQL9

CREATE QUERY [Trbn Filtered] (

  PROPERTY 'Text'

    'SELECT * FROM [Trbn-WFL001 Table] WHERE [Prjct/Othr]=\'P\';',

  PROPERTY 'FieldCoordSystem.Geom'

    ComponentProperty([Trbn-WFL001 Table], 'FieldCoordSystem.Geom')

);

CREATE DRAWING [Trbn Filtered Drawing] (

  PROPERTY 'Table' '[Trbn Filtered]',

  PROPERTY 'FieldGeom' 'Geom'

);

CREATE DRAWING [Prprty Drawing] (

  PROPERTY 'Table' '[Prprty Table]',

  PROPERTY 'FieldGeom' 'Geom'

);

UPDATE CALL GeomOverlayTouchingFilter(

  [Prprty Drawing], [Trbn Filtered Drawing], 0)

SET [Infr] = TRUE;

First, we create a query that filters data in [Trbn-WFL001 Table]. That can be done interactively, obviously. The only interesting part of the CREATE QUERY statement is that in addition to setting the text of the query, we are also copying the coordinate system of the Geom field -- we need that because we are going to create the drawing on that query.

Second, we create a drawing on the query, and also a drawing on the other table. Nothing special. This can also be done interactively.

Last, we call GeomOverlayTouchingFilter on the two drawings and then we UPDATE the result. Once your data becomes large, you can upgrade GeomOverlayTouchingFilter to GeomOverlayTouchingFilterPar and specify that you want to perform the overlay using multiple threads, this will make it run faster.

Hope this helps.

Hi Adam,

Thanks for this third method. I find it more readable (like the Manifold 8 queries). In addition, the lack of Drawings is not a drawback. There are Drawings associated with my Tables. I just didn't include them with the .map file in order to keep the size down. (I guess a drawing doesn't add much to the file size, though.) However, I guess it would be necessary to create a drawing with just the turbines where " Prjct/Othr = 'P' ", although, as you noted, it could be created on the fly.

You wrote "...Once your data becomes large...". For this purpose (which you may have guessed has to do with designing and assessing wind farms including the relationship between project and other turbines, owned and not-owned properties, noise receptors which are and are not associated with the wind farm, etc.), it is very unlikely that any of the quantities would become greater than hundreds (and more likely in the dozens) for a single wind farm (or even group of wind farms). Because of this, ease of understanding the query (particularly, say, two years from now) is paramount and computational efficiency is less important (to me).

Before encountering your latest post, I proceeded with your previously suggested method and updated a long string of Manifold 8 queries to Manifold 9. They all are working well and efficiently. Again, many thanks to you and everyone for this.

I am about to submit another question on updating Manifold 8 queries to Manifold 9 which I think may touch on similar issues.

Again, many thanks and cheers, Jim

Not sure if this will resolve your problem, but it seems to me that you missed changeing one occurance of [Geom (I)] from M8 to just [Geom] in M9.

M8 ...

WHERE (Touches([Prprty].[Geom (I)],[Trbn].[Geom (I)]) AND ([Trbn].[Prjct/Othr]="P")) ) ...

M9 ...

WHERE (GeomTouches([Prprty].[Geom],[Trbn].[Geom (I)],0) AND ([Trbn].[Prjct/Othr]='P')) )

Check the presence of [Geom (I)] in the M9 query where it probably need be [Geom].

Like Art mentioned you may need a unique "mfd_id" field in your Prprty table too, if there isn't yet one.

Hi Yves,

I don't think this is the problem. I checked the Schema's and the Trbn Table indeed uses "Geom (I)". Presumably this is a holdover from Manifold 8. I will change it though, because you are correct that having some "Geom" and some "Geom (I)" will indeed be confusing.

Also, the SELECT portion of the query (on its own) works fine.

Thanks and cheers, Jim

Have a look at the following as an example

https://manifold.net/doc/mfd9/join_example__update_an_existing_field_with_join.htm

Click the "Setup Join and Edit Query" button to study the syntax and fine-tune further on if necessary.

Check for the resulting "select query" if it is"greyed". If "greyed" it is not updatable for M9. If it is "greyed" it will likely not return unique records and/or misses a unique index field eg mfd_id.

Hi Yves,

Thanks for this reference. I looked at it, and I tried Art Lembo's suggestion. You can see the results in my reply to Art.

Many thanks for your assistance.

Cheers, Jim