Subscribe to this thread
Home - General / All posts - table joins via GUI
artlembo


3,400 post(s)
#13-Dec-17 03:36

There has been some great discussion on updating tables here. I hope that the contributions have been beneficial to people.

But, as a general purpose GIS tool, I think MF would really benefit from some point-and-click table joins (wow, this must be the end times - I'm advocating for point-and-click wizards over SQL!).

Two things that come to mind:

1. Join tables based on an attribute, then right click columns to "flatten" them as we did with 8. That is super handy, and can be done with one hand, while the other hand is having a drink. The Table -> Relations capability was really useful in this way.

2. Take a tip from ArcGIS with their Select by Location, by joining two tables based on their geometry columns. Then, you can do another flatten. In this case, when performing the Table -> Relation, you can select the geometry column from one table, and a geometry column with another table. Of course, there is some added complexity as you need to determine what the relationship is (i.e. contains, touches, intersects, etc.).

I think this is an excellent way to perform these kinds of tasks for users who don't want to write SQL, and as I'm teaching people how to use MF, it is really obvious that if something simple like the above examples are desired, the Table -> Relations route is much faster than trying to write an update statement.

tjhb
10,094 post(s)
#13-Dec-17 07:06

Devil’s advocate:

People are lazy. Cheat sheets help lazy people. Teach people cheat sheets.

SQL is for deep-learning robots, not humans.

Joins should not exist at all. They are hard even for robots.

Students are generally right. They know their own best interests.

Ironriba
12 post(s)
#13-Dec-17 09:18

I agree here, table relation is a one of the best features in Manifold. i use it all the time. Simple & fast. I hope this option will be applied in Radian Studio.

adamw


10,447 post(s)
#13-Dec-17 13:36

Replying to the whole thread.

We aren't against such things. The culprit is always that abstract tools like joins allow way too much to express the full range of possibilities coherently in the UI. Consequently, we are always talking about making UI tools that do some part of what abstract tools can do, because doing it all takes too many controls and then the dialogs look no less complex than queries.

So, what's most important here is scenarios for circling out the most frequent uses and looking into doing UI tools for those uses specifically.

What specifically are you doing with related fields?

(Here is a dry technical start: We have two tables with matching fields and we set up the relationship, then allow bringing fields from one table to the other. Is it mostly about seeing the values of the brought fields? Or do you want to edit these values as well? Is the relation on one field or on multiple fields, and is it 1-1 or 1-N or M-N? If the relation is 1-N or M-N, what of the N matching values do you want to see for a record, are you looking for means to specify an aggregate? Etc.)

artlembo


3,400 post(s)
#13-Dec-17 13:53

Two scenarios, assuming 1:1, based on a single field (in my career, 90% of the time, these are the joins I create):

1 select one or two columns to flatten, so they are part of the original table now

2. Use a column transform to perform a calculation on a field in the original table based on a field (or fields) in the joined table

Related to #2, think about the SQL required to update a column in a table based on values in another table. That was slightly complex, requiring us to know enough SQL to perform the join, replicate the unique ID, etc

In this case, simply:

Table -> add relation -> calculate the field

This is one example where a couple of right clicks accomplished a great deal

tjhb
10,094 post(s)
#13-Dec-17 21:22

How about both:

(a) Allow NATURAL JOIN syntax in SQL9.

(b) Provide a means to perform a natural join (but no others) via GUI. This would be to create a new third table.

Would that be any good? It sounds like what you are both pointing at.

tjhb
10,094 post(s)
#13-Dec-17 21:38

[Added. Is there any way that a natural join could be piped into a computed field? That would be 99% like relations in 8. Eventually I think we will need computed fields addressing multiple tables, regardless.]

adamw


10,447 post(s)
#14-Dec-17 06:08

Computed fields can do relations right now, but only as long as key fields in related tables are named differently.

I am attaching an example MXB with Categories and Products from Northwind, I renamed all fields in Categories to make their names unique and then carried over the name of a product category to the Products table:

--SQL9

ALTER TABLE [Products] (ADD [CategoryName] NVARCHAR AS (

  SELECT [_CategoryName] FROM [Categories]

  WHERE [_CategoryID] = [CategoryID]

));

If I didn't rename the fields, there would be no way to disambiguate CategoryID in the WHERE. We will provide such a way in the future.

An important caveat: the values in the related fields won't follow changes in the Categories table. They are computed for a new record and recomputed when you change one of the fields *in their own table* that they depend on (so, if you change CategoryID in the Products table, the value of CategoryName for that record will be recomputed). If you change the name of a category in the Categories table, the change will not carry over to the Products table. The easiest way to carry it over is to drop and readd the computed field.

But this is mostly beside the point because the request is for an easy UI tool. Performing a join in a query is already very easy, the desire is to avoid writing a query.

PS: The size of the MXB is 4 KB.

Attachments:
table-relations-9.mxb

lionel

995 post(s)
#17-Dec-17 10:38

is there a DROP COLUMNS in SQL9

-- $manifold$

ALTER TABLE [Products]

 DROP COLUMN [CategoryName];

the size ratio is 518 !!!! when compare mxp and map file with the same content ( table-relations-9.mxb )

It is strange that the category Name definition is viewable in the right side of the query builder when slide the table item "products" in this area !! !!

it's seem column is dynamic : SQL is a link to the real value and not raw value !

Attachments:
radian_delete_column.png
radian_mxp-map-size.png
table_create_column-AND-data.map


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

Dimitri


7,413 post(s)
#18-Dec-17 09:00

the size ratio is 518 !!!! when compare mxp and map file with the same content ( table-relations-9.mxb)

Must read Cutting Edge commentary by adamw on mxb. See also the index for mxb in the user manual for topics that discuss mxb.

adamw


10,447 post(s)
#20-Dec-17 09:01

DROP COLUMNS is just DROP: ALTER TABLE <table> (DROP <field>, DROP <another-field>, ...).

lionel

995 post(s)
#17-Dec-17 11:06

What ll be the right syntax to copy content using SQL ?

SQL9

ALTER TABLE [Products 2] (ADD [CategoryName] NVARCHAR AS (

  SELECT [Categories 2].[CategoryName] FROM [Categories 2]

  WHERE [Categories 2].[CategoryID] = [Products 2].[CategoryID]

)); //Return 'Products 2.CategoryID': Unknown name.

the character "=" in the WHERE is comparison not affectation !!

Attachments:
radian_copy_content_usingSQL.png
table_create_column-AND-data.map


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

adamw


10,447 post(s)
#20-Dec-17 09:05

As I said, you have to make the names of the key fields different between the tables.

Currently, an expression for a computed field refers to the values for its record using just field names. Using [Products 2].[CategoryID] in an expression for a computed field in [Products 2] does NOT refer to the value of the CategoryID field in the current record, it refers to something else.

Rename the field in Categories 2 to, say, _CategoryID and alter the expression to refer to the field in Products 2 as just CategoryID.

lionel

995 post(s)
#17-Dec-17 11:16

the documentation seem to avoid the term COLUMN since we can only delete/drop column !!

http://manifold.net/doc/radian/sql_statements.htm

ALTER TABLE [Products] DROP [CategoryName];

return  Invalid object reference.


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

Dimitri


7,413 post(s)
#18-Dec-17 09:02

the documentation seem to avoid the term COLUMN

"column" and "field" are synonyms with no differentiated meaning whatsoever attached to the one or the other term. See the discussion under Table Basics in the Tables topic.

adamw


10,447 post(s)
#20-Dec-17 09:06

You have to use parens:

--SQL9

ALTER TABLE [Products] (DROP [CategoryName]);

tjhb
10,094 post(s)
#13-Dec-17 21:55

Depends on what Art thinks.

dchall8
1,008 post(s)
#18-Dec-17 21:24

I use the Table > Relations wizard once a week minimum. It involves undoing the link I made the previous week and creating a new link to the updated data from our office database. My base table has only parcel ID numbers, a computed acreage field, and two text fields for grouping parcels when needed. The office db has values, names, addresses, dates, deeds, and various codes used in the appraisal biz. During a certain time of year I might use the wizard 10 times per week as my co-workers need specialized maps for their areas. This involves appending a third table (one field deep) to match my base table.

The way Manifold Future seems to be heading, if such a useful wizard feature were implemented I would envision it with a "View SQL" button so those us who are SQL challenged might learn a thing or two. Of all the features MF has, the ability to view the SQL is really cool.

artlembo


3,400 post(s)
#19-Dec-17 01:32

if such a useful wizard feature were implemented I would envision it with a "View SQL" button so those us who are SQL challenged might learn a thing or two

that is a great idea.

And, like you, I find myself using the Table -> Relations tool once a week. In fact, this week I've been using it, saving the .map file in 8, and then opening it up in 9. The Relations tool is THAT GOOD.

On another note: I can get by without it. But, I think some might consider me a "power user" of Manifold. I don't know who the target audience is, but if it is the traditional GIS (or data) analyst, I think the tool is very necessary. The main reason being that the little trick we play with the mfd_id is a bit cumbersome.

Just as an anecdote: I had to do the multi table update again, so I copied the SQL I had written previously and went to modify it. After looking at it for a few minutes and making a couple of typing mistakes, I said oh, hell, let me just do this in 8. I brought the data into 8, two mouse clicks and I had the tables joined, and one more right-click, and I had the column flattened. I then saved it, and opened it up in 9.

I don't think we want people saying let me just do this in 8. - :-)

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