ST_AsMVT

classic Classic list List threaded Threaded
20 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

ST_AsMVT

Björn Harrtell
As a continuation of my work with ST_AsGeobuf (1) I've started work on St_AsMVT (2), which is a function intended to output a Mapbox Vector Tile (3).

A feature of Mapbox Vector Tiles are that they can contain several layers of data within a tile, typically corresponding to a separate source schema. So I'm thinking about how to specify the parameters to ST_AsMVT to support fetching the data for a tile and this is my initial proposal:

CREATE FUNCTION ST_AsMVT(query text, extent box2d, resolution double, buffer integer, pixelratio integer)

The query parameter is expected to produce rows with a specific schema like follows:

CREATE TABLE output
(
  layer text,
  id bigint,
  geom geometry,
  attributes json
)

The above schema would allow unioning data from multiple tables with differing attributes (represented using the json datatype, alternatively it could be a hstore?). Note that it's up the the query to include geometries to be considered (usually extent + buffer * resolution).

The extent parameter defines the geographic extent to be visible within the tile (i.e does not include buffer).

The resolution parameter defines units per pixel used to translate geometric coordinates into tile coordinates.

The buffer parameter defines how many pixels should be included outside visible tile coordinates until clipping the geometry. This is used to avoid border rendering artifacts.

The pixelratio parameter optionally multiplies tile coordinate space to support hdpi displays. Default should probably be 1.


/Björn

_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Paul Ramsey-3
Would be cooler if it just took in a ROW. :)
P

On Fri, Sep 16, 2016 at 4:27 AM, Björn Harrtell <[hidden email]> wrote:
As a continuation of my work with ST_AsGeobuf (1) I've started work on St_AsMVT (2), which is a function intended to output a Mapbox Vector Tile (3).

A feature of Mapbox Vector Tiles are that they can contain several layers of data within a tile, typically corresponding to a separate source schema. So I'm thinking about how to specify the parameters to ST_AsMVT to support fetching the data for a tile and this is my initial proposal:

CREATE FUNCTION ST_AsMVT(query text, extent box2d, resolution double, buffer integer, pixelratio integer)

The query parameter is expected to produce rows with a specific schema like follows:

CREATE TABLE output
(
  layer text,
  id bigint,
  geom geometry,
  attributes json
)

The above schema would allow unioning data from multiple tables with differing attributes (represented using the json datatype, alternatively it could be a hstore?). Note that it's up the the query to include geometries to be considered (usually extent + buffer * resolution).

The extent parameter defines the geographic extent to be visible within the tile (i.e does not include buffer).

The resolution parameter defines units per pixel used to translate geometric coordinates into tile coordinates.

The buffer parameter defines how many pixels should be included outside visible tile coordinates until clipping the geometry. This is used to avoid border rendering artifacts.

The pixelratio parameter optionally multiplies tile coordinate space to support hdpi displays. Default should probably be 1.


/Björn

_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel


_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Paul Norman
In reply to this post by Björn Harrtell

On 9/16/2016 4:27 AM, Björn Harrtell wrote:
CREATE FUNCTION ST_AsMVT(query text, extent box2d, resolution double, buffer integer, pixelratio integer)

As an interface I propose taking in a row or similar instead of a query. Anything that takes SQL as text can be a problem.

For the other parameters I recommend name text, bounds box2d, extent integer DEFAULT 4096, buffer integer DEFAULT 0, clip_geoms bool DEFAULT true.

name is the required name of the layer.

bounds is the area which corresponds to the unbuffered area of the vector tile, and will typically be the area of an xyz tile.

Extent is the extent for the vector tile, as specified in the spec.

Buffer is how far to go out for geometries to include as well as used in clipping.

clip_geoms is if geometries should be clipped at the buffered extent or not. It's valid to include a geom that goes beyond the buffered extent, but this is not normally done for rendering.

For a return type, I'd go with bytea, being the binary data for the layer. Don't apply gzip compression, it's not part of the vector tile itself.

Thanks to the properties of mapbox vector tiles, you can then build a multi-layer tile by concatenating the results of multiple ST_AsMVT calls

_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Björn Harrtell
2016-09-16 23:35 GMT+02:00 Paul Norman <[hidden email]>:

On 9/16/2016 4:27 AM, Björn Harrtell wrote:
CREATE FUNCTION ST_AsMVT(query text, extent box2d, resolution double, buffer integer, pixelratio integer)

As an interface I propose taking in a row or similar instead of a query. Anything that takes SQL as text can be a problem.

For the other parameters I recommend name text, bounds box2d, extent integer DEFAULT 4096, buffer integer DEFAULT 0, clip_geoms bool DEFAULT true.

name is the required name of the layer.

