ST_Intersection is much slower than relation checks such as
ST_CoveredBy, and ,
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:
This kind of question comes up a lot: As discussed in stackexchange Acquiring ArcGIS speed in PostGIS
For this exercise, we’ll grab the portion of each parcel that falls in a neighborhood.
Unlike stack exchange one, we’ll use
ST_CoveredBy instead of
ST_Within check. Both constructs are similar except that
for geometries to be wholly within the boundary of another and tends to be a bit faster to compute than
This subtlety becomes more important if you are comparing linestrings such as what portion of a road falls in a county
as detailed in Subtleties OGC Covers Spatial.
SELECT p.parcel_id, n.nei_name , CASE WHEN ST_CoveredBy(p.geom, n.geom) THEN p.geom ELSE ST_Multi( ST_Intersection(p.geom,n.geom) ) END AS geom FROM parcels AS p INNER JOIN neighborhoods AS n ON ST_Intersects(p.geom, n.geom);
In this particular case, we’d probably want to exclude the case where a parcel just borders a neighborhood (in spatial speak
We wouldn’t really consider a bordering parcel as having any part in the neighborhood, though it intersects the neighborhood.
So a slightly more complicated but more accurate statement would be to exclude from consideration the case where a parcel borders a neighborhood using
SELECT p.parcel_id, n.nei_name , CASE WHEN ST_CoveredBy(p.geom, n.geom) THEN p.geom ELSE ST_Multi( ST_Intersection(p.geom,n.geom) ) END AS geom FROM parcels AS p INNER JOIN neighborhoods AS n ON (ST_Intersects(p.geom, n.geom) AND NOT ST_Touches(p.geom, n.geom) );
As of PostGIS 2.3, the postgis extension was changed to no longer allow relocation. All function calls within the extension are now schema qualified.
While this change fixed some issues with database restore, it created the issue of if you installed PostGIS in a schema other than the one you wanted to it is not intuitive how to move it to a different schema. Luckily there is a way to do this.
For this exercise, I will install PostGIS in the default schema and then demonstrate how to move it into another schema location.
You can run these steps using psql or pgAdmin or any other PostgreSQL tool you want.
The error ‘postgis.backend’ is already set comes up every so often in PostGIS mailing list. The issue arises often during or after an upgrade. I’ll go over causes for this I am aware of and how to fix.
The question goes something like this
After upgrading to Postgis 2.3 from 2.1, my server log is filled with these messages :
“WARNING ‘postgis.backend’ is already set and cannot be changed until you reconnect”
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.