PostGIS

Spatial and Geographic objects for PostgreSQL

• Home
• Download
• Documentation
• Development
• Support
• 

pgAdmin shows no data

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

More Posts

Move PostGIS extension to a different schema

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.

Read more...

---

Causes for 'postgis.backend' is already set

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”

Read more...

---

Selecting only pixels of particular range of values with ST_Reclass

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.

Read more...

---