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

Sun Sep 28 11:11:06 PDT 2014

Ok, for ST_AsTWKB it works great to use default values (without
"strict"):

CREATE OR REPLACE FUNCTION ST_AsTWKB(geometry,int4,int8 default
null,boolean default false, boolean default false)
	RETURNS bytea
	AS '$libdir/postgis-2.2','TWKBFromLWGEOM'
	LANGUAGE 'c' immutable;

But for aggregates it seems more difficult. There is no default option
for the aggregate definition. So I think we need 4 variants of that

CREATE AGGREGATE st_astwkbagg(geometry,int,int8,boolean,boolean) 
CREATE AGGREGATE st_astwkbagg(geometry,int,int8,boolean) 
CREATE AGGREGATE st_astwkbagg(geometry,int,int8n) 
CREATE AGGREGATE st_astwkbagg(geometry,int) 

That I can live with , but I don't like that we also need another four
variants of the transfer function. They can be defines with default but
the aggregate needs a transfer function with matching arguments:

CREATE OR REPLACE FUNCTION pgis_twkb_accum_transfn(internal,
geometry,int,int8,boolean,boolean)
CREATE OR REPLACE FUNCTION pgis_twkb_accum_transfn(internal,
geometry,int,int8,boolean)
CREATE OR REPLACE FUNCTION pgis_twkb_accum_transfn(internal,
geometry,int,int8)
CREATE OR REPLACE FUNCTION pgis_twkb_accum_transfn(internal,
geometry,int)

The final function is not affected by the input options.

Is htere another way to do this or will this aggreagate need 5 functions
and 4 aggregate definitions?

Thanks
Nicklas

On lø., 2014-09-27 at 22:42 +0200, Nicklas Avén wrote:
> 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
> used.
> 
> 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
> problems.
> 
> 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:
> https://github.com/nicklasaven/offlineMap/blob/master/offlineMap.pdf
> 
> 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
> points.
> 
> 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
> https://github.com/mourner/rbush/blob/master/rbush.js
> 
> 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
> 
> Nicklas
>  
> 
> 
> 
> 
> 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
> > 
> 
> 
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
> 