bounds is the area which corresponds to the unbuffered area of the vector tile, and will typically be the area of an xyz tile.

Extent is the extent for the vector tile, as specified in the spec.

Buffer is how far to go out for geometries to include as well as used in clipping.

clip_geoms is if geometries should be clipped at the buffered extent or not. It's valid to include a geom that goes beyond the buffered extent, but this is not normally done for rendering.

For a return type, I'd go with bytea, being the binary data for the layer. Don't apply gzip compression, it's not part of the vector tile itself.

Thanks to the properties of mapbox vector tiles, you can then build a multi-layer tile by concatenating the results of multiple ST_AsMVT calls

Thanks Paul, I thought the first Paul was messing with me but now I see he was serious. :) To clarify, I understand that SQL as text is a problem I was just not seeing any alternative.

I think your recommendations for the other parameters make sense.

I was unaware of the possibility to concatenate layers in binary format, it seems reasonable but need to investigate further. I'm still confused about using a "row or similar" type instead of a query, if by that you mean to encode just a single geometry at a time, because I can't see how that could be possible to concatenate as binary. Perhaps my confusion can be explained by that I also have problems understanding the "40.3.4. Row Types" chapter in the PostgreSQL documentation.
 

_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel


_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Paul Ramsey-3
A row parameter will be tricky to code, but worth it, I think. You’ll probably have to hunt through the pgsql source to find one to work off of, but you’ll learn a lot about pgsql internals in the process.

P.

On Sep 16, 2016, at 3:29 PM, Björn Harrtell <[hidden email]> wrote:

2016-09-16 23:35 GMT+02:00 Paul Norman <[hidden email]>:

On 9/16/2016 4:27 AM, Björn Harrtell wrote:
CREATE FUNCTION ST_AsMVT(query text, extent box2d, resolution double, buffer integer, pixelratio integer)

As an interface I propose taking in a row or similar instead of a query. Anything that takes SQL as text can be a problem.

For the other parameters I recommend name text, bounds box2d, extent integer DEFAULT 4096, buffer integer DEFAULT 0, clip_geoms bool DEFAULT true.

name is the required name of the layer.

bounds is the area which corresponds to the unbuffered area of the vector tile, and will typically be the area of an xyz tile.

Extent is the extent for the vector tile, as specified in the spec.

Buffer is how far to go out for geometries to include as well as used in clipping.

clip_geoms is if geometries should be clipped at the buffered extent or not. It's valid to include a geom that goes beyond the buffered extent, but this is not normally done for rendering.

For a return type, I'd go with bytea, being the binary data for the layer. Don't apply gzip compression, it's not part of the vector tile itself.

Thanks to the properties of mapbox vector tiles, you can then build a multi-layer tile by concatenating the results of multiple ST_AsMVT calls

Thanks Paul, I thought the first Paul was messing with me but now I see he was serious. :) To clarify, I understand that SQL as text is a problem I was just not seeing any alternative.

I think your recommendations for the other parameters make sense.

I was unaware of the possibility to concatenate layers in binary format, it seems reasonable but need to investigate further. I'm still confused about using a "row or similar" type instead of a query, if by that you mean to encode just a single geometry at a time, because I can't see how that could be possible to concatenate as binary. Perhaps my confusion can be explained by that I also have problems understanding the "40.3.4. Row Types" chapter in the PostgreSQL documentation.
 

_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel

_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel


_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Björn Harrtell


2016-09-17 0:41 GMT+02:00 Paul Ramsey <[hidden email]>:
A row parameter will be tricky to code, but worth it, I think. You’ll probably have to hunt through the pgsql source to find one to work off of, but you’ll learn a lot about pgsql internals in the process.

Yay. :)

But you still have to convince me it's at all possible to produce a vector tile by binary concatenation, for layers and/or geometries. When diving into this subject I related to the ongoing work (unfortunately it seem to have stalled) to make Geobuf streamable. As it is Geobuf is not streamable which is discussed at https://github.com/mapbox/geobuf/issues/37 and as far as I can understand it the same applies to the vector tile format?
 

P.


On Sep 16, 2016, at 3:29 PM, Björn Harrtell <[hidden email]> wrote:

2016-09-16 23:35 GMT+02:00 Paul Norman <[hidden email]>:

On 9/16/2016 4:27 AM, Björn Harrtell wrote:
CREATE FUNCTION ST_AsMVT(query text, extent box2d, resolution double, buffer integer, pixelratio integer)

As an interface I propose taking in a row or similar instead of a query. Anything that takes SQL as text can be a problem.

For the other parameters I recommend name text, bounds box2d, extent integer DEFAULT 4096, buffer integer DEFAULT 0, clip_geoms bool DEFAULT true.

name is the required name of the layer.

