Subscribe to this thread
Home - General / All posts - Moving to M9
wvayens108 post(s)
#27-May-19 17:27

My learning curve is pretty steep going from M8 to M9...the biggest obstacle I haven't figured out yet is how to do the equivalent of Table/Relations to link a field from another table so I can use that field to color code my map.

If someone could point me in the right direction, what's the best practice to use here?

Thx!

Dimitri


7,413 post(s)
#27-May-19 20:00

Use a JOIN to create a new table. The topic has good examples. From the original drawing's table take all the fields, including the Geom field, and JOIN that with the table from which you will take the field you will use for coloring.

You're basically going to be doing the first, very simple example in the topic. However, you want to add an INTO statement to create a new table. That's covered in the Notes at the end of the topic, where you'd do something like:

SELECT States.State, States.Population, Capitals.Capital

   INTO [States with Capitals]

   FROM States JOIN Capitals

   ON States.State = Capitals.State;

The first time you do it it may seem complicated, but once you get used to using the example as a pattern and do it a few times it becomes obvious that what you are doing is really easy.

Now you have a new table that's a combined table. Create a drawing from that, using the Geom field.

Joins are very quick and easy, and creating drawings from tables is very quick. The latest video on hunting neolithic relics (a re-do of a video that needed updating) shows a couple of examples of creating drawings from tables, done in just a couple of clicks each. It's that easy.

wvayens108 post(s)
#28-May-19 01:12

Thanks for the quick reply...I usually just need to know I'm going in the right direction...will also check out the video suggestion...those vids are very helpful!

Dimitri


7,413 post(s)
#28-May-19 07:24

Turns out it's even simpler than I wrote. You don't have to use an INTO to create a new table, you can create a drawing directly from a query. Sorry for overlooking that.

Two ways to do it:

1. Launch the command window and enter the SQL like I wrote above. When you run that, you get a new table that is a combination of the two previous tables. You can create a drawing from that.

That's conceptually simple: join two tables to make a new table and then make a drawing. But, if the two tables are very big tables you end up creating another very big table. Suppose the two tables change, like if they are tables in a database that is being updated? To get an updated joined table you have to run the query every time you want to update it.

2. Create a new query in the Project pane, double-click it open, and enter the above text without the INTO:

SELECT States.State, States.Population, Capitals.Capital

 FROM States JOIN Capitals

 ON States.State = Capitals.State;

Now, whenever you run the query you get a results table, a dynamic, temporary thing. OK. But, if the query contains a Geom field as part of the results you can also right-click onto the query in the Project pane and create a drawing from that query, just like you can from a table that has a Geom field in it.

What's good about this is when you open the drawing the objects (geometry and attributes) in that drawing get created on the fly by the query. There isn't any third, new, big table like you create with an INTO. Everything the drawing needs is grabbed from the existing tables on the fly by the query.

Using a query like this is dynamic, so if the tables are coming from databases (maybe one of them is a table that has positions of trucks in a delivery network) and are being updated constantly, whenever you open the drawing or refresh it you get the latest data, because the query is grabbing it on the fly from the originating tables.

A possible downside is that if the data is very, very large then queries can take a moment or two to run, so opening a drawing that is based on a query can be slightly slower than opening a drawing that is based on a pre-built table. But 9 is so fast with queries that even if you have huge tables I doubt you'd notice the lag.

Sounds like this would be a fun video... how to do JOINs, making a drawing from the resulting query.

dchall8
1,008 post(s)
#28-May-19 19:41

I'm really trying to understand this because I create and uncreate a relation in M8 at least once a week. Figuring out that ability will be a big step to me running full time in M9. If I got it to work I wouldn't be writing this.

I have two databases. One is called [parcels Table] and contains the map with property ID numbers called PROP_ID. I imported it to M9 from a shape file in order to keep this as purely simple as possible. As an imported .shp it got the index needed. The second database is called Sheet1$ and comes from our office appraisal records. There are hundreds of fields in that db, but I use a query that extracts the most pertinent to what I do for the office. I copy the query results from SQL Server and paste into Excel. Then import the worksheet into M9 to join to the map. This db has a field called prop_id with data matching the parcels in the map. So [parcels Table].PROP_ID = Sheet1$.prop_id. I hope I have that right, because it seems hard to screw that part up.

