Re: [postgis-users] Postgres as cache and renderer for vector tile server

[Brian Panulla]

I use TileStache for my tile layers (http://tilestache.org/doc/), all data
stored in PostGIS. While my tiles are raster images rendered with Mapnik,
TileStache also now has Vector support, which might meet your needs: (
http://tilestache.org/doc/TileStache.Vector.html).

TileStache is very simple and lightweight and needs very little in the way
of infrastructure outside of Python, a few package requirements, and a
WSGI-compatible Web server.

-B

On Mon, Mar 21, 2016 at 8:59 AM, Peter Devoy  wrote:

> Hi all,
>
> Has anyone here tried using Postgres as the caching layer in a vector tile
> server?
>
> I need to set up a tile server and what I am thinking is have PostGIS cut
> up
> geometries into GeoJSON vector tiles as requests come in and cache the
> JSON for
> said requests in some other Postgres table(s) using a JSON/JSONB column.
> Then
> just have a cron job or something set up to prune the cache table so that
> the
> least frequently accessed tiles get removed.
>
> I know there are existing vector tile server solutions but I would like to
> be
> serving tiles in British National Grid rather than web mercator so they
> would
> all need some hacking.  I also have reservations about their architecture
> --
> I would rather not complicate my stack if Postgres can handle it.
>
> Is it a dumb endeavour?
>
> Kind regards
>
>
> Peter
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Postgres as cache and renderer for vector tile server

[Burgholzer, Robert (DEQ)]

Thanks Paul & Peter for the stimulating topics and resources!

-Original Message-
From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Peter Devoy
Sent: Monday, March 21, 2016 1:34 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Postgres as cache and renderer for vector tile 
server

>Drupal
HI Robert, thanks for the suggestion but unfortunately I am not using Drupal in 
any projects.  Best of luck with your modules though, Mapzen's SQL functions 
may be of interest to you:
https://github.com/mapzen/vector-datasource/tree/master/data

> I think a generic HTTP cache in front of your web service would be the 
> most architecturally attractive solution, no?
Thanks Paul, I think you are on to something there.  I am very much throwing my 
self into the deep end here so this is the sort of obvious thing I can miss.

--
Peter Devoy
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Postgres as cache and renderer for vector tile server

[Peter Devoy]

>Drupal
HI Robert, thanks for the suggestion but unfortunately I am not
using Drupal in any projects.  Best of luck with your modules
though, Mapzen's SQL functions may be of interest to you:
https://github.com/mapzen/vector-datasource/tree/master/data

> I think a generic HTTP cache in front of your web service
> would be the most architecturally attractive solution, no?
Thanks Paul, I think you are on to something there.  I am very much
throwing my self into the deep end here so this is the sort of obvious
thing I can miss.

--
Peter Devoy
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Postgres as cache and renderer for vector tile server

[Paul Ramsey]

ISTM that map-specific tile caching solutions are mostly there to
provide things that generic HTTP caching systems don't do, like
metatiling, or guttering the map requests. If you don't need those
features, I think a generic HTTP cache in front of your web service
would be the most architecturally attractive solution, no? Stick in
squid or varnish and apply whatever cache lifetime parameters you want
at the cache level.

At the higher end, using a generic tool like varnish makes it easy to
bolt in a CDN as well. There's a cool talk about using S3 and AWS CDN
for tile caching https://vimeo.com/142215056. S3 gets more supple for
this kind of thing all the time.

ATB,

P

On Mon, Mar 21, 2016 at 8:59 AM, Peter Devoy  wrote:
> Hi all,
>
> Has anyone here tried using Postgres as the caching layer in a vector tile
> server?
>
> I need to set up a tile server and what I am thinking is have PostGIS cut up
> geometries into GeoJSON vector tiles as requests come in and cache the JSON 
> for
> said requests in some other Postgres table(s) using a JSON/JSONB column. Then
> just have a cron job or something set up to prune the cache table so that the
> least frequently accessed tiles get removed.
>
> I know there are existing vector tile server solutions but I would like to be
> serving tiles in British National Grid rather than web mercator so they would
> all need some hacking.  I also have reservations about their architecture --
> I would rather not complicate my stack if Postgres can handle it.
>
> Is it a dumb endeavour?
>
> Kind regards
>
>
> Peter
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Postgres as cache and renderer for vector tile server

[Robert Burgholzer]

Hey Peter - no dumb endeavors only dumb implementations :)?  Don't know if
my implementation is dumb or not but I am currently using Drupal to
generate GeoJSON vectors.  Since Drupal has caching in it's core I am
relying on it to provide this which it seems to do OK (up to about 500
points it performs acceptably, large polygons obviously pose greater
challenges).

The dumb part of this stack is that the "tiling" part of the module that
serves up the vectors is not tiled at all, rather, it relies on BBOX
containment, so it is not a very efficient cache.  I am currently setting
out the steps for modifying the module in question (View GeoJSON) to
respond to Vector tile requests.  If you are at all interested in this
solution, below are some links to the proposed tiling schema and the
modules that form the basis for the approach.

Vector Tiling in Drupal/Views GeoJSON: https://www.drupal.org/node/2690877
Drupal GeoField: https://www.drupal.org/project/geofield
Geofield PostGIS sub-module: https://www.drupal.org/project/geofield_postgis

Regards,
robert

On Mon, Mar 21, 2016 at 11:59 AM, Peter Devoy  wrote:

> Hi all,
>
> Has anyone here tried using Postgres as the caching layer in a vector tile
> server?
>
> I need to set up a tile server and what I am thinking is have PostGIS cut
> up
> geometries into GeoJSON vector tiles as requests come in and cache the
> JSON for
> said requests in some other Postgres table(s) using a JSON/JSONB column.
> Then
> just have a cron job or something set up to prune the cache table so that
> the
> least frequently accessed tiles get removed.
>
> I know there are existing vector tile server solutions but I would like to
> be
> serving tiles in British National Grid rather than web mercator so they
> would
> all need some hacking.  I also have reservations about their architecture
> --
> I would rather not complicate my stack if Postgres can handle it.
>
> Is it a dumb endeavour?
>
> Kind regards
>
>
> Peter
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users




-- 
--
Robert W. Burgholzer
 'Making the simple complicated is commonplace; making the complicated
simple, awesomely simple, that's creativity.'  - Charles Mingus
Athletics: http://athleticalgorithm.wordpress.com/
Science: http://robertwb.wordpress.com/
Wine: http://reesvineyard.wordpress.com/
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] Postgres as cache and renderer for vector tile server

