PostGIS – How to Populate the Geometry Column in a PostGIS Table?

postgispostgis-2.0

After loading a polygon shapefile into PostGIS, the geometry column (geom) is blank for several features. In one case, only a single feature has the geometry calculated and all others are blank.

I'm using the PostGIS SHP and DBF loader v.2.0. I am creating a spatial index, and using COPY rather than INSERT.

I have loaded several other point datasets and they geom column is populated just fine…

(Otherwise, things are working just fine for the polygons: when I create a new feature, the geometry is calculated / geom field populated…etc.)

Have I done something wrong when importing the data?

Solution by @ThomasG77 (in comments)

PgAdmin3 [doesn't] display features that are too complex but they are present. You can try SELECT length(ST_astext(the_geom)) from your_table to confirm or you can find "true" empty geometry with SELECT * from your_table WHERE the_geom IS NULL;.

Best Answer

Per comments, in occasions the geom column might not be visible to the client as a human readable string (a geom column is encoded as an EWTB - Extended Well Known Binary ).

There are two ways to visually check if the geom column is populated:

  1. Load the table to your favourite gis client (eg. Qgis)
  2. Count the NULL and NOT NULL values of the geom column :
      SELECT COUNT(geom), 'null_tally' AS narrative 
      FROM 'myTable' 
      WHERE a IS NULL 
      UNION
      SELECT COUNT(geom), 'not_null_tally' AS narrative 
      FROM 'myTable'  
      WHERE a IS NOT NULL;

count sql snippet from : https://stackoverflow.com/questions/1271810/counting-null-and-non-null-values-in-a-single-query )

Related Question