Subscribe to this thread
Home - Cutting Edge / All posts - Manifold System 9.0.175.4
adamw


10,447 post(s)
#21-Oct-21 16:46

9.0.175.4

manifold-9.0.175.4-x64.zip

SHA256: 1d8a8cbe4a15b03a2ae81cdf990288a21637d30f5edd303f58d8c6f9ec15e1e8

manifold-viewer-9.0.175.4-x64.zip

SHA256: 1746b9d0134183f6440012d6b37b5dfe85d59c515cc5f3564323639e12141c27

sql4arc-9.0.175.4-x64.zip

SHA256: 04a46267a625a8d07bfd5b96f803cf704156f5a37cc84bfc682ee77c2ebdb39a

We are extending formatting options for field values. This build adds query functions that format values of various types. Next builds will allow applying this formatting to fields in tables and queries, and automatically carry it over to labels. You can use this build to format values in labels right away by using expressions in the label text pattern.

adamw


10,447 post(s)
#21-Oct-21 16:51

Changes

The New Labels dialog is reworked to include a field combo for an existing drawing with a special '(pattern)' choice. Selecting a field, will create labels with the text taken from that field (this is the simplest scenario which we want to keep as simple as possible). Selecting '(pattern)' allows editing the text pattern directly. Switching a drawing tries to select a field which makes the most sense to display in labels, the priority order is: a text field > a non-text field that does not have a unique index built on it (to deprioritize fields like MFD_ID or SHAPEID) > a non-text field that has a unique index built on it.

The Components tab of the Info pane no longer shows the 'Text' field for labels. (Labels are now using text patterns.)'

The Info pane automatically determines fields referenced in the text pattern for a labels component, and shows a distinctive icon near each referenced field in the Values tab. Starting to add a new label or starting to edit an existing label also automatically puts the cursor onto the value of the first such field.

New query function: StringFormatDateTime. The function takes a datetime value to format, a format pattern, a language, and returns a formatted string. The language can be an empty string (neutral) or a language code like 'en-US' or 'de-DE'. The format pattern can be either a single letter that defines the whole format, or a combination of the format parts.

(Format patterns for all new functions are roughly compatible with .NET even though the functions themselves are implemented using native Windows calls. We are not using .NET directly because there are cases where this would waste too much resources. We are not currently using ICU, because it is only available on Windows 10 and we need to have reasonable fallback behavior for non-Windows 10 systems anyway. In the future we will likely allow formatting calls to use ICU, if it is available.)

Single-letter formats are (example value is '25 March 2020 13:45:00', example language is 'en-US'):

  • d = short date (3/25/2020)
  • D = long date (Wednesday, March 25, 2020)
  • f = long date + short time (Wednesday, March 25, 2020 1:45 PM)
  • F = long date + long time (Wednesday, March 25, 2020 1:45:00 PM)
  • g = short date + short time (3/25/2020 1:45 PM)
  • G = short date + long time (3/25/2020 1:45:00 PM)
  • m = short month and day (25 Mar)
  • M = long month and day (25 March)
  • r/R = RFC1123, the language does not matter and is always neutral (Wed, 25 Mar 2020 13:45:00 GMT)
  • s = sortable, the language does not matter (2020-03-25T13:45:00)
  • t = short time (1:45 PM)
  • T = long time (1:45:00 PM)
  • u = universal sortable, the language does not matter (2020-03-25 13:45:00Z)
  • y/Y = year and month (March 2020)

An empty format string is interpreted as 'G' = short date + long time.

If the format string differs from any of the single-letter formats and is not empty, it is interpreted as a combination of the following format parts:

  • d/dd = day, two-letter variants of this and other parts add a leading zero for one-digit values (25)
  • ddd = short week day (Wed)
  • dddd = long week day (Wednesday)
  • g/gg = era (A.D.)
  • h/hh = hour on a 12-hour scale (1)
  • H/HH = hour on a 24-hour scale (13)
  • m/mm = minute (45)
  • M/MM = month as a number (3)
  • MMM = short month (Mar)
  • MMMM = long month (March)
  • s/ss = second (0)
  • t/tt = time marker (PM)
  • y/yy = short year (20)
  • yyy/yyyy/yyyyy = long year, accepts up to five letters because some languages allow up to five (2020)

