Author: Regina Obe 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
Examples
Check there is really data. This will work for anything and will return a count of all objects that have no data in geom column This query will also work for raster, geometry, and geography
SELECT COUNT(*) FROM mytable WHERE geom IS NULL;
How big are my spatial objects? These next examples may take a while to run depending on how big your table, so proceed with caution.
How big are my geometries? This will return the max number of points in a geometry column
SELECT MAX(ST_NPoints(geom)) FROM mytable;
How big are my geographies?
Unfortunately geography doesn’t have a native ST_NPoints
function, but you can piggy back on the geometry one.
SELECT MAX( ST_NPoints(geog::geometry) ) FROM mytable;
How big are my rasters? A raster column has widthxheight number of pixels in it. So the below query will give you a sense of how many pixels you’ve got in each row.
SELECT MAX(ST_Width(rast)) AS w , MAX(ST_Height(rast)) AS h FROM mytable;
If you loaded your rasters with -C constraint option, you can do the much much faster check of rastercolumns. rastercolumns will only show a blocksizex and blocksizey if you applied constraints and all your raster tiles are off the same width/height dimensions.
SELECT blocksize_x AS w , blocksize_y AS h FROM raster_columns WHERE r_table_name = 'mytable' AND r_raster_column='rast';
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,
where ST_Reclass
does the same thing but orders of magnitude faster.