In an attempt to keep this trivial, here is the query.

SELECT [parcels Table].PROP_ID, Sheet1$.file_as_name

from [parcels Table] join Sheet1$

on [parcels Table].PROP_ID = Sheet1$.prop_id;

To me this looks identical to what you have in your examples and in the fine manual. Yet, I get a Cannot parse query error. Here is what the query builder shows.

I see something that would make me pause, but there is nothing mentioned in the manual. PROP_ID is int64 while prop.id is float64. Shouldn't the two key fields be the same type? And, assuming so, what is the easiest way to do that considering I will do this frequently. PROP_ID will always be typed into the map as an integer. Excel doesn't seem to have an integer type although you can set the decimal places to zero.

Attachments:
Join Query Parse Error.jpg

tjhb
10,094 post(s)
#28-May-19 19:55

As a rule, use square brackets around all table names and field names.

I think that is your only problem but will test.

dchall8
1,008 post(s)
#28-May-19 22:55

Cool so far. Square brackets stopped the parse error and it's running. I was trying to look exactly like the examples and use the square brackets only when the table name was two words.

The query ran and took a long time. I noticed that the results did not include all the parcels from the map, so, reading ahead in the help file, I rewrote the query as a Left Join. I created an index on the second db and tried it again. The query ran in 0.002 seconds (versus 0.042 seconds the first time); however, it takes a long time to show the results from the Query. I didn't time it but I'm guessing 15 minutes. So I tapped back to the Project pane and created a drawing from the query. That process has been running for 45 minutes which locks you out of doing anything else in Manifold. In M8, relating the two databases was an instantaneous process. I could undo last week's relation and redo this week's db in 15 seconds and be back to work.

This was a small test. If this process can't be sped up I'll have to do my db relating in M8 and then bring it over to M9.

tjhb
10,094 post(s)
#29-May-19 00:25

Cool so far. Square brackets stopped the parse error and it's running. I was trying to look exactly like the examples and use the square brackets only when the table name was two words.

That's a forgivable error on your part. The examples should use square brackets too.

The query ran and took a long time. I noticed that the results did not include all the parcels from the map, so, reading ahead in the help file, I rewrote the query as a Left Join. I created an index on the second db and tried it again. The query ran in 0.002 seconds (versus 0.042 seconds the first time); however, it takes a long time to show the results from the Query. I didn't time it but I'm guessing 15 minutes. So I tapped back to the Project pane and created a drawing from the query. That process has been running for 45 minutes which locks you out of doing anything else in Manifold. In M8, relating the two databases was an instantaneous process. I could undo last week's relation and redo this week's db in 15 seconds and be back to work.

You're really flying blind here aren't you. It's time to learn some basic SQL from the ground up.

You're an analogue guy, like me, right? Buy this book and read it. Buy it on paper so you can write while you read. (It also sequesters some carbon.)

Dimitri


7,413 post(s)
#29-May-19 09:55

That's a forgivable error on your part. The examples should use square brackets too.

Agree and disagree. All errors are forgivable (we are all human), but it is still better to learn when it is necessary to use square brackets and when not.

The easiest way is to read the Queries topic: the rule is to enclose names in square brackets [ ] or back quotes ` ` whenever the name may be ambiguous.

That advice is repeated in many other topics, including the JOIN Statements topic:

No brackets- In this topic to reduce visual clutter we do not use square [ ] brackets around simple field and table names. Square brackets are optional if the name is unambiguous.

I realize that using a word like "ambiguous" may be ambiguous, but the purpose is to provide a "head's up" warning there may be a need to use square brackets [ ] , so anybody who is uncertain what might make a name ambiguous can look into that.

One common way names may be ambiguous is when they contain space characters. But they can also be ambiguous because they use reserved words such as select or where, use special characters, and so on. For example, it is not a good idea to name a field "city + state" or "city+state" even though you can work with such field names in SQL by calling them [city + state]. :-)

