Zen

Source string Source string

326/10000 · 326

The string is read only.

English Actions
ST_Histogram
Generally you won't want stats for your whole table, but instead just stats for a particular area, in that case, you'll want to also employ our old friends :command:`ST_Intersects` and :command:`ST_Clip`. If you are also in need of a raster statistics function that doesn't have an aggregate version, you'll want to carry :command:`ST_Union` along for the ride.
For this next example we'll use a different stats function `ST_Histogram <https://postgis.net/docs/RT_ST_Histogram.html>`_ which has no aggregate equivalent, and for this particular variant, is a set returning function. We are using the same area of interest as some prior examples, but we also need to employ geometry :command:`ST_Transform` to transform our NY state plane meters geometry to our NYC State Plane feet rasters. It is almost always more performant to transform the geometry instead of raster and definitely if your geometry is just a single one.
the above query completes in under 60ms and outputs:
Creating Derivative Rasters
PostGIS raster comes packaged with a number of functions for editing rasters. These functions are both used for editing as well as creating derivative raster data sets. You will find these listed in `Raster Editors <https://postgis.net/docs/RT_reference.html#Raster_Editors>`_ and `Raster Management <https://postgis.net/docs/RT_reference.html#Raster_Management_Functions>`_.
Transforming rasters with ST_Transform
Most of our data is in NY State Plane meters (SRID: 26918), however our DEM raster dataset is in NY State Plane feet (SRID: 2263). For the least cumbersome workflow, we need our core datasets to be in the same spatial reference system.
The `raster ST_Transform <https://postgis.net/docs/RT_ST_Transform.html>`_ is the function most suited for this job.
In order to create a new nyc dem dataset in NY State Plane meters, we'll do the following:
The above on my system took about 1.5 minutes. For a larger data set it would take much longer.
The aforementioned examples used two variants of the :command:`ST_Transform` raster function. The first was to get a reference raster that will be used to transform the other raster tiles to guarantee that all tiles have the same alignment. Note the second variant of :command:`ST_Transform` used doesn't even take an input SRID. This is because the SRID and all the pixel scale and block sizes are read from the reference raster. If you used `ST_Transform(rast, srid)` form, then all your rasters might come out with different alignment making it impossible to apply an operation such as :command:`ST_Union` on them.
The only problem with the aforementioned :command:`ST_Transform` approach is that when you transform, the transformed often exists in other tiles. If you looked at the above output closely enough by outputting the convex hull of the rasters, in the next example you'd see annoying overlaps around the borders.
viewed in pgAdmin would look something like:
Using ST_MakeEmptyCoverage to create even tiled rasters
A better approach, albeit a bit slower, is to define your own coverage tile structure from scratch using `ST_MakeEmptyCoverage <https://postgis.net/docs/RT_ST_MakeEmptyCoverage.html>`_ and then find the intersecting tiles for each new tile, and ST_Union these and then use `ST_Transform(ref, ST_Union...)` to create each tile.
For this we'll be using quite a few functions, we learned about earlier.
Repeating the same exercise as earlier:
viewed in pgAdmin we no longer have overlaps:
On my system this took ~10 minutes and returned 3879 rows. After the creation of the table, we'll want to do the usual of adding a spatial index, primary key, and constraints as follows:
Which should take under 2 minutes for this dataset.
Creating overview tables with ST_CreateOverview
Just as with our original dataset, it would be useful to have overview tables to speed up performance of some operations. `ST_CreateOverview <https://postgis.net/docs/RT_CreateOverview.html>`_ is a function fit for that purpose. You can use :command:`ST_CreateOverview` to create overviews also if you neglected to create one during the raster2pgsql load or you decided, you need more overviews.
We'll create level 2 and 3 overviews as we had done with our original using this code.
This process sadly takes a while, and a longer while the more rows you have so be patient. For this dataset it took about 3-5 minutes for the overview factor `2` and 1 minute for the overview factor `3`.
The :command:`ST_CreateOverView` function also adds in the needed constraints so the columns appear with full detail in the `raster_columns` and `raster_overviews` catalogs. It does not add indexes to them though and also does not add an rid column. The rid column is probably not necessary unless you need a primary key to edit with. You would probably want an index which you can create with the following:
ST_CreateOverview has an optional argument for denoting the sampling method. If not specified it uses the default `NearestNeighbor` which is generally the fastest to compute but may not be ideal. Resampling methods is beyond the scope of this workshop.
The intersection of rasters and geometries
There are a couple of functions commonly used to compute intersections of rasters and geometries. We've already seen :command:`ST_Clip` in action which returns the intersection of a raster and geometry as a raster, but there are others. For point data, the most commonly used is :command:`ST_Value` and then there is the :command:`ST_Intersection` which has several overloads some returning rasters and some returning a set of `geomval`.
Pixel values at a geometric Point
If you need to return values from rasters based on intersection of several ad-hoc geometry points, you'll use `ST_Value <https://postgis.net/docs/RT_ST_Value.html>`_ or it's nearest relative `ST_NearestValue <https://postgis.net/docs/RT_ST_NearestValue.html>`_.

Loading…

User avatar None

String updated in the repository

  1. postgis-workshop
  2. rasters
  3. English
A better approach, albeit a bit slower, is to define your own coverage tile structure from scratch using `ST_MakeEmptyCoverage <https://postgis.net/docs/RT_ST_MakeEmptyCoverage.html>`_ and then find the intersecting tiles for each new tile, and ST_Union these and then use `ST_Transform(ref, ST_Union...)` to create each tile.
A better approach, albeit a bit slower, is to define your own coverage tile structure from scratch using `ST_MakeEmptyCoverage <https://postgis.net/docs/RT_ST_MakeEmptyCoverage.html>`_ and then find the intersecting tiles for each new tile, and ST_Union these and then use `ST_Transform(ref, ST_Union...)` to create each tile.
a year ago
Browse all string changes

Things to check

Ellipsis

The string uses three dots (...) instead of an ellipsis character (…)

Reset

Glossary

English English
No related strings found in the glossary.

String information

Flags
read-only
Source string location
../../en/rasters.rst:880
String age
a year ago
Last updated
a year ago
Source string age
a year ago
Translation file
postgis-intro/sources/locale/pot/rasters.pot, string 162