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.
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.
We show examples using geography.
Note geometry would use much the same except units are in the spatial reference units.
Finding closest things within 1609 meters (~1 mile)
SELECT roads.roadname, pois.poiname FROM roads INNER JOIN pois ON ST_DWithin(roads.geog, pois.geog, 1609);
Finding roads with nothing of interest within 1 mile.
We are using the fact that a LEFT JOIN returns null in the left table when no match is found
SELECT roads.roadname, pois.poiname FROM roads LEFT JOIN pois ON ST_DWithin(roads.geog, pois.geog, 1609) WHERE pois.gid IS NULL;
This raster question comes up quite a bit on PostGIS mailing lists and stack overflow and the best answer often involves
the often forgotten
ST_Reclass function that has existed since PostGIS 2.0.
People often resort to the much slower though more flexible
ST_MapAlgebra or dumping out
their rasters as Pixel valued polygons they then filter
with WHERE val > 90,
ST_Reclass does the same thing but orders of magnitude faster.
PostGIS 2.2.0 came out this month, and the SFCGAL extension that offers advanced 3D and volumetric support, in addition to some extended 2D functions like
ST_ApproximateMedialAxis became a standard PostgreSQL extension
and seems to be a fairly popular extension.
I’ve seen several reports on GIS Stack Exchange of people trying to install PostGIS SFCGAL and getting things such as
ERROR: could not open extension control file /Applications/Postgres.app/Contents/Versions/9.5/share/postgresql/extension/postgis_sfcgal.control
as in this question: SFCGAL in PostGIS problem.
There are two main causes for this:
I’ve been asked this question in some shape or form at least 3 times, mostly from people puzzled why they get this error. The last iteration went something like this:
I can’t use
ST_AsPNG when doing something like
SELECT ST_AsPNG(rast) FROM sometable;
Gives error: Warning: pg_query(): Query failed: ERROR: rt_raster_to_gdal: Could not load the output GDAL driver.