Subscribe to this thread
Home - General / All posts - SQL query conversion from Manifold 8 to Manifold 9: INSERT INTO
Jim Salmon16 post(s)
#20-Sep-22 19:36

Further to my previous post on query conversion (8 to 9), I have used the following series of queries in Manifold 8 in many projects. The purpose is simply to place buffers areas (circles) in Stbck-RPV Drawing centred on points in RPV Drawing with a different size buffer for the [RPVQ] field values in [RPV Table] which are 'R's (noise Receptors), 'V's (vacant lot surrogate receptors) or 'P's (participants). R's and V's get the same (550 m) setback.

' Generate setback buffers

' initialize - delete all setback buffers from Stbck-RPV table/drawing

DELETE FROM [Stbck-RPV Table]

' generate 550 m setback from R's and V's

INSERT INTO [Stbck-RPV Table]([Geom (I)])

( SELECT Buffer([RPV].[Geom (I)],550.0)

FROM [RPV Table] AS [RPV]

WHERE [RPV].[RPVQ] = "R" OR [RPV].[RPVQ] = "V" )

' generate 275 m setback from P's. Note: No setback from Q's

INSERT INTO [Stbck-RPV Table]([Geom (I)])

( SELECT Buffer([RPV].[Geom (I)],275.0)

FROM [RPV Table] AS [RPV]

WHERE [RPV].[RPVQ] = "P" )

After everyone's very helpful discussion regarding the 8-to-9 conversion of UPDATE queries and some experimentation, I got the following queries to work in 9 as desired.

-- generate setback buffers

-- initialize - delete all setback buffers from Stbck-RPV table/drawing

DELETE FROM [Stbck-RPV Table]

;

-- generate 550 m setback from R's and V's

INSERT INTO [Stbck-RPV Table]

SELECT [mfd_id], GeomBuffer([RPV].[Geom],550.0,0) FROM [RPV Table] as [RPV]

WHERE ([RPV].[RPVQ]='R') OR ([RPV].[RPVQ]='V')

;

-- generate 275 m setback from P's. Note: No setback from Q's

INSERT INTO [Stbck-RPV Table]

SELECT [mfd_id], GeomBuffer([RPV].[Geom],275.0,0) FROM [RPV Table] as [RPV]

WHERE ([RPV].[RPVQ]='P')

;

So, my question is not how to make it work but to ask for a clarification. In the above, it seems that I'm forcing the index ([mfd_id]) from the [RPV Table] to be the index in the [Stbck-RPV Table]. If this is really the case, it doesn't seem (to me) that this would be good practice (or perhaps it is?). Wouldn't it be better to let the query generate its own index numbers for the [Stbck-RPV Table], one for each new Geom inserted? And if so, how would I go about enabling that?

Many thanks for any insight that anyone can provide.

Cheers, Jim

tjhb
10,094 post(s)
#20-Sep-22 23:25

Jim,

In the above, it seems that I'm forcing the index ([mfd_id]) from the [RPV Table] to be the index in the [Stbck-RPV Table]. If this is really the case, it doesn't seem (to me) that this would be good practice (or perhaps it is?). Wouldn't it be better to let the query generate its own index numbers for the [Stbck-RPV Table], one for each new Geom inserted? And if so, how would I go about enabling that?

Your instinct and your question(s) are exactly right.

(1) Yes. While it's not terrible to specify mfd_id explicitly, it's usually not great and there are probably circumstances where this could bite you in the bum. (I can't think of a great example just now, but I agree with you.)

(2) If you want to keep track of the source value of mfd_id (as you frequently do), then it's better to cast it to an ordinary field using an alias (viz. mfd_id AS source_id).

(3) So yes. Better to leave the SQL engine to assign unique mfd_id values automatically--given that it can.

(4) How? (a) Use a CREATE TABLE statement to make the target table, including a mfd_id field. Or (b) use a preexisting table with that field, just as you are doing, with a prior DELETE statement (provided of course that it deletes everything--no WHERE filter used). Whatever is more convenient.

(5) Then, in the INSERT INTO statement, just leave out the mfd_id field from the SELECT list. Manifold will supply one.

Yes, it's that simple!

Tim

Jim Salmon16 post(s)
#26-Sep-22 16:21

Hi Tim,

Sorry for the slow response.

However, I did try your suggestion as follows:

-- generate setback buffers

-- initialize - delete all setback buffers from Stbck-RPV drawing

DELETE FROM [Stbck-RPV Table]

;

-- generate 550 m setback from R's and V's

INSERT INTO [Stbck-RPV Table]

SELECT GeomBuffer([RPV].[Geom],550.0,0) FROM [RPV Table] as [RPV]

WHERE ([RPV].[RPVQ]='R') OR ([RPV].[RPVQ]='V')

;

-- generate 275 m setback from P's. Note: No setback from Q's

INSERT INTO [Stbck-RPV Table]

SELECT GeomBuffer([RPV].[Geom],275.0,0) FROM [RPV Table] as [RPV]

WHERE ([RPV].[RPVQ]='P')

;

I simply left the [mfd_id] field out of the SELECT statement(s). When I ran the query listed above, the response was "Tables must contain same number of fields." I can sort of see why this is happening but I can't see how to get around it.

Again, any suggestion would be gratefully accepted.

Thanks and cheers, Jim

tjhb
10,094 post(s)
#27-Sep-22 03:17

Jim,

I simply left the [mfd_id] field out of the SELECT statement(s). When I ran the query listed above, the response was "Tables must contain same number of fields." I can sort of see why this is happening but I can't see how to get around it.

There are two aspects here.

(1) When you use INSERT INTO in Manifold 9, you must specify the vector of target fields to insert into. The target fields follows the name of the table. So

INSERT INTO table (field1, field2, field3...)

This is followed by the underlying SELECT statement. The list of source fields here must exactly match the vector of target fields given above, both in number and in type. So

SELECT a, y, func(z)...

FROM...

Here the type of x must match the type of field1, the type of y must match the type of field2, and the return type of func(z) must match the type of field3. Similarly for expressions.

By the same token, fields omitted after INSERT INTO need not be listed after SELECT--actually they cannot be. If they are then that is a syntax error (giving the message you are seeing).

(2) If you don't include mfd_id in either list, that is perfectly valid. If the target table has a mfd_id field, it will then be populated automatically.

(If you do include mfd_id in both lists, the engine will try to use the supplied values, and will throw an error--"Can't add record" I think-- if uniqueness is violated. Same goes for violations of constraints on other target fields.)

Tim

tjhb
10,094 post(s)
#27-Sep-22 22:23

Sorry for typo. Second example should of course begin

SELECT x, y, func(z)...

Jim Salmon16 post(s)
#27-Sep-22 22:35

Hi Tim,

It worked great. I had tried something like that before but I must have had some sort of formatting error as I just got error messages which I think I interpreted as I couldn't insert just the [Geom] into the target table. Apparently I can.

Thanks for all your help!

Cheers, Jim

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