bounds is the area which corresponds to the unbuffered area of the vector tile, and will typically be the area of an xyz tile.

Extent is the extent for the vector tile, as specified in the spec.

Buffer is how far to go out for geometries to include as well as used in clipping.

clip_geoms is if geometries should be clipped at the buffered extent or not. It's valid to include a geom that goes beyond the buffered extent, but this is not normally done for rendering.

For a return type, I'd go with bytea, being the binary data for the layer. Don't apply gzip compression, it's not part of the vector tile itself.

Thanks to the properties of mapbox vector tiles, you can then build a multi-layer tile by concatenating the results of multiple ST_AsMVT calls

Thanks Paul, I thought the first Paul was messing with me but now I see he was serious. :) To clarify, I understand that SQL as text is a problem I was just not seeing any alternative.

I think your recommendations for the other parameters make sense.

I was unaware of the possibility to concatenate layers in binary format, it seems reasonable but need to investigate further. I'm still confused about using a "row or similar" type instead of a query, if by that you mean to encode just a single geometry at a time, because I can't see how that could be possible to concatenate as binary. Perhaps my confusion can be explained by that I also have problems understanding the "40.3.4. Row Types" chapter in the PostgreSQL documentation.
 

_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel

_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel



_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Björn Harrtell
2016-09-17 0:45 GMT+02:00 Björn Harrtell <[hidden email]>:


2016-09-17 0:41 GMT+02:00 Paul Ramsey <[hidden email]>:
A row parameter will be tricky to code, but worth it, I think. You’ll probably have to hunt through the pgsql source to find one to work off of, but you’ll learn a lot about pgsql internals in the process.

Yay. :)

But you still have to convince me it's at all possible to produce a vector tile by binary concatenation, for layers and/or geometries. When diving into this subject I related to the ongoing work (unfortunately it seem to have stalled) to make Geobuf streamable. As it is Geobuf is not streamable which is discussed at https://github.com/mapbox/geobuf/issues/37 and as far as I can understand it the same applies to the vector tile format?

Found https://github.com/mapbox/vector-tile-spec/issues/11 supporting the claim that a multi-layered vector tile is indeed a set of independent layer messages. Still not sure about the possibility of aggregating the contents of a layer as separate calls.

 

P.


On Sep 16, 2016, at 3:29 PM, Björn Harrtell <[hidden email]> wrote:

2016-09-16 23:35 GMT+02:00 Paul Norman <[hidden email]>:

On 9/16/2016 4:27 AM, Björn Harrtell wrote:
CREATE FUNCTION ST_AsMVT(query text, extent box2d, resolution double, buffer integer, pixelratio integer)

As an interface I propose taking in a row or similar instead of a query. Anything that takes SQL as text can be a problem.

For the other parameters I recommend name text, bounds box2d, extent integer DEFAULT 4096, buffer integer DEFAULT 0, clip_geoms bool DEFAULT true.

name is the required name of the layer.

bounds is the area which corresponds to the unbuffered area of the vector tile, and will typically be the area of an xyz tile.

Extent is the extent for the vector tile, as specified in the spec.

Buffer is how far to go out for geometries to include as well as used in clipping.

clip_geoms is if geometries should be clipped at the buffered extent or not. It's valid to include a geom that goes beyond the buffered extent, but this is not normally done for rendering.

For a return type, I'd go with bytea, being the binary data for the layer. Don't apply gzip compression, it's not part of the vector tile itself.

Thanks to the properties of mapbox vector tiles, you can then build a multi-layer tile by concatenating the results of multiple ST_AsMVT calls

Thanks Paul, I thought the first Paul was messing with me but now I see he was serious. :) To clarify, I understand that SQL as text is a problem I was just not seeing any alternative.

I think your recommendations for the other parameters make sense.

I was unaware of the possibility to concatenate layers in binary format, it seems reasonable but need to investigate further. I'm still confused about using a "row or similar" type instead of a query, if by that you mean to encode just a single geometry at a time, because I can't see how that could be possible to concatenate as binary. Perhaps my confusion can be explained by that I also have problems understanding the "40.3.4. Row Types" chapter in the PostgreSQL documentation.
 

_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel

_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel




_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Sandro Santilli-3
On Sat, Sep 17, 2016 at 04:25:06PM +0200, Björn Harrtell wrote:

> Found https://github.com/mapbox/vector-tile-spec/issues/11 supporting the
> claim that a multi-layered vector tile is indeed a set of independent layer
> messages. Still not sure about the possibility of aggregating the contents
> of a layer as separate calls.

If I recall correctly, a good reason to have all the data at once
before starting to encode it was the indexing of values.

That is, attribute _values_ can be put in a dictionary and only
referenced per-feature (think of a palette for graphics).
Such "dictionary" can help in case of large repeated values.

