Subscribe to this thread
Home - General / All posts - Multiple Table Relations for Drawings M8
NithField12 post(s)
#25-Sep-19 13:57

Hi Group,

I have a Drawing that I want to add multiple attributes from a table too..... easy enough...however. The drawing has the relationship attribute POLYID. As does the table i wish to relate to the drawing. But the table has more "lines" with duplicate POLYID numbers. With in the Table there are attributes that have several lines associated to the POLYID of the drawing.

Is there a way to bring the table relationship into the drawing and still segment the multiple lines of table data for each attribute?

Some reference pics below for clarification.

Attachments:
Polygon Data PROBLEM.pdf

atrushwo51 post(s)
#25-Sep-19 15:20

Hi Nith,

This is the very question I had a couple of years ago when I transitioned from the GUI to SQL in M8. The issue you're having is different types of relationships. There are three different kinds.

  1. One-to-One: This is the easiest, when there is only a single matching record between tables. This is the type where table relationships through the GUI work the best.
  2. One-to-Many / Many-to-One: This occurs when a single object in one table matches multiple objects in another table. This is your situation. If you want to move data from your CMP table to your unique table, it is Many-to-One. If you want to move data from your unique table to your CMP table, it is One-to-Many.
  3. Many-to-Many: These are far less common so I won't go into them in detail. They require a junction table though.

Given that you want to move data from your CMP table to your unique table, you're dealing with a Many-to-One relationship. These are most commonly used in conjunction with an aggregation statement (max, min, avg, etc.). Aggregation statements tell the program which value you want to transfer from your CMP table to your unique table. A quick example for the maximum value is below. Just remember to change your destination and data columns to match what you want to move where.

UPDATE

