pgAdmin shows no data

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

1SELECT count(*)
2 FROM  mytable
3  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

1SELECT Max(ST_NPoints(geom))
2FROM 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.

1SELECT Max(
2 ST_NPoints(geog::geometry)
3 )
4FROM 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.

1SELECT
2 Max(ST_Width(rast)) As w
3 , Max(ST_Height(rast)) As h
4FROM mytable;

If you loaded your rasters with -C constraint option, you can do the much much faster check of raster_columns. raster_columns will only show a blocksize_x and blocksize_y if you applied constraints and all your raster tiles are off the same width/height dimensions.

1SELECT
2 blocksize_x As w
3 , blocksize_y As h
4FROM raster_columns
5WHERE r_table_name = 'mytable'
6 AND r_raster_column='rast';