Subscribe to this thread
Home - General / All posts - Calling all Manifold users: What examples do you want to see in Radian?
Dimitri


7,413 post(s)
#14-Sep-16 10:19

This is a call to the Manifold community similar to a post I made in the Radian beta community forum. I'm posting here in the open forum as well because I think everyone can benefit from participating even if you are not working with Radian in the beta community.

Background:

As you may have read in http://www.georeference.org/forum/t129214 the first Manifold product using new generation technology will be Radian Studio, a sort of "Swiss army knife" database and GIS tool that is aimed at manipulating spatial data. Pretty much anything that you can imagine that can be done to manipulate raster or vector or table (DBMS) data in either a spatial or non-spatial context can be done with RS, and it can import / export / read/write edit to a truly stupendous range of file formats, databases and other data sources as well as reading pretty darned much any web server source you might have heard of. Radian has big capacity and it is fast.

Radian has plenty of dialogs for interactive work with data but the real power comes in having a truly spectacular spatial SQL as well as easy use of various languages. V8, for example, is built in and it is trivial to use other languages like Python if you prefer those.

Radian SQL and built-in Radian DBMS capabilities are fully formed, that is, totally worked out to pretty much the last detail at a level one normally sees only in the very most sophisticated and fully implemented DBMS products like Oracle or PostgreSQL. Those DBMS products (and Radian) are way beyond simpler or more limited tools like MySQL or SQLite. Radian easily works with other DBMS servers so if you have data in Oracle or whatever you can do Radian things with it.

So even if you don't count Radian's ability to seamlessly work with other DBMS servers, like Oracle or PostgreSQL, etc., there is a vast amount of capability in Radian. If you've seen how the SQL solutions published in this forum using Release 8 work like magic to automate something that otherwise is impossibly tedious, you've seen the tremendous power of spatial SQL. Radian makes that even more powerful and more useful.

All that is good but it is a lot to learn from a cold start if you are new to Radian or new to SQL. To make that easier Radian will be published with many worked examples in Radian SQL. We think providing as many "real world" examples as possible will make it much easier for people to put Radian to work productively right away and to "learn by doing." We are talking hundreds, not tens, of examples.

A Call for Examples:

We are asking everyone in this forum to provide a list of typical tasks they do involving data manipulation or analysis which they would like to see included as examples when Radian is published. Just post to this thread with examples you would like to see so everyone can work together. You don't really need to know what and how Radian does things to contribute: all you need to know is what you find yourself doing frequently in GIS or DBMS that involves working with the data or the geometry.

If anything, it may be useful that you don't know Radian because then you won't be limiting what you ask for based on any preconceptions that Radian might not be able to do it.

Ideally, examples will be tasks like manipulating images and surfaces and other rasters, doing NDVI, slicing and dicing vector objects in drawings, performing various spatial overlays and topology overlays, manipulating data and/or geometry to make interoperability easier and other frequently encountered tasks which could be done using Radian SQL or SQL in combination with a bit of scripting. If it is a task you find yourself doing a lot and wishing could be done better / easier / faster than using whatever tool set you are currently using, toss it into the list of examples you want to see. Tasks you do in DBMS that have nothing to do with GIS are also welcome.

Although Radian has visualization (it displays huge rasters with eye-popping speed) it is not a GIS like the upcoming Release 9. It is a tool for manipulating data and not for manipulating displays. So we are not looking for examples that focus on the display and not on the data, for example, such as "I want to create a series of map books for many different clients where the formatting used changes automatically according to rules I have for each client." Instead the examples should be about the data, like "I have a folder with a thousand images in it and I need to create an NDVI for each and then clip the image to a bounding box given in a drawing with a similar name in the folder, reproject to a given projection and save in the following format."

We want the focus to be on real world tasks, so an ideal example would include a link to downloadable real world data that is the subject in real life. But even if you don't have that link send in the example. Think big and think broadly. If it is something you find yourself doing a lot do not worry about whether it is a good fit to Radian SQL or SQL plus scripting, just include it as part of your list.

The only thing we ask is that this is a real world task that you already encounter and is not something that you think would be useful for other people but have not ever actually have had to do yourself in real life. Best of all would be a real world task that you find yourself going nuts over having to do in Arc or some other system because some part of it is way too manual or too tedious or whatever.

A Golden Opportunity:

Our objective is to have a couple of hundred worked examples written up and published online the moment Radian is released to the public so that there will be a kernel of immediate productivity boosters in various frequently encountered tasks.

For people already familiar with Radian SQL it is easy to do lots of examples, but building up how to do them from the bottom is not so easy if you are just starting. Lots of examples will provide a kickstart to learning and will provide templates to copy and adjust slightly for those who want to do something similar but who have not yet acquired the skills to do the task from a cold start. We think having hundreds of good examples will be the best and most useful part of the documentation.

Best of all, this is a golden opportunity if you find yourself doing something a lot to have it automated for you for free by the experts at Manifold.

Even if you are already really good at SQL it is an opportunity to see how people who really know the ins and outs of Radian would approach a task. A deeper understanding of the tool really opens up possibilities and can achieve even better performance than the already quick default.

Please Stay on Topic:

Please post only lists of examples you would like to see to this thread. Please do not post comments or questions about Radian, Release 9 or make any other posts except examples you want to this thread. If you feel you can't contribute without knowing more about the target environment that is a perfectly fair point but in that case you will have to wait to ask for examples in any subsequent waves of examples that are done after Radian is published. My advice is to not over-analyze or worry about that but just post what you find yourself doing a lot. Don't miss the boat.

There are no guarantees any specific example will get done or will get done exactly as you like, but hey, the chance it might be is too good to pass up. It is not unusual for manual work that takes hours or days to get done in seconds with a few lines of Radian SQL. It would be wonderful to get that for free.

Thanks in advance for any contributions!

dale

630 post(s)
#14-Sep-16 11:28

Quick example: Topological Overlay/identity that respects area overlaps. See forum posting here, long with tjhb (Tim) suggested 8 SQL solution, <edit> and a link to example data set and description.

dale

630 post(s)
#14-Sep-16 11:54

I have many hundreds of surfaces derived from LIDAR, tiled to 1km blocks. I have a need to merge either a subset , or all tiles, then clip to an area.

Rough workflow: Import multiple surfaces from a folder, assign projection to all, batch paste append, change projection, then clip intersect using a drawing AOI.

geozap
264 post(s)
#15-Sep-16 06:33

Same, but usually instead of surfaces I have aerial images of high resolution.

Usually I follow these additional steps:

After having done the dale's steps I resize (downgrade) the image according to the specific project requirements.

Then export it to ecw format, and link the produced ecw. (to make it redraw faster)

KlausDE

6,410 post(s)
#14-Jun-17 15:55

A variation of this would like to see an example where this is done based on linked images grouped in map and that finally are unioned and exported as one large ECW.

(We often have catalogs of TIFF files which really are legacy)


Do you really want to ruin economy only to save the planet?

mdsumner


