gem-hunter
Language: PLpgSQL
postgres helper functions for making vector tiles
A set of PostgreSQL functions that are useful when creating vector tile sources, either at the query stage in Mapbox Studio or in the earlier data preparation stages.
Everything you need is in postgis-vt-util.sql. This project is also
available as an NPM module if that's useful to your workflow.
Load the file into your database with psql or whatever your usual method is.
Eg:
psql -U <username> -d <dbname> -f postgis-vt-util.sql
Returns an array of the bounding coordinates of the input geometry -
{xmin, ymin, xmax, ymax}. Useful for interfacing with software outside of
PostGIS, among other things.
If an SRID is specified the output will be the bounds of the reprojected geometry, not a reprojected bounding box.
Parameters:
geometry g - Any geometryinteger srid (optional) - The desired output SRID of the bounds, if
different from the input.Returns: float[] - an array of 4 floats, {xmin, ymin, xmax, ymax}
Returns the input text as an integer if possible, otherwise null.
Parameters:
text i - Text that you would like as an integer.Returns: integer
Returns the input text as an numeric if possible, otherwise null.
Parameters:
text i - Text that you would like as an numeric.Returns: numeric
Returns a "hash" of a geometry's position on a specified grid to use in a GROUP BY clause. Useful for limiting the density of points or calculating a localized importance ranking.
This function is most useful on point geometries intended for label placement
(eg points of interest) but will accept any geometry type. It is usually used
as part of either a DISTINCT ON expression or a rank() window function.
Parameters:
geometry g - A geometry.numeric grid_size - The cell size of the desired grouping grid.Returns: text - A text representation of the labelgrid cell
Example Mapbox Studio query:
( SELECT * FROM ( SELECT DISTINCT ON (LabelGrid(geom, 64*!pixel_width!)) * FROM ( SELECT id, name, class, population, geom FROM city_points WHERE geom && !bbox! ) AS raw ORDER BY LabelGrid(geom, 64*!pixel_width!), population DESC, id ) AS filtered ORDER BY population DESC, id ) AS final
Returns the largest single part of a multigeometry.
ST_GeometryN(<geom>, 1) will be
returned. (See the documentation for that function.)Parameters:
geometry g - A geometry.Returns: geometry - The largest single part of the input geometry.
This function tries to estimate whether a line geometry would be long enough to have the given text placed along it at the specified scale.
It is useful in vector tile queries to filter short lines from zoom levels where they would be unlikely to have text places on them anyway.
Parameters:
numeric zoom - The Web Mercator zoom level you are considering.text label - The label text that you will be placing along the line.geometry(linestring) g - A line geometry.Returns: boolean
Creates a CircularString arc based on 3 input points.
Parameters:
geometry(point) p0 - The starting point of the arc.geometry(point) p1 - A point along the path of the arc.geometry(point) p2 - The end point of the arc.integer srid (optional) - Sets the SRID of the output geometry. Useful
when input points have no SRID. If not specified the SRID of the first
input geometry will be assigned to the output.Returns: geometry(linestring)
Examples:
SELECT MakeArc( ST_MakePoint(-100, 0), ST_MakePoint(0, 100), ST_MakePoint(100, 0), 3857 );
Wraps ST_Buffer to adjust the buffer distance by latitude in order to approximate real-world measurements. Assumes input geometries are Web Mercator and input distances are real-world meters. Accuracy decreases for larger buffer distances and at extreme latitudes.
Parameters:
geometry g - A geometry to buffer.numeric distance - The distance you would like to buffer, in real-world
meters.Returns: geometry
Wrapper for ST_DWithin that adjusts distance by latitude to approximate real- world measurements. Assumes input geometries are Web Mercator and input distances are real-world meters. Accuracy decreases for larger distances and at extreme latitudes.
Parameters:
geometry g1 - The first geometry.geometry g2 - The second geometry.numeric distance - The maximum distance to check againstReturns: boolean
Wrapper for ST_Length that adjusts distance by latitude to approximate real- world measurements. Assumes input geometries are Web Mercator. Accuracy decreases for larger y-axis ranges of the input.
Parameters:
geometry g - A (multi)linestring geometry.Returns: numeric
Returns an oriented minimum-bounding rectangle for a geometry.
Parameters:
geometry g - A geometry.Returns: geometry(polygon)
Filters small rings (both inner and outer) from a multipolygon based on area.
Parameters:
geometry g - A multipolygonfloat area_threshold - the minimum ring area to keep.Returns: geometry - a polygon or multipolygon
Buffers a polygon progressively (on an exponential scale) until the area of the result hits a certain threshold ratio to the original area. The result is also simplified with a tolerance matching the inset distance.
Parameters:
geometry g - A (multi)polygon.float ratio - The threshold for how much smaller (by area) you want
the shrunk polygon to be compared to the original. Eg a value of 0.6
would result in a polygon that is at least 60% as large as the input.boolean simplify - Defaults to false. Whether or not you would
like the shrunk geometry simplified.Returns: geometry
Given a Web Mercator tile ID as (z, x, y), returns a bounding-box geometry of the area covered by that tile.
Parameters:
integer z - A tile zoom level.integer x - A tile x-position.integer y - A tile y-position.integer srid - SRID of the desired target projection of the bounding
box. Defaults to 3857 (Web Mercator).Returns: geometry(polygon)
Helper to wrap ST_PointOnSurface, ST_MakeValid. This is needed because of a ST_PointOnSurface bug in geos < 3.3.8 where POLYGON EMPTY can pass through as a polygon geometry. If the input geometry is a polygon with less than 5 points the ST_Centroid of the polygon will be used instead of ST_PointOnSurface to speed up calculation.
Parameters:
geometry g - A geometry.Returns: geometry(point)
Example:
-- Create an additional point geometry colums for labeling ALTER TABLE city_park ADD COLUMN geom_label geometry(point); UPDATE city_park SET geom_label = ToPoint(geom);
Takes a web mercator zoom level and returns the pixel resolution for that scale, assuming 256x256 pixel tiles. Non-integer zoom levels are accepted.
Parameters:
integer or float z - A Web Mercator zoom level.Returns: float
Examples:
-- Delete all polygons smaller than 1px square at zoom level 10 DELETE FROM water_polygons WHERE sqrt(ST_Area(geom)) < ZRes(10); -- Simplify geometries to a resolution appropriate for zoom level 10 UPDATE water_polygons SET geom = ST_Simplify(geom, ZRes(10));
Returns a Web Mercator integer zoom level given a scale denominator.
Useful with Mapnik's !scale_denominator! token in vector tile source queries.
Parameters:
numeric scale_denominator - The denominator of the scale, eg 250000
for a 1:250,000 scale.Returns: integer
Example Mapbox Studio query:
( SELECT * FROM roads WHERE Z(!scale_denominator!) >= 12 ) AS data