{"id":125,"date":"2012-01-21T21:17:57","date_gmt":"2012-01-22T03:17:57","guid":{"rendered":"http:\/\/cartometric.com\/blog\/?p=125"},"modified":"2012-04-16T19:55:20","modified_gmt":"2012-04-17T00:55:20","slug":"postgis-count-all-features-of-each-geometrytype-in-a-spatial-table","status":"publish","type":"post","link":"https:\/\/elrobis.com\/blog\/2012\/01\/21\/postgis-count-all-features-of-each-geometrytype-in-a-spatial-table\/","title":{"rendered":"PostGIS: count all features of each GeometryType in a spatial table"},"content":{"rendered":"

Sometimes, just when you think you’ve got something figured out –you get reminded that you really\u00a0don’t.\u00a0 :\/<\/p>\n

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.\u00a0I 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.<\/p>\n

In thise case, I was getting a\u00a0PostGIS\u00a0error tying to do a Dissolve-By-SQL, so I thought why not get a quick\u00a0count of each GeometryType in the dataset? Maybe I was\u00a0running 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\u00a0\u00a0in case\u00a0I needed a reminder later.<\/p>\n

SELECT \u00a0\r\n  GeometryType( wkb_geometry ) as geomType, \u00a0\r\n  COUNT( wkb_geometry ) as featureCount\r\nFROM anyGeoDataTable\r\nWHERE wkb_geometry IS NOT NULL\r\nGROUP BY GeometryType( wkb_geometry );<\/pre>\n
 <\/p>\n
[Update 4.16.2012]<\/strong>
\nI 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:<\/p>\n
SELECT\r\n  CONVERT( GeometryType( shape ) USING utf8 ) as geomType,\r\n  COUNT( shape ) as featureCount\r\nFROM anyGeoDataTable\r\nWHERE shape IS NOT NULL\r\nGROUP BY GeometryType( shape );<\/pre>\n
 <\/p>\n
Without applying the CONVERT() function, MySQL Workbench just shows “blob” in the return set.
\n <\/p>\n
Anyway.. back to cracking some nut..\u00a0 :]<\/p>\n","protected":false},"excerpt":{"rendered":"
Sometimes, just when you think you’ve got something figured out –you get reminded that you really\u00a0don’t.\u00a0 :\/ 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.\u00a0I live just […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[42],"tags":[17,16],"_links":{"self":[{"href":"https:\/\/elrobis.com\/blog\/wp-json\/wp\/v2\/posts\/125"}],"collection":[{"href":"https:\/\/elrobis.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/elrobis.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/elrobis.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/elrobis.com\/blog\/wp-json\/wp\/v2\/comments?post=125"}],"version-history":[{"count":7,"href":"https:\/\/elrobis.com\/blog\/wp-json\/wp\/v2\/posts\/125\/revisions"}],"predecessor-version":[{"id":132,"href":"https:\/\/elrobis.com\/blog\/wp-json\/wp\/v2\/posts\/125\/revisions\/132"}],"wp:attachment":[{"href":"https:\/\/elrobis.com\/blog\/wp-json\/wp\/v2\/media?parent=125"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/elrobis.com\/blog\/wp-json\/wp\/v2\/categories?post=125"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/elrobis.com\/blog\/wp-json\/wp\/v2\/tags?post=125"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}