[Peter Devoy]

Hi all,

Has anyone here tried using Postgres as the caching layer in a vector tile
server?

I need to set up a tile server and what I am thinking is have PostGIS cut up
geometries into GeoJSON vector tiles as requests come in and cache the JSON for
said requests in some other Postgres table(s) using a JSON/JSONB column. Then
just have a cron job or something set up to prune the cache table so that the
least frequently accessed tiles get removed.

I know there are existing vector tile server solutions but I would like to be
serving tiles in British National Grid rather than web mercator so they would
all need some hacking.  I also have reservations about their architecture --
I would rather not complicate my stack if Postgres can handle it.

Is it a dumb endeavour?

Kind regards


Peter
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Slow performance when selecting a geometry column

[Rémi Cura]

Hey
pgadmin takes a long time to __display__ the result of any query.
If you want a somehow better timing,
user EXPLAIN ANALYSE, that is Maj+F7.
It will perform all the real computing, plus some time for measure, but no
time for output.

Cheers,


2016-03-18 15:43 GMT+01:00 Paul Ramsey :

> Aah! PgAdminIII. Watch your CPU meter and see how busy PgAdmin is vs
> PostgreSQL. See how long this runs in:
>
> SELECT ST_Area(geom) FROM mytable;
>
> Still has to rip every geometry off disk, and has to do a *calculation* on
> it, before returning the result to the client.
>
> P
>
> On Fri, Mar 18, 2016 at 6:07 AM, David Robison 
> wrote:
>
>> Actually the timing test was done on the same machine using PGAdmin-III.
>> What is interesting is that if I return the geometry using something like
>> ST_Simplify(the_geom, 0.1, false) then it returns in just a few hundred
>> milliseconds.
>>
>> David
>>
>>
>>
>> *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] *On
>> Behalf Of *Nordgren, Bryce L -FS
>> *Sent:* Friday, March 18, 2016 8:59 AM
>>
>> *To:* PostGIS Users Discussion 
>> *Subject:* Re: [postgis-users] Slow performance when selecting a
>> geometry column
>>
>>
>>
>> What kind of network separates client and server? Conservatively assuming
>> that each point is only two 64-bit binary floats, your 56 points equals
>> 9MB of additional payload.
>>
>>
>>
>> If you did something like “ST_AsText(geom)”, the additional payload is
>> much, MUCH larger.
>>
>>
>>
>> I’d also be interested to know if there’s some kind of data
>> manipulation/packing/compression going on between client and server.
>>
>>
>>
>> Bryce
>>
>>
>>
>> *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org
>> ] *On Behalf Of *David Robison
>> *Sent:* Friday, March 18, 2016 6:14 AM
>> *To:* PostGIS Users Discussion 
>> *Subject:* Re: [postgis-users] Slow performance when selecting a
>> geometry column
>>
>>
>>
>> So there is a total of over 560,000 points in the 8000 records returned.
>> I would have assumed that it was simply returning the contents of the
>> geometry column and that the number of points (albeit they take up space)
>> would not affect the select time. Is PostGIS doing something with the
>> geometries as it is fetching them from the DB other than just returning
>> them? Thanks, David
>>
>>
>>
>> *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org
>> ] *On Behalf Of *Paul Ramsey
>> *Sent:* Thursday, March 17, 2016 9:53 PM
>> *To:* PostGIS Users Discussion 
>> *Subject:* Re: [postgis-users] Slow performance when selecting a
>> geometry column
>>
>>
>>
>> SELECT Sum(ST_NPoints(geom)) FROM thetable;
>>
>>
>>
>> On Thu, Mar 17, 2016 at 5:57 PM, David Robison 
>> wrote:
>>
>> I am having an issue with a postgis database with the time it takes to
>> query the geometry column. The query selects 8000 records. The time to
>> retrieve the records when not returning the geometry column is about 100ms.
>> However, the time for the same query when requesting the geometry column
>> takes about 9 seconds.  Any thoughts on how I can improve the performance
>> reading the geometry column from a postgis database? I am using PostgreSQL
>> 9.4 and Postgis 22.1.
>>
>> Thanks, David
>>
>>
>>
>> *David Robison*
>>
>> *Principal System Engineer*
>>
>> O. +1 757 546 3401
>>
>> M. +1 757 286 0022
>>
>> david.robi...@q-free.com
>>
>> www.q-free.com/openroads
>>
>> [image: cid:image001.png@01D15905.23A1F460]
>>
>> *Q-Free Open Roads*
>>
>> 103 Watson Road
>>
>> Chesapeake VA 23320
>>
>> [image: cid:image001.png@01D15905.23A1F460]
>>
>> [image: cid:image002.png@01D15905.23A1F460]
>> --
>>
>> This email communication (including any attachments) may contain 
>> confidential and/or privileged material intended solely for the individual 
>> or entity to which it is addressed.
>> If you are not the intended recipient, please delete this email immediately.
>>
>>
>>
>>
>>
>>
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>>
>>
>>
>>
>>
>>
>> This electronic message contains information generated by the USDA solely
>> for the intended recipients. Any unauthorized interception of this message
>> or the use or disclosure of the information it contains may violate the law
>> and subject the violator to civil or criminal penalties. If you believe you
>> have received this message in error, please notify the sender and delete
>> the email immediately.
>>
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>
>
>