4,260 post(s)
#14-Sep-16 13:17
  • Read LiDAR point clouds from LAS files, and identify points that collectively provide a "reasonable" ground surface.
  • Extract nearest SST value for all of a table of longitude, latitude, date-time coordinates from remote sensing / model-blended time series maps. A good data source is daily NOAA Optimum Interpolation 1/4 Degree Daily Sea Surface Temperature Analysis. These are available since 1 September 1981 in gzipped NetCDF format from ftp://eclipse.ncdc.noaa.gov/pub/OI-daily-v2/NetCDF (use wget flags --recursive --level=inf --accept=\"avhrr-only*\" --reject=\"*preliminary*\" --follow-ftp --no-parent).
  • Decompose any drawing data to multiple tables, linked via relations, of the underlying entities: Objects, Branches, Coordinates. Create link tables between Objects/Branches, and Branches/Coordinates to normalize any duplicated branches/coordinates. (This provides a "generic DB" structure suitable for data transfer and restructuring via tables without any specialist "spatial" tools.)
  • Provide dynamic "segmentization" of lines and polygons in the currently used projection by comparing the geodetic path between coordinates with the segment path and inserting new geodetic coordinates where necessary to represent the great circle path. This "Adaptive Resampling" is explained visually by Mike Bostock here: https://bost.ocks.org/mike/example/
  • Allow specifying the constraint of maximum area (of a triangle) or minimum angle (of any triangle's internal angles) when apply constrained triangulation to an Area.

https://github.com/mdsumner

antoniocarlos

609 post(s)
#14-Sep-16 13:40

Hi.

Lately I have had to create seamless mosaics of 100s of small historic aerial images that overlap. (1) These mosaics must have a specific chosen order as some of the images are better (clearer, more contrast ect) than others. (2) The end product should look seamless. That is, when you look at the final mosaic you should not see the boundaries between the original images. (3)

Cheers


How soon?

mdsumner


4,260 post(s)
#14-Sep-16 13:42
  • Read the new NetCDF form of the Level-3 binned ocean colour file format, this is grouped NetCDF file with compound types (analogous to the old HDF4 L3bin files that Manifold 8.0 reads as tables of bin values, bin index, and metadata). Compound types are a NetCDF/HDF way of providing a table-like data structure, so these translate into Manifold tables with no interpretation required, but the integrized bin scheme is described here: http://oceancolor.gsfc.nasa.gov/cms/L3Bins.html and an example for the VIIRS sensor, monthly chlorophyll-a is here: http://oceancolor.gsfc.nasa.gov/cgi/l3/V20160012016031.L3b_MO_SNPP_CHL.nc?sub=bin
  • Provide access to the "clip coordinates" feature in Change Projection, as properties stored with the data after reprojection (rather than just throwing those clipped parts away).
  • Apply ear-clipping algorithm as an alternative to the constrained Delaunay algorithm used when triangulating an Area.
  • Generate lines from a linked table of X/Y coordinates segmentized by intervals within a third coordinate (i.e. date-time, so that the line may be segmentized on equal time intervals rather than Cartesian distance).
  • Export any Surface to GeoTIFF (or BigTIFF, as required).

https://github.com/mdsumner

Dimitri


7,413 post(s)
#14-Sep-16 14:39

Thanks! But, a bit of a course correction... As phrased most of these are great requests / suggestions for features more than they are examples of tasks that you want worked out in a step by step way or via an SQL query.

For example, the first point is cool but once you read the NetCDF format data in, what do you do with it?

Could you give specific examples of what you are doing with the various other points (such as the next to last) so they could be worked out as examples?

artlembo


3,400 post(s)
#14-Sep-16 15:18

I think as a feature within SQL, the implementation of ear-clipping is really important. Manifold 8 does this as a function within the Triangulation Areas transform. Being able to do this from SQL is quite important when attempting to work with large data. For a practical reason for this, see my post here.

mdsumner


4,260 post(s)
#15-Sep-16 00:25

Art, I'm not sure why you put it this way, but the terminology I use is "constrained triangulation", which for the Delaunay criterion means that the criterion is relaxed locally in order to include the input edges (the individual segments of the polygon boundary) and to not include triangles outside the polygon but inside the convex hull of all coordinates. Manifold uses Delaunay *always*, but can apply this constrained version for lines and areas. Ear clipping provides a constrained triangulation as well, but Manifold doesn't use it anywhere afaik. I particularly mentioned ear-clipping so we can explore the difference it would make in a workflow using triangulations. It's generally miuch faster than Delaunay or near-Delaunay, but it's a lot less better result in terms of minimum angle inside triangles.

Ear-clipping would work as well for your large data example since it's only membership inside an area that matters, but for other applications the "quality" of the triangles would matter - like surface generation from points using triangulation - Delaunay, or "mostly-Delaunay after constraints applied" is much better for that.

I think the workflow could be "show that Art's example is as good with near-Delaunay or ear-clipping, but that interpolations from those triangulations are better with the former", but that's getting pretty large in terms of requirements, for a decomposed-to-script/sql surface-generation workflow. In terms of scope, I was thinking that workflows where users could build these algorithms was of interest - and seeing how the relatively simpler ear-clipping algorithm could be built with user tools, but I can see from Dimitri's post that it's not so I'll review them.


https://github.com/mdsumner

artlembo


3,400 post(s)
#15-Sep-16 01:15

Yes, you are technically correct about the definition of ear-clipping. I was primarily focused the generation of triangles in the interior of a polygon, and jumped the gun in my description. Thanks for the clarification And correction. I had one of my students write an ear clipping algorithm last week and we could check some of your theoretical questions – that would be interesting. What kinds of things do you want to co what kinds of things do you want to compare?

mdsumner


4,260 post(s)
#15-Sep-16 02:39

For ear-clipping vs. Delaunay just the raw speed, I wonder if it could be even faster for your big data example. For other applications it's for visualization in OpenGL using R as a middle-tool to read from Manifold.


https://github.com/mdsumner

adamw


10,447 post(s)
#15-Sep-16 12:58

Just to be sure, by ear-clipping you mean ear-clipping areas without holes, correct? (Because if we are doing it for the performance *and* are also considering holes, Delaunay is suddenly not so slow.)

Added: the reason I am asking is that if you want to have just *any* set of triangles as long as they cover the entire area, and you want to have them fast, and are willing to pay for the speed by, say, having vertices that are on the edges, then there are way better methods to have that than ear-clipping - like decomposition to trapezoids (with a trivial conversion to triangles). That also allows for holes, among other things.

mdsumner


4,260 post(s)
#18-Sep-16 00:21

Thanks Adam, I'm keen to explore this. I didn't know that adding holes to ear clipping changed things so much. I also haven't compared Manifold's triangulation speed to Shewchuk's Triangle (in R package RTriangle), but I've been consistently surprised that it just isn't very costly - it's not usually the bottleneck for my workflows in R - though I always expected it would be.


https://github.com/mdsumner

adamw


10,447 post(s)
#18-Sep-16 18:32

Regarding ear-clipping and holes, it's rather simple. When you add holes, the driving invariant - "there's always an ear to clip" - becomes false.

An illustration:

You can try and make ear-clipping work regardless, but it stops being simple (and loses a lot of performance).

Attachments:
ear-clipping-n-holes.png

Forest
625 post(s)
#19-Sep-16 04:38

My request is that there be a range of samples that cater to differing levels of skill. In a previous post I showed how the example for SQL for linear referencing was tough for a novice to get through. http://www.georeference.org/forum/t131712.19.

Apart from that, the other folks are raising my main topics of interest. The biggest issues I have are the ones that make me export data from Manifold to something else so that I can do something that manifold does not do well enough to be worth using. Working with Lidar, working with surfaces that are almost completely flat (still matters for flow accumulation, hill shading etc.) are pretty close to the top of my list.

With SQL, possibly my biggest weakness is being able to paramatise attributes harvested from users to prevent SQL injection attacks. I am still building SQL statements by concatenating strings using VB and this works really well but can't be used in public facing applications.

Manifold's method of importing some xml based formats and cad formats where it created a new drawing for every layer was terrible. QGIS creates a single drawing with a single table and 'layers is just a column in the table. With a single table, the full force of SQL can be used on the attributes.

Using SQL in battle. I once made a topologically correct model of a sewerage pipe network. The pipes which are connected to each pumping station define the catchment of the pumping station and we need to know how much flow will be generated by the future population of this catchment. I created voronoi polygons on the maintenance hole layer for the pipelines and then used vbscript code to draw a connection from each house/potential house which was enclosed by the voronoi polygon to the maintenance hole which the voronoi polygon was centred on. This was just a cheap way of getting the houses connected to roughly the right spot on the pipeline, which was good enough for modelling flows. Another part was edge matching and mosaicing DTM tiles (as md sumner has asked for) so that I could make a single DTM layer as I need to assign an elevation to each house by transferring elevation from the DTM to the house. Finally, I would use SQL to see if any of the houses were below the level of the connection point on the sewerage pipeline which would cause the sewerage to flow backwards! Manifold 8 did this really well. The only grief was that the voronoi polygons on the edge of the set of maintenance hole points were 'unbounded' in the direction that faced away from the cloud. In practice, I had to visually assign houses to maintenance holes around the margins of the point cloud. I was using spatial transfer a lot but maybe those parts could be done in SQL.

Another SQL issue is relating gps points that are off track back to a chainage on a track. The gps point might be a rare plant and I need to be able to say that the plant is 35 m from the road centreline at chainage 62.5 kilometres. Tim has previously provided the SQL that does this but it breaks my brain trying to figure out how he uses SQL to find the closest point on line to the plant, then draw a perpendicular line to the plant and measure the length of the line.

A final one which so ugly it scares me is finding vegetation offsets for mining projects. This is layers of SQL deep and the trick is to use several layers of filtering to create a final result. Some of the filters are:

  • if riparian vegetation is cleared the offset must be riparian and be associated to the same stream order or greater than the vegetation cleared (spatial and attribute);
  • the offset must have an area to perimeter ration of better than 1 to 5 (1 km square but perimeter of less that 5 km);
  • the offset can't have been claimed by anyone else (spatial overlay with claimed areas);
  • the offset must be available (can't claim a nature reserve or other existing mature vegetation);
  • the amount of land needed for the offset is calculated by an equation that says you need 3 units of cleared land for each unit of vegetation cleared but if your regrowth is so good it looks like forest then a one to one ratio is fine. However the government's way of calculating the ratio takes pages and they expect you to use their method. The data used is a combination of field survey results, size and shape of area and conservation priorities assigned to each type of vegetation. This is the sort of thing that FME is supposed to do well. I did a course and their examples were similar (main roads was showing us how use FME to create predictive habitat models based on a database of fauna sightings and known behavior (follow creeks etc) to try and work out where to spend money preventing big animals getting run over)

Another unrelated point. I still use an visual query builder (ms-access) to see what the SQL statement looks like before rewrite the query in MySQL workbench or whatever. For really complex queries involving many tables, I like using the SQL created by a visual query builder as a guide, to take the 'undifferentiated heavy lifting' off my shoulders. I am wondering if there are any aids to drafting complex queries in Radian?

forest

adamw


10,447 post(s)
#19-Sep-16 07:11

Thanks!

Regarding CAD formats:

Manifold's method of importing some xml based formats and cad formats where it created a new drawing for every layer was terrible. QGIS creates a single drawing with a single table and 'layers is just a column in the table. With a single table, the full force of SQL can be used on the attributes.

We'll look into what we can do there, that's very useful feedback.

To be clear, we are talking about DGN / DWG / DXF, correct? Something else?

Regarding aids to drafting complex queries in Radian - yes, we do have some. (No further comments from me on that, however, that's not the right thread.)

Forest
625 post(s)
#19-Sep-16 11:33

Thanks Adam,

Importing a kml file and making a separate drawing for every folder is terrible. Make one drawing for each kml file and make each folder path an attribute in a field called 'folder'. QGIS uses this method for kml and for CAD (dxf, dwg, dgn etc). Most of the CAD drawings I see have many tens of layers and most layers only have one or two features in them but some layers are full of essential detail. Imagine a proposed quarry site. There will be one layer with streams, another with contour lines, then a bunch that don't make sense unless the other layers are present such as spot heights, survey peg locations, groundwater bore locations etc. With QGIS, you simply symbolise by layer and you can quickly see the layers you need and then use a filter to only select the layers you need (i.e. the layer names in the layer field). If that is too much effort, then I can just uncheck the categories that I don't want to display in the layers pane (shows layers and the categories within layers) Changing the symbology of each layer category to be similar to the CAD drawing is trivial. See attached drawing.

QGIS is slow to start up. That is currently the main area where M8 still shines and also for doing complex spatial queries and attribute transfer rules.

Attachments:
CAD_in_QGIS.jpg

mdsumner


4,260 post(s)
#09-Feb-18 05:19

I happened to explore this some more recently following links from here: https://github.com/mapbox/earcut.hpp#earcut

Particularly this one: https://www.geometrictools.com/Documentation/TriangulationByEarClipping.pdf

I didn't reallize that one way into the hole problem is to link each ring by just one segment. Just FYI in case you're interested, thanks!


https://github.com/mdsumner

artlembo


3,400 post(s)
#15-Sep-16 13:47

In terms of the point-in-polygon test I illustrated above, I think the Delaunay triangulation will be faster in solving the problem. The reason I think that is because the Voronoi dual criterion (the dual of every Delaunay triangle is a Voronoi polygon) and the adherence that the circumcirles of each triangle contain exactly 3 points assures that you maximize the smallest angle in every triangle. Therefore, you have more a balanced triangulation, and avoid long, skinny triangles wherever possible. This means each triangle likely covers a smaller overall area and thus has a smaller footprint for the initial check to see if a point falls within its bounding box. So, I think it becomes more efficient when using the spatial index.

Of course I'm not 100% sure, but it would make a fun assignment for my class to try out :-)

I think they'll be ready to try this out next week, and I'll let you know what we discover.

mdsumner


4,260 post(s)
#14-Sep-16 15:37

For the NetCDF L3-binned format, a workflow is to obtain a season's worth of data (all days between December 1 and March1), import all of them into a single table in a single project, appending the bin values for each file

then summarize via

  • group_by on bin number
  • sum the value-sum, value-ssq, value-count

then create an equal-area grid across the southern hemisphere at 50km resolution and

  • classify the bin values by 50km cell
  • group by 50km cell
  • calculate 50km cell mean value and variance from the sum, ssq, and counts

(then, depending on scope, apply the above workflow to all available seasons.)

In terms of scope, I wonder if scripting against the NASA ocean colour getfile tool for obtaining the files is something the example should show, or not? (It's quite a lot simpler now that it's in NetCDF4 with internal compression, and there's also no need for the MODIS and later sensors to spread a day across multiple HDF4 files with their 32-bit index limit. )


https://github.com/mdsumner

cab76 post(s)
#15-Sep-16 03:45

[Deleted] Please stay on topic.

Dimitri


7,413 post(s)
#15-Sep-16 07:42

Please stay on topic. This thread is for worked examples, not for requesting features.

bclement
275 post(s)
#14-Sep-16 17:36

When I think of Spatial SQL examples, I immediately think of Art’s great book “How do I do that in Manifold SQL (Manifold v.8).” In addition to updating his examples for the new platform, perhaps you could “fill in the blanks” where Art had to admit that things like contour generation or raster resampling were not supported by the Manifold v.8 SQL engine. Of course it does occur to me that Art may want to write that book. Either way, we win!

artlembo


3,400 post(s)
#14-Sep-16 17:43

ha, ha. Yes, I'm about half way through a new book :-)

cab76 post(s)
#15-Sep-16 03:52

[Deleted] Please stay on topic.

geozap
264 post(s)
#15-Sep-16 06:49

[Deleted] Please stay on topic.

tonyw
736 post(s)
#15-Sep-16 18:57

Here are my requests for examples. At times I'll be working Manifold GIS in real-time at a meeting. As the discussion flows, people will ask questions and I try to do the analysis in Manifold as everyone is watching on the big screen. So it's a matter of working quickly and accurately and minimizing the amount of trial and error. I can see the following examples as useful to demonstrate RS's and Manifold 9's SQL capabilities. Simple tasks that can be done quickly. I'll pose the examples in the form of the questions I get asked.

1. Where are the flatter areas in that region suitable for development?

I'll ask their definition of "flatter" and determine its 15% slope or less. My workflow is to start with either contours (vector) or DEM. I'll use thematic formatting and make 3 slope classes: flat (0%), up to and including 15%, and over 15%. Flat I colour blue, <0% to 15% I'll colour white, then I can't make transparent in a surface so I colour >15% dark grey. Can similar results be arrived at by SQL query?

2. Show us past forestry logging, which areas were logged first and which logged more recently?

My vegetation layer has the date of logging expressed as yyyymmdd. I can't do this task with the crowd watching on the big screen. I'm not good enough with SQL and it's trial and error to get the syntax correct. I'm hoping RS or Manifold 9 can help me out perhaps by being menu driven. So now, at a break, without the meeting participants watching, I'll create a new column, parse out the LEFT 4 characters from the date, populate the new column with just the year. Then use thematic formatting with intervals to put the logging years in 5 year classes then apply a colour theme to show oldest and newest logging. So this is a relatively simple procedure in parsing that would make a good example to show how easy (and fast) it could be done.

3. Combine the last three iterations and see what is the total area.

A committee will be selecting areas of interest. There may be several iterations, each one a variation on the one before. The final outcome sometimes as an amalgamation of iterations. An intermediate task is finding duplicate areas in a drawing. Visually, duplicate areas sit right over top of each other and duplicates are not discernable. My work flow is to select an area by drawing a box around single areas then go into the table to see how many records have been selected and delete any duplicates. This can be tedious if there are many areas. The meeting participants think asking for total area is a trivial question but see me fiddling to find all the duplicate areas. An SQL example showing the ease in cleaning up data would be useful.

I hope these are the types of examples you are looking for, they are simple but typical of my most common tasks.

adamw


10,447 post(s)
#15-Sep-16 19:14

These are great examples.

Thanks!

artlembo


3,400 post(s)
#15-Sep-16 19:47

Tony,

That really is a cool example from the field of natural resource management. If you send me you data (.map), I will create queries for you to do all 3 using the current version of Manifold - if you would be ok with that, I can post the solution here and on my blog.

I would also be willing to help out the cause and try running the same queries in Radian and let you know how it goes.

tonyw
736 post(s)
#15-Sep-16 20:47

Hi Art,

Thanks for the offer. Let me see what I can pull together, most of the data I'm using is available publicly so not proprietary. The other I can whip up a mock up such as the task with over lapping areas.

tonyw
736 post(s)
#19-Sep-16 18:28

Hi Art,

I've assembled a map file with two scenarios to demonstrate SQL in my examples #2 and #3 above and #4 below (size of area(s) in hectares).

a) Scenario: I chose a small random area in British Columbia where logging has occurred over a number of years. I downloaded the vegetation information from here (freely available, use the Guest option)

https://apps.gov.bc.ca/pub/dwds/home.so

The "HARVEST_DATE" field has the year of logging as yyyymmddxxxxxx. The task is to parse out the first 4 digits for the year of harvest.

b) Scenario: through multiple iterations a committee has settled on six Areas of Interest (brown areas). What is the total area (hectares)? Issue: in re-working previous iterations there may be duplicates of areas caused by copying and pasting between iterations. What is the total area without double counting duplicate areas? There are six areas visible but there are 11 records in the table.

CRS is BC Albers (in Manifold it's National Grids > Canada > British Columbia) or ESPG 3005

Attachments:
Example for SQL exercise (Manifold RS and M9).map

artlembo


3,400 post(s)
#19-Sep-16 18:55

I hope I understand this correcty. For question 1:

SELECT *

FROM [Vegetation Resource Inventory Drawing]

WHERE left([HARVEST_DATE],4) = "2005"

for question 2, while there are 6 areas visible, some are not identical that others. So, this query will give 7 objects:

SELECT [Geom (I)] 

FROM [Areas of Interest Final Compliattion Drawing] AS A

GROUP BY [Geom (I)]

the object in the upper right has a smaller polygon that is inside of it. Do you want that one included? Or, do you want the last 6 objects included, like this:

SELECT TOP 6 [Geom (I)]

FROM [Areas of Interest Final Compliattion Drawing] AS A

ORDER BY ID DESC

In either case, you can simply wrap the query into another SQL query:

SELECT Sum(Area([Geom (I)],"m")*0.0001) AS hectares

FROM

(

  SELECT TOP 6 [Geom (I)]

  FROM [Areas of Interest Final Compliattion Drawing] AS A

  ORDER BY ID DESC

)

Is that what you were looking for?

This is in 8, I can do it in Radian, but I have to run to class.

artlembo


3,400 post(s)
#19-Sep-16 19:07

oh, what the heck. Students blow off class, why can't I (plus, I'm tenured, what are they gonna do about it). This is what it would look like in Radian:

SELECT Sum(GeomArea([Geom (I)],0.1)*0.0001) as hectares

FROM (

  SELECT [Geom (I)]

  FROM [Areas of Interest Final Compliattion Drawing Table]

  ORDER BY ID DESC

  FETCH 6

      )

tonyw
736 post(s)
#19-Sep-16 19:38

Thanks Art, you can assure your students that your time is going to a very worthwhile cause!

On #1, following your example, I can now reduce my workflow to 3 steps which I should be able to do in a meeting. The folks will be impressed.

1. Add a new column "Year_Logged"

2. Write and run the query

UPDATE [Vegetation Resource Inventory Drawing Table]

SET [Year_Logged]=LEFT([HARVEST_DATE],4)

3. Apply thematic formatting using the HARVEST_DATE field


With the duplicate areas the end result is I would want the total area of the areas on top and ignore the areas that are covered up, even the smaller one in the top right. Now whether I have to find and delete all the duplicates or use the TOP function to only sum the areas on top and ignore any that are covered up, as I understand it, the resulting sum of area would be the same.

tonyw
736 post(s)
#15-Sep-16 20:28

Another potential SQL example

4. "What is the size of that area"?

One approach is I will open the table and Add an Active Column then write a script to calculate hectares from square metres then we wait while the column recomputes. A great SQL example would be to create that column and populate it with hectares. Plus at the same time have the displayed figures formatted to say, 1 decimal place and with commas separating the '000s.

If the folks at the meeting want the answer immediately and to keep the flow of discussion going, I will jusst double click on the area, turn on the intrinsic values, and folks at the meeting will mentally move the decimal place over 4 places. A more elegant presentation of hectares would be nice.

Better yet, though this is getting into feature requests, is to be able for RS and M9 to display in desired units, that are variations of native units, by a check box in Options (hectares or sq km instead of sq m).

tonyw
736 post(s)
#15-Sep-16 23:37

P.S. Linking to a previous discussion on calculations in columns and displaying hectares vs square metres here http://www.georeference.org/forum/t132471.15 Finding out about Viewbots gives me another tool for displaying data.

Graeme

990 post(s)
#16-Sep-16 05:49

Linked drawing sub-set via relations and SQL

I often have requests to show a range of post / zip code areas thematically formatted with a calculated quantity e.g. total dollar value sold per zip for the period represented. Raw data is typically supplied as an excel spreadsheet, zip in one column and quantities in other columns. The objective is to create a map showing where all the zip codes with sales are and thematically format them on the the grouped sum of the quantity.

Existing workflow in 8 is:

  1. Import xls(x) into 32 bit Manifold, copy and paste result as a table into 64 bit project. Probably exit 32 bit Manifold.

  • Tools / Server Console, import copy of postcode / zip areas from configured SQL Server based Enterprise server. Could be import from just about anywhere in just about any format..

  • Edit / create an action query to sum / count records grouped by zip from new table and create a relation in the zip drawing table to add the aggregated values based on common zip value OR add a new column to zip attribute table and edit an update query to populate it with the aggregate values.

  • New query using "options coordsys" clause to select all records where relate / updated column > 0

  • File / link / drawing / this project change Type to table with geometry column, navigate to query from 4. Result is a clean new drawing of only zip areas with a value.
  • Change linked drawing name from ".... Query 2" to "....Dwg"
  • Format linked drawing to suit and proceed.

    Not particularly onerous as a workflow. Writing it down step wise makes me think 3. and 4. could probably all be accomplished with one query using a join. The numbered points seem to appear as bullets after 1..

  • HMS
    185 post(s)
    #16-Sep-16 18:59

    I really don't know if this example fits in here, but it's one of the most common actions that I do within Manifold GIS, and it's related with the "visible area" analysis from multiple points.

    As an end result of this process I'll generate a drawing that allows me to know the specific point that is visualized within a defined area (the point that originated the visible area) and the sum of how many points are perceived within the same area (when different points generate overlapping visible areas).

    The rough workflow I adopted to pursue this goal is: 1. perform an individual "visible area" analysis to each point; 2. edit each "visible area" table to add the point references (including the "1" value for each visible area); 3. union of all the drawings (one at each time) with the Topology Overlay dialog in order to achieve a final drawing with the number of visible overlaps in the table; 4. editing the table to get the sum of all the visible points.

    If Radian allows a faster methodoly to perform a multiple "visible area" analysis with multiple points would be great.

    dchall8
    1,008 post(s)
    #16-Sep-16 22:44

    I work in an appraisal district in Texas and have been using Manifold for 12 years. Once a week I export parcel files for use by co-workers who use both Google Earth and Pictometry. Thanks to the experts on this forum I have coded a field for GE that includes HTML coding to format the appearance in GE. By exporting that column there's a ton of value added to Google Earth. Recalculating the GE field takes about 5 seconds and the export goes very quickly. Loading GE takes far longer than all the processing.

    For the Pictometry export the flow goes like this:

    1. Update my data (about 5 minutes each time)

    • Export latest data from DBMS to .xlsx using Microsoft SQL Server Management Studio 2012 (I did not write the query, nor am I qualified to.).
    • Open .xlsx in Excel and export to .xls for Manifold 32 bit
    • Import .xls into Manifold 32 bit
    • Copy imported table
    • Paste table into Manifold 64 bit for use
    • Delete old related table and relate new table
    • Use Manifold for another week with data from the new table

    2. Export Drawing from Manifold to shape file (about 10 seconds)

    3. Import shape files to ArcMap using the historical .prj file that I know already works. For some reason the .prj file exported from Manifold does not match up with ArcMap. (about 2 minutes)

    4. Use ArcToolbox>Geocoding Tools>Data Management Tools>Features>Repair Geometry. For whatever reason Pictometry will not work with raw export from either Manifold or from ArcMap unless the ArcMap Repair Geometry tool is run first. (about 2 minutes)

    5. Export data from ArcMap to get new shape files compatible with Pictometry. (10 seconds)

    6. Upload files (.shp, .dbf, .prj, and .shx) to Pictometry using their online tools. (about 5 minutes with formatting).

    I have LiDAR data for my county. If the Radian program works with LiDAR files, I will use that to identify buildings hidden under tree canopies in our aerial imagery. I would also use the LiDAR to draw new "topo" maps for the Realtors and other users who want them. Or perhaps a topo export for Google Earth would work for them.

    If I knew more about making map books, I would use Manifold to make them for my co-workers. I follow those topics on this forum, but have not had time to figure out the SQL I've seen. My county is 800 square miles, so fast automation would be important. Printing would be to Acrobat.

    All day long I split and join parcels as demonstrated in Art's original (2004) video help tools. I also update the shapes of our parcels using the invaluable PlotTraverse add-in to Manifold. These tasks are my daily routine. As far as I know I am the only person in Texas using Manifold for an appraisal district. Everyone else uses ESRI or contracts with someone using ESRI.

    Another task I do in batches a few times a year is to prepare neighborhood maps for printing to Acrobat. We have 4 appraisers who each have 15 neighborhoods to visit. I have a separate .map file for that, because all the print settings are set up.

    1. Copy latest neighborhood parcels from my main updated file and paste to the neighborhoods file.

    2. Create new labels for the neighborhood. Adjust label sizes for large scale printing.

    3. Set new colors for neighborhood parcels based on block numbering.

    4. Create new layout for the neighborhood using the template built in.

    5. Create and adjust legend and titles for the layout.

    6. Use Page Setup to print 3 feet by 4 feet to Adobe Acrobat. I have one landscape and one portrait template I use to copy and paste.

    7. Print.

    Rakau110 post(s)
    #17-Sep-16 01:04

    My contribution if suitable.

    When creating irrigation runs for farmers. Take a paddock and:

    1: Create a boundary 7.5 meters into the paddock.- Used to clip the irrigation lines later.

    2: Create parallel lines every 15 meters across the paddock.

    3: Divide the lines into 2,3 or 4 equal lengths ( depending on farmer requirements)

    4: create parallel lines 7.5 meters either side of the lines created in 3 above

    5: Clip all the lines drawn in 2: above

    6: delete unwanted lines

    7: Name each line.

    Naming of lines in 7

    There are hydrants placed across the paddock. Some have 1 outlet, others have two outlets. These are named as A, BC, DE etc

    The irrigation run will start at one end, closest to hydrant with hydrant name and run such as A1. Lets assume there are 3 pulls across paddock, so we would have A1, A2 A3 covering the first 15 meters inside the paddock. Next run will be A4,A5, A6. Then A7, A8 & A9

    When the A run is extended to approx half way between hydrants A & B, the runs will change to B, now working backwards to the hydrant. B9, B8, B7; B6, B5, B4 ; B3, B2, B1

    Illustration to try and explain attached

    Attachments:
    HydrantRun.PNG

    Dimitri


    7,413 post(s)
    #17-Sep-16 07:18

    These are all wonderful examples! Please keep them coming... the more, the merrier!

    Gustavo Palminha

    1,010 post(s)
    #19-Sep-16 12:20

    What comes to mind right now would be to have many built in interpolation algorithms (for point to raster data interpolation) as seen using common "heat map" samples.

    This could open the door to other tasks.


    Stay connected:

    Linkedin >> http://www.linkedin.com/in/gustavopalminha

    Twitter >> http://twitter.com/gustavopalminha

    Other >> http://www.spatialmentor.com

    Dimitri


    7,413 post(s)
    #19-Sep-16 13:39

    What comes to mind right now would be to have many built in interpolation algorithms

    Ahh... that's a feature request, not a specific example you would like to see worked. Please stay on topic in this thread.

    For feature requests (always welcome), please send in a suggestion as advised in http://www.manifold.net/info/suggestions.shtml

    dale

    630 post(s)
    #21-Sep-16 13:32

    What comes to mind, is import a point drawing, then copy paste as surface, using a selected number of interpolation algorithms in Radian, resulting in one surface per algorithm, as layers in a map window.

    Useful in learning the differences in output, and hopefully not in choosing the one that "looks right"

    tjhb
    10,094 post(s)
    #21-Sep-16 23:23

    That's still a feature request.

    Try saying what you actually do now, with whatever tools you have.

    tjhb
    10,094 post(s)
    #22-Sep-16 00:04

    ... Manifold 8, QGIS, GDAL, PDAL, ArcGIS, Arc online tools, Global Mapper, Surfer, Excel, GRASS, Palantir, whatever.

    tjhb
    10,094 post(s)
    #22-Sep-16 01:41

    Whatever tools you use, if you need the space of a new thread, just start one (I'm going to).

    Call it something like "Data workflow example for Radian Studio".

    The more of these, and the more varied, the better the new product can be.

    dale

    630 post(s)
    #12-Oct-16 00:46

    Real world task: We use transfer selection to clip and export a number of data sets for use within Caris Easy View. (until mfd 9 comes out...)

    The task: Import a surface, a raster, and a number of drawings. Using an AOI drawing, select the AOI, transfer the section to the surface, the raster, and the drawings. Invert section and clip each, then export the resultant clipped to AOI surface, raster, and vectors layers (neat if this was a zipped folder)

    We do get asked for subsets of many State wide data sets, so this process would be useful.

    tjhb
    10,094 post(s)
    #12-Oct-16 02:57

    Global Mapper quickly pays for itself with this sort of task (load data, then one very fast step to export with everything clipped and cropped). I agree that it would be wonderful for Manifold/Radian products to make it similarly easy.

    Forest
    625 post(s)
    #21-Oct-16 01:51

    I would like to see some volume calculations between surfaces. This has come up a few times in my career including calculating the extra volume that would be held in a water supply dam if the wall was raised and also calculating the volume of mine spoil heaps. The input data in both cases is CAD plans with contours, which need to be converted to surfaces in Manifold.

    dale

    630 post(s)
    #21-Oct-16 12:09

    I'll plus one you on that Forest, did a job surveying an estuary using a name brand fish finder, then processed the recorded sonar data in DrDepth a cheap and cheerful sonar analysis package.

    We wanted to know the volume of each of the deep pools within the estuary, and a volume depth profile. The user could input an interval (metres) and the query would return volume per interval.

    Another query would be to return surface (in my case river, or cliff) profile at a preset intervals.

    KlausDE

    6,410 post(s)
    #21-Oct-16 15:52

    Radian Studio is not the GIS with surface tools. For Radian as the data shoveler an example of volume calculations between 3D vector data would perhaps cover a more general approach and save the additional step to create a surface.

    How do you cookiecut objects in 3D perpendicular to objects, that lie on a 2D plane in space - with the simplification of Z = 0 for the 2D plane ?

    How do you calculate a volume between 3D objects ?

    ...


    Do you really want to ruin economy only to save the planet?

    tjhb
    10,094 post(s)
    #22-Oct-16 01:51

    How do you calculate a volume between 3D objects ?

    I suppose, we would create a triangulation of each dataset (A and B), then subdivide each triangle in A with each overlapping triangle in B, and vice-versa (Identity overlay, in 2D), then calculate surface area for each triangle, then subtract each lower surface area from the matching upper surface area, then multiply each difference by the triangle's projected 2D area.

    A complication is to allow for data where A is not wholly above B (or vice versa), i.e. where some triangles intersect. Intersecting planes would need splitting as well, and treating separately.

    I think a simpler example would be to use raster data (surfaces), of matching extents (to start with). That would be much easier.

    atomek

    422 post(s)
    #22-Sep-16 15:10

    A good proportion of my work now revolves around computing network flows (of people / vehicles).

    Manifold 8's Business Tools has Shortest Path / Optimal Route tool. On many occasions it drew errorneous lines and it's overall slow compared with what I'm using instead i.e. MapInfo Route Finder. Hoping that ShortestPath function is/gets improved (vs M8) and exposed in Radians SQL / Scripting functionality I write this "example". If you see it more as a feature request, please do delete this post. My process is as follows:

    1. Having 3 drawings: Origins (points), Destinations (points), Network (lines)
    2. Split both point drawings into pieces, each with ~300 elements, more tends to lock MapInfo
    3. Export all drawings as MIF
    4. Import all into MapInfo
    5. Batch Distance Matrix in MapInfo for every combination of my Origin and Destination files
    6. After each combination export Results as MIF
    7. When all is done import Results all MIF's back to Manifold
    8. In Results Table link to Flows Table containing all travel numbers through Table>Relations... and pick columns for e.g. each mode of travel
    9. Duplicate the Network drawing and Normalize it (so that later generated centroids don't fall on two lines when the Network contains over/under passes)
    10. In that Normalized Network drawing select all lines made of just 2 geoms
    11. Add 1 segment to these selected lines
    12. For each line in the Normalized Network do BoundingBoxes(Buffer(Coord([geom],((coordCount([geom])-1)/2),1)) - thats to generate a "Middle Box" in the middle of each line
    13. Unlink the Middle Boxes linked drawing
    14. Remove all Middle Boxes that touch more than one line from Normalized Lines
    15. Copy over one object from Results drawing and paste into Middle Boxes so that the Middle Boxes Table contains all the columns I will want to transfer
    16. Do Spatial Overlay and transfer from Results to Middle Boxes where Lines intersect with Areas
    17. Do Table>Relations for the Normalized Network and Middle Boxes to get the accumulated volumes from Results to Normalized Network

    Viola!

    Even in MapInfo the shortest path generating algorithm is not perfect creating lines that are sometimes missing some geoms or slightly offline, that's why I need Middle Boxes and not Middle Points.

    My hope is to see a worked example for 1-7 if Shortest Path can be done efficiently in Radian and 9-17 so values from Routes running over the Network can be accumulated on the latter.

    #27-Sep-16 18:37

    Divide a digitized polygon into a grid of specified size (with a minimum and maximum size (acres). We use this to divide a farm field into equal sized areas for soil sampling.

    Right now, we do this manually with a (borrowed and modified) tool that splits the selected polygon with a selected line, then deletes the cutting line. We manually check the acreage and split again or combine polygons to obtain the correct area.

    For larger fields, we may create a grid (solid lines) and use them as a template or guide.

    Each grid section is named by letter (A-Z, if there are more than 26 grid sections in one field, start with AA-AZ, then BA-BZ, etc.)

    Thanks.


    cbrown

    dchall8
    1,008 post(s)
    #27-Sep-16 19:05

    Something else I do on a weekly basis (or I should do but it's too fiddly), is reformat the fields in my database.

    1. Import new database update into Manifold 32 bit
    2. Copy table from 32 bit and paste to 64 bit
    3. Un-relate the old table and relate newest table to parcels using parcel ID number key.

  • Format the value fields to show comma separators with 0 places behind the decimal and right justified in the column.

    Then after I relate the new table, I lose the color theme of my parcel drawing and have to redo that.

    This is probably simple in SQL with the current Manifold, but I am a pre-novice on writing my own queries.

  • tonyw
    736 post(s)
    #27-Sep-16 19:27

    Hi dchall8

    Unrelated to SQL but does it help to save the thematic formatting to then apply to the newly imported data after importing it? See attached jpeg showing the save and load-from-file icons.

    Attachments:
    Save_Thematic_Formatting.JPG

    dchall8
    1,008 post(s)
    #28-Sep-16 17:22

    Thanks, Tony. I have used saved themes before but not since 2012 in previous work. In this case I use the Unique Values method for formatting. The problem is a few of the unique values change every week. Which is to say the key in the relational database is a moving target. I just saved the theme and will try it when I update the database this Friday.

    tonyw
    736 post(s)
    #28-Sep-16 18:50

    Are the map users looking for or expecting the colour scheme need to be the same from update to update? If not, the interpolate option in thematic formatting gets me out of the situation when the categories of data change. I set my desired colours at both ends of unique values then click on interpolate and I get a spectrum between my two end point colours. In my case it's months between meetings when I show the same map and people just look at my new Legend.

    dchall8
    1,008 post(s)
    #29-Sep-16 17:32

    I'm the only one who sees these colors. There are only 2 colors. I have green color for parcels which have ID numbers matching an ID number in the database. If the ID number is 0 or does not match an ID in the database, then it shows up brown giving me a red flag that I need to update the ID number on that parcel.

    The problem with the theme is that every ID number is specifically colored green. If the next database upload has changed ID numbers, those will show up brown. Thus the red flag is checked; however, the theme is going to be the same even after I correct the ID on the map. The theme becomes a historical snapshot of ID numbers.

    Maybe there's another way to do the theme...which would be a discussion for another forum topic.

    tonyw
    736 post(s)
    #29-Sep-16 21:41

    Hi,

    This still might be the basis for an example of SQL in Radian Studio or M9. I'll give the narrative and leave it to the SQL experts to show how it's done. As I understand it, you have a main database that doesn't change and then you have a second database that you import and relate to the main database. What if in the main database you have a column that is named "Has_Parcel_ID" which is binary, either has the value 1 or 0 (or "yes" or "no"), the value of which is used in thematic formatting to colour either green or brown. The SQL query than compares the values in the main database to the imported database and updates the value in the column "Has_Parcel_ID". Once the "Has_Parcel_Id" column is updated then the thematic colouring will reflect which new parcels have ID numbers or not. As the column "Has_Parcel_ID" is in the main database, the query doesn't change nor the settings for the thematic formatting.

    I don't quite follow the part that the theme becomes the historical snapshot of ID numbers. Could it be that before updating the column "Has_Parcel_ID", what shows as brown are new parcels or parcels lacking parcel ID numbers? But you mention that all the incoming parcels are coloured brown?

    Anyways, for the SQL experts, is this an example scenario? The scenario is to import data or do a relational database and identify new items, new parcels, or new records otherwise lacking particular data and the query quickly remedies the deficiency?

    dchall8
    1,008 post(s)
    #05-Oct-16 15:10

    I'm attaching an image of the screen. The key field in the drawing table is PROP_ID. A field with the same name is imported once a week to update changes to ownership as well as changes to a few PROP_ID numbers. The imported database has a field, prop_id, which, when related to the parcel table is recognized as a duplicate name and comes in as prop_id 2. Then I can start the coloring theme. First I color everything green on the drawing. Then I go to Theme which defaults to PROP_ID field. I change that to prop_id 2, Unique Values. At first everything is green, but I change the default and 0 valued elements to brown. By doing it this way the individual parcels with a matching prop_id 2 number remain green while the zero value and the unmatched PROP_ID numbers default to brown.

    In the picture you can see the effect. One of the PROP_ID values was 0 which does not match any records in the imported table and is colored brown. Two other PROP_ID values do not match the imported records and are colored brown. Six of the selected parcels have PROP_ID values matching prop_id in the imported table and are colored green. Thus I can visually scan the drawing and see which parcels need my attention to correct the PARCEL_ID numbers.

    You can also see the prop_id 2 numbers listed as green in the theme. Every prop_id 2 number is listed in the theme. If I save that particular theme, only those prop_id 2 numbers would be saved. As a few of the prop_id numbers change from week to week, the theme needs to be updated. I don't see how one theme could be saved for reuse without updating.

    Attachments:
    PID demo1.jpg

    tonyw
    736 post(s)
    #05-Oct-16 18:48

    Hi,

    Thanks, the image helps. If I understand, it seems the updating of property ID numbers happens not in Manifold but back in the external database or data source. Your process uses Manifold to identify properties in the external database which have no prop_id numbers. The colouring scheme (green, brown) tells you which records in the external database you need to update and enter a property ID number in that external database? Then the next time you import the external database, the ones you previously updated are coloured green by virtue they now have a property ID number. The updating of the prop_ID2 field is not done in Manifold, correct?

    The SQL folks might have a way of editing the external database from findings in Manifold and could still be an example in Radian Studio and/or Manifold 9.

    If property ID numbers are missing from some properties, is there another field to use to make the relationship in Manifold when you import the data? It won't be property ID number, because the Prop_ID is zero in some cases. There must be a field that exists both in the Manifold table and in the incoming database to make the match in the relationship?

    tonyw
    736 post(s)
    #05-Oct-16 22:13

    A follow-up question, after importing the external data, is there any comparison being done between data in the columns in the original table to the data in the imported table? Or is Manifold simply using the column Property_ID2 for thematic colouring and to show the new properties that don't have Property IDs in the external database?

    SQL can do queries and update cell contents:

    1. For each record, check that the contents of Property_ID2 and Property_ID are the same. If the same do nothing.

    2. If different, copy and paste the contents (update) of Property_ID2 with contents of Property_ID.

    Then export your table back to whence it came.

    Alternatively, link the external database to Manifold. You save your "history" for posterity before running the SQL query. After running the query, the external database is updated. This eliminates the steps of exporting from the external database and importing to Manifold, the going back to the external database and updating new properties with Property IDs.

    dchall8
    1,008 post(s)
    #06-Oct-16 22:45

    Your understanding as stated in the 18:18 time-stamp reply is correct.

    Using Manifold as a source for mass updates to the master database is intriguing. I find mistakes all the time. Some are as simple as typos (mass typos) but there are also incorrect, inconsistent, and missing entries. Some of the inaccuracies are subtle and some are egregious, but using Manifold makes it fairly simple to find the full range of them.

    The master database is very proprietary. We lease it from a contractor out of Dallas. Although I can make changes within my purview, I cannot make mass changes. When I see something worth changing en masse, I export a spreadsheet with the changes as simplified as I can make it. Usually it's two fields including the PROP_ID number and the field to be changed. This is a little country town where it's hard to find sophisticated database manipulators - me included.

    tonyw
    736 post(s)
    #07-Oct-16 01:53

    Hi,

    It seems there could be a role for Manifold's SQL capabilities to streamline your workflow. One of my rules is "if it's already in the computer, don't re-type it". Manifold SQL can populate columns with info in other column(s) including concatenating contents of multiple fields into one field. And it can export tables in various formats including .csv You can either send the .csv file back to the contractor or as you say, you have some ability to update records in the proprietary database and can update yourself.

    So you could do as you do now. At the point of importing, before running any Query, the brown shows the properties that have no property ID number. Then the SQL Query can "copy from Prop_ID" to "Prop_ID2" WHERE Prop_ID2 is blank or zero. The Query can at the same time update a second column used to mark which records now have Property ID numbers. Say the added content is "Updated", so those records that are marked "Updated" have updated Property ID numbers.

    Manifold SQL is very fast. I have a Table with 36,961 records. My Query was simple, extract 4 digits for the year from a string making up YYYYMMDD and paste the YYYY into a new column. The Query finished it's work in a second or two, so fast, I had to check that it ran at all. Once you have a Query setup (and you can reuse the same one from week to week) from the point you import your new data into Manifold 32 bit, run the query, do some quality assurance checking, to the time you export a .csv file with the changes, you should be done in 15 minutes.

    jockeryl
    178 post(s)
    #22-Oct-16 02:41

    I have around 30,000 vehicles sending GPS information and Status updates every 30 seconds.

    The Status updates are Speed, Availability, DateTime, Location etc.

    Today we process the data on the fly and push it to web apps for 'real-time' visualization on a map.

    That is working very well for the last 3 years.

    This data is aggregated by the end of the day by combining the path for each vehicle and calculating the distance as straight line distance.

    We also do a Point in Polygon with 1,000 underlying areas to aggregate where the Vehicle was at each time of the day. In addition we snap the GPS location to the nearest Road. 80 million times per day 24/7.

    We are now evaluating how to create an approximate route using OSRM between key points to gather a more accurate real distance traveled during a shift.

    With the distance we can get the income earned and more accurate know which toll fares have been paid based in the route. So we need to calculate which Tolls the route was passing and add the costs.

    We also need to know how long each job was, where the vehicle started and stopped each job and if it was a paid job, how much they earned etc. All that can be calculated from the raw data attributes.

    This we can do quite well in real-time for the 80 million points collected for a 24h shift, but it's becomes very cumbersome to do aggregates and spatial analysis over a year or the last 3 years data we have.

    3*365*80mil -> 90 Billion data records becomes a pain and we are now looking at setting up a Hadoop/SPARK cluster to distribute the aggregation calculations and clustering with custom Spatial processing in Scala. We would like to do more spatial queries on this type of huge data sets.

    The customer is looking at ESRI Hadoop Geoprocessing solution that they promise for next version. We have yet to see it, but the logic is sound.

    We would like to see a good story for Radian/Manifold to work well with Big Data platforms like Hadoop, HIVE and Spark, to be able to read/save to HDFS as JSON, GeoJSON, CSV, ORC/Avro/Parquet.

    In the world where IoT (new fancy word for old fashion device data collection like GPS tracking and SCADA) becomes more pervasive, the need to quickly process huge amounts of data with SQL and Spatial tools becomes very important. I have not even mentioned similar Lidar requirements.

    I can see Radian to be a middleware tool in the ETL chain between ingestion of data and before saving to HDFS/SQL/Disk.

    I also would like to see that Radian possibly could be run in a highly parallel fashion on a cluster, Hadoop or otherwise in the cloud as Azure HDInsight or Azure Batch HPC jobs.

    Now that .NET is running on Linux I think it would be a great idea to create a small part of Radian to be distributed for map/reduce jobs, similar to Microsoft Mobius. Then I could use Manifold/Radian to create advanced SQL Spatial queries which is quickly distributed to the workers and processed, the result is seamlessly sent back to my Desktop/Web version of Manifold/Radian.

    This is also the idea behind Hive, which enables you to send normal SQL commands to a Hadoop cluster to get a consolidated result set back. It hides much of the headaches of figuring out how to read millions of text files stored in HDFS.

    I also work on another project where I need to create Routes from A>B and aggregate the result of neighboring route distance to scores of accessibility. I also need to generate detailed Isochrones for each location. This is done for up to 100,000 locations for each simulation, which translates to millions of complex route calculations and aggregations. Then the user can change the underlying data and ask us to re-calculate it all again. We can't control when the user request a new simulation and the area affected. We also would like a fast simulation response, not telling the user that they get their answer next week.

    Today I have to use C# parallel code and custom A* code to run this on 40 cores and it takes days for a single run. So another user will have to wait for the first run to complete, or both will take longer if we divide the compute resources.

    I did a brute force test to spin up 2,400 cores in Azure Batch to run it faster, but that cost a lot of $$$ for each run.

    So routing on a dense grid/mesh network using parallel A* would be very beneficial. Features to create good convex meshes, and convert to navigable graphs which can be used for parallel route calculations would be great.

    That would make it very easy to take a network of lines and polygons that is not normally navigable by Routing engines from Manifold/ESRI/OSRM etc. and then get accurate result out of it. It can be used to drainage, irrigation, flood calculations etc.

    As the technique uses regular 2D meshes, the logic can be applied to 3D meshes without much change, to add the volume of flow after the direction and route is calculated.

    To summarize key features and examples we would like to see:

    • Fast parallel Routing algorithm on large Mesh/Graph based networks
    • Parallel Spatial processing on Hadoop/Spark/Distributed cluster managed from a user Manifold/Radian application

    <!--[if !supportLists]-->

    danb

    2,064 post(s)
    #05-Jun-17 23:53

    I just wanted to add my vote for something that was mentioned in the following thread: http://www.georeference.org/forum/t136513.4

    I would really like to see an optimised Radian example of how to calculate true surface area and surface length using a DEM and lines or polygon layers. This would be enormously beneficial to some of my workflows, but is currently not practical (M8 queries) over the extents that I am typically working with.


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

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