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


8,204 post(s)
#26-Oct-18 17:31

9.0.168.4

manifold-9.0.168.4-x64.zip

SHA256: 51c36bbac5000107f8c392a99fb008d79ef487d0b6d9a91eced29c25af3b9b04

manifold-viewer-9.0.168.4-x64.zip

SHA256: 8441fb9a1443ffbbb4d5e6787c1247a66dbdd273011fd1e6a612294aabd8f94d

adamw


8,204 post(s)
#26-Oct-18 17:32

The focus of the build was styles and the UI related to styles. We completed most of the things we planned to add for styles for the current series of cutting edge builds with the exception of: asymmetric lines, lines with arrows, curved labels. All of the mentioned items are being worked on and will soon appear in cutting edge builds.

Changes

Area format sample have been changed to a triangle to make it easier to tell from a color sample, and is no longer widened to occupy all of the available space.

Controls in the style editing dialog have been rearranged to better use screen space. Preview is moved to the left. Pickers for colors, sizes and rotations are moved to right below the preview (hopefully this makes it clearer that the fill / stroke colors - controllable both in the dialog as well as in the Style pane - are main colors and colors for individual style elements use them as defaults instead of the other way around). Grids showing format samples have thin row handles.

Preview in the style editing dialog allows changing canvas color by clicking color picker button in the top right.

Buttons with format samples in the Style pane have been resized and rearranged to occupy less space while being more legible. Buttons for colors, sizes and rotations have been reduced, while buttons for symbols have been slightly increased.

The Style pane no longer allows selecting multiple format parameters simultaneously and instead allows selecting full format for area / line / point / label. When this happens, the list of format values at the bottom displays and edits full formats (this allows for very quick thematic formatting). All commands used by the list of format values can be applied to full formats: Apply Palette sets fill color to colors from the palette and stroke color a shade darker, Interpolate interpolates fill color, stroke color, size and rotation simultaneously, Darken / Lighten / Grayscale adjust both fill color and stroke color, Reverse reverses everything.

Rendering vector data with thematic formatting optimizes for cases when all values for a format are the same (this frequently happens when thematic formatting is applied to full formats: eg, colors change, but sizes stay the same).

Selecting a button with an already established thematic formatting in the Style pane no longer automatically computes field statistics (to show how many records are in each interval / value, this information is not vital and given that computing it can take a while we are now postponing it). To compute statistics, click Refresh Statistics in the toolbar above the list of format values.

The Style pane for images no longer has a button for a "pixel" format sample which previously had to be clicked to show the rest of controls and automatically shows image formatting.

Color picker dropdown automatically sorts colors by hue on startup and includes a button to switch back to sorting by name.

Font picker dropdown includes choices for bold / italic / bold italic versions of the default font (Tahoma).

Symbol picker dropdowns include numerous new symbols and display symbol names.

Format picker dropdowns include a scrollbar and support common scroll keys (up / down, pageup / pagedown, home / end).

Format picker dropdowns display tooltips for format choices. The tooltip includes the name of the choice and its group.

