Subscribe to this thread
Home - General / All posts - Getting info from drawing linked to a query
Mike Pelletier

2,122 post(s)
#25-Sep-19 23:14

When linking a drawing in from a read only database, I can select and alt click to see a feature's info. Good.

When I create a drawing from a query that performs a left join of parcels to assessor information and then I create a drawing from the query, the resulting drawing is not selectable nor can you view the record information. This is because the indexes have changed to allow duplicates or null values. Don't want to be able to writeback in those situations. That's understandable, but it sure would be nice if we were able to select and see record info (like with a linked read only layer), otherwise you get very little out of linking to the query.

My attempted work around is in the attached project using a SELECT ... INTO statement. This creates a new table that you can link a drawing to and then be able to select and see record info. If this is the best method is there a way to make this quickly updated? Perhaps a query that deletes records and then refills with latest info (and automatically refreshes the drawing) would be good. The attached project shows my unsuccessful attempts. Thanks for your help.

Also some odd behavior that I can't repeat but maybe worth reporting. Working on this project I apparently deleted a bunch of components although I don't see how that could have happened. So I exited the project and without closing the program reopened the same project and I was back to having those components lost. Exited the program completely and then opened up the project again and got to my last save. Tried repeating this and reopening project without exiting program took me back to last save.

Attachments:
join test.mxb

adamw


10,447 post(s)
#26-Sep-19 08:43

First, regarding the joins - you have two tables, TaxParcel and Assessor, and you join them on ParcelNumber. The Assessor table has a BTREEDUP index on the join field, the TaxParcel table has no indexes on that field. The $100 question - should ParcelNumber be unique both in TaxParcel and in Assessor? Can you have more than one TaxParcel with the same ParcelNumber? Can you have more than one Assessor with the same ParcelNumber? If the answers to both these questions are no, then you can just add a unique index to each table and this is going to make the relation 1:1 and the result table of the join will be editable.

Eg, if you do this:

--SQL9

ALTER TABLE [TaxParcels Table] (

  ADD INDEX [ParcelNumb_x] BTREE ([ParcelNumb])

);

ALTER TABLE [Assessor] (

  DROP INDEX [Index],

  ADD INDEX [ParcelNumb_x] BTREE ([ParcelNumber])

);

...then this produces a table with a unique index, pickable and selectable and editable, you can link it as a drawing and use with no further tricks:

--SQL9

SELECT P.*, A.*

FROM [TaxParcels] as P LEFT JOIN [Assessor] as A

  ON P.[ParcelNumb] = A.[ParcelNumber];

If there are multiple assessors for the same ParcelNumber, then yes, when you click into a TaxParcel you cannot see all of the matching records in Assessor, because there might be several of them and the Record pane does not traverse 1:N relationships. But maybe you don't want to see all of the matching records, and just want to see how many Assessors the clicked TaxParcel has or the AccountNumber of the first such Assessor. If so, that can be done by rewriting the query and using a different type of join and a group.

On to the queries that create copies of data - the 'Delete records and insert new records' query should probably look like this:

--SQL9

DELETE FROM Temp;

INSERT INTO Temp (

   [mfd_id][ParcelNumb][Geom],

   [mfd_id 2][ParcelNumber][AccountNumber]

)

 SELECT P.*, A.*

 FROM [TaxParcels] as P LEFT JOIN [Assessor] as A

  ON P.[ParcelNumb] = A.[ParcelNumber];

You could also DROP TABLE Temp; SELECT ... INTO Temp ...; but DELETE / INSERT is better because it lets you keep formatting, coordinate systems, etc, which DROP / SELECT INTO would erase.

The list of fields in the SELECT is best expanded from SELECT P.*, A.* into citing specific fields, otherwise if you add some fields to TaxParcels or Assessor, or rename them, the list of fields in the SELECT will stop matching the list of fields in INSERT and the query will fail.

Regarding closing and reopening the MAP file producing different results depending on whether you exit the application in the middle or not - this is a little weird, and the only thing that comes to mind is that there is something else holding the MAP file opened (eg, a failed attempt at linking) and that an attempt to save from within the application actually failed, and the file was kept in memory, and after you exited the application, the stray bit of code holding the MAP file had to yield and you got to see that the save indeed failed. If you can ever reproduce it, please contact tech support, and also keep an eye on the log window - it might contain some clues. In any case, it is good that a failed save did not result in a loss of data beyond what failed to save, and old data was kept protected.

adamw


10,447 post(s)
#26-Sep-19 08:54

In general, on picking objects to view their values - there is an argument that we should allow this without any indexes whatsoever because after all we do allow browsing tables without indexes in the table window, and that it's only editing that should be disabled. We tend to agree with this and maybe we will relax our rules specifically for picking in the future.

Mike Pelletier

2,122 post(s)
#26-Sep-19 16:01

Thanks for stepping through logic on this Adam and suggesting better SQL. In general, the parcel number in TaxParcel is unique but not so in the Assessor table. Could you please show an example of what you mean by using group with joins if you have some time.

I don't know the downsides, but definitely would like to be able to view values and select in read only cases.

Also, I think people will find it frustrating when you cannot Ctrl click or Alt click features and not know why. I've used it enough now to understand why, but in the past I have been confused thinking it is something to do with indexes but instead it was because a feature was already Alt clicked somewhere else on the map. It leaves you with that not so good what the heck is going on feeling :-)

Dimitri


7,413 post(s)
#27-Sep-19 09:23

because a feature was already Alt clicked somewhere else on the map