Using a row-by-row approach, makes it impossible to build such
dictionary, which is why my early attemps were just at producing
the geometric part, very much like the GML output works, delegating
composition of the whole final product to the caller (which could
eventually also be a ROW-taking wrapper).

--strk;
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Björn Harrtell
2016-09-17 17:09 GMT+02:00 Sandro Santilli <[hidden email]>:
On Sat, Sep 17, 2016 at 04:25:06PM +0200, Björn Harrtell wrote:

> Found https://github.com/mapbox/vector-tile-spec/issues/11 supporting the
> claim that a multi-layered vector tile is indeed a set of independent layer
> messages. Still not sure about the possibility of aggregating the contents
> of a layer as separate calls.

If I recall correctly, a good reason to have all the data at once
before starting to encode it was the indexing of values.

That is, attribute _values_ can be put in a dictionary and only
referenced per-feature (think of a palette for graphics).
Such "dictionary" can help in case of large repeated values.

Using a row-by-row approach, makes it impossible to build such
dictionary, which is why my early attemps were just at producing
the geometric part, very much like the GML output works, delegating
composition of the whole final product to the caller (which could
eventually also be a ROW-taking wrapper).

Thanks for this insight. I've still not reached a good enough understanding of what is and what is not possible/practical regarding protobuf binary (packed message) composition so will have to do more research.

As a side note, while trying to increase my knowledge about protobuf I've also looked at flatbuffers and wonder if it wouldn't be the better alternative for future spatial formats. Lightning talk at https://www.youtube.com/watch?v=olmL1fUnQAQ by Wouter van Oortmerssen who is to blame for getting me into programming in the first place by creating the now ancient language Amiga E. :)

--strk;


_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Paul Norman
In reply to this post by Sandro Santilli-3
On 9/17/2016 8:09 AM, Sandro Santilli wrote:
On Sat, Sep 17, 2016 at 04:25:06PM +0200, Björn Harrtell wrote:

> Found https://github.com/mapbox/vector-tile-spec/issues/11 supporting the
> claim that a multi-layered vector tile is indeed a set of independent layer
> messages. Still not sure about the possibility of aggregating the contents
> of a layer as separate calls.
If I recall correctly, a good reason to have all the data at once
before starting to encode it was the indexing of values.

That is, attribute _values_ can be put in a dictionary and only
referenced per-feature (think of a palette for graphics).
Such "dictionary" can help in case of large repeated values.

Using a row-by-row approach, makes it impossible to build such
dictionary, which is why my early attemps were just at producing
the geometric part, very much like the GML output works, delegating
composition of the whole final product to the caller (which could
eventually also be a ROW-taking wrapper).

You're right. Joining different layers is fine, but each layer has some common information that is needed.

Should it be an aggregate function then?



_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Sandro Santilli-3
On Sat, Sep 17, 2016 at 05:47:39PM -0700, Paul Norman wrote:
> On 9/17/2016 8:09 AM, Sandro Santilli wrote:

> >Using a row-by-row approach, makes it impossible to build such
> >dictionary, which is why my early attemps were just at producing
> >the geometric part, very much like the GML output works, delegating
> >composition of the whole final product to the caller (which could
> >eventually also be a ROW-taking wrapper).
>
> You're right. Joining different layers is fine, but each layer has
> some common information that is needed.
>
> Should it be an aggregate function then?

Probably, which means you'd also need to define a type
to represent the state (including the dictionary, for example).
It's enough stuff that might be better to move into its own
extension.

--strk;
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Björn Harrtell
2016-09-18 7:48 GMT+02:00 Sandro Santilli <[hidden email]>:
On Sat, Sep 17, 2016 at 05:47:39PM -0700, Paul Norman wrote:
> On 9/17/2016 8:09 AM, Sandro Santilli wrote:

> >Using a row-by-row approach, makes it impossible to build such
> >dictionary, which is why my early attemps were just at producing
> >the geometric part, very much like the GML output works, delegating
> >composition of the whole final product to the caller (which could
> >eventually also be a ROW-taking wrapper).
>
> You're right. Joining different layers is fine, but each layer has
> some common information that is needed.
>
> Should it be an aggregate function then?

Probably, which means you'd also need to define a type
to represent the state (including the dictionary, for example).
It's enough stuff that might be better to move into its own
extension.

What do you think about the alternative of simply supplying the function with a name of a table or view assuming the the proposed structure then dynamically query it internally using the supplied bounds?
 

--strk;
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel


_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Paul Ramsey-3
I think that “pass in a SQL” string, or “table name” are the roads to hackyland. You’re building a database: take in and emit primitive values/objects or tuples or tuplesets. You build primitives and let other folks build the SQL stuff on top.

P.