It also helps to use tools like the query builder to write your queries. Double-clicking on a field to add it to a query automatically drops it in using [ ] square brackets, and it does so without risk of any typographical errors. Typos are a frequent cause of errors in queries.

I respectfully disagree that square brackets should always be used even when not needed. SQL as written in real life often skips using square brackets to keep queries visually as simple as possible. That can be done using simple names for fields and tables, also a good thing if clarity and legibility are desired. Those are good things, helpful both to experts and to beginners.

In fact, one good reason to use aliases is exactly to be able to use simple, clear names that don't need brackets, to keep queries as short and visually simple as possible.

adamw


10,447 post(s)
#29-May-19 07:39

Regarding the performance of the query:

You say you created an index on the second db (I guess meaning the table named 'Sheet1$' in the MAP file), but you did not specify on what field - you have to create an index on the PROP_ID field. If the field values are supposed to be unique and non-NULL, create a BTREE index, otherwise create a weaker index from the btree family. This should help the performance of the join. You can improve the performance further by creating a similar index on the PROP_ID field on the first table.

Overall, if you have any issues with either the syntax or the performance of a query (or a script, or anything in particular), try preparing and posting a model MAP file - then everyone can see what you are seeing and advise more intelligently.

PS: We are hearing you and others on the ease of use of M8 relations. We are working to add something similar to 9.

dchall8
1,008 post(s)
#29-May-19 23:36

I have some more statistics to report. I realized I did not include the geom field in the query so making a drawing out of that wasn't going to work. I redid the index using a btreedupnull type, because the map does have duplicated PROP_ID numbers for special uses, and there are some mistakes in the Sheet1$ data resulting in null values for prop_id. Running the query was 0.007 seconds but rendering the results took 29 minutes. Then I created a new drawing from the query which was instantaneous. Then I opened the drawing which appeared blank. I used the zoom to move to wherever the drawing was. That took 600 seconds to render the drawing. Then I zoomed in to the general area of interest and that took 218 seconds to render. As a point of reference, zooming on the original drawing took more like 0.03 seconds to render. The elements in the new drawing (from the query) cannot be selected, so that's a problem.

I ran the query as a SELECT INTO to make a new table.

SELECT [parcels Table].[mfd_id][parcels Table].[PROP_ID][parcels Table].[Geom],[Sheet1$].[file_as_name]

into JoinedTables

from [parcels Table] left join [Sheet1$]

on [parcels Table].[PROP_ID] = [Sheet1$].[prop_id];

This SELECT INTO query took 1698 seconds (28 minutes) to run on 32,000 parcels. I created a drawing from that table (0.225 seconds). Zooming on the drawing renders in 0.060 seconds, and the elements can be selected and edited, so this drawing runs at full speed. My concern is that this process to make a practical join of the two tables still takes a half hour.

I hope to have a small version of my databases to upload tomorrow. For some reason Excel is choking on a paste of 4000 records.

You're really flying blind here aren't you. It's time to learn some basic SQL from the ground up.

I'm not sure what I said to make it that obvious, but yes. I have tried learning SQL and seem to develop SQL-induced amnesia about a week after each attempt. What is frustrating is when sometimes you need square brackets and sometimes you don't. Another frustration is the renaming of tables seemingly willy-nilly sometime before or after the new name is first used. I know why it's done, but the language to do it seems subtle, and the ability to use the nickname prior to when the nicknamed table has been named makes me scream. But these comments are off this topic. If we could focus on fast joins, that would be where I want to put the effort in this topic.

For example, what would a person working on really large files, like Art's taxi data, do for a join?? Are people happy waiting for hours to make related databases?

Dimitri


7,413 post(s)
#30-May-19 08:46

Are people happy waiting for hours to make related databases?

No, of course not. If you are waiting hours to do a join on a mere 32,000 records you are doing something fundamentally, terribly wrong. Something also is well and truly wrong for comments like this:

Running the query was 0.007 seconds but rendering the results took 29 minutes.

