Cartometric Blog

A scrapbook of GIS tricks, with emphasis on FOSS4G.

PostGIS: count all features of each GeometryType in a spatial table

with one comment

Sometimes, just when you think you’ve got something figured out –you get reminded that you really don’t.  :/

As you may know, ESRI allows for single and muli-part geometries to live in the same FeatureClass. So, if I have a shapefile of roads, there might be both LINESTRING and MULTILINESTRING features in that dataset. I live just loose-enough not to care about that. But I do need to be aware of it when I’m cobbling data in PostGIS.

In thise case, I was getting a PostGIS error tying to do a Dissolve-By-SQL, so I thought why not get a quick count of each GeometryType in the dataset? Maybe I was running into issues single and multi-part geometries were blurred together. It took me an embarassing chunk of time to get this right, so I figured I’d post the recipe  in case I needed a reminder later.

  GeometryType( wkb_geometry ) as geomType,  
  COUNT( wkb_geometry ) as featureCount
FROM anyGeoDataTable
WHERE wkb_geometry IS NOT NULL
GROUP BY GeometryType( wkb_geometry );


[Update 4.16.2012]
I wanted to do the same thing in MySQL the other day. It’s essentially the same query, but note the need to use CONVERT() function to make the output properly render in MySQL Workbench:

  CONVERT( GeometryType( shape ) USING utf8 ) as geomType,
  COUNT( shape ) as featureCount
FROM anyGeoDataTable
GROUP BY GeometryType( shape );


Without applying the CONVERT() function, MySQL Workbench just shows “blob” in the return set.

Anyway.. back to cracking some nut..  :]

Written by elrobis

January 21st, 2012 at 9:17 pm

Posted in PostGIS

Tagged with ,

One Response to 'PostGIS: count all features of each GeometryType in a spatial table'

Subscribe to comments with RSS

  1. mbt footwear fo sale

    mens mbt

    30 Jun 15 at 7:04 am

Leave a Reply