[postgis-devel] Call for advice about sql-signature for twkb functions

Nicklas Avén nicklas.aven at jordogskog.no
Sat Sep 27 13:42:59 PDT 2014

Thank you Regina

About the twkb sql.functions you gave me the answer that I was hoping
for. But we don't have many functions with default values so I thought
that was something we maybe should avoid.

Do you know if default values works with aggregates too? I think I tried
some weeks ago but didn't success. But I will give it a try again.

About offlineMap

Nice the map worked in Firefox. I thought I had tries it and failed, but
now I see it works.
The csv-part will not work in Firefox because I think Firefox has an
open ticket about IndexedDB not working from webworkers which is what is

About phones and tablets it works great in Android with Chrome. The
problem is as long as the spatial index is kept in memory it needs some
RA. So the 500 mb file I cannot build the index for, but it works on the
smaller file that is on Github including styling. But what is nessecary
is to read the index into maybe IndexedDB or something like that so it
can be kept on disc. From the styling in IndexedDB I'm quite confident
that it should be speedy enough.

iOS is another story. I haven't found a way to read anything but images
from the File API, but all I read on the net is that the File API is
supported. I cannot understand that it is called supported if it only
can handle images and nothing els. I hope I have just missed something
trivial. Paul, if you read this. You are an apple defender, aren't you?
How to to it?

That I use Leaflet is just a way to visualize the result. At first I
struggled for some evenings with getting pixijs to show my result
through webGL, but then I realized that my first goal was just to get
something on a map and then I through the coordinates on Leaflet.

About OL3 I actually put quite a big effort in getting twkb there last
winter. I had working code for all basic types. But as you see if you
look at my javascript code it is quite ugly and shows that I haven't
understood how to write the object orientated way. So Tom Payne made an
effort to rewrite it the OpenLayers way. He did get stuck in geometry
collections I think. And I haven't tryed to really understand his code.
And now I think he has changed his job and don't work with openLayers
any more. And there is also a problem with just implementing twkb like
that. OpenLayers have no good way (as far as I know) to handle the
attribute data separately. Tom Payne thought that the twkb-format should
include the attributes. But that will make things way more complicated
with handling very many different data types. Then I think protobuffer
is maybe a better choice. The thing with twkb is that it can focus on
the geometry complexity. For instance building a topological model is
much easier when ignoring the attribute data. So I am not sure that
implementing twkb-support in ol3 or a leaflet plugin solves that many

About performance, the bottleneck in the example on github is not in the
TWKB technique. There is two things that takes time I think
1) rendering, of course
2) The twkb is read in webworkers. To get the data back to the main page
it needs to be copied as long as it isn't a arraybuffer (binary array).
Then transferable objects can be used. But because of the structure of
the coordinates that leaflet expects arraybuffer isn't an efficient
option for the transfer.

This was long.
But the summary is that there is a lot more that can be done, but it is
very interesting.

I have also put a pdf on github describing a little what is happening:

What is interesting is that it is that fast to red directly from
different parts of a quite large file. Even with the 500 mb file I think
it works great. And then I only use 1 webworker for that. 

Maybe most impressing is when building the index. 
My 505mb file has almost 3 millin rows and about 220 million vertex

In PostGIS it takes about 60 seconds to build hte index for that table.
But in offlineMap when building the index for the same dataset it takes
about 22 seconds. THen all bounding boxes is also calculated. So all 220
million vertex-points is visited and a RTree index is built using rbush
script from Mourner

But then I use 7 webworkers so all 8 threads in this laptop is at full
speed for the first 16 seconds.

I will look at the sql functions tomorrow.

Thanks again


On fr., 2014-09-26 at 19:04 -0400, Paragon Corporation wrote:
> Hi Nicklas,
> Took a quick look at offlineMap - the twkb file seems to load pretty fast on
> my firefox browser.  Couldn't figure out how the csv part works though
> --maybe that's the part you are talking about that works only in Chrome.
> Have you tried this on any smart phones/tables -- Android/iPhone etc?
> That's where I think offline is most needed. 
> Now regarding cleanest design, I don't think there is one we can all agree
> on.  I see raster for example and in hindsight we went a bit too far with
> the permutations of things, but then again having a ton of functions named
> differently that do the same thing would probably have been just as
> annoying.
> So here is my 2 cents
> >  1) What is the cleanest design with many optional parameters
> I look at this page:
> http://postgis.net/docs/manual-dev/ST_AsTWKB.html  (and really you should
> only have 1 function  this one -- bytea ST_AsTWKB(geometry g1, integer
> decimaldigits, int8 geometryID, boolean include sizes); )
> With all others just having default values and the order of the defaults
> should be in the order you expect people will want to override them
> So for example digits is probably something people are going to want to set
> more than geometryID and probably include sizes as well.  So the order you
> have those is great.
> Now to get rid of all of these and save the single one, 
> > 2) What do I need to do since everybody with trunk code already have a
> set of sql-functions so I don't mess things up in your databases. It is
> some place to put code that deletes earlier functions right  --
> If you go with my advice in 1 then you need to put the drop statements in 
> postgis/postgis_drop_before.sql
> Otherwise when you change the remaining function to use default args, it
> will croak since you would get an ambiguous something or other error if
> those others exist while you are changing the remaining to use default args
> -- A BIT OF DIGRESION HERE and hopefully won't start any flame wars--
> Also was wondering if you have an openlayers equivalent (preferably OL3).
> I've been playing with leaflet recently too but still debating which one
> works best for my needs leaflet or OL3.  OL3 still needs a lot of
> documentation, Leaflet docs seem much easier to follow, but I've always
> liked the everything I need in one box (and the better styling options
> without resorting to extra plugins that the OL2 offered which I think OL3
> does as well. 
> Hope that helps,
> Regina
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel

More information about the postgis-devel mailing list