That's just over the top incredible. I can render every road in the US, millions of them, on a weak machine in about five minutes. I'm curious to hear details of what you did, because on the face of it, it seems impossible to render a drawing with 32,000 polygons in 29 minutes. If you have found some way to do that, as a matter of science it is worth investigating how that could ever happen.

Back to the speed of joins: I suggest getting back to basics and providing full info. Statements like "I redid the index using a btreedupnull type" do not tell us which fields in which tables you did a btreedupnull index on. Was it on one table, or both?

If you are using a query, post a screenshot of the Command Window with the query that also has the full schemas for both tables visible in the lower right corner panel. That will show if you do not have an index on the prop_id field in your Sheet1$ table as well as on the prop_id field in your [parcels Table], for example.

Tell us also about the system. Are all the tables and drawings imported into your project, or are some of them linked from slow storage, like a zip drive? Writing out data to a USB drive is slow.

What happens when you use JOIN and not a LEFT JOIN?

What is frustrating is when sometimesyou need square brackets and sometimesyou don't.

It's the same rule always, and it's a simple rule: If the name does not have white space in it, doesn't use weird special characters, and is not a word that SQL itself uses, you don't need square brackets.

MyField doesn't need brackets.

mfd_id doesn't need brackets... The underscore "_" character is not a weird special character

[My . $Field** ; to select a @parcel] does need brackets. Amazing, but true: you can actually create a field that is named "My . $Field** ; to select a @parcel" and, if you put brackets around it, it will work.

If that seems too complicated, just use square brackets all of the time.

Don't overthink it. Use common sense. Somewhere there is a guy who thinks he should be able to give a name to his table or a field like "select" or "where" and the system should be able to use telepathy to save him from himself when he writes

Select mfd_id, select from where where select > 1;

It's true you can use square brackets and use really nutty names if you want:

Select mfd_id, [select] from [where] where [select] > 1;

... but anybody with common sense won't use names that SQL itself uses for statements.

More tips:

It's also a good idea (like Fehily recommends in his books) to pick a style for capitalization and stick to it. SQL doesn't care, but people usually find it easier to read queries if the SQL statements are capitalized. So, instead of "into" write "INTO" and instead of "from" write "FROM" and so on. This is a matter of taste, and opinions vary.