Any character or any character sequence different from those above (eg, 'x' or 'hhh') is copied without any changes.

Example pattern: d-MMM-yy h:mm (25-Mar-20 1:45)

The default conversion of a datetime value to a string now uses leading zeros for month, day and hour to coincide with the output of the empty format for the neutral language.

New query function: StringFormatDurationDays. The function takes a number of days to format, a format pattern, a language, and returns a formatted string. The format pattern is a combination of the format parts:

  • d = days
  • f/ff/fff/ffff/... (up to 9x) = fractions of a second
  • h/hh/H/HH = hours
  • m/mm = minutes
  • s/ss = seconds

The format parts are required to follow from bigger to smaller.

An empty format string is interpreted as 's' = seconds.

Example: ? StringFormatDurationDays(1.133, '', '') -- 97891 (seconds)

Example: ? StringFormatDurationDays(1.133, 'd:hh', '') -- 1:03

Example: ? StringFormatDurationDays(1.133, 'h:mm:ss', '') -- 27:11:31

New query function: StringFormatDurationSeconds. Same as StringFormatDurationDays, but the value to format is in seconds.

New query function: StringFormatNumber. The function takes a numeric value to format, a format pattern, a language, and returns a formatted string. The format pattern consists of a single-letter format and an optional integer number specifying the desired number of digits or decimal digits, depending on the format.

Single-letter formats are (example value is 123456.7890123, example language is 'en-US'):

  • c/C = currency, with the optional number of decimals ($123,456.79)
  • d/D = decimal integer, with the optional total number of digits and optional leading zero, the language does not matter (123457, or, for 'd08': 00123457)
  • e/E = exponential, with the optional number of decimals, the default number of decimals is 6 regardless of the language (1.234568e+05)
  • f/F = fractional, with the optional number of decimals (123456.79)
  • n/N = fractional + digit groups, with the optional number of decimals (123,456.79)
  • x/X = hexadecimal integer, with the optional total number of digits and optional leading zero, the language does not matter (1e241)

If the format string is empty or invalid, the number is formatted as the shortest form that preserves all significant digits, the language does not matter (123456.7890123). The case of 'e' / 'E' or 'x' / 'X' specifies the case of the exponent character or the case of the hexadecimal letters.

New query function: StringFormatVector. The function takes a vector value to format, a format pattern, a language, and returns a formatted string. The format pattern is the same as for StringFormatNumber. The formatted string consists of strings for the individual vector values separated using a list separator for the specified language, and enclosed in square brackets (example: [ 2.34, 5.67 ]).

New query function: StringFormatUuid. The function takes an UUID value to format, a format pattern, and returns a formatted string. The format can be either 'x' or 'X', the case controls the case of the hexadecimal letters. The default is 'x'.

New query function: StringFormatBoolean. The function takes a boolean value to format, a format pattern, and returns a formatted string. The format can be either 'b' / 'B' (output TRUE as '1' and FALSE as '0') or 't' / 'T' ('true' / 'false' with the case controlled by the letter). The default is 't'.

For all format functions, using an empty format string and an empty language string produces the same string as the default conversion to a string: StringFormatXxx(..., '', '') produces the same result as CAST (... AS NVARCHAR).

New query function: DateTimeCurrent. The function returns the current date.

New query functions: DateTimeCurrentRef and UuidMakeNewRef. Both functions take a single argument of an arbitrary type. DateTimeCurrentRef then returns the current date. UuidMakeNewRef generates and returns a new UUID. Neither function uses the value of the argument. The purpose of having an argument is to tell the query engine that the it might not cache the result returned by the function between calls where the argument value might change. This is useful in multiple ways.