(

SELECT

[1].[DESTINATION] AS [A],

[B]

(

SELECT

[1].[ID] AS [SOURCE],

MAX([2].[DATA]AS [B]

FROM [DSS_V3_AB] AS [1]

INNER JOIN [DSS_V3_AB_CMP] AS [2] ON [1].[POLY_ID] = [2].[POLY_ID]

GROUP BY [1].[ID]

)

INNER JOIN [DSS_V3_AB] AS [1] ON [SOURCE] = [1].[ID]

)

SET [A] = [B]

Now, if you would like to move all of your data from the CMP table to your unique table, that is a different situation. Right now, your data is normalized. Simply put, that means that your tables are configured optimally with the least redundant data. Moving all of your data to your unique table would denormalize your table and create redundant data. Denormalized data is more difficult to work with and often causes problems. For instance, record:ABD192000002 has 4 duplicates and would require 4 columns to move 4 pieces of data (data1, data2, data3, data4). However, a different record may have 2 duplicates, or 100 duplicates. You would end up with a very difficult dataset to work with.

I'd highly recommend reading Chapter 2 of Chris Fehily's "Visual Quickstart Guide - SQL Third Edition". It explains everything I've been talking about in more (and better) detail. I prefer a hard copy, and bought mine on Amazon, but I believe you can find a PDF online as well.

NithField12 post(s)
#25-Sep-19 15:36

Thanks atrushwo

The challenge is I do want to move all the data from the CMP table to the base drawing. My only way around this is to duplicate the attribute names as many times as there is CMP levels. So for ABD192000002 it would have 4 sections of attributes a CMP1, CMP2,CMP3 and CMP4 then all other attributes will have a numerical value added at the end of the name like PERCENT2 & SLOPE_P2

This keeps the attributes unique to the polygon spatially and still allows for algorithms to utilize the data.

Everything would be in one line in the table this way.

Thats the only way i have figured out how to make this clean and obvious for queries and such,

I also believe that this will be quicker.... but not sure the best way to go about it.

Ideas?

atrushwo51 post(s)
#25-Sep-19 15:37

Can you post the file? It would be easier if I could just take a look.

NithField12 post(s)
#25-Sep-19 21:27

Thanks,

I just trimmed it down to few hundred polygons instead of all of it. Its quite large, Thanks

Attachments:
Drawing.dbf
Drawing.shp
Drawing.shx
Table.csv

atrushwo51 post(s)
#26-Sep-19 15:24

I certainly don't feel good about this. But this visual basic script will denormalize your data as you've described. I highly recommend you learn about the relational model. Querying this mess is going to be ugly. I'd prefer to work with your original dataset any day. PS... Always nice to help out a fellow Albertan!

Sub Main

 Set Components = Document.ComponentSet

 Set query = document.NewQuery("Temp")

 'FINDS MAX CMP VALUE

 query.text = "SELECT MAX([CMP]) as [COUNT] INTO [COUNT] FROM [TABLE]"

 query.run

 set component = components.Item("Count")

 set records = component.recordset

 set record = records.item(0)

 Count = record.Data("Count")

 QUERY.TEXT = "DROP TABLE [COUNT]"

 QUERY.RUN

 'CREATES PIVOT SELECT STATEMENT

 set component = components.Item("Table"

 set columns = component.columnset

 QUERY.TEXT = "SELECT [A].[POLY_ID],[A].[GEOM]"

 For i = 1 to Columns.Count - 1

 set column = columns.Item(i)

 A = COLUMN.NAME

 For j = 0 to Count - 1

 query.Text = QUERY.TEXT & ",[" & I & "-"&  J & "].["&COLUMN.NAME & J+1&"]"

 next

 next

 QUERY.TEXT = QUERY.TEXT & " INTO [RESULTS] FROM(SELECT[1].[POLY_ID],MAX([2].[GEOM (I)]) AS [GEOM]FROM [TABLE] AS [1]INNER JOIN [DRAWING] AS [2] ON [1].[POLY_ID] = [2].[POLY_ID]GROUP BY [1].[POLY_ID]) AS [A]"

 'CREATES PIVOT SUBQUERIES

 For i = 1 to Columns.Count - 1

 set column = columns.Item(i)

 A = COLUMN.NAME

 For j = 0 to Count - 1

 query.Text = QUERY.TEXT & "LEFT JOIN (SELECT [POLY_ID],MAX(["& COLUMN.NAME &"]) AS ["&COLUMN.NAME& J+1&"] FROM [TABLE] WHERE [CMP] = " & J+1 & " GROUP BY [POLY_ID]) AS [" & I & "-"&  J & "] ON [A].[POLY_ID] = [" & I & "-"& J & "].[POLY_ID]" 

 next

 next

 'RUNS QUERY AND DELETES

 QUERY.RUN

 components.Remove(components.ItemByID(query.ID))

End Sub

NithField12 post(s)
#28-Sep-19 19:40

atrushwo.....yes denormalized, I got that book you mentioned and am cracking out many-many relation ship models... its a good book for relationships, clear and direct easy to follow and work with... thanks. Im not sure that will be usable in my situation... but Im getting there thanks to your help.

changing directions here with a question, but i thought you might be interested in another problem Im looking to try, In the two attached images i have a boundary of the province of Alberta and some centroids of an old soil map of Alberta.

Looking to accomplish the following in M9

Using the points from the centriods, build a map and usable drawing that has inverse weighted distance raster that is adjustable to smooth and blend different attributes (Like soil organic matter or... soil clay content)

Then restrict it to the Alberta Soil Map boundary polygon.

Any experience in this? never done Krig or IWD in M9

Attachments:
2019-09-28_1420.png
2019-09-28_1421.png

adamw


8,696 post(s)
#02-Oct-19 09:40

Given a set of points with a numeric attribute, you can create a raster with pixels interpolating that numeric attribute. See the Interpolate transforms, I believe the online manual contains examples on how to use them and the YT channel contains videos demonstrating that.

adamw


8,696 post(s)
#25-Sep-19 16:08

Why do you want to move all data from the CMP table to the base drawing?

Let's say we have a drawing object. It has a specific POLYID, eg, ABD19200001. There are zero or more records with the same POLYID in the CMP table, they correspond to the object. If you want to find how many of these records there are, you can, that's a query. If you want to find what's the min / max / average / whatever value of, say, SLOPE_P from the CMP table for the object is, you can, that's another query. Why do you want to bring the values from multiple records in the CMP table into the drawing? I am not saying there's no reason to do it, maybe you have to feed the resulting table to a reporting tool you don't control and it needs data structured that way, but knowing why you want to do it would help suggest how to do it.

A simple way to bring data from the CMP table into the drawing is to create a new drawing with as many objects as there are records in the CMP, taking geometry corresponding to the POLYID. This will create duplicate geometry, yes. Whether or not that's fine or what would be better depends on what you want to do with the resulting table.

NithField12 post(s)
#25-Sep-19 21:32

Thanks AdamW

Yes I agree, if it was staying all in Manifold...query away. But this is going into some unknown application and it has been requested that all of the spatial polygons have the data attached to them. So they are looking for the least amount of polygons with no duplicates.

Its for spatial algorithms for a soil/weather model. The data set is a soil map of a province. They want to be able to plug in all of the soil data attributes and use them for the process.

dchall8
639 post(s)
#25-Sep-19 23:13

Maybe you're making this too complicated.

I work with a soils layer in M9 to calculate animal carrying capacity. The master soil layer doesn't change. I make a copy of that layer and run a transform with the ownership parcels to chop the soil layer into parcel sized bits. Each bit has the property ID number of the ownership parcel, so multiple bits with the same PID is the norm. There are 8 attributes for each soil type. Once I have the chopped parcels I select all the land bits owned by one owner, multiply the acreage by the productivity to get a number and calculate carrying capacity from that. The heavy lifting is done in queries. The result is a table of parcels, a map of the parcels, and the queries indicating the carrying capacity for each PID and total for the owner. Here's an example.

Attachments:
2019 Alexander Robert 468.42 Acres - Carrying Capacity 32.48 AU.jpg

adamw


8,696 post(s)
#02-Oct-19 09:32

So they are looking for the least amount of polygons with no duplicates.

If polygons should not multiply, and you just want to take each polygon and assign it the data from *any* record out of potentially multiple records corresponding to it in the related table, you can do that using just the UI, no need for queries.

Using the files from a different post of yours up the thread:

Start Manifold 8. Create a new MAP file. Import SHP, select all fields (creates a drawing + table). Import CSV, select all fields (creates a table).

Open the drawing's table (Drawing Table). Invoke Table - Relations. Click New Relation toolbar button. Set 'Match key fields in' to the table imported from the CSV (Table). In the list of fields in the original table on the left, select POLY_ID. In the list of fields in the matched table on the right, select POLY_ID. Click OK (creates a relation between the two tables). In the list of fields to bring from the matched table to the original table, check CMP, PERCENT, SLOPE_P, etc (I checked everything except POLY_ID because there's no point bringing POLY_ID, it's already in the original table). Click OK. This adds fields from the related table to the drawing's table. When a record in the drawing's table has multiple matching records in the related table, the system picks one of those records - if you want to pick a record using a specific criteria, eg, use the record with the highest PERCENT or whatever, you can do this, too, using a query.

You can export the modified drawing back to SHP and the export will write the values from the related table to the DBF. You can then use the produced SHP in further analysis.

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