Color picker dropdown shows hex codes (#RRGGBB) for color values on the right side in the wide mode as well as in the tooltip.

Point styles can use SVG paths. Normal graphics only.

There are many (hundreds) new point symbols based on SVG paths from Font Awesome.

New exterior option for points and labels: halo. Supported parameters: stroke width (default 3 pt). Works for both normal and reduced graphics.

Also

(Fix) The query engine no longer sometimes misoptimizes an OUTER join with a complex join expression.

(Fix) Testing connection to a database or web server no longer continues to use specific login and password if those were provided earlier and the dialog has then been switched to use integrated security.

(Fix) Reading TIFF files with tiles in separate planes no longer sometimes fails.

Reading data for an ad-hoc query on SQL Server automatically recognizes geometry values and seamlessly converts them to Manifold geometry.

Reading data for an ad-hoc query on GPKG / Spatialite automatically recognizes geometry values and seamlessly converts them to Manifold geometry.

Reading Spatialite geometry supports TinyPoint geometry subtype.

Connecting to SQL Server caches SRID values for geometry columns in tables and views in MFD_META. This allows not recomputing these values for each session.

End of list.

BerndD
90 post(s)
#26-Oct-18 20:27

Looks great.


The Future of Spatial Data // www.drahola.com // www.digiterra.de

rhowitt47 post(s)
#26-Oct-18 20:12

Just tried this query against my SQLserver:

SELECT Field

,[AcresFromMap]

,[Geometry]

FROM [Geom_of_Fields]

where [Company_ID] = '561'

Got an error message "Invalid Type", which refers to the Geometry column.

I can run the query without Geometry and the query runs correctly.

Your release notes say this should work.

Dimitri

5,082 post(s)
#26-Oct-18 20:33

where [Company_ID] = '561'

Got an error message "Invalid Type", which refers to the Geometry column.

Can't say without knowing the types. For example, if Company_ID is a numeric type, then 561 should not be in single quotes: that's a type mismatch.

Anyone curious, who might not have SQL Server, can try it against Nwind:

SELECT * FROM [Products] WHERE [Unit Price] = '18';

Generates an error, because [Unit Price] is numeric.

SELECT * FROM [Products] WHERE [Unit Price] = 18;

Works fine.

rhowitt47 post(s)
#26-Oct-18 21:43

Company_ID is a string field.

As I mentioned above, if I remove the Geometry column from the query it runs correctly. If Geometry is in the query then the error is "Invalid Type"

rhowitt47 post(s)
#26-Oct-18 21:50

My bad. Was using adonetSqlserver, switched to straight Sqlserver and a valid result is returned.

But now I can not create a drawing based on the results. When the query is right clicked there is no Schema listed.

tjhb

8,335 post(s)
#26-Oct-18 21:55

Are you writing a native SQL Server query against your datasource, then trying to create a Manifold drawing from the query result?

Well, can that be done? I don't know.

Maybe you should try a Manifold query against the SQL Server datasource, then making a Manifold drawing from the result?

rhowitt47 post(s)
#26-Oct-18 22:19

yes it is a native query, that is the whole point. With a native query, 1) there is no speed penalty, 2) the data should be directly editable in the sqlserver.

The natvie query returns resuls nearly instantaneous, while the manifold query takes 30 seconds.

Yes, I can create a drawing from the manifold query.

But is this data editable both geometry and column data and will is the data updated back in the SQLserver DB?

tjhb

8,335 post(s)
#26-Oct-18 22:55

But is this data [Manifold drawing created from Manifold query on SQL Server data] editable both geometry and column data and will is the data updated back in the SQLserver DB?

I think that is the idea, yes, but I haven't tested. (You can?)

Whether you should be able to do the same using a Manifold drawing created from an SQL Server query on SQL Server data, I don't know.

Dimitri

5,082 post(s)
#27-Oct-18 06:54

Whether you should be able to do the same using a Manifold drawing created from an SQL Server query on SQL Server data, I don't know.

It sounds like this has gone beyond the specific features of the new build, 168.4, per se, and has become a generic discussion "how do I create Manifold drawings from native DB queries." That should be in a new thread.

There is usually a way to do what you want, but the details matter and this isn't the thread to discover all the details we need to know. For example, if you have geometry in your SQL Server database, is that SQL Server geometry? etc.

adamw


8,204 post(s)
#27-Oct-18 10:08

I understand where you are coming from and I understand what you are after - or at least I think I do. The ultimate purpose is to be able to do filtering on the database, work with the results as a drawing, in read-write mode, and do all that without creating views - correct?

There is a difficulty here.

Let's look at the requirements.

Performing filtering on the database makes absolute sense (the server is faster and will always be faster than the client at reading a million records and filtering them down to a couple of thousands). It also means that the query performing the filtering has to be native to the database.

SQL Server has two types of queries: views and ad-hoc. Our last requirement precludes using views, so the query has to be ad-hoc. Ad-hoc queries enjoy much less support from the side of the database than views though, so there might be some hoops to jump through and the question is whether we can jump through all of them.