For example, this allows forcing a new UUID value for each record in a SELECT:

With:

--SQL9

SELECT [name], UuidMakeNew() AS [uuid] FROM [cities table];

...all records are going to have the same UUID. Because the query engine sees that the call to UuidMakeNew() is exactly the same for each record, so it is only going to call it once, cache the result, then copy the result into each record.

With:

--SQL9

SELECT [name], UuidMakeNewRef([name]AS [uuid] FROM [cities table];

...each record is going to have a new UUID generated. Because the calls to UuidMakeNewRef(...) are different between records, the query engine sees that (sighs) and dutifully calls the function anew for each record.

For another example, this allows creating a computed field with the current date which would update itself after you change some of the fields.

This query:

--SQL9

ALTER TABLE [cities table] (

  ADD [last_edited] DATETIME AS [[

    DateTimeCurrentRef([name] & [state] & CAST([population] AS NVARCHAR))

  ]]

);

...takes a table named 'cities table' and adds a computed datetime field named 'last_edited'. Adding a field populates it with the current date. If you then pick some record and change the value of one of the fields mentioned in the expression: 'name', 'state' or 'population', the computed field in that record will be recomputed and set to the now-current date.

End of list.

joebocop
520 post(s)
#21-Oct-21 18:43

Good stuff, you've effectively replaced the function I paste into every new project! Good riddance, dtf.

FUNCTION dtf(@val NVARCHAR) NVARCHAR AS

  CAST(DateTimeYear(CAST(@val AS DATETIME)) AS NVARCHAR) & '-' &

  CASE 

    WHEN StringLength(CAST(DateTimeMonth(CAST(@val AS DATETIME)) AS NVARCHAR)) < 2 

      THEN '0' & CAST(DateTimeMonth(CAST(@val AS DATETIME)) AS NVARCHAR)

    ELSE

      CAST(DateTimeMonth(CAST(@val AS DATETIME)) AS NVARCHAR)

  END & '-' &

  CASE 

    WHEN StringLength(CAST(DateTimeDay(CAST(@val AS DATETIME)) AS NVARCHAR)) < 2

      THEN '0' & CAST(DateTimeDay(CAST(@val AS DATETIME)) AS NVARCHAR)

    ELSE

      CAST(DateTimeDay(CAST(@val AS DATETIME)) AS NVARCHAR)

  END

END;

adamw


10,447 post(s)
#22-Oct-21 15:54

StringFormatDateTime should also be faster. :-)

(But the real win, of course, is not having to carry a relatively big bit of custom code.)

tjhb
10,105 post(s)
#21-Oct-21 21:40

I think the mechanism behind DateTimeCurrentRef() is the cleverest thing I have seen in my life. And it is a side-effect! What a brainwave (perhaps in the middle of the night, I don't know).

(The sighing is also very funny.)

adamw


10,447 post(s)
#22-Oct-21 15:57

Thanks. :-)

We might also end up adding Rnd() and RndRef(...). We did not add these right away because we want to add rank functions first, there's some interplay there.

Also, if you ever need to combine multiple values into one but do not care about the result, as in the example with DateTimeCurrentRef(...), there is an alternative that is somewhat better than converting everything into a string and concatenating the strings together: you can call DataLength(...) on each value (takes an arbitrary value and returns the number of bytes it takes to store it) and sum the numbers.

danb

2,067 post(s)
#22-Oct-21 19:39

We might also end up adding Rnd() and RndRef(...).

I hope so. That would be very useful.


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

tjhb
10,105 post(s)
#22-Oct-21 20:20

I agree with Dan, very much.

I want to ask:

Part of the "sigh" is that the compiler will have to disable multiple threading, won't it, for nodes where it infers that statements including *Ref functions must disable caching and proceed record-by-record?

If so, that's not bad, just sensible. Unique data has a cost in time, of course.

This may not apply to DateTimeCurrentRef() as that function would normally have no uniqueness requirement. But to RndRef() I think, and possibly UuidMakeNewRef(), it would?