On Sep 18, 2016, at 8:24 AM, Björn Harrtell <[hidden email]> wrote:

2016-09-18 7:48 GMT+02:00 Sandro Santilli <[hidden email]>:
On Sat, Sep 17, 2016 at 05:47:39PM -0700, Paul Norman wrote:
> On 9/17/2016 8:09 AM, Sandro Santilli wrote:

> >Using a row-by-row approach, makes it impossible to build such
> >dictionary, which is why my early attemps were just at producing
> >the geometric part, very much like the GML output works, delegating
> >composition of the whole final product to the caller (which could
> >eventually also be a ROW-taking wrapper).
>
> You're right. Joining different layers is fine, but each layer has
> some common information that is needed.
>
> Should it be an aggregate function then?

Probably, which means you'd also need to define a type
to represent the state (including the dictionary, for example).
It's enough stuff that might be better to move into its own
extension.

What do you think about the alternative of simply supplying the function with a name of a table or view assuming the the proposed structure then dynamically query it internally using the supplied bounds?
 

--strk;
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel

_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel


_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Björn Harrtell
2016-09-18 17:28 GMT+02:00 Paul Ramsey <[hidden email]>:
I think that “pass in a SQL” string, or “table name” are the roads to hackyland. You’re building a database: take in and emit primitive values/objects or tuples or tuplesets. You build primitives and let other folks build the SQL stuff on top.

Agreed.

I got my hands dirty concatenating encoded protocol buffer messages and fields. Finally I think the wire format have sinked in with me.

I see now that it should be possible to not only binary concatenate layers into a tile but also features into a layer message. I have a feeling I'm late to the party in understanding this, but I guess better late than never.

I'll be back if I survive the trip deeper into PostgreSQL.

/Björn


P.


On Sep 18, 2016, at 8:24 AM, Björn Harrtell <[hidden email]> wrote:

2016-09-18 7:48 GMT+02:00 Sandro Santilli <[hidden email]>:
On Sat, Sep 17, 2016 at 05:47:39PM -0700, Paul Norman wrote:
> On 9/17/2016 8:09 AM, Sandro Santilli wrote:

> >Using a row-by-row approach, makes it impossible to build such
> >dictionary, which is why my early attemps were just at producing
> >the geometric part, very much like the GML output works, delegating
> >composition of the whole final product to the caller (which could
> >eventually also be a ROW-taking wrapper).
>
> You're right. Joining different layers is fine, but each layer has
> some common information that is needed.
>
> Should it be an aggregate function then?

Probably, which means you'd also need to define a type
to represent the state (including the dictionary, for example).
It's enough stuff that might be better to move into its own
extension.

What do you think about the alternative of simply supplying the function with a name of a table or view assuming the the proposed structure then dynamically query it internally using the supplied bounds?
 

--strk;
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel

_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel



_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Björn Harrtell
Hi list,

I'm back from the deep with an initial complete implementation of ST_AsMVT aggregate function (1).

The signature is as follows:

CREATE AGGREGATE ST_AsMVT(name text, bounds box2d, extent int4, buffer int4, clip_geom bool, geom_name text, anyelement)

Typical usage is with a subquery to create a single layer. Here is an example from the regression tests:

SELECT ST_AsMVT('test', ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false, 'geom', q)
FROM (SELECT 1 AS c1, 'abcd'::text AS c2, ST_GeomFromText('POINT(25 17)') AS geom) AS q;

I have been able to use it on real data to produce a set of more than 400 000 tiles with multiple layers and attributes without known issues. Tiles with multiple layers can be created by simply concatenating output from ST_AsMVT.

Feedback on the implementation, usage and/or test results will be much appreciated.


/Björn

2016-09-19 0:22 GMT+02:00 Björn Harrtell <[hidden email]>:
2016-09-18 17:28 GMT+02:00 Paul Ramsey <[hidden email]>:
I think that “pass in a SQL” string, or “table name” are the roads to hackyland. You’re building a database: take in and emit primitive values/objects or tuples or tuplesets. You build primitives and let other folks build the SQL stuff on top.

Agreed.

I got my hands dirty concatenating encoded protocol buffer messages and fields. Finally I think the wire format have sinked in with me.

I see now that it should be possible to not only binary concatenate layers into a tile but also features into a layer message. I have a feeling I'm late to the party in understanding this, but I guess better late than never.

I'll be back if I survive the trip deeper into PostgreSQL.

/Björn


P.


On Sep 18, 2016, at 8:24 AM, Björn Harrtell <[hidden email]> wrote:

2016-09-18 7:48 GMT+02:00 Sandro Santilli <[hidden email]>:
On Sat, Sep 17, 2016 at 05:47:39PM -0700, Paul Norman wrote:
> On 9/17/2016 8:09 AM, Sandro Santilli wrote:

