Esri has a cool tool called SplitByAttributes where it creates a new feature class for every unique value. I've created this in Manifold 8 using VBScript, but wondered if Manifold 9 has a way of doing this in SQL entirely.

Basically, for each unique attribute in a drawing, I want to create a drawing that reflects that attribute. For example, if we have parcels with the landuse field of "residential", "industrial", "government", I would like three drawings created for each of the different land use attributes.

made some progress on this. I can write a DISTINCT query to get the distinct records:

SELECT Distinct([landuse]) AS lu

FROM [parcels]

ORDER BY lu);

Now, I can wrap another query around that to issue the SQL statements:

SELECT 'SELECT * INTO luclass_' & CAST(lu AS nvarchar)  &  ' FROM [parcels] WHERE [landuse] = ' & CAST(lu AS nvarchar)

INTO splitybyTable

FROM 

(

SELECT Distinct([landuse]) AS lu

FROM [parcels]

ORDER BY lu);

this gives me a table with N records that have the appropriate SELECT statement in it (i.e. SELECT * INTO luclass_residential FROM parcels where [landuse] = 1). In this case, I'm assuming the landuse codes are numeric.

Now, I want to run a query for each record in the new table called [splitbyTable]. I don't know if the EXECUTE command can make that happen, or if I have to write some VBScript to cycle through each record in the table, and then do a db.Run. Any thoughts? Once this is done, we have a very nice re-creation of the Esri SplitByAttributes command.

Morning Art, if I want to do something iterative completely in SQL, I use the following method to construct an individual query for each iteration and then use an EXECUTE statement to run the stack.

https://georeference.org/forum/t163176.4#163179

The example here is for a specific task, but I'm sure the method could be adapted (or hopefully someone might have something more elegant).

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

Yes, SplitByField plugin by Sloots.

https://georeference.org/forum/t156583.12#157395

Manifold 9 you can definitely reproduce SplitByAttributes-style behavior using pure SQL, since 9’s query engine makes it easy to iterate through unique attribute values and create drawings from filtered subsets.

Here’s the general approach:

1. Get the unique values

   SELECT DISTINCT [Landuse] FROM [Parcels]; 

   This gives you the list of all categories you want to split on.

2. Loop through values and create new tables/drawings

   In Manifold 9 SQL you can dynamically generate components with

   CREATE TABLE

   /

   CREATE DRAWING

   statements. For each distinct

   Landuse

   value, you can:

   CREATE TABLE [Parcels_residential] AS SELECT * FROM [Parcels] WHERE [Landuse] = 'residential'; CREATE DRAWING [Parcels_residential Drawing] TABLE [Parcels_residential]; 

   Repeat for “industrial,” “government,” etc.

3. Automate with a query

   Instead of hand-coding each land use, you can write a script/query that loops through the distinct values, builds component names, and issues the

   CREATE TABLE

   +

   CREATE DRAWING

   statements automatically. In Manifold 9, this can be done with a SQL script that uses

   EXECUTE

   to run dynamically constructed SQL for each unique value.

✅ Bottom line: Yes, Manifold 9 can do a full SplitByAttributes in SQL. The workflow is:

• Select distinct attributes.

• For each, generate a new table filtered by that attribute.

• Create a new drawing linked to each table.

thanks. That will require the user to create multiple steps, of course, and do things manually. Last night I was able to do this with a VBScript.

Here is a code fragment:

Set uniquelus = db.Run("SELECT distinct(landuse) AS lu FROM parcels")

 Set sequence = uniquelus.SearchAll(Array("lu"))

 Do While sequence.Fetch()

 thelu=  sequence.GetValues()(0).Data

 sqlquery = "SELECT * INTO [depth_" + cstr(thelu) + "] FROM parcels " &_

    " WHERE landuse = " + cstr(thelu)

 db.Run(sqlquery)

 sqlquery = "CREATE DRAWING [depth_" + cstr(thelu) + " Drawing] "

.

.

.

 Loop

In case anyone was interested, I've genericized the script into a VBScript function. You simply pass the script 3 items: the drawing you want to split, the column that has the values you want to split into, and another drawing with the coordinate system you are interested in.

When the script is done, you'll have N drawings that are represented by the attribute value that you wanted to split by. I'm assuming the geometry field is called 'g', but we could also add the geometry field as a variable to pass into the function.

This is pretty easy to script, but it would be nice to have it as a Transform. However, if Manifold isn't going to add lots and lots of Transforms, maybe we could create some kind of code challenge library where people write their code to do certain functions, and others can pull from it.

As another thought, it would be cool if Manifold could create a Transform tool maker that would allow users to create their own transforms with VBScript. Sort of like how Esri does it with Script Tools. The variables could be directives to create a Transform interface. So, in the example of my script, we could have a transform with 3 inputs: the drawing we want to transform, the column we want to transform, and the coordinate system we want to use.

' VBScript

Set app = Manifold.Application

Set db = app.GetDatabaseRoot()

Sub Main

SplitByAttributes "allpoints", "start_depth", "allpoints Drawing"  

End Sub

Sub SplitByAttributes (drawing_name, column_name, coordDrawing)

    Set db = app.GetDatabaseRoot()

 thesql = "SELECT distinct([" + column_name + "]) AS uniquename FROM [" + drawing_name + "]"

 Set uniquefeatures = db.Run(thesql)

 Set sequence = uniquefeatures.SearchAll(Array("uniquename"))

 Do While sequence.Fetch()

 theuniqueval =  sequence.GetValues()(0).Data

 sqlquery = "SELECT * INTO [split_" + cstr(theuniqueval) + "] FROM " + drawing_name + " " &_

    " WHERE " + column_name + " = " + cstr(theuniqueval)

 app.log sqlquery

 db.Run(sqlquery)

 sqlquery = "CREATE DRAWING [split_" + cstr(theuniqueval) + " Drawing] " &_

 " ( PROPERTY 'FieldGeom' 'g',   PROPERTY 'Table' '[split_" + cstr(theuniqueval) + "]' );"

 db.Run(sqlquery)

 sqlquery = "ALTER TABLE [split_" + cstr(theuniqueval) + "] (ADD PROPERTY 'FieldCoordSystem.g' ComponentCoordSystem([" + coordDrawing + "]));"

app.log sqlquery

 db.Run(sqlquery)

 Loop

End Sub

Pretty cool Art. That VBScript is to run it in M8, right?

No, it's for M9.

I wrote the M8 version here.

For wasifali758595...

Interesting... looks like ChatGPT wrote that. Did it? It's getting a lot better with Release 9 SQL. :-)

yes, it is getting better. I especially like using it for VBScript - ChatGPT knows VBScript more than I do!

But, for Manifold things, I'm still better - LOL! I just had someone ask me to help them take a ChatGPT solution in Manifold 9 that would take 15 days to run, and I got it down to 81 seconds. That said, it won't be too much longer before ChatGPT passes me up.

It sort of reminds me of the children's story I read as a kid, Mike Mulligan and the Steam Shovel. It won't be long before I'm put out to pasture.