I can think of one more function where the *Ref mechanism might be useful: if we had a function that read current GPS location through a device. There might be others--it's fun to think about this. Possibly network reads, for example, where there can be multi-user writes.

tjhb
10,105 post(s)
#22-Oct-21 21:05

It might be that you would trust the user to avoid declaring multiple threads for a node where a *Ref function is in play.

If he or she does, then duplicate values might arise, in some cases.

That would be fine I think, easily handled by a warning in the manual not to do this inadvertently.

Dimitri


7,479 post(s)
#23-Oct-21 05:37

Part of the "sigh" is that the compiler will have to disable multiple threading, won't it, for nodes where it infers that statements including *Ref functions must disable caching and proceed record-by-record?

I think it's just because instead of running the function once and caching the result, it has to do more work by running it many times.

tjhb
10,105 post(s)
#23-Oct-21 22:58

Thanks. Yes. I was thinking it might also suspend multithreading to preserve entropy.

But I think this will be left to the user. The chances of anyone shooting themselves in the foot in this regard are tiny.

Did you shoot the photo in the current splash screen Dimitri, by any chance?? As always it is all too brief!

danb

2,067 post(s)
#24-Oct-21 00:10

It comes and goes pretty quickly. Plenty of time with ESRI products, but I know which I would rather have


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

Dimitri


7,479 post(s)
#24-Oct-21 04:41

Did you shoot the photo in the current splash screen Dimitri, by any chance?? As always it is all too brief!

No, it's a stock photo. I'm pretty sure it's Stonehenge.

danb

2,067 post(s)
#24-Oct-21 05:12

Looks like it with the shape of the stones. I remember going there back in the days when you could walk amongst the sarsens.


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

adamw


10,447 post(s)
#25-Oct-21 14:54

Dimitri is right, I meant that the query engine was regretting that it cannot make just one call instead of multiple calls. :-)

danb

2,067 post(s)
#22-Oct-21 20:37

I was intrigued by this comment ...

there is an alternative that is somewhat better than converting everything into a string and concatenating the strings together: you can call DataLength(...)

Do you mean something like the following?

--SQL9

ALTER TABLE [NZ_Primary_Parcels] (

  ADD [LAST_EDIT] DATETIME AS [[

    DateTimeCurrentRef(INLINE Sum(DataLength([appellation]), DataLength([affected_surveys]), DataLength([parcel_intent])))

  ]]

);

This is really neat BTW


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

tjhb
10,105 post(s)
#22-Oct-21 21:01

Right on I think Dan.

The result doesn't matter at all, but evidently the speed of computing it might.

All that matters is for the compiler to be able to see that the result may depend on individual records--so that it must decide not to cache.

tjhb
10,105 post(s)
#24-Oct-21 07:11

Thinking about this again, I think you should just use the addition operator (+).

Not SUM or any other aggregate.

This is not an aggregate (unless there were a group) but simple arithmetic, within a record.

adamw


10,447 post(s)
#25-Oct-21 14:57

Yes, exactly. Whether to use INLINE Sum(...) or just a series of + is a matter of choice, I would probably use the latter because it looks simpler to me, but you can use the former if you prefer it for some reason. The good thing about DataLength(...) is that its argument can also be of any type, similarly to CAST. And the benefit over CAST is that DataLength(...) is faster.

artlembo


3,425 post(s)
#22-Oct-21 16:10

I like UuidMakeNewRef a lot. Having the ability to know the latest date for a change is really important when working in a multiuser setup. Are you guys thinking of moving 9 into the realm of a multi-user database, without the need for SQLServer, Postgres, or Oracle?

adamw


10,447 post(s)
#22-Oct-21 16:21

We are planning something like that, yes.

(The idea is not to compete with big databases at what they are great at, but rather to make it easy to enjoy things traditional databases are great at with spatial data. We already have SQL, etc, we want a couple of other things, too.)

Mike Pelletier