On an earlier attempt to use an ad-hoc query we saw the first hoop: geometry values not being recognized as geometry. This was specific to SQL Server and this is now solved (for SQL Server connections, we'll likely make it work for ADO.NET SQL Server connections as well).

There is now a second hoop to jump through: despite what the word 'ad-hoc' means, we can store an ad-hoc query on the database as a Manifold component, however since we don't tell the database that we are storing a query that we are going to run repeatedly (that would be a view), the database does not pre-analyze the query and does not compute its schema. We can know what the schema of the result is when we run the query, but not before. We *can* jump through this particular hoop - say, by caching the last known schema, or by allowing one to create a drawing on an ad-hoc query without knowing its schema and just entering the name of the geometry field / providing other details similarly. (The latter can already be done in the UI - you can copy and paste an existing drawing and just adjust its properties, replacing the name of the source table and the name of the geometry field.)

There is the next hoop: the result of an ad-hoc query lacks index information. When the result table is the product of an ad-hoc (this is important) SELECT * FROM t WHERE ..., you cannot use a spatial index on T to filter it further. You just get a stream of records and you cannot tell the database 'please filter this further with this criteria' - the stream of records does not have a name, there is no way to refer to it. This means we cannot really show the result table of an ad-hoc query right away on a map and have to create a temporary index to do that. We do offer to create a temporary spatial index to show the data automatically, but this is unpleasant because you have to click the button and because it might take some time (not overly long, but still). If SELECT ... was in a view, we would have been able to use an existing spatial index on T to filter it.

There is one more hoop related to the previous one: because the result of an ad-hoc query lacks index information, it is read-only. We want writeback and we cannot have it, because we do not know which table to write to. Now, in this particular place we might be able to do something by specifying manually which table to write to and which fields to use as a primary key -- or possibly by writing fancier queries like SELECT FOR UPDATE, although this has tons of other drawbacks.

There might be more hoops ahead although I think this should be mostly it.

All of the hoops are perhaps solvable. But as you can see, what we basically end up with is having the user writing an ad-hoc query, storing it, and then telling Manifold instead of the database what the query means to do and how to handle it. Honestly, this feels like creating a crutch and doing what database views do, but on the client, because of the requirement to not use views. Could we revisit why it is undesirable to have views? There are obviously reasons, but solutions for them might be easier and cleaner.

adamw


8,204 post(s)
#27-Oct-18 10:28

One more thing:

Yes, I can create a drawing from the manifold query.

But is this data editable both geometry and column data and will is the data updated back in the SQLserver DB?

Yes.

If you use Manifold queries (as long as you structure them properly, but that is not difficult to do), the result is going to be writable. The only problem is that the filtering will be performed on the client, not on the server, filtering on the server is faster.

A simple example.

We connect to SQL Server and run this:

--SQL

CREATE TABLE t5 (id INT IDENTITY (1, 1), a INT, g GEOMETRY);

INSERT INTO t5 (g) VALUES

  (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));

ALTER TABLE t5 ADD CONSTRAINT t5_id PRIMARY KEY CLUSTERED (id);

CREATE SPATIAL INDEX t5_g ON t5 (g) WITH (BOUNDING_BOX=(0, 0, 200, 200));

This creates a table with a geometry field.

If you perform the above from a Manifold command window, right-click the data source in the Project pane and click Refresh to let it catch up to the changes - or close and reopen the project, or delete and re-create the data source.

We then create the following query in the MAP file and reference the table we just created on SQL Server:

--SQL

SELECT * FROM [SQL Server]::[dbo.t5] WHERE id=1;

We can then right-click that query and create a drawing based on it. We can open the drawing and it will show filtered records. If you Alt-click an object, you can edit its fields (the A field was added specifically to show that).

Dimitri

5,082 post(s)
#27-Oct-18 06:35

As I mentioned above, if I remove the Geometry column from the query it runs correctly.

Ah, I see. I missed that.

tjhb

8,335 post(s)
#26-Oct-18 21:51

[cross posted; removed]

adamw


8,204 post(s)
#27-Oct-18 09:17

Here is a simple test that I did.

Connected to a SQL Server database. Opened a command window on the SQL Server data source (it opens in native mode by default = running all queries on the database, using SQL syntax of the database, and I left it in that mode).

Ran the following:

--SQL

CREATE TABLE t5 (id INT IDENTITY (1, 1), g GEOMETRY);

INSERT INTO t5 (g) VALUES

  (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));

Then ran this:

--SQL

SELECT * FROM t5;

The geometry field came back as geometry.

Could you repeat this test on your system? Does the geometry field come back as geometry in the SELECT?

What is the type of the Geometry field in your original table? What is the version of SQL Server Native Client?

PS: Just saw that this has been resolved already: you used ADO.NET SQL Server data source instead of SQL Server. Will read the whole thread first in the future.

rhowitt47 post(s)
#27-Oct-18 19:26

I think we are all on the same page. My post here was simply my test of what I THOUGHT was included in this release. ( Running a native SQLserver query, get a geom in the results, and being able to convert that to a drawing ). If converting to a drawing is not currently supported, no problem.

Adding the ability to edit data in SQLserver will truly be cutting edge.

rhowitt47 post(s)
#28-Oct-18 13:48

In addition to Manifold , Our company also use GeoServer, an open source web mapping server. GeoServer allows us to publish our clients data via a web interface. The reason I mention GeoServer, is that it also connects to our SQLserver. And this in conjunction with OpenLayers ( javascript library ), provides a fast , efficient and flexible interfaces for presenting data. Manifold, for paper output, data analysis, and generation of vector data, is still our go to tool.