It's also a good idea to use the same upper case or lower case style for the fields in your table. Sure, you might call it PROP_ID in one table and call it prop_id in a different table, and SQL won't care (it's not case sensitive), but humans get confused by different cases when they read queries.

Another frustration is the renaming of tables seemingly willy-nilly sometime before or after the new name is first used.

A query doesn't rename anything on its own. It does what you tell it. Do you mean using an alias? If so, don't call it a "nickname," as that's not a word in Manifold or SQL. Call it an "alias," so people know what you mean. Aliases are usuallly used for fields, not tables, so maybe that's not what you meant.

Aliases are useful but you have to RTFM to learn how to use them properly. I don't see you using aliases, so maybe that's not what you meant. If you find the rules for using them not clear, don't use aliases. They're a convenience, not a necessity.

Venting is OK, but if something is frustrating, give an example so people know what you mean.

tjhb
10,094 post(s)
#30-May-19 09:21

Where is the "upvote" button?

adamw


10,447 post(s)
#30-May-19 10:13

I did a quick test trying to reproduce the times you are seeing and I cannot see the issue.

I started with a new MAP file, then created a model table called 'parcels' with 10,000 records, and a model table called 'sheet1' with 5,000 records:

--SQL9

 

-- create parcels, no index on prop_id

CREATE TABLE [parcels] (

  [mfd_id] INT64[prop_id] INT32[geom] GEOM,

  INDEX [mfd_id_x] BTREE ([mfd_id])

);

 

-- insert 10,000 parcels with duplicate ids and fake geometry

INSERT INTO [parcels] ([prop_id][geom])

SELECT

  ([x].[value] + [y].[value]MOD 1000,

  GeomMakePoint(VectorMakeX2([x].[value][y].[value]))

FROM

  CALL ValueSequence(1, 100, 1) AS [x],

  CALL ValueSequence(1, 100, 1) AS [y];

 

-- create sheet1, no index on prop_id

CREATE TABLE [sheet1] ([prop_id] INT32[file_as_name] NVARCHAR);

 

-- insert 5,000 records with duplicate ids and fake names

INSERT INTO [sheet1] ([prop_id][file_as_name])

SELECT

  [value] MOD 1000,

  'name ' & CAST([value] AS NVARCHAR)

FROM CALL ValueSequence(1, 5000, 1);

Then I tried SELECT INTO with a JOIN like yours:

--SQL9

SELECT

  [parcels].[mfd_id] AS [id]-- rename mfd_id because of duplicates

  [parcels].[prop_id],

  [parcels].[geom],

  [sheet1].[file_as_name]

INTO [test]

FROM [parcels] LEFT JOIN [sheet1]

  ON [parcels].[prop_id] = [sheet1].[prop_id];

...without any indexes to accelerate the join condition, the SELECT INTO took a sizeable time: 113.282 sec.

Then I added an index to accelerate the join to 'sheet1':

--SQL9

ALTER TABLE [sheet1] (ADD INDEX [prop_id_x] BTREEDUPNULL ([prop_id]));

...deleted the result of the previous SELECT INTO and repeated the SELECT INTO, this time the query completed much faster: 0.912 sec.

Then I added an index to the second part of the join:

--SQL9

ALTER TABLE [parcels] (ADD INDEX [prop_id_x] BTREEDUPNULL ([prop_id]));

...deleted the result of the previous SELECT INTO and repeated the SELECT INTO, the second index accelerated things a little more: 0.624 sec.

So, I cannot see any issues. Join without indexes is slow, adding indexes (on correct side - you were right to add an index to sheet1, adding the first index to parcels wouldn't have done much because the join is asymmetric, index in sheet1 matters more) makes it fast.

I guess the above differs from what you are doing in some important detail. It would have been great if you posted example data.

dchall8
1,008 post(s)
#30-May-19 17:34

I did a quick test trying to reproduce the times you are seeing and I cannot see the issue.

That is because you followed the correct process for creating the indices. Thank you for the details. My mistake was in the indexing process. In the schema for Sheet1, I Ctrl-clicked the PROP_ID field and then clicked the Add Identity button. My thinking was that since the PROP_ID field was selected before I clicked the Add Identity, that the new index field would be created on the selected field. That is not how it works, however intuitive it may seem to do it like that. This time I used the Add (new field, index or constraint) button. Then selected PROP_ID in the first Field dialog.

When M9 imported the parcels shape file an index was created. When running the query with that original indexed table along with the brand new index in Sheet1, the query ran in 6.249 seconds. Then I added a second index in the parcels table using PROP_ID as above, and the query ran in 2.437 seconds. That is 40% of the original time, which I consider a huge performance increase.

So if Dimitri is interested in the science of how that works, start by creating the indices incorrectly and do it again.


If I could explain how to do the equivalent of Table/Relations to link a field from another table, I would summarize it like this...

  • Import shape file and data table to be joined.
  • Create new indices on the common key field in both tables
  • Join tables into a new table

  • Create new drawing from new table

    It works to put all that into one query. For my application the query looks like this and ran in 2.54 seconds

    -- $manifold$

    --create new index in parcels table

    ALTER TABLE [parcels Table] (ADD INDEX [prop_id_x] BTREEDUPNULL ([PROP_ID]));

    --create new index in data table

    ALTER TABLE [Sheet1$] (ADD INDEX [prop_id_x] BTREEDUPNULL ([prop_id]));

    --join all fields in both tables

    SELECT [parcels Table].* ,[Sheet1$].*

    into JoinedTables

    from [parcels Table] left join [Sheet1$]

    on [parcels Table].[PROP_ID] = [Sheet1$].[prop_id];

    --create a projection property for the new table

    ALTER TABLE [JoinedTables] (

      ADD PROPERTY 'FieldCoordSystem.Geom' '{ 

    "Base""North American 1983 (mean for CONUS)"

    "CenterLat": 27.83333333333333, 

    "CenterLon": -99, 

    "Eccentricity": 0.08181919104281579, 

    "FalseEasting": 600000, 

    "FalseNorthing": 3999999.9999999986, 

    "FirstStdLat": 28.38333333333333, 

    "LocalScaleX": 0.3048006096012192, 

    "LocalScaleY": 0.3048006096012192, 

    "MajorAxis": 6378137, 

    "Name"

    "State Plane - Texas South Central\\n(NAD 83, feet)"

    "SecondStdLat": 30.28333333333333, 

    "System""Lambert Conformal Conic"

    "Unit""US Survey Foot" }'

    );

    --create a new drawing based on the proper projection

    CREATE DRAWING [JoinedTables Drawing] (

      PROPERTY 'FieldGeom' 'Geom',

      PROPERTY 'Table' '[JoinedTables]'

    );

  • Dimitri


    7,413 post(s)
    #31-May-19 08:29

    I'm glad progress is being made. But...

    So if Dimitri is interested in the science of how that works, start by creating the indices incorrectly and do it again.

    What interested me was how it was possible that a drawing with only 32,000 polygons could take 29 minutes to render. You haven't closed the loop on that.

    My guess is that you experienced that 29 minute rendering time when you create a drawing from a query, and not from a table that you created using a SELECT ... INTO query. I'm also guessing that in one or more ways there were not necessary indexes in place, the coordinate system was incorrect, etc. I'm guessing, because you haven't told us explicitly the context of that 29 minute rendering time.

    It is good you posted a query. Is the result the drawing which had the 29 minute rendering time, or was it a different drawing? Does the result from the query as written still have a 29 minute rendering time?

    If it still takes 29 minutes, there isn't enough info to diagnose. For example, [parcels Table].* doesn't tell us if there is a geometry field with index in [parcels Table], or what type that geometry field may be.

    That's why I asked you to post the Command Window showing the schemas of the table. It eliminates the need to guess about such things.

    Likewise, it's great you assign a projection in the query. But I don't take it on faith the projection you assigned is the right projection. That's why I asked wvayens 1) what coordinate system his original drawing used and 2) what coordinate system did he assign to the drawing. For all I know, your [parcels Table] drawing is in Lat/Lon and you may think that ALTER TABLE does a reprojection from lat/lon into whatever you specify. (It doesn't).

    One last tip: when things do not work as you think they should, debug by breaking the process down into simpler parts. Don't try to do everything in one big query. Instead, break the process into one, simple JOIN query, and then do everything else using point-and-click dialogs. That will help isolate the problem.

    Also, make the query as simple and as explicit as can be. Don't use constructions like [parcels Table].*in JOINs. Explicitly list the fields, so you yourself as well as other people do not accidentally make wrong assumptions about the fields you are using.

    dchall8
    1,008 post(s)
    #31-May-19 21:14

    I'm ecstatic that progress is being made! This is a major step for me to move to M9. Using the Edit Query button to see how the SQL is written, I have expanded the query to create the indexes. I have also added a new field to calculate acreage within the query. Someday I hope to use the query to apply thematic style to the resulting drawing (StyleAreaColorBack), set layer transparency, and create thematic labels.

    Here is the .prj file that came with the original parcels

    PROJCS["NAD_1983_StatePlane_Texas_South_Central_FIPS_4204_Feet",

    GEOGCS["GCS_North_American_1983",

    DATUM["D_North_American_1983",

    SPHEROID["GRS_1980",6378137.0,298.257222101]],

    PRIMEM["Greenwich",0.0],

    UNIT["Degree",0.0174532925199433]],

    PROJECTION["Lambert_Conformal_Conic"],

    PARAMETER["False_Easting",1968500.0],

    PARAMETER["False_Northing",13123333.33333333],

    PARAMETER["Central_Meridian",-99.0],

    PARAMETER["Standard_Parallel_1",28.38333333333333],

    PARAMETER["Standard_Parallel_2",30.28333333333333],

    PARAMETER["Latitude_Of_Origin",27.83333333333333],

    UNIT["Foot_US",0.3048006096012192]]

    Comparing that to the projection elements previously posted, there are some differences. Is there one correct way to match the Manifold projection to the original prj? False Easting and Northing are considerably different values from the prj to the M9 projection; however, when I open the original and new projected drawing in a map and make one layer translucent, the parcels lie identically on top of each other - at least to the exhaustion of my interest in zooming (873,998:1).

    Yes the new drawing created by the query is the same one that took 29 minutes to render. This new one, based on the index on PROP_ID in both tables, render in the hundredths of a second. In the interest of science, I am trying to reproduce my 29 minute rendering results... Here's what I got. In rereading my previous posts, here, you are correct that it was a drawing created from a query rather than the new table. The index on Sheet1 was created using the Add Identity button in the Schema and going with defaults. The log does not track how long it takes to plow through the join, so I did not get a time on that. I'd say roughly a half hour for that. Then I opened the drawing created from the query. Render time is listed as 647.961 seconds, but I have a screenshot taken at almost 15 minutes, so something isn't measuring the same. Then I zoomed in to 1:65,000. Render time for that zoom was 410.63 seconds. Then I right-clicked the bottom tab on the drawing and selected zoom again. That render time took 685 seconds. It is almost comical watching the parcels render at such a slow rate.

    I do try to keep things simple when first trying something. That is why I tried only selecting one field for the original join. I have 42 fields in the Sheet1 db. Are you suggesting I itemize those in the query rather than using SELECT * ? SELECT [Sheet1$].[prop_id],[Sheet1$].[owner_tax_yr],[Sheet1$].[file_as_name],[Sheet1$].[legal_acreage],[Sheet1$].[hood_cd],[Sheet1$].[school],[Sheet1$].[city],[Sheet1$].[county],[Sheet1$].[Last_Appr_Date],[Sheet1$].[legal_desc],[Sheet1$].[tract_or_lot],[Sheet1$].[abs_subdv_cd],[Sheet1$].[land_val],[Sheet1$].[imprv_val],[Sheet1$].[market],[Sheet1$].[assessed_val],[Sheet1$].[block],[Sheet1$].[map_id],[Sheet1$].[geo_id],[Sheet1$].[situs_num],[Sheet1$].[situs_street_prefx],[Sheet1$].[situs_street],[Sheet1$].[situs_street_sufix],[Sheet1$].[situs_city],[Sheet1$].[situs_state],[Sheet1$].[situs_zip],[Sheet1$].[addr_line1],[Sheet1$].[addr_line2],[Sheet1$].[addr_line3],[Sheet1$].[addr_city],[Sheet1$].[addr_state],[Sheet1$].[zip],[Sheet1$].[chg_of_owner_id],[Sheet1$].[deed_book_id],[Sheet1$].[deed_book_page],[Sheet1$].[Deed_Date],[Sheet1$].[deed_num],[Sheet1$].[seq_num],[Sheet1$].[state_cd],[Sheet1$].[ls_table],[Sheet1$].[Group_Codes],[Sheet1$].[entities]

    I would be more afraid of missing something if I did it like that.

    adamw


    10,447 post(s)
    #01-Jun-19 08:22

    False Easting and Northing are considerably different values from the prj to the M9 projection; however, when I open the original and new projected drawing in a map and make one layer translucent, the parcels lie identically on top of each other - at least to the exhaustion of my interest in zooming (873,998:1).

    I take it you are comparing values in the PRJ with values in the created JSON definition of the coordinate system. If so, the difference is that the values for false easting / northing in the PRJ are in feet (see the UNIT clause in the PRJ) and the corresponding values in the JSON are in meters.

    I have 42 fields in the Sheet1 db. Are you suggesting I itemize those in the query rather than using SELECT * ?

    Using * works, but in a SELECT with a join you frequently *want* to leave out some of the fields from the joined tables - eg, if both tables have MFD_ID, you frequently want to leave out both and just synthesize a new unique ID for each record automatically.

    Manifold is much better at letting you use * everywhere, by the way, we do a lot of things here that other databases don't (carry over indexes auto-renaming fields and auto-adjusting index types as needed, etc). Outside of Manifold, seeing queries with huge lists of fields in the SELECT section is not just normal, it's standard - people tend to use * for something that is quick and dirty and when that starts working well and they want to save the query for later repeated use, they expand * into a list of specific fields. This has some pros and cons.

    dchall8
    1,008 post(s)
    #03-Jun-19 17:35

    Thank you. I've gotten a lot from this topic with Tim's, Dimitri's, yours, and the fine manual's help.

    When I import the shape file and spreadsheet, they always import with the same names (parcels and Sheet1$). That makes it possible to write a query based on those names for future use. I realize there could be some dangers with writing a multitask query, but with all this help and using the Edit Query button, I now have a query which creates indices in the two imported tables, joins the tables, creates the projection property in the table, creates a new drawing from the joined table, creates a computed field for acreage in the joined table, and creates a field in the joined table to hold text for labels. The query runs in 5.063 seconds.

    During this process I looked back at some M9 files from several months ago to read my notes from those experiences. Manifold has evolved more than I realized. I will have to revisit this query in the next few months to see what needs to change.

    Dimitri


    7,413 post(s)
    #29-May-19 14:35

    This may help: See the new video on the videos page, Joins are Easy. It shows how to add a population field to a drawing, so you can use that field for thematic formatting. It also shows how to create indexes for the fields used in the ON condition.

    wvayens108 post(s)
    #30-May-19 13:44

    Thanks for the push in the right direction...I'm doing well with JOINS, getting the resulting drawing that I needed. Decided to try your second approach, as the dynamic approach fits my needs better. However, when I create a drawing from the query, I end up with a blank output and haven't been able to figure out what I'm doing. If I run the query by itself, I get the results. If I add an INTO statement, I can create a drawing from the result that looks fine. But a drawing from the query, just blank. Here's a screen shot of my SQL and the schema of the two tables (the first table is VERY large, so I scrolled down so you could see the indices I'm using).

    Attachments:
    Screen Shot 2019-05-30 at 8.39.57 AM.jpg

    dchall8
    1,008 post(s)
    #30-May-19 14:21

    When I made a drawing from the query, it appeared to be blank, too. I right-clicked the tab and selected Zoom. It rendered but it took 29 minutes. The .map file is too big to upload here, so I want to make another with 4,000 records and see if I get the same scale of time.

    Dimitri


    7,413 post(s)
    #30-May-19 14:58

    Responding to wvayens:

    1. What coordinate system does the geom in the SUM table use? [By the way, lose about a zillion style points for naming a table the same name as a SQL function... sigh!]

    2. When you created the drawing from the query, what coordinate system did you specify for the drawing?

    3. When you pop open the drawing from the query, is there any message icon in the drawing's tab?

    If there is no message icon, the usual problem is that the coordinate system was incorrectly specified.

    If there is a message icon, read the message. If it is a message like the message in the View - Messages topic, it's telling you there's no spatial index and offering to build a temporary index for you.

    The way to avoid that is to use the technique shown in the Example: Create a Drawing Dynamically from a Geocoded Table topic: wrap your SELECT within a use of TableCacheIndexGeoms:

    TABLE CALL TableCacheIndexGeoms((

    <put the SELECT query here... without a ; at the end>

    ), TRUE);

    The TableCacheIndexGeoms automatically provides a spatial index on the geom field, so the drawing just pops open right away.

    wvayens108 post(s)
    #30-May-19 20:04

    Thanks, it was a mix-up in the coordinate systems that was causing the problem.

    Yes, mea culpa, I've been dealing with the SUM county abbreviation (Sumter County in Florida) for a long time...the good news is that I always use brackets in my queries because of that! But as I'm transitioning from M8 to M9 I will bite the bullet (finally) do my best to get rid of that style!

    The bottom line is that I'm easily duplicating the old M8 Table/Relations on the fly and am one step closer to feeling comfortable doing things in M9.

    As always, you've pointed me in the right direction and helped me learn a little more! Thanks.

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