2,142 post(s)
#27-Oct-21 22:23

Perhaps a good step toward multi-user could be allowing multi-user read only access. It seems a shame to have to involve one of big relational databases for this purpose. Of course, I'm speaking about what I'd like and have now clue to what it would take.

Along these lines, I'm curious what are the best options today for someone (like me) who wants to keep his data all in 9 and share some of his data with multiple novice 9 users who will take their machine into the field. They don't need to see my current data, just relatively recent.

Can I export my data to a separate 9 project that others access via a query in their project that overwrites their old data? If more than one person tries to update their data at the same time will it fail? If that is a problem perhaps I export the layers to a .gpkg files and their queries pull from them. Perhaps that is better than setting up MySQL for this purpose.

One problem with the update query approach above is that changing field names on my end will break the update queries used by others. Not sure if there is anyway around that though.

Thoughts on these issues?

danb

2,067 post(s)
#28-Oct-21 07:04
Perhaps a good step toward multi-user could be allowing multi-user read only access.

+ 1 for that.

I store all my LiDAR data and various other large datasets in M9 'favorite' projects. When I want some a particular base data set stored in one of these projects, I simply link it into my current working project and use it without replication.

The problem I am now having is that we have other M9 users who would also like to have access to these base datasets. Read only would be fine (perhaps even preferable), but currently we are trying to juggle access between us.


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

adamw


10,447 post(s)
#28-Oct-21 14:51

Can I export my data to a separate 9 project that others access via a query in their project that overwrites their old data? If more than one person tries to update their data at the same time will it fail? If that is a problem perhaps I export the layers to a .gpkg files and their queries pull from them. Perhaps that is better than setting up MySQL for this purpose.

Yes, of course, you can do that.

You export data to a separate .MAP file. Other users then link that .MAP file into their projects using the New Data Source dialog so that they can check 'Open as read-only' option. This allows multiple users to access the same .MAP file simultaneously. To obtain a writable copy of the data, they can either copy / paste the desired components from the read-only 'master' .MAP file to their read-write 'working copy' .MAP file interactively, or run a query that would do that for them. This all will work. It will even be reasonably safe to be opening the read-only .MAP file over a network share in that if the network connection goes down temporarily, this will not affect the read-only .MAP file at all, and could only affect the client with the read-write .MAP file that is linking into it (might hang or throw an error).

We are going to allow linking data in .MAP files via other ways in the future as well. (Something like the ODBC driver, but specific to Manifold.)

danb

2,067 post(s)
#28-Oct-21 20:00

Thanks Adam, I never thought of others opening as read only. This is very useful to know.


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

Mike Pelletier


2,142 post(s)
#29-Oct-21 15:54

Thanks as well Adam. This appears to work well.

apo
173 post(s)
#22-Oct-21 16:54

Very good stuff, the new UUID functions are welcome based on the last discussions we had on the subject. I appreciate the new formatting functions too. In the date domain, a mean to create new date forcing a schema would be really appreciated too for example to take into account a DD,MM,YYYY formatting at entrance instead of a MM,DD,YYYY schema.

The rank future is very good news.

What is considered as the most wished item on our side, would be an "AS" in the COLLECT syntax to better anticipate the name of the resulting columns... as the removing of the strange annoying char send back as discussed in this post https://georeference.org/forum/t153944.25#153982

adamw


10,447 post(s)
#25-Oct-21 15:04

On taking a date in DMY and reformatting to MDY, if CAST (<your string> TO DATETIME) returns the result that you want, you are all set. Ie, you then take the result of the CAST and format it as MDY using StringFormatDateTime(...). If the CAST interprets the string wrongly though, which can happen when the month is a numeric value (because the default CAST treats dates as 'en-US'), you can parse the date using a script function (eg, in .NET, make a function that calls DateTime.TryParse(...)), or just wait until we add our own function to do that, this should happen soon.

Hear you on the result table of COLLECT. We are aware of the issue. We will try to solve it (it is a tricky one).

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