GeoServer does not interrogate the SQLserver database and then provide all of tables, if forces me to pick the tables I want to be available for presentation. By doing this, it collects only the table definitions and SRID needed for the tables I intend to use. And by defining these tables up front it is able to pre-populate the table meta data . Although I have not looked at the internals of the API code, using the OpenLayers' API, the table data can be filtered, so that only the subset of records needed to display on a web page is sent to the client. This filtering of the data happens in seconds not tens of seconds. And if I code the web page correctly both the vector data and tabular data is editable.

I bring the above up, because I think it is a model that addresses many of the points you made about what is needed in order to provide geometry data and make it editable.

Dimitri

5,082 post(s)
#29-Oct-18 08:07

If converting to a drawing is not currently supported

It is supported. You just have to do it correctly. See adamw's discussion.

rhowitt47 post(s)
#29-Oct-18 13:44

Yes the query returns a result showing that g is geom field.

No, I can not create a drawing from the results.

1) the column in my table geom_of_fields ( this table was upload to Manifold for testing ) is a Geometry

2) the system is using Microsoft SQL Server 2008 R2 Native Client

rhowitt47 post(s)
#30-Oct-18 16:26

Today i switch to usinig the Microsoft SQLServer 2012 Native Client. And then reran the native query... Still no joy. The query results could not be turned into drawing.

As per the Manifold documentation, the results have a gray background, meaning there is no index available and there fore no drawing can be created.

tjhb

8,335 post(s)
#31-Oct-18 00:54

As per the Manifold documentation, the results have a gray background, meaning there is no index available and there fore no drawing can be created.

That's not correct.

You can create a drawing provided that there is an RTREE index on a Geom field.

A grey background means that there is no BTREE index. That does not mean that a drawing can't be created, but rather that objects (in table or drawing) can't be selected.

rhowitt47 post(s)
#31-Oct-18 02:00

OK, so why can't a drawing be made from my native query?

The table in question has a spatial index

tjhb

8,335 post(s)
#31-Oct-18 05:43

I understand that you're frustrated, and I'm sorry I'm not experienced enough to help.

I suggest, though, that you re-read Adam's post above concerning all the hoops that the software must jump through to do exactly what you are asking--re-read as many times as is necessary, plus a few extra, bearing in mind that Adam's writing is exceptionally precise and succinct, so an unusually long post from Adam like this really something to take your time over. Don't worry if you don't feel you understand everything after "only" 3 or 4 reads.

Also: try replying to the question in his last paragraph in that post. You might have missed it. Speaking strictly from experience, in trying to draft an answer to Adam's question, you might find that murky things start to become much clearer. Even if that's not the case, you might trigger a great suggestion in return.

[Added] Also, write down and post absolutely all of your steps, with the actual result in each case (and always avoid empty conclusions like "it doesn't work").

adamw


8,204 post(s)
#01-Nov-18 08:33

Like Tim says, I answered above.

The long and short of the answer is: not recognizing geometry as geometry in ad-hoc queries was one of the issues standing in the way of your desired workflow. That particular issue is now solved, but there are several other issues left to solve. We can solve them all if needed, however, since the solutions necessarily involve you as a user to provide additional input regarding the query manually, essentially describing what it does, instead of letting the database figure it out automatically, maybe it is a better idea to leave that to the database and use views. I am asking at the end why specifically do you want to not be using views. Maybe the reason you don't want to be using them has a cleaner solution.

rhowitt47 post(s)
#01-Nov-18 14:28

View are problematic for a couple of reasons:

1) Views are not dynamic. That is a view can not be defined with a "where" clause that has a parameter to be filled in on demand.

2) Because of 1) above , a view has to be defined for every way that one would like to slice up a table into sizable chunks. As an example, a table contains all parcels for all clients, but for editing purposes, only one client's parcels should be shown in a client's project drawing. So a view has to be created for every client.

3) Because of 2) above we are currently managing over 2000 views and this number grows every time a new client is added.

If instead of a view, a query could be used , only a couple of queries would be needed that would manage all of the ways to slice a table.

If there is a better way to manage the editing of a large dataset , i am all ears.

rhowitt47 post(s)
#01-Nov-18 14:51