> >Using a row-by-row approach, makes it impossible to build such
> >dictionary, which is why my early attemps were just at producing
> >the geometric part, very much like the GML output works, delegating
> >composition of the whole final product to the caller (which could
> >eventually also be a ROW-taking wrapper).
>
> You're right. Joining different layers is fine, but each layer has
> some common information that is needed.
>
> Should it be an aggregate function then?

Probably, which means you'd also need to define a type
to represent the state (including the dictionary, for example).
It's enough stuff that might be better to move into its own
extension.

What do you think about the alternative of simply supplying the function with a name of a table or view assuming the the proposed structure then dynamically query it internally using the supplied bounds?
 

--strk;
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel

_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel




_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Nicklas Avén
Hi Björn

I have succeeded in installing and can run the example query.

But I do not understand the parameters at once. Could you give some
guidance?

What is extent and buffer?

Sorry if I miss the obvious.

Thanks

Nicklas



On Mon, 2016-10-10 at 11:42 +0200, Björn Harrtell wrote:

> Hi list,
>
> I'm back from the deep with an initial complete implementation of
> ST_AsMVT aggregate function (1).
>
> The signature is as follows:
>
> CREATE AGGREGATE ST_AsMVT(name text, bounds box2d, extent int4,
> buffer int4, clip_geom bool, geom_name text, anyelement)
>
> Typical usage is with a subquery to create a single layer. Here is an
> example from the regression tests:
>
> SELECT ST_AsMVT('test', ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096,
> 4096)), 4096, 0, false, 'geom', q)
> FROM (SELECT 1 AS c1, 'abcd'::text AS c2, ST_GeomFromText('POINT(25
> 17)') AS geom) AS q;
>
> I have been able to use it on real data to produce a set of more than
> 400 000 tiles with multiple layers and attributes without known
> issues. Tiles with multiple layers can be created by simply
> concatenating output from ST_AsMVT.
>
> Feedback on the implementation, usage and/or test results will be
> much appreciated.
>
> 1) https://git.osgeo.org/gogs/postgis/postgis/pulls/5
>
> /Björn
>
> 2016-09-19 0:22 GMT+02:00 Björn Harrtell <[hidden email]>:
> > 2016-09-18 17:28 GMT+02:00 Paul Ramsey <[hidden email]>:
> > > I think that “pass in a SQL” string, or “table name” are the
> > > roads to hackyland. You’re building a database: take in and emit
> > > primitive values/objects or tuples or tuplesets. You build
> > > primitives and let other folks build the SQL stuff on top.
> > >
> >
> > Agreed.
> >
> > I got my hands dirty concatenating encoded protocol buffer messages
> > and fields. Finally I think the wire format have sinked in with me.
> >
> > I see now that it should be possible to not only binary concatenate
> > layers into a tile but also features into a layer message. I have a
> > feeling I'm late to the party in understanding this, but I guess
> > better late than never.
> >
> > I'll be back if I survive the trip deeper into PostgreSQL.
> >
> > /Björn
> >
> > > P.
> > >
> > >
> > > > On Sep 18, 2016, at 8:24 AM, Björn Harrtell <bjorn.harrtell@gma
> > > > il.com> wrote:
> > > >
> > > > 2016-09-18 7:48 GMT+02:00 Sandro Santilli <[hidden email]>:
> > > > > On Sat, Sep 17, 2016 at 05:47:39PM -0700, Paul Norman wrote:
> > > > > > On 9/17/2016 8:09 AM, Sandro Santilli wrote:
> > > > >
> > > > > > >Using a row-by-row approach, makes it impossible to build
> > > > > such
> > > > > > >dictionary, which is why my early attemps were just at
> > > > > producing
> > > > > > >the geometric part, very much like the GML output works,
> > > > > delegating
> > > > > > >composition of the whole final product to the caller
> > > > > (which could
> > > > > > >eventually also be a ROW-taking wrapper).
> > > > > >
> > > > > > You're right. Joining different layers is fine, but each
> > > > > layer has
> > > > > > some common information that is needed.
> > > > > >
> > > > > > Should it be an aggregate function then?
> > > > >
> > > > > Probably, which means you'd also need to define a type
> > > > > to represent the state (including the dictionary, for
> > > > > example).
> > > > > It's enough stuff that might be better to move into its own
> > > > > extension.
> > > >
> > > > What do you think about the alternative of simply supplying the
> > > > function with a name of a table or view assuming the the
> > > > proposed structure then dynamically query it internally using
> > > > the supplied bounds?
> > > >  
> > > > > --strk;
> > > > > _______________________________________________
> > > > > postgis-devel mailing list
> > > > > [hidden email]
> > > > > http://lists.osgeo.org/mailman/listinfo/postgis-devel
> > > > >
> > > >
> > > > _______________________________________________
> > > > postgis-devel mailing list
> > > > [hidden email]
> > > > http://lists.osgeo.org/mailman/listinfo/postgis-devel
> > >
> > >
> >
> >
>
> _______________________________________________
> postgis-devel mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/postgis-devel
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Björn Harrtell
In reply to this post by Björn Harrtell
Hi Nicklas,