(Always important to avoid frustration, I agree, but perhaps this is worth a separate discussion given it's a different thing than a drawing based on a query not being selectable, etc., for lack of indexes.)

In general, if you select an object by ctrl-clicking it, you can still pick an object for editing (attributes or geometry) with an alt-click.

If you pick one object for editing by alt-clicking, you can still pick a different object for editing by alt-clicking the different object.

But if you are in the middle of editing an object, you can't do other commands outside of editing, like ctrl-clicking an object to select it. How to remind folks of that if they forget?

If you alt-click an object, the Record pane pops open loaded with that object. That's a pretty good reminder an object has been picked for editing, but I agree that in the process of learning editing that might be overlooked. If somebody tries to Ctrl-click another object anyway, how to handle that?

One approach is to take a ctrl-click as a command to abandon edits and simply select the object. But that can be very messy... suppose the user is in Insert Area mode and has forgotten he is in the middle of a very complicated area (went out to lunch or whatever, before finishing)... should it be just abandoned? Saved? Raise a dialog and then if confirmed, automatically switch from Create Area to Default cursor mode? If a dialog is to be raised, should the system pan and zoom to the object that's been picked for editing, to remind the user of the context?

Another approach is to pop open an error message, like "Please wrap up the editing session first" or other useful reminder.

A third option is to enable Ctrl-clicks on other objects without ending the current editing session. I like that best, since it would play well with a possible option to Snap to Selected Objects Only, where you might want to on the fly select/deselect objects while editing so that where you are clicking can snap to only selected subsets of objects. :-)

My gut feel is that as people get more muscle memory on editing they'll glance at the Record pane and realize something is still being edited, so it's probably not worth doing anything except the third option.

Mike Pelletier

2,122 post(s)
#27-Sep-19 16:28

Like your thinking and conclusion! Snap to selected could be very useful and quick.

adamw


10,447 post(s)
#01-Oct-19 14:54

Could you please show an example of what you mean by using group with joins if you have some time.

Sure.

Take a look at the attached MXB. Open Map. There are two layers: the original TaxParcels drawing at the bottom (gray) and the new TaxParcels with Assessor Data Drawing at the top (yellow). The new drawing is linked from the query named TaxParcels with Assessor Data, the query joins TaxParcels with Assessors and outputs the original fields from TaxParcels plus the AccountNumber of an example assessor for the tax parcel (chosen using First - you can use Min / Max / etc). Since some records in TaxParcels have no corresponding records in Assessors, the number of records shown in the new drawing is lower than the number of records shown in the original drawing. You can Alt-click objects in the new drawing and see their data - this works because the join in the query is 1:1.

Here's how I did it:

Added a BTREE index to ParcelNumb in TaxParcels:

--SQL9

ALTER TABLE [TaxParcels Table] (

  ADD INDEX [ParcelNumb_x] BTREE ([ParcelNumb])

);

Created the TaxParcels with Assessor Data query.

Created the TaxParcels with Assessor Data Drawing drawing by right-clicking the query, selecting New Drawing, etc.

Noted that I forgot to tell the new drawing that it uses the same coordinate system as the old drawing and copied the coordinate system using another command (could use coordinate system favorites / etc):

--SQL9

ALTER QUERY [TaxParcels with Assessor Data] (

  ADD PROPERTY 'FieldCoordSystem.Geom' ComponentCoordSystem([TaxParcels])

);

Created the map showing the new drawing together with the old drawing both as an illustration that there are some missing objects and because if you open the drawing linked from the query on its own, it does not know where to zoom (because this is a query and computing the bounding box of all objects in a query is expensive) - the layer with the old drawing provides that knowledge.

Hope this helps.

Attachments:
join-test-mod.mxb

Mike Pelletier

2,122 post(s)
#02-Oct-19 21:14

Thanks a lot for the examples.

I'm pondering how best the GUI should assist users with viewing data from one to many joins. It can be forced into a 1:1 join with the use of grouping as you did above. Alternatively it can duplicate geometry with a left join.

What users will want is to click on a parcel and be able to toggle through a list of all owners. The left join does that fairly well because the alt click allows scrolling through all the features under the click. Edit a parcel and the changes reiterate back to the original geom through the query. Still I wonder if this is the best approach.

Thinking out loud here, what if there was another tab in the Record pane for a 1:X (many) joins with a selector for the table to join and matching fields.

Mike Pelletier

2,122 post(s)
#02-Oct-19 22:32

Along the same lines as above discussion of 1:x relations, what is a good strategy for data managment of multiple photos that are associated with a point? Seems like storing them in a mfd 9 project and linking them to the point would be good. Click on the point and cycle through the photos of the site.

adamw


10,447 post(s)
#03-Oct-19 08:55

On photos - we have been talking internally about scenarios like "photos associated with a record" for some time, we have a lot of ideas here. In many cases, photos could just be extended tiles, with an entire photo (pixels + metadata) stored as a record *value*. We could then have UI to work with these photos in the Record pane, to show these photos on click in a map window or show them as part of record style during rendering, etc.

On managing 1:X relations - we are planning to add a UI tool for transferring data between components without writing code. We might have some smarts in the Record pane to manage this, too - eg, we could allow you to specify in the Manifold 8-like manner that table X and table Y share a common key, and then when you are looking at a record from table X we could show you corresponding records from table Y and vice versa.

Both things above are just a question of priorities. Working hard to squeeze as much as we can in as little time as possible!

Mike Pelletier

2,122 post(s)
#03-Oct-19 15:48

Sounds excellent and all the hard work is much appreciated. Looking forward to storing all the family photos in my favorite GIS.

Dimitri


7,413 post(s)
#03-Oct-19 09:20

[Crossed with adamw's post... he said it better than me. :-) ]

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