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
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';
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.