Subscribe to this thread
Home - General / All posts - Big data geo-analytics
artlembo


3,400 post(s)
#20-Nov-17 19:18

Hi everyone,

I'm preparing a course for next semester on Big data analytics with GIS, and we'll be using Manifold, Postgres, SQL Server, and anything else I can get my hands on! I've been putting together the syllabus, and was looking over what ESRI has with their GeoAnalytics Server - really cool stuff, and cool use cases.

So, I am beginning to try and re imagine the use-cases in SQL and Manifold Future. Have a look at this video, and let me know what you think.

There will be more coming as this semester wraps up - I'm having fun testing out all kinds of scenarios. I'm sure some will translate well, while others will not.

artlembo


3,400 post(s)
#21-Nov-17 14:55

I dug a little deeper into the last videos, and just created a new one that shows how to convert point based time-series data into line with useful information. You can check out the video here.

ych616 post(s)
#21-Nov-17 15:30

Your videolink (wordpress) is asking for a login and password ?

artlembo


3,400 post(s)
#21-Nov-17 15:41

thanks. Sorry about that.

Try this link

{Adam: if you would like to replace the link above with this one, that would be much appreciated}

adamw


10,447 post(s)
#24-Nov-17 08:16

Sure, I replaced the link.

Nice video!

Two notes:

1. We can't click into lines produced by the query in the video, because the result table does not have a unique index = the table does not tell how to distinguish lines between each other. Doing a copy of the drawing and adding all necessary indexes automatically via a transform was pretty smart. Perhaps in the future, it would make sense to have a tool to do just that (convert a query into a table, and making the table editable / selectable).

2. The query has an issue in that it relies on an implementation detail that might change in the future. The points on a line have to be ordered by date. The current query does this using ORDER in the inner SELECT that is then grouped. The grouping does not have to carry the original ordering, it is free to rearrange records inside groups however it wants. That's likely going to be used by optimizations in the future. When this happens, the points in lines will stop being ordered. We should get rid of ORDER in the inner SELECT and perform the ordering in the groups explicitly using COLLECT ... ORDER on the groups before they are passed to GeomMergePoints. When I have time, I will provide an example, it's nothing terribly involved (or maybe Tim can help here, we discussed COLLECT a lot).

Good video!

artlembo


3,400 post(s)
#24-Nov-17 13:24

Thanks, Adam. I’m glad you posted this because as you develop new tools like you say, you will be aware of some of the use cases and can alert users of the best way to carry them out.

tjhb
10,094 post(s)
#24-Nov-17 21:21

Here is a thread-safe version using COLLECT.

The COLLECT expression gathers all point geoms for each group, ordering them by date and time.

The resulting table is passed to BuildLine, a custom aggregate function returning one line for the table (for the group).

Function:

FUNCTION BuildLine(p TABLE) GEOM AS

    -- p -> ([Geom (I)])

    (

    SELECT GeomConvertToLine(

        GeomConvertToPoint( -- dissolve branches

            GeomMergePoints([Geom (I)]),

            FALSE

            )

        )

    FROM p

    )

    END

;

Query calling it:

SELECT

    --[STORM_NUM],

    [NAME][YEAR_],

    AVG([WIND]AS [avgwind],

    MAX([PRESSURE]AS [maxpressure],

    BuildLine(

        (

        COLLECT [Geom (I)]

        ORDER BY [YEAR_][MON][DATE_][TIME_]

        )

        ) AS [Line]

FROM [huricanepts]

GROUP BY [STORM_NUM][NAME][YEAR_]

--THREADS SystemCpuCount()

;

(The custom aggregate function could calculate the AVG and MAX results too, returning a table rather than a scaler, but I don't think that would be cleaner. And--now!--BuildLine could equally be a script function instead of SQL.)

adamw


10,447 post(s)
#25-Nov-17 07:18

Thanks, Tim, that's exactly what I meant.

lionel

995 post(s)
#26-Nov-17 07:03

Hi

what is a scaler ( scalar) in manifold context ? a vector matrix ?

so what difference between scaler matrix table ?

regard's

http://www.manifold.net/doc/radian/gpgpu.htm

Note that some operations are so trivial in terms of computational requirements it makes no sense to dispatch them to GPGPU, the classic case being scalars (trivial) as opposed to tiles (more bulk). CASE expressions, conditionals and similar constructions or functions that operate on scalar values stay on the CPU while functions that operate on tile values generally go to GPGPU unless they use tiles in a trivial fashion, such as making a simple comparison.

http://www.manifold.net/doc/radian/sql_functions.htm

All types of values- Operators and functions generally support all types of values so that, for example, we can use comparison or boolean operators on tiles and not just on scalar values.


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

lionel

995 post(s)
#26-Nov-17 07:32

Hi

this SQL is for manifold 8 since last version use [Geom] instead [Geom(I)]

it seem sql collect must be use with sql orders and sql group

Is there a way to copy cell of retun result of type Geom to another table cell attach to a drawing that contain also [geom] ?

Does use [drawing name ] or [table attach to same Drawing name ] behave the same in SQL 8 /9. work on drawing return drawing and work on table return table ?

regard's

GeomReverseLine in SQL9

convert points to lines


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

adamw


10,447 post(s)
#01-Dec-17 10:13

COLLECT is an aggregate and as such, it is used with GROUP. ORDER is irrelevant. If there is no GROUP, you cannot use COLLECT.

Is there a way to copy cell of retun result of type Geom to another table cell attach to a drawing that contain also [geom] ?

Does use [drawing name ] or [table attach to same Drawing name ] behave the same in SQL 8 /9. work on drawing return drawing and work on table return table ?

Could you elaborate on these two items a bit?

There is certainly a way to copy geoms between tables in Radian / Future / Viewer. A drawing is just a set of strings which includes the name of the table / field to take geometry data from.

rk
621 post(s)
online
#01-Dec-17 09:18

I have x,y,z-points. I cannot use BuildLine to build x,y,z-lines because, while GeomConvertToPoint and GeomConvertToLine preserve Z values, GeomMergePoints removes them.

http://www.manifold.net/doc/radian/sql_functions.htm

adamw


10,447 post(s)
#01-Dec-17 10:14

We will extend merges to handle 3d data. Likely via GeomMergeXxx3 variants which will skip non-3d data.

otm_shank
50 post(s)
#22-Nov-17 21:18

Hi Art, I'm interested in how you're defining 'big data' and how that differs from regular GIS analysis?

It seems the main feature of Esri GeoAnalytics Server is that it runs across multiple machines - are you looking at anything similar?

Regards, Tim.

artlembo


3,400 post(s)
#24-Nov-17 13:31

Right now I am just defining some of the use cases. But, as you hint at, I plan to introduce more about the insanely large data sets that span multiple servers.

I expect more posts on some of the SQL, then I am going to move more into some statistical analysis of big data using principles from the central limit theorem, and then finally, moving into database that span across a number of servers.

That last point, I am already testing some of it out using things like Postgres and the execute command inside of MF. Behind the scenes I am advocating for manifold to be it’s own server. That way, a master MF project can distribute the load to MF worker processes on other computers or servers. Each of which, as you imagine, can run multiple CPUs.

This is something MF will have to develop into, but I and others are trying to advocate for an implementation like that. I am sure we will see it at some point. But even if it is a ways off, there are ways to pull this off using data bases like Postgres. That is next week’s task :-) .

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