New side affect of Manifold 9. When creating a command window in Manifold 9, Manifold is creating a an [mfd_meta] in the SQLserver database to which it is connected. Due to all of the views this [mfd_meta} table has had 1000s of items created by Manifold. This first time setup of the meta table is requiring 10s of minutes by Manifold. And what ever query Manifold is doing on the SQL server is pegging the CPU. ( This is related to my original bug, which has been submitted to Manifold , "Select Top 1" from a very large takes a very long time ).

In addition, our Manifold 8 projects, that accesses this database, are now taking 10s of minutes to start.

adamw


8,204 post(s)
#01-Nov-18 15:54

This is a product of the change that we did that caches SRID values for tables and views.

A view (like SELECT TOP 1 ... which you sent and we verified) taking a very long time to run is not related to anything in our code, it is plain a view taking a long time to run, perhaps because there is a second database involved. With the caching code we will only run it once as long as we can write the produced SRID value back to the database.

We will check what's up with 8 taking a long time to connect to the database. If that's a side effect of the change that we implemented, we will of course fix it. Just to be clear, does it take a long time to connect to the database from 8 when 9 is still running or when it is not running as well?

adamw


8,204 post(s)
#01-Nov-18 16:04

You can use a limited number of views and modify them on demand instead of just keeping a view for each client.

The meat of a view can be put into a table-valued function and the view by itself can be just SELECT * FROM <function>(<parameters>), so that its text is easy to modify.

You can actually modify a view by running a query in 9: EXECUTE [[ ALTER VIEW ... ]] ON <datasource>. This way there are minimal changes to the workflow.

Instead of:

open 9, open the MAP file with a data source for the database, open a query that retrieves data from the database, adjust query text to do the filtering you currently want, then open a drawing linked to the query, do Refresh on the drawing to re-fetch the data with adjusted filtering, then add / change / delete objects in the drawing,

we:

open 9, open the MAP file with a data source for the database, open a query that does EXECUTE / ALTER VIEW on the database, adjust query text to do the filtering you currently want, run the query, then open a drawing linked to the query, then add / change / delete objects in the drawing.

There are currently two issues with the latter sequence, both related to SQL Server, one of them requires a fix on our side (which we already implemented a couple of days ago), but other than that, the second workflow seems usable, no?

I will try to provide a specific example tomorrow. (I'll also respond to your last post which I haven't yet seen, we cross-posted.)

rhowitt47 post(s)
#01-Nov-18 19:30

Is there is one master view that get altered?

How does this work with multiple users that want to edit different clients?

adamw


8,204 post(s)
#02-Nov-18 06:41

One master view per user. Or several views per user, but a limited amount.