The specification explains extent like this:

"A layer MUST contain an extent that describes the width and height of the tile in integer coordinates. The geometries within the Vector Tile MAY extend past the bounds of the tile's area as defined by the extent. Geometries that extend past the tile's area as defined by extent are often used as a buffer for rendering features that overlap multiple adjacent tiles."

Note that "integer coordinates" are local coordinates to the tile itself. A nice graphical explanation can be seen here:

The buffer parameter is also in local tile units and is only relevant if clip_geom is true, in which case ST_AsMVT will clip input geometries at the tile border + eventual buffer. Clipping obviously saves space and an appropriate buffer will ensure the tile contains enough information to avoid border rendering artifacts. For label points a reasonably large buffer might be needed.

Note that the bounds parameter is assumed to be in geographic coordinates and it's the callers responsibility to map these bounds to a tile in a grid. I opted to do it this way to not assume a specific tile grid even if vector tiles is rarely seen with anything else than mercator and the google tile grid scheme.

I plan to expand the documentation to hopefully explain these things better there and provide a better example than one from the regression tests.

/Björn

PS. Mail quote below might look funny because for some reason I did not receive it, it's copy pasted from the list archive.

> Nicklas Avén nicklas.aven at jordogskog.no 
> Mon Oct 17 23:26:38 PDT 2016
>
> Hi Björn 

> I have succeeded in installing and can run the example query. 

> But I do not understand the parameters at once. Could you give some 
guidance? 

> What is extent and buffer? 

Sorry if I miss the obvious. 

> Thanks 

> Nicklas

2016-10-10 11:42 GMT+02:00 Björn Harrtell <[hidden email]>:
Hi list,

I'm back from the deep with an initial complete implementation of ST_AsMVT aggregate function (1).

The signature is as follows:

CREATE AGGREGATE ST_AsMVT(name text, bounds box2d, extent int4, buffer int4, clip_geom bool, geom_name text, anyelement)

Typical usage is with a subquery to create a single layer. Here is an example from the regression tests:

SELECT ST_AsMVT('test', ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false, 'geom', q)
FROM (SELECT 1 AS c1, 'abcd'::text AS c2, ST_GeomFromText('POINT(25 17)') AS geom) AS q;

I have been able to use it on real data to produce a set of more than 400 000 tiles with multiple layers and attributes without known issues. Tiles with multiple layers can be created by simply concatenating output from ST_AsMVT.

Feedback on the implementation, usage and/or test results will be much appreciated.


/Björn


2016-09-19 0:22 GMT+02:00 Björn Harrtell <[hidden email]>:
2016-09-18 17:28 GMT+02:00 Paul Ramsey <[hidden email]>:
I think that “pass in a SQL” string, or “table name” are the roads to hackyland. You’re building a database: take in and emit primitive values/objects or tuples or tuplesets. You build primitives and let other folks build the SQL stuff on top.

Agreed.

I got my hands dirty concatenating encoded protocol buffer messages and fields. Finally I think the wire format have sinked in with me.

I see now that it should be possible to not only binary concatenate layers into a tile but also features into a layer message. I have a feeling I'm late to the party in understanding this, but I guess better late than never.

I'll be back if I survive the trip deeper into PostgreSQL.

/Björn


P.


On Sep 18, 2016, at 8:24 AM, Björn Harrtell <[hidden email]> wrote:

2016-09-18 7:48 GMT+02:00 Sandro Santilli <[hidden email]>:
On Sat, Sep 17, 2016 at 05:47:39PM -0700, Paul Norman wrote:
> On 9/17/2016 8:09 AM, Sandro Santilli wrote:

> >Using a row-by-row approach, makes it impossible to build such
> >dictionary, which is why my early attemps were just at producing
> >the geometric part, very much like the GML output works, delegating
> >composition of the whole final product to the caller (which could
> >eventually also be a ROW-taking wrapper).
>
> You're right. Joining different layers is fine, but each layer has
> some common information that is needed.
>
> Should it be an aggregate function then?

Probably, which means you'd also need to define a type
to represent the state (including the dictionary, for example).
It's enough stuff that might be better to move into its own
extension.

What do you think about the alternative of simply supplying the function with a name of a table or view assuming the the proposed structure then dynamically query it internally using the supplied bounds?
 

--strk;
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel

_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel





_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Björn Harrtell
In reply to this post by Björn Harrtell
Feedback on the initial ST_AsMVT implementation encouraged me to rework it into two functions, ST_AsMVTGeom and ST_AsMVT, which I have recently commited to svn-trunk.

The main issue with the original function was that it could produce invalid tiles as per specification and you could only find out by inspecting the produced end result, as the invalidity might occur in the process of transforming geometry to tile coordinate space.

By separating the geometry transformation into tile coordinate space with additional logic to make sure it's valid, it should not be as easy to accidentally or unintentionally create invalid tiles.

The API of the original ST_AsMVT has changed and is now expected to be used in concert with ST_AsMVTGeom which should be reflected in the updated documentation at http://postgis.net/docs/manual-dev/reference.html#Geometry_Outputs.

See https://trac.osgeo.org/postgis/ticket/3712 for background and discussions leading up these changes.

/Björn

2016-10-10 11:42 GMT+02:00 Björn Harrtell <[hidden email]>:
Hi list,

I'm back from the deep with an initial complete implementation of ST_AsMVT aggregate function (1).

The signature is as follows:

CREATE AGGREGATE ST_AsMVT(name text, bounds box2d, extent int4, buffer int4, clip_geom bool, geom_name text, anyelement)

Typical usage is with a subquery to create a single layer. Here is an example from the regression tests:

SELECT ST_AsMVT('test', ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false, 'geom', q)
FROM (SELECT 1 AS c1, 'abcd'::text AS c2, ST_GeomFromText('POINT(25 17)') AS geom) AS q;

I have been able to use it on real data to produce a set of more than 400 000 tiles with multiple layers and attributes without known issues. Tiles with multiple layers can be created by simply concatenating output from ST_AsMVT.

Feedback on the implementation, usage and/or test results will be much appreciated.


/Björn


2016-09-19 0:22 GMT+02:00 Björn Harrtell <[hidden email]>:
2016-09-18 17:28 GMT+02:00 Paul Ramsey <[hidden email]>:
I think that “pass in a SQL” string, or “table name” are the roads to hackyland. You’re building a database: take in and emit primitive values/objects or tuples or tuplesets. You build primitives and let other folks build the SQL stuff on top.

Agreed.

I got my hands dirty concatenating encoded protocol buffer messages and fields. Finally I think the wire format have sinked in with me.

I see now that it should be possible to not only binary concatenate layers into a tile but also features into a layer message. I have a feeling I'm late to the party in understanding this, but I guess better late than never.

I'll be back if I survive the trip deeper into PostgreSQL.

/Björn


P.


On Sep 18, 2016, at 8:24 AM, Björn Harrtell <[hidden email]> wrote:

2016-09-18 7:48 GMT+02:00 Sandro Santilli <[hidden email]>:
On Sat, Sep 17, 2016 at 05:47:39PM -0700, Paul Norman wrote:
> On 9/17/2016 8:09 AM, Sandro Santilli wrote:

> >Using a row-by-row approach, makes it impossible to build such
> >dictionary, which is why my early attemps were just at producing
> >the geometric part, very much like the GML output works, delegating
> >composition of the whole final product to the caller (which could
> >eventually also be a ROW-taking wrapper).
>
> You're right. Joining different layers is fine, but each layer has
> some common information that is needed.
>
> Should it be an aggregate function then?

Probably, which means you'd also need to define a type
to represent the state (including the dictionary, for example).
It's enough stuff that might be better to move into its own
extension.

What do you think about the alternative of simply supplying the function with a name of a table or view assuming the the proposed structure then dynamically query it internally using the supplied bounds?
 

--strk;
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel

_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel





_______________________________________________
postgis-devel mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Sandro Santilli-3
On Tue, Mar 07, 2017 at 11:31:59PM +0100, Björn Harrtell wrote:

> http://postgis.net/docs/manual-dev/reference.html#Geometry_Outputs.

Good work !
May I suggest to add a See Also section, containing at least the
ST_AsMVTGeom function link ? (I think you can also turn the
first mention of ST_AsMVTGeom into a link).

--strk;
_______________________________________________
postgis-devel mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_AsMVT

Björn Harrtell
2017-03-08 9:56 GMT+01:00 Sandro Santilli <[hidden email]>:
On Tue, Mar 07, 2017 at 11:31:59PM +0100, Björn Harrtell wrote:

> http://postgis.net/docs/manual-dev/reference.html#Geometry_Outputs.

Good work !
May I suggest to add a See Also section, containing at least the
ST_AsMVTGeom function link ? (I think you can also turn the
first mention of ST_AsMVTGeom into a link).

Thanks Sandro,

Improved docs as suggested.
 

--strk;


_______________________________________________
postgis-devel mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-devel
Loading...