2014/09/26
in tips
(
newbie, raster, ST_ValueCount, ST_Value )

PostGIS raster has so so many functions and probably at least 10 ways of doing something some much much slower than others. Suppose
you have a raster, or you have a raster area of interest — say elevation raster for example, and you want to know the distinct pixel values in the area.
The temptation is to reach for `ST_Value`

function in raster, but there is a much much more efficient function to use, and that is the `ST_ValueCount`

function.

`ST_ValueCount`

function is one of many statistical raster functions available with PostGIS 2.0+. It is a set returning function that returns 2 values for each row: a pixel value (value), and a count of pixels (count) in the raster that have that value. It also has variants that allow you to filter for certain pixel values.

This tip was prompted by the question on stackexchange How can I extract all distinct values from a PostGIS Raster?

Read More…

2014/03/14
in tips
(
newbie, geometry, ST_Intersects, ST_Intersection, ST_CoveredBy )

Doing an `ST_Intersection`

is much slower than relation checks such as
`ST_Intersects`

, `ST_CoveredBy`

, and , `ST_Within`

.
In many situations you know the intersection of 2 geometries
without actually computing an intersection. In these cases, you can skip the costly `ST_Intersection`

call. Cases like this:

- geometry a is covered by geometry b -> intersection is geometry a
- geometry b is covered by geometry a -> intersection is geometry b
- geometry a does not intersect geometry b -> intersection is empty geometry

This kind of question comes up a lot: As discussed in stackexchange Acquiring ArcGIS speed in PostGIS

Read More…

2013/10/05
in tips
(
newbie, geometry, geography, raster )

Every one or two months, someone asks on the PostGIS users list usually somewhat panicked, *My geometry is missing when I look in pgAdmin III*

This is documented in the PostGIS Frequently Asked questions section of the manual I tried to use PgAdmin to view my geometry column and it is blank, what gives?

Since this question gets asked so much, I thought it best to highlight it again.

Spatial objects can be big things and if your geometry, geography, or raster row field is big enough, pgAdmin is not going to load it
and will just show an empty field. This is by design. There are a couple of ways to verify you really have data and even how big your geometry, geography or raster column is

Read More…

2013/08/30
in tips
(
newbie, geometry, ST_SetSRID, ST_Transform )

People often get confused between the `ST_Transform`

and `ST_SetSRID`

functions.

`ST_SetSRID`

doesn’t change the coordinates but adds meta data to state what spatial reference system the coordinate actually are.
If you stamped your WGS 84 long lat data as a meter based projection. Guess what? Its still long lat.
A spade by any other name is still a spade so don’t use ST_SetSRID and expect to magically get meter coordinates.

`ST_Transform`

is used to change the underlying coordinates
from a known spatial reference system to another known spatial reference system.

In PostGIS 2+ it’s pretty easy to correct mistakes you’ve made with standard ALTER TABLE commands. We’ll demonstrate a couple of scenarios

Read More…

2013/08/26
in tips
(
newbie, geometry, geography, ST_DWithin )

If you have a problem that involves finding the things within X distance of other things or finding what things
have nothing within X distance
do not use `ST_Distance`

for filtering and also do not try to use `ST_Intersects + ST_Buffer`

.

Use `ST_DWithin`

instead. Why?

`ST_DWithin`

can use an index and `ST_Distance`

can not

`ST_Buffer`

is just an approximation of a buffer and not an exact buffer

Also note that `ST_DWithin`

is supported for both geometry and geography.

Read More…