Say, I am a user. I log in and I want to work with client X. I adjust my view on the database to show data for client X. After some time I am done with client X and want to now work with client Y. I adjust my view to show data for client Y. If I want to look at client Z in the middle of my work with client Y, I adjust my second view (set up a new one if I don't have any) and work with client Y through view 1 and with client Z through view 2.

When you log in as a different user, you are working with your own views, our views are separate.

Views themselves can be organized like this:

--SQL for SQL Server

 

-- parameterized view

CREATE FUNCTION filter_func(@client NVARCHAR(50))

RETURNS TABLE AS

RETURN (SELECT * FROM "data" WHERE client_id = @client);

 

-- specific view, currently selects data for 'bob'

CREATE VIEW filter_adamw_1 AS SELECT * FROM filter_func('bob');

All of the adjusting can be done from 9:

--SQL9

EXECUTE [[

  ALTER VIEW filter_adamw_1 AS SELECT * FROM filter_func('joe');

]] ON [SQL Server];

As I said, the build you currently have has an issue (views on SQL Server appear readonly even when they are updatable), but we already have a fix for that.

rhowitt47 post(s)
#01-Nov-18 15:59

So here is an example of the type of system I want to Manifold 9 to help manage, especially in terms of editing Geometry data, but also where manifold could help when adding a new client to the database.

CREATETABLE [dbo].[Geom_of_Parcels](

[OID] [int] IDENTITY(1,1) NOT NULL,

[Version] [int] NULL,

[Year] [int] NULL,

[ParcelName] [nchar](50) NULL,

[Client_ID] [nchar](10) NULL,

[PrintOrder] [int] NULL,

[Geometry] [geometry] NULL,

[Attr_a][nchar](10) NULL,

[Attr_b] [nchar](10) NULL,

[Attr_c] [nchar](10) NULL,

[Attr_d] [nchar](10) NULL,

[Active] [char](1) NULL,

[AcresFromMap] [float] NULL,

[AcresManuallyEntered] [float] NULL,

[editDate] [date] NULL,

[EditedBy] [int] NULL,

[WKT_temp] [varchar](max) NULL,

CONSTRAINT [PK_Geom_of_PARCELS3] PRIMARY KEY CLUSTERED

(

[OID] ASC

)WITH (PAD_INDEX= OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

)ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATETABLE [dbo].[Geom_Of_SubParcels](

[OID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[Version] [int] NULL,

[ParcelName] [char](50) NULL,

[SubSection] [char](50) NULL,

[Client _ID] [char](10) NULL,

[Year] [int] NULL,

[Geometry] [geometry] NULL,

[Active] [char](1) NULL,

[EditedBy] [nchar](40) NULL,

[EditDate] [date] NULL,

[AcresFromMap] [float] NULL,

[AcresManuallyEntered] [float] NULL,

[Attr_a][nchar](10) NULL,

[Attr_b] [nchar](10) NULL,

[Attr_c] [nchar](10) NULL,

[Attr_d] [nchar](10) NULL,

[LabValue1] [smallint] NULL,

[LabValue2] [float] NULL,

[LabValue3] [char](5) NULL,

[LabValue4] [smallint] NULL,

[LabValue5] [float] NULL,

[LabValue6] [float] NULL,

[DateOfReport] [datetime] NULL,

[geometry_temp] [varchar](max) NULL,

CONSTRAINT[PK_Geom_Of_Sections1] PRIMARY KEY CLUSTERED

(

[OID] ASC

)WITH (PAD_INDEX= OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

)ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

These two table will track hundreds of clients who each have anywhere from 1 to 500 ( or more) parcels.And each of the parcels can have anywhere from 1 to 50 ( or more ) subparcels.In addition to these two tables in SQLserver there are many more tables that track all manner of tabular data about the client.Most of this data is available via a Software management system that allows multiple people to have access to this data, all at the same time. ( I.E. This is not a standalone database sitting on one desktop computer )

Because there may be Tens of thousands of parcel and subparcel records, a starting set of capabilities, in Manifold, that would be of great use are:

  • 1)be able to pull in just one Client/Year set of Records via a Query.
  • 2)Be able to edit the geometry data.
  • 3)Be able to edit some of the tabular data.
  • 4)Create new entries in the table for the current client/year
  • 5)This data should be available within seconds, not tens of seconds.
<!--[if !supportLists]-->

There are other functions that Manifold could probable perform once this basic set of capabilities was in place (e.g. Do a join query with another dataset and based on those results update the Parcel table ).

From a programming standpoint I am not sure why you are using the model you are. That is, why are you storing all of the metadata back to SQLserver.

If one wants to write a query about the Parcel data, why is Manifold not maintaining an object, within Manifold, that has the source of the data, the list of geometry fields, SRIDs, list of indexes, etc. All of this data can be queried from SQLserver. This would allow Manifold to have only the data it needs to manage the set of tables that the end user has specified are needed within the current project.

If you want me to continue with filling in my wants for a GIS data management system I can continue....

adamw


8,204 post(s)
#02-Nov-18 10:32

Thanks for the explanation.

All of your requirements are doable.

The most future-proof approach is, I think, this:

Use SQL Server views. Have one or more views for each user selecting data for the client / year combinations with which that user has to currently work. Work with drawings based on these views (they will be automatically created for geometry fields). Can edit geometry, edit attributes, etc. See my posts above for details.

There is an alternative approach:

Instead of SQL Server views, use queries in 9. The stickiest point is having the filtering be performed on the database, to avoid transporting a million records to the client and having the client perform filtering locally. But if the filtering is just ... WHERE field1=... [AND field2=...], we can generally offload it completely to the database, as long as we can detect that the database will be able to do it fast. That is, even though you only have a table on the database, with indexes, but without any views, and a query in 9 that works with that table, 9 will compose and send a query to the database that will engage the indexes and get all the benefits of on-database filtering that a view would have.

For this to work well, 9 has some requirements from the database. But they are nothing too unusual and they are being relaxed all the time as our query engine gets smarter and learns how to get more out of each individual database that we support. Right now, on SQL Server, the best scenario is when the table has a single field, with no nulls allowed, with a clustered index on it, and the filtering is done on that field. Your scenario above (a) filters on two fields instead of one (Client ID, Year), and (b) has clustered primary key on a different field (OID). However, (a) can be solved by having a single combined field with text for both Client ID and Year. And if you can then make *that* your clustered primary key, you are all set: you can use no views, just queries in 9 and WHERE on that combined field will go straight to the database and the database will be fast to serve it. Or, if most of the filtering is on the ClientID and not on Year (say, there are 1000 clients and only 3-4 years per client on average), you can keep the fields separate, make a clustered index on just ClientID and then when you use WHERE ClientID=... AND Year=... in a query in 9, the part for ClientID=... will go to the database, and the part with Year=... will stay on the client, but the performance will still be fine because the database still does most of the filtering.

