Subscribe to this thread
Home - General / All posts - Accessing turf.js functions in queries
joebocop
514 post(s)
#19-Feb-20 19:10

Ok, a bit of an arts & crafts project here, but perhaps interesting to some of you all the same.

A client asked for a "hex grid" covering an area. I thought to myself, turf.js can do this, and maybe I can supply turf's methods with data inside Manifold, where I typically work with this client's data. Here is the reference for the turf.js hexGrid() method (https://turfjs.org/docs/#hexGrid)

I downloaded turf.js, and created a JavaScript Script component in a Release 9 project (attached), and pasted all ~500kb of un-minified turf.js into the component.

At the bottom of that script, I created a function Main() to test the turf hexGrid() method, and it appears to work correctly. You can run the script and see the output in the Log pane; good times.

I then created a Query component which defines a SQL function that (fails to) reference the hexGrid method's ENTRY point over in the turf script component. I can't figure it out, I am not smart.

Using the attached project and your JavaScript and Manifold know-hows, are you able to call turf's hexGrid method from a Release 9 query?

Thank you in advance.

Attachments:
turf.js_test.map

joebocop
514 post(s)
#19-Feb-20 21:01

One more example map, identical to the one I originally posted, but which contains the v5.2.0 turf.js code. I think that first one I had tried was v3.something.

Either way, result is the same. Thanks again.

Attachments:
turf.js.5.2.0_test.map

tjhb
10,094 post(s)
#20-Feb-20 02:57

I'm not great at javascript, can look.

Before that though, would you be happy with a hex grid created in SQL?

I wrote code to do that for Manifold 8. Can't find where I posted it, but I still have the code.

Would be happy to update for Manifold 9. (Code would be better because SQL9.)

StanNWT
196 post(s)
#20-Feb-20 04:48

Hi Tim,

I'm wondering if useful references for hannibal Frida and tools that generate them and those fields of spatial analysis that use them would be helpfulto the Manifold community if they're not aware of them?

I spent alot of years working in marine spatial planning and Marxan is a critial tool used for environmental marine spatial planning, e.g. Marxan.org. also Xtools has hexagonal grid options for ArcGIS. I'm hoping that Manifold can do them several orders of magnitude better in performance alone.

joebocop
514 post(s)
#20-Feb-20 07:25

Oh for sure, if it can be done in SQL9, that's slick.

I was able to get the ENTRY point errors resolved by commenting-out [turfjs] lines #3 through #7, as well as #44292 through #44480. Sweet!

Now, of course, I have to sort out how to pass JavaScript arrays into those turf functions via SQL9. FLOAT32X4 seems to pass through as an object data type but stringify returns null. I can pass an NVARCHAR string of comma-separated numbers, but then I'll need to wrap each of the turf functions within a helper that calls string.split and then array.map converting the array elements from strings to floats. Will be a labour of love (see attached).

Once I got a few basic js functions written and wrapped into SQL9 functions, that got me dreaming up the possibilities of using other libraries like d3, lodash, the arcgis js api, or even *gasp* tensorflow.

Turf, of course, is the obvious one to start with. Kmeans clustering, center-mean algorithms, etc. Manifold is cool as hell.

Attachments:
turf.js.5.2.0_test.map

Sloots

678 post(s)
#20-Feb-20 10:47

I'm not aware of a SQL script you wrote in M8 either. I used to work with a script in M8 to make these hexagones. Based on that script I made a query in M9 to perform the same task.

I'm sure it can be optimized, but as a first attempt I'm delighted about the speed.

I created a hexagonal grid of 2.2 million records in about 4 minutes. (The script I used in M8 took almost a day for the same grid!).

It first creates polygones consisting of two branches, each holding 1 hexagon. In the seconds phase I split these in two. This could be done more effiencient in one go I believe.

Attachments:
Hexagon generator (M9).map


http://www.mppng.nl/manifold/pointlabeler

joebocop
514 post(s)
#20-Feb-20 22:16

Beauty, Sloots, that's some spicy code!

I'm going to test it out and compare to the turf.js SQL9 integration idea; surely the native SQL9 code will be much faster.

Thank you for sharing!

joebocop
514 post(s)
#22-Feb-20 00:30

Not clear if this is of interest to any of us, really, but for some reason, I'm still trying this.

Accessing the hex features returned by the turf.js script within SQL9 is still a work in progress, though is showing promise.

Purely generating the hex features within the turf.js script component and inserting them via the Manifold API is also intriguing. For initial timings, I'm able to generate ~1.7 million features in 5 seconds on my laptop, but attempting to do any more than that results in an error "Cannot compile script", which may be a deliberate limitation on what is desirable within a Release 9 script component, I'm not sure about that. Still, 1.7m hexagons in 5 seconds feels... fast?

I've attached the updated project file if you are interested in testing. Just run the [turfjs] script component and then review the log pane output. Change either the bbox array values to enlarge the area, or whatever.

Attachments:
turf.js.5.2.0_test.map

worthind4 post(s)
#22-Feb-20 01:32

sloots, could u link to the M8 script too, please

Sloots

678 post(s)
#22-Feb-20 05:13

http://www.georeference.org/forum/t741.15#753


http://www.mppng.nl/manifold/pointlabeler

tjhb
10,094 post(s)
#23-Feb-20 02:04

I'm not aware of a SQL script you wrote in M8 either. I used to work with a script in M8 to make these hexagones. Based on that script I made a query in M9 to perform the same task.

I'm sure it can be optimized, but as a first attempt I'm delighted about the speed.

I created a hexagonal grid of 2.2 million records in about 4 minutes. (The script I used in M8 took almost a day for the same grid!).

It first creates polygones consisting of two branches, each holding 1 hexagon. In the seconds phase I split these in two. This could be done more effiencient in one go I believe.

Chris,

You're right, I never did post my old (2011) Manifold 8 code to the forum.

It took the alternative approach you mention, drawing each hexagon separately, no need to split. Instead I used a vertical offset for every second column.

The other main difference from your approach is that it fills the AOI of a given drawing with hex cells at the specified circumradius, instead of specifying a number of hex cells in X and Y.

I have rewritten my code for Manifold 9 and optimized it. (I have also adopted your term "apothem", a new word for me.)

The results aren't exactly comparable, bearing in mind the differences in approach, but with this code making a drawing of 2.1 million hex cells takes 37 seconds.

--SQL9

-- source drawing and table

VALUE @drawing TABLE = [Drawing];

VALUE @table TABLE = [Drawing Table];

-- set hex cell size here

VALUE @circumradius FLOAT64 = 10;

    -- distance from hex cell centre to a vertex

    -- equals length of sides (equilateral triangles)

VALUE @apothem FLOAT64 = @circumradius * Cos(30 * PI / 180);

    -- distance from cell centre to midpoint of a side

--DROP TABLE [Hex grid Table];

--DROP DRAWING [Hex grid];

-- "invalid entity type" if table and drawing do not exist

CREATE TABLE [Hex grid Table]

    (

    [mfd_id] INT64,

    [xindex] INT32[yindex] INT32,

    [Geom] GEOM,

    INDEX [mfd_id_x] BTREE ([mfd_id]),

    INDEX [Geom_x] RTREE ([Geom]),

    PROPERTY 'FieldCoordSystem.Geom' ComponentCoordSystem(@drawing)

    );

CREATE DRAWING [Hex grid]

    (

    PROPERTY 'FieldGeom' 'Geom',

    PROPERTY 'Table' '[Hex grid Table]',

    PROPERTY 'StyleAreaColorBack' '{ "Value": -16777216 }' -- transparent fill

    );

INSERT INTO [Hex grid Table]

    ([xindex][yindex][Geom])

SELECT

    --[mfd_id], -- handled automatically

    [xindex][yindex],

    GeomConvertToArea(

        GeomConvertToPoint( -- pro forma to combine branches

            INLINE GeomMergePoints(

                GeomMakePoint(VectorMakeX2(

                    [xcentre] - @circumradius,

                    [ycentre]

                    )),

                GeomMakePoint(VectorMakeX2(

                    [xcentre] - @circumradius / 2,

                    [ycentre] + @apothem

                    )),

                GeomMakePoint(VectorMakeX2(

                    [xcentre] + @circumradius / 2,

                    [ycentre] + @apothem

                    )),

                GeomMakePoint(VectorMakeX2(

                    [xcentre] + @circumradius,

                    [ycentre]

                    )),

                GeomMakePoint(VectorMakeX2(

                    [xcentre] + @circumradius / 2,

                    [ycentre] - @apothem

                    )),

                GeomMakePoint(VectorMakeX2(

                    [xcentre] - @circumradius / 2,

                    [ycentre] - @apothem

                    )),

                GeomMakePoint(VectorMakeX2(

                    [xcentre] - @circumradius,

                    [ycentre]

                    )) -- first point repeated

                ),

            FALSE -- combine branches

            )

        )

        -- vertices

        --     1   2

        --   0   +   3

        --     5   4

        -- offsets from centre

        --    (-r/2, +a) (+r/2, +a) 

        -- (-r, 0)            (+r, 0)

        --    (-r/2, -a) (+r/2, -a)

        -- r circumradius, a apothem

FROM

    (

    SELECT

        [xindex][yindex],

        [xmin] + [xindex] * 3 * @circumradius / 2

            AS [xcentre],

        CASE [xindex] MOD 2

            WHEN 0

                THEN [ymin] + [yindex] * @apothem * 2

                -- even column

            WHEN 1

                THEN [ymin] + ([yindex] + 0.5) * @apothem * 2

                -- odd column

                -- offset cell upwards by one apothem

        END AS [ycentre]

    FROM

        (

        SELECT

            [xmin][ymin],

            [ylimit],

            [Value] AS [xindex],

            [Value 2] AS [yindex]

        FROM

            (

            SELECT 

                [xmin][ymin],

                --[xlimit], 

                [ylimit],

            SPLIT CALL ValueSequence(0, [xlimit], 1),

                --> [Value] -> [xindex]

            SPLIT CALL ValueSequence(0, [ylimit], 1)

                --> [Value 2] -> [yindex]

            FROM

                (

                SELECT

                    [xmin][ymin]--[xmax], [ymax],

                    Ceil(([xmax] - [xmin]) / (3 * @circumradius / 2)) AS [xlimit],

                    Ceil(([ymax] - [ymin]) / (@apothem * 2)) AS [ylimit]

                FROM

                    (

                    SELECT

                        VectorValue([box], 0) AS [xmin],

                        VectorValue([box], 1) AS [ymin],

                        VectorValue([box], 2) AS [xmax],

                        VectorValue([box], 3) AS [ymax]

                    FROM

                        (

                        SELECT

                            GeomUnionRects(GeomBoundsRect(

                                CASE GeomIsPoint([Geom]

                                    WHEN TRUE THEN GeomConvertToLine([Geom])

                                    ELSE [Geom]

                                END

                                ))

                                AS [box]

                                -- common bounding box of all geoms (FLOAT64X4)

                        FROM @table

                        --FROM CALL Selection(@table, TRUE)

                        THREADS SystemCpuCount() DIV 2

                        )

                    THREADS 1

                    )

                THREADS 1

                )

            )

        THREADS 1

        )

    WHERE [xindex] MOD 2 = 0

        -- even column: all rows

    OR [yindex] < [ylimit]

        -- odd column, offset upwards

        -- omit top row

    THREADS 1

    )

THREADS SystemCpuCount() DIV 2

;

Attachments:
Draw hex grid m9 g.txt

tjhb
10,094 post(s)
#23-Feb-20 05:26

Two things to add (OK three).

(1) The 37s timing is on a 6-year old laptop (a really good one though).

(2) It would obviously be good to compare the above with my Manifold 8 query (just as Chris did), apples for apples.

Currently my original Manifold 8 query (itself over 8 years old) is running over the same data, on the same old-but-great laptop. It reports that it is a third of the way through, after 2h 26mn.

That's against 37 seconds total on Manifold 9, with the same task and data.

Yes, I rewrote and optimized the query for 9. It would be good and fair to do the same retrospectively for 8, to make its performance as good as it can be. (There might be time for that after this run.)

(3) What would also be cool is if an ESRI expert on this forum (lurking or open) could give a reproducible report of how long a task of similar scale would take in an Arc* product, tweaked to the gunwales, money never an object of course.

danb

2,064 post(s)
#23-Feb-20 05:49

I'm no expert in ArcGIS, but I do have access to it. A quick google shows a few options:

https://pro.arcgis.com/en/pro-app/tool-reference/data-management/generatetesellation.htm

https://www.arcgis.com/home/item.html?id=03388990d3274160afe240ac54763e57

So if I get chance I will give it a go tomorrow and report back.

Looks also like there is a QGIS option and if I remember correctly also something in spatialite.

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=tesselations-4.0

PS I used to know how to do hyperlinks but my former ability seems to have gone


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

tjhb
10,094 post(s)
#23-Feb-20 06:18

Thanks Dan. That would be great.

Maybe we can specify a task more clearly, so it can be directly compared between platforms?

For this task I was going by instinct--to fill a target AOI at a given (circum)radius, with a known origin.

Other things can be done--as in Chris's example.

One common task, for a given AOI? Whatever is normal/most useful.

danb

2,064 post(s)
#23-Feb-20 23:42

I gave this a go this morning on the builtin ArcGIS script (ArcMap 10.4.1). For my sample area to tessellate, I chose a square region just shy of 16 km2.

Anyway, same machine Tim's query M9 cutting edge 9.0.17.4:

2020-02-24 12:00:23 -- Query: [M9 TJBH TESSELLATE] (2.455 sec)

Generated 107,054 hexagons

ArcMap:

Generated 107,204 hexagons

See image 1

It seems strange, but if known, Arc requires you to input an area for the polygons. To get this figure I calculated the M9 area and used this as the input.

See image 2

Interestingly when you look at the areas generated by the ArcGIS tool, the polygon size varies bettween two values.

See image 3

Attachments:
1 ArcMap Params.png
2 ArcMap Runtime.png
3 Output Tables.png


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

tjhb
10,094 post(s)
#24-Feb-20 00:57

Thanks Dan!

So to summarize,

  • ArcMap is slightly slower than Manifold 8;
  • Manifold 9 is over 100x faster than either;
  • ArcMap may have a bug in that the cells are not all identically sized (good grief). [Maybe this is due to optional alignment of vertices to a discrete grid? Can't remember what that is called. Possible to have it switched on unintentionally, can really f**** with data, absolutely stupid software design IMHO.)

Looks quite good then.

tjhb
10,094 post(s)
#23-Feb-20 22:48

In the end I gave up on my original Manifold 8 query writing 2.1 million hex cells. Too many hours.

I rewrote the query for 8, adopting as many of the optimizations for 9 as could be applied, then tested making a smaller number of hex cells.

Results for ~130000 cells, same laptop as before:

  • Manifold 9, query as above: 0mn 2.5s
  • Manifold 8, original: 25mn 49s
  • Manifold 8, rewrite: 4mn 1s

So that is apples for apples.

Both Manifold 8 queries are attached. Version a is wasteful, version b more efficient (and its coverage is slightly more accurate). But still.

Attachments:
Draw hex grid m8 a.txt
Draw hex grid m8 b.txt

tjhb
10,094 post(s)
#24-Feb-20 00:23

...But of course I couldn't resist running my freshly optimized Manifold 8 query to draw the previous 2.1 million cells.

To recap, the time for Manifold 9 was 37s.

The time for Manifold 8 is 3954s, that is 1 hour 5mn 54s.

With fully optimized code in both cases, Manifold 9 runs 107x faster than Manifold 8 for the same task. Same data, same hardware.

And no, neither task is either memory-bound or disk-bound. This is due to intelligent storage, super-efficient data transport, and pure execution speed.


An obvious question. How much of the speedup from 8 to 9 depends on using multiple cores and multiple threads?

In the Manifold 9 query I specified multiple threads for two of the query stages. The work in the other stages either is trivial or cannot benefit from parallelism (so that adding extra threads would slightly slow things down).

Let's compare Manifold 9 single-threaded. If we change the two relevant lines from

THREADS SystemCpuCount() DIV 2

to

THREADS 1

then the time for 2.1 million hex cells (same data on same hardware) increases from 37s to 70s.

So the speedup for Manifold 9 using multiple cores is not insignificant, and it is absolutely great to have. But again those times compare with just over an hour for the same task in Manifold 8. The single-threaded speedup from 8 to 9 is still 56x.

My SQL in the two cases is, as closely as possible, the same (as you can confirm if you look).

tjhb
10,094 post(s)
#07-May-20 00:37

I have revised the Manifold 9 query posted above.

The differences are

  • A 1.8x speedup. This is mainly due to drawing the hexagon cell only once (nominally at (0, 0)), then using GeomShift() to copy it to every grid position. (The previous version drew a new geom at every grid position.) There is some other tuning as well.
  • I've fixed some bugs that meant the AOI would usually be slightly over-filled around the edges.
  • Improved readability, through better use of named values and better notes.

As noted inthis thread, the next job is to improve the query so that it can efficiently fill an irregular AOI, rather than always filling the whole bounding box of all objects.


--SQL9

-- source drawing and table

VALUE @drawing TABLE = [Drawing];

VALUE @table TABLE = [Drawing Table];

-- output drawing and table

-- comment out if table and drawing do not already exist

-- (else "invalid entity type")

DROP TABLE [Hex grid Table];

DROP DRAWING [Hex grid];

-- set hex cell size by circumradius

VALUE @circumradius FLOAT64 = 1;

    -- distance from cell centre to a vertex

    -- equals length of sides (equilateral triangles)

VALUE @apothem FLOAT64 = @circumradius * Cos(30 * PI / 180);

    -- distance from cell centre to midpoint of a side

VALUE @height FLOAT64 = @apothem * 2;

    -- row height (equals cell height)

VALUE @width FLOAT64 = 3 * @circumradius / 2;

    -- column width (less than cell width since interleaved)

-- convenience functions to rename result fields

FUNCTION xindex(@xlimit INT32TABLE AS

    (

    SELECT [Value] AS [xindex]

    FROM (TABLE CALL ValueSequence(0, @xlimit, 1))

    )

    END;

FUNCTION yindex(@ylimit INT32TABLE AS

    (

    SELECT [Value] AS [yindex]

    FROM (TABLE CALL ValueSequence(0, @ylimit, 1))

    )

    END;

CREATE TABLE [Hex grid Table]

    (

    [mfd_id] INT64,

    [xindex] INT32[yindex] INT32,

    [Geom] GEOM,

    INDEX [mfd_id_x] BTREE ([mfd_id]),

    INDEX [Geom_x] RTREE ([Geom]),

    PROPERTY 'FieldCoordSystem.Geom' ComponentCoordSystem(@drawing)

    );

CREATE DRAWING [Hex grid]

    (

    PROPERTY 'FieldGeom' 'Geom',

    PROPERTY 'Table' '[Hex grid Table]',

    PROPERTY 'StyleAreaColorBack' '{ "Value": -16777216 }' -- transparent fill

    );

INSERT INTO [Hex grid Table]

    ([xindex][yindex][Geom])

SELECT

    --[mfd_id], -- handled automatically

    [xindex][yindex],

    GeomShift([Cell], VectorMakeX2([xcentre][ycentre]))

        -- GeomShift does not benefit from multiple threads

        -- (if it did we should move it from the transport layer)

FROM

    (

    SELECT

        [xindex][yindex],

        -- draw hex cell nominally centred at (0, 0)

        -- vertex order

            --     1   2

            --   0   +   3

            --     5   4

        GeomConvertToArea(

            GeomConvertToPoint( -- pro forma to combine branches

                INLINE GeomMergePoints(

                    GeomMakePoint(VectorMakeX2(

                        -@circumradius, 0

                        )),

                    GeomMakePoint(VectorMakeX2(

                        -@circumradius / 2, @apothem

                        )),

                    GeomMakePoint(VectorMakeX2(

                        @circumradius / 2, @apothem

                        )),

                    GeomMakePoint(VectorMakeX2(

                        @circumradius, 0

                        )),

                    GeomMakePoint(VectorMakeX2(

                        @circumradius / 2, -@apothem

                        )),

                    GeomMakePoint(VectorMakeX2(

                        -@circumradius / 2,

                        -@apothem

                        )),

                    GeomMakePoint(VectorMakeX2(

                        -@circumradius,

                        0

                        )) -- first point repeated

                    ),

                FALSE -- combine branches

                )

            ) AS [Cell],

        -- list cell centres

        [xmin] + [xindex] * @width AS [xcentre],

        [ymin] + [yindex] * @height + (@height / 2) * [xindex] MOD 2 AS [ycentre]

            -- odd columns offset upwards by half cell height (equals apothem)

    FROM

        (

        SELECT

            [xmin][ymin]--[ymax],

            [xindex],

        -- splitting by yindex as an extra step, after xindex is known

        -- to allow for cases where cell height subdivides the AOI bounds exactly

        SPLIT CALL 

            yindex(

                -- for even columns, round up to ensure the top row of cells is not missing

                -- for odd columns, round normally to prevent an extra row of cells above bounds

                CASE [xindex] MOD 2

                    WHEN 0 THEN Ceil(([ymax] - [ymin] - @height / 2) / @height) -- even column

                    WHEN 1 THEN Round(([ymax] - [ymin] - @height / 2) / @height) -- odd column

                END

                -- alternative

                -- reduce the range for odd columns by an extra half cell height (and round up)

                --Ceil(([ymax] - [ymin] - (@height / 2) * (1 + [xindex] MOD 2)) / @height)

                ) --> [yindex]

        FROM

            (

            SELECT

                [xmin]--[xmax],

                [ymin][ymax],

            SPLIT CALL xindex(Ceil(([xmax] - [xmin] - @width / 2) / @width)) --> [xindex]

            FROM

                (

                SELECT

                    VectorValue([box], 0) AS [xmin],

                    VectorValue([box], 1) AS [ymin],

                    VectorValue([box], 2) AS [xmax],

                    VectorValue([box], 3) AS [ymax]

                FROM

                    (

                    SELECT

                        GeomUnionRects(GeomBoundsRect(

                            CASE GeomIsPoint([Geom]

                                WHEN TRUE THEN GeomConvertToLine([Geom])

                                ELSE [Geom]

                            END

                            ))

                            AS [box]

                            -- common bounding box of all geoms (FLOAT64X4)

                    FROM @table

                    --FROM CALL Selection(@table, TRUE)

                    THREADS SystemCpuCount() DIV 2

                    )

                THREADS 1

                )

            THREADS 1

            )

        THREADS 1

        )

    --THREADS 1

    THREADS SystemCpuCount() DIV 2 + 1

    )

THREADS 1

;

Attachments:
Draw hex grid m9 i.sql

mlinth
447 post(s)
#30-Nov-20 18:22

Wonderful query, Tim.

I just used it to create a grid to show store clusters. It's screamingly fast.

I'm finally getting to use Manifold for some store network analysis. Really enjoying M9.

Regards,

Martin

tjhb
10,094 post(s)
#30-Nov-20 20:05

I just realised the query doesn't do what I said it does. In particular, it doesn't draw the hexagon just once (but multiple times, after the two SPLITs). How did I miss that?

I'll fix it. This will speed it up.

tjhb
10,094 post(s)
#30-Nov-20 21:19

Not much faster than version i (about 5%), but more sensible.

Attachments:
Draw hex grid m9 j.sql

tjhb
10,094 post(s)
#30-Nov-20 23:20

I revised the Manifold 8 code as well, making similar improvements as for 9.

Now with matching test data, on the same machine:

Manifold 9 SQL (version j) completes in 1.28s.

Manifold 8 SQL (version d) completes in 58s. A big improvement over the 6mn 49s for revision b posted in February above.

Attachments:
Draw hex grid m8 d.txt

oeaulong

521 post(s)
#01-Dec-20 17:55

Thanks for the updates.

I am wondering if your threaded manual creation is faster than the approach of making the center points of the hexes with the indexing info and running a voronoi followed by a bounds clip on them.

tjhb
10,094 post(s)
#01-Dec-20 18:17

It is always faster to work in raw numbers (if they are known and simple, like hexagons) than to use indexing, analysis or topology functions on top.

Is there any risk in keeping things so simple? Yes. We might see floating-point representation create topology errors, where raw numbers that start out the same may be treated differently after creation.

Is that the case here? Drawing one hexagon centred at (0, 0) then moving it by an arbitrary number of different offsets in X and Y?

Without knowing enough, yes, I imagine that there is scope here (in theory) for floating-point rounding errors to introduce infinitessimal topology errors between the boundaries of hexagonal cells, especially after large displacements (which will often be normal).

It might be safer to move vertices separately, rather than moving whole hex cells, to make sure that identical FP64 values will be offset to identical FP64 results.

But I doubt that that is necessary in this simple case, since I would bet that the Manifold GeomShift function does exactly that internally. (Why would it, for example, calculate a weighted centroid, move the centroid, then reposition all source vertices with respect to the new centroid? That could create rounding problems for different centroids, but it would involve a non-sensible unnecessary amount of work AFAIK.)

Still, your questions make me think that this needs testing, i.e. the topology of my hex grid after creation.

Should test that adjacent cells exactly share coordinates.

oeaulong

521 post(s)
#01-Dec-20 18:34

I ran a quick test on the centroids of your hex generation run through the voronoi with the result in all appearances coincident. I did not drill into the coordinate numbers for them. something next to try. after posting I did further searching in the turfjs library. They appear to have a voronoi section, but haven't found where it is driven to create the grid.

Also, it would appear that you are using the outer circle in generation, I can see where driving the generation from 1/2 distance of centroids may be of need. This would result in the inner circle of the hex being the needed cell size. It seems like the ability to derive from either the edge or the vertices might be handy. could your code adapt?

I loved that in beginning manifold (mumble years ago) part of the introduction steps was to use the grid/graticule tools to help both teach the system and differences between. I would like to see some form creation migrating over to v9, but willing to wait. The preview and transform tools seem to get a big leap there as is of course the Sql everything language base.

Just some more ideas the think about. Seriously not trying to push anything. This is good work. It has been on my wish list for years when thinking of manifold as a game mapping tool (in about the v6-8 release time frame) was something to pursue.

ColinD

2,081 post(s)
#08-Apr-21 23:57

A huge Like for this post Tim. I needed a hex grid of 1.2 mil cells and M9 did it instantaneously whereas M8 would have taken days, if ever!


Aussie Nature Shots

cartomatic

905 post(s)
#20-Feb-20 06:40

i played a bit with calling an inline js script (docu has a c# example) but so far no luck.

idea of using externally provided js tools (or scripts in general) from sql is really cool though :)


maps made easy - www.cartomatic.pl || www.cartoninjas.net

joebocop
514 post(s)
#20-Feb-20 07:33

I was working on automating creation of "CSV Webserver" datasources, when I realized I needed to URI encode my lengthy URLs. I started writing a URI encoder function in SQL9, then realized, wow, I can just use JavaScript's encodeUriComponent() and call that from a SQL9 function.

Now, about 24 hours later, I'm way down the rabbit hole.

adamw


10,447 post(s)
#25-Feb-20 08:41

...exactly! Don't re-code what you already have - either in Manifold or in a library that you can call. .NET also has plenty of useful stuff, URI encoding included.

Dimitri


7,413 post(s)
#20-Feb-20 06:42

Well, the script works, in that it does create some hexagons in the JSON string written to the log. There is only a partial JSON string in the log (cut off since each log line has a limit), but if you take just the first feature and close it off, replacing the final comma with a ] and } , you get a valid JSON expression that can be put into a .geojson file (attached) and imported into Manifold. :-) So that's progress. Haven't had time to look into it further but this looks like it could be a lot of fun.

Attachments:
hex.geojson

adamw


10,447 post(s)
#25-Feb-20 08:36

A fascinating thread!

Time to give my two cents.

I went into the MAP file from the post I am replying to, opened the script, found the Main. I briefly mulled with the idea of remaking Main into returning a table, then realized that we have all the necessary bits for creating that table in SQL already, so I modified Main to simply return a string (returned just the 'features' property which contains the features, but could have returned everything verbatim, that's not a big deal):

//Javascript

 

... (tons of code from turf.js)

 

var Main = function()

{

 var bbox = [-95, 30 ,-85, 40];

 var cellSide = 50;

 var hexGrid = turf.hexGrid(bbox, cellSide);

 return JSON.stringify(hexGrid.features); // the line I added

}

Here's how to plug that function into a query:

--SQL9

FUNCTION test() NVARCHAR AS SCRIPT [turfjs] ENTRY 'Main';

We now have a function that we can call and get the result as a string.

Here's how to transform that string into a table of geoms:

--SQL9

SELECT StringJsonGeoGeom(StringJsonValue(value'geometry', false)) AS geom

  FROM CALL StringToJsonArrayValues(test());

Explained: the string denotes a JSON array, we transform it into individual values using StringToJsonArrayValues. Then we take each value, that's a JSON object, we take its 'geometry' property. The value of the 'geometry' property is also a JSON object, but we don't have to parse it any further because that's GeoJSON and we can transform GeoJSON into a Manifold GEOM using StringJsonGeoGeom.

Let's look at the geoms we got:

--SQL9

 

CREATE TABLE t (mfd_id INT64INDEX mfd_id_x BTREE (mfd_id),

  g GEOMINDEX g_x RTREE (g));

CREATE DRAWING t_d (PROPERTY 'Table' 't'PROPERTY 'FieldGeom' 'g');

 

INSERT INTO t (g)

SELECT StringJsonGeoGeom(StringJsonValue(value'geometry', false))

  FROM CALL StringToJsonArrayValues(test());

This creates a nice drawing of hex tiles.

Let's now parameterize the function to make it useful - I added a new function to the script:

//Javascript

var hexGridFunc = function(xmin, ymin, xmax, ymax, cellSide)

{

  var bbox = [ xmin, ymin, xmax, ymax ];

  var hexGrid = turf.hexGrid(bbox, cellSide);

  return JSON.stringify(hexGrid.features);

}

...and here's how to bring it to SQL:

--SQL9

FUNCTION hexGrid(

  @xmin FLOAT64, @ymin FLOAT64, @xmax FLOAT64, @ymax FLOAT64,

  @cell FLOAT64NVARCHAR AS SCRIPT [turfjs] ENTRY 'hexGridFunc';

The final test to see if it works:

--SQL9

SELECT StringJsonGeoGeom(StringJsonValue(value'geometry', false))

FROM CALL StringToJsonArrayValues(hexGrid(-95, 30 ,-85, 40, 50));

Javascript is cool, indeed.

(I guess we should allow $include http://...$ to allow using libraries without copy-pasting their source.)

joebocop
514 post(s)
#25-Feb-20 18:53

Very awesome, thank you for weighing-in adamw.

The tjhb SQL9 code is fantastic, fast, easy, so thank you for sharing that. But where's the fun in doing things correctly, directly, efficiently and professionally?

The turf.js investigations have been interesting (well, to me anyway). Here are some of the limitations that I've encountered, along with some "would-be-nice-if" items for Release 9, misguided and otherwise.

  • v8 facilities are memory-limited. "Cannot compile script" in Release 9 is reproducible using a desktop Node.js server when the object returned in code is too large for the default memory configuration of the v8 engine. Node.js allows a CLI argument "--max-old-space-size=8192" to increase the limit, which resolves the limitation. Could Release 9 allow tweaking the amount of memory available to the v8 engine? Other aspects of the v8 engine?
  • JavaScript's JSON.stringify() can only handle objects up to a certain size as well. Js being Js, there are other libraries that can cope (big-json), but which would need to be imported into a Release 9 project, as a workaround.
  • While turf.js returns an object from the hexGrid() call pretty quickly, the JavaScript "object" data type at present needs to be passed through JSON.stringify() to be delivered "up" to a calling SQL9 function; perhaps there could be a more efficient mapping between JavaScript v8 "object" and SQL9 that could circumvent this manual conversion needed in publishing to NVARCHAR?
  • As adamw mentions, the ability to $include$ from a URL, or from a js text file on disk (maybe $reference$ already does this; I'll check) would be a useful addition
  • I understand that work has been progressing on the Manifold JavaScript API documentation since 9.0.169.1, and its completion and publication will be welcome. I tried guessing my way to "getSchema" and "createSchema" equivalents for v8 JavaScript scripts, but the usefulness of JS will increase meaningfully when we can talk to the Release 9 facilities (tables!) from within js scripts
  • The inclusion of v8 in Release 9 is great, and should, according to me, be the focus of more marketing efforts (the YouTube Sales videos, I guess) and internal development/documentation. Web-GIS people work with big data, and may someday outnumber the rest of us. Highlighting JS as a point of entry into Manifold desktop use, and focusing some effort on consumption of web-native file formats, potentially grows the size of our tent

Obviously, thank you all again for responding on this thread. I learn so much on georeference.org.

adamw


10,447 post(s)
#26-Feb-20 08:11

We'll look into allowing a V8 script to set up the execution environment in terms of memory limits, etc. This might be a global option or a per-script setting, we'll see what would make more sense.

Issues with little vs big JSON strings will be solved either by control over memory limits or by using libraries, depending on what they are (we'll look into what is going on in V8 as we embed it, and make sure the issues are indeed solved).

We don't have to be transporting data through JSON even now, that's just a simple way to get data into SQL as fast as possible that I used for an illustration. The alternative is to let the script create a Manifold table and populate it. Agree we need to document the object model for V8 to make this feasible, right now it is largely undocumented (the reason why is that we also want to extend it significantly for web features, so this all is waiting for web features to start arriving).

We have $include <component>$ in queries, we will add it to scripts. This will cover imported components and components in files (you can link source code files into the project right now). As I said, perhaps we should also allow including scripts on web resources.

Overall, we completely agree that Javascript is a very useful language to have, we are planning to extend what we do for it / with it significantly in the future.

Dimitri


7,413 post(s)
#26-Feb-20 12:06

The inclusion of v8 in Release 9 is great, and should, according to me, be the focus of more marketing efforts

I agree with you, but the classic GIS world is fanatically Python-centric. It's an uphill slog educating classic GIS folks about the benefits of SQL. :-) It's still worth doing what you suggest, to do a good job supporting JS in Manifold while also making sure Manifold supports Python.

rk
621 post(s)
#26-Feb-20 15:01

There is a (limited) way to explore Manifold JavaScript API.

//JavaScript

Object.getOwnPropertyNames( Manifold )

//Application

Object.getOwnPropertyNames( Manifold.Application )

//Name,CreateDatabase,CreateDatabaseForFile,GetDatabaseRoot,Log,MessageBox,OpenLog

db = Manifold.Application.CreateDatabase()

Object.getOwnPropertyNames( db )

// CanSave,CanRun,CanRename,CanInsert,CanDelete,Dispose,Run,Search

root = Manifold.Application.GetDatabaseRoot()

Object.getOwnPropertyNames( root )

// CanSave,CanRun,CanRename,CanInsert,CanDelete,Dispose,Run,Search

mfd_meta = root.Search("mfd_meta")

// this evaluates to Manifold table

Object.getOwnPropertyNames( mfd_meta )

// CanUpdate,CanRename,CanInsert,CanDesign,CanDelete,Dispose,SearchAll

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