The main benefit of the first approach (views) compared to the second (queries in 9) is that if the filtering criteria suddenly has to be complex (for example, if you'd want to join a secondary table and look into its values to determine whether you want to return a record or not), views will keep all of that work on the database, while queries on the client might only be able to keep part of it there. This might or might not be important for the performance, depending on the data. Everything else is mostly better with queries on the client.

rhowitt47 post(s)
#02-Nov-18 13:18

Thanks for the road map of how to possibly implement a 9 solution. I will experiment with your suggestions.

rhowitt47 post(s)
#05-Nov-18 03:32

Started to work through your road map.

A note to Manifold Developers: it would be helpful in the Query Log if it included the execution time of each query. Although the query shows execution time while it is running, it is lost once the query is done. Execution time is value information for determining the efficiency of different queries.

In SqlServer the following three items were defined:

 

CREATE TABLE

[dbo].[GIS_M9Testing](

    [ClientID] [nchar](10) NOT NULL,

    [Year] [int] NOT NULL,

    [Parcel] [nchar](50) NOT NULL,

    [Geometry] [geometry] NULL,

    [Geometry_Temp] [varchar](max) NULL,

    [OID] [int] IDENTITY(1,1) NOT NULL

ON

[PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

- defined a cluster index on ClientID

- [Geometry] has a spatial index

- OID is the identity column that drawings need

- populated

this table with data from an existing table.

 

CREATE FUNCTION

filter_func( @client nvarchar(50))

returns table

as

return

(Select * from GISM9Testing

   

    where ClientID = @client );

 

CREATE VIEW filter_rickh_1

as Select * from filter_func('100');

 

Then in

Manifold a data source and two queries were created:

 

    Datasouce to access the SQLserver

 

UpdateView

Query

 

-- $manifold$

--

-- Auto-generated

EXECUTE [[

   

ALTER VIEW filter_rickh_1 as Select * from filter_func('100');

]]

on [AZSQL1_GISForManifold]

 

 

Select Data from

SQLserverView Query

 

-- $manifold$

--

-- Auto-generated

SELECT * FROM

[AZSQL1_GISForManifold]::[dbo.filter_rickh_1]

 

The query returned results with a schema which showed an rtree index on the geometery column. These results could be turned into a drawing. However, neither the tabular results or the drawing were editable.

I also tried a Manifold query of the SQLserver table with a where clause on the field that has a clustered index. This was done to test: how fast the query was, were the results editable and could the results be turned into a drawing.

-- $manifold$

--

-- Auto-generated

SELECT * FROM

[AZSQL1_GISForManifold]::[dbo.GISM9Testing]

where StringTrim(company_ID, ' ') =

'561';

This query executed rather quickly, but the tabular results were not editable. In addition, although the results did have a schema and the results could be turned into a drawing ( which had a red exclamation point icon), the schema did not show an rtree on the geometry column even though the schema showed the column as a geom and I was unable to display any of the polygons.

I am not sure where to go from here.

adamw


8,204 post(s)
#05-Nov-18 07:29

As I said in an earlier post, the build you currently have has an issue (views on SQL Server appear readonly even when they are updatable). That's perhaps why the view seems to work fine, except it is non-editable.

We already have a fix for that. The fix will be in the next build. We currently plan to issue the next build at the end of this week, but if you contact tech support, we can provide you with an early version of it which will contain the fix - this will allow us to move further.

Also, regarding this:

- defined a cluster index on ClientID

- [Geometry] has a spatial index

could you specify the exact commands you used?

You say later that if you run a Manifold query (SELECT * with filtering in WHERE) on the table, then the schema of the result table does not show an r-tree index on the geom. Does the schema of the original table on the server show an r-tree index? Does it appear if you refresh the data source (if you add a spatial index to a table outside of our UI or using SQL native to SQL Server, we won't know that you added it until you refresh the table / data source)?

Last, we do display execution times for queries, they are in the log window (View - Panes - Log Window). Maybe we should display them in the log specific for the command window as well.

rhowitt47 post(s)
#05-Nov-18 19:49

Looking deeper into SQL Server, here are some limitations per Microsoft:

Clustered indexstructure overview. In a Clusteredtable, a SQL Server clustered indexis used to store the data rows sorted based on the clustered indexkey values.SQL Serverallows us to create only one Clustered indexper each table, as the data can be sorted in the table using one order criteria.

Creates a spatial indexon a specified table and column in SQL Server. An indexcan be created before there is data in the table. Indexescan be created on tables or views in another database by specifying a qualified database name. Spatial indexesrequire the table to have a clustered primary key.

This says to have spatial indexing, there has to be a primary key. The primary key has to be unique and will be THE Clustered Index in the database. SQL server will not allow another Clustered Index.

So one of the solutions proposed above, defining a clustered index only on ClientID or even ClientIDYear, so that the table could be quickly filtered by a Manifold Query will not work. Because those columns alone will not produce a unique identifier. (item 'a' below )

For this to work well, 9 has some requirements from the database. But they are nothing too unusual and they are being relaxed all the time as our query engine gets smarter and learns how to get more out of each individual database that we support. Right now, on SQL Server, the best scenario is when the table has a single field, with no nulls allowed, with a clustered index on it, and the filtering is done on that field. Your scenario above (a) filters on two fields instead of one (Client ID, Year), and (b) has clustered primary key on a different field (OID). However, (a) can be solved by having a single combined field with text for both Client ID and Year. And if you can then make *that* your clustered primary key, you are all set: you can use no views, just queries in 9 and WHERE on that combined field will go straight to the database and the database will be fast to serve it. Or, if most of the filtering is on the ClientID and not on Year (say, there are 1000 clients and only 3-4 years per client on average), you can keep the fields separate, make a clustered index on just ClientID and then when you use WHERE ClientID=... AND Year=... in a query in 9, the part for ClientID=... will go to the database, and the part with Year=... will stay on the client, but the performance will still be fine because the database still does most of the filtering.

So that leaves us with just “Programmable Views” by user. With each GIS user having a set of programmable views.

While programmable views could work, at this point it is not an ideal solution. Currently every one of our clients has a manifold project. Each client has their own project because each client has a lot of unique geospatial infrastructure that is mapped with in the project. This was done because it was not possible to keep all this data in SQL server tables, without building out Views for each of the infrastructure layers. ( we track in the neighborhood of 20-30 infrastructure layers ).

Switching to programmable views means either: that in a clients project the "parcel layer" will not look like the correct set of parcels until the programmable view is updated to the current client or

We move all infrastructure data to SQL server spatial tables and then make only one Manifold Project that switches the view for each layer to the current client. ( This is interesting, but for use to implement would require a large effort to move all of the data ).

Parameter driven queries that can determine their schema ultimately provide the most flexible interface. I know that that puts a lot of work in Manifolds lap to make that work; but at the end of the day you would have one awesomely powerful GIS engine.

adamw


8,204 post(s)
#06-Nov-18 07:03

Fair enough. We realize that clustered is a highly contested position between indexes, and that frequently what you want to filter on does not have the luxury of being clustered. We do get some mileage out of non-clustered indexes on SQL Server as well, it is just that we can currently get more out of a clustered index, SQL Server optimizes much heavier if the index it hits is clustered. We have some ideas on how we can get more out of both types of indexes though - and not just on SQL Server - so, stay tuned.

rhowitt47 post(s)
#09-Nov-18 17:21

Changing subject slightly...

We have something like a few hundred M8 project files, we have been unable to open any of these projects successfully in M9.

Is that to be expected at this point( i.e. M9 is not fully compatible with M8 ) or is there a way that M8 projects can be included in an M9 project that works today?

tonyw
465 post(s)
#09-Nov-18 17:34

is there a way that M8 projects can be included in an M9 project that works today?

With your project open in M9 try File > Import then navigate to and import your Manifold 8 .map file. Importing the M8 project brings in all the components from the M8 project. All the thematic formatting from M8 is preserved too which was a pleasant surprise.

In my case, I started my new map project in M9 but needed components from a previous map project in M8. My intention wasn't to carry on with the M8 project in M9, only to re-use some of the components.

tjhb

8,335 post(s)
#09-Nov-18 17:37

That is completely abnormal. All Manifold 8 files should open without problem in Manifold 9 (with automatic conversion).

Something is up at your end.

What happens when you try to open one of these files in 9? Any message?

Can you open the same file(s) in 8?

Dimitri

5,082 post(s)
#10-Nov-18 15:41

Changing subject slightly...

Please start a new thread for a new subject.

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