How to display multiple geometry types from single PostgreSQL table

Question

Hello!

I have a PostGIS enabled PostgreSQL database, with a table with spatial data in it. I have multiple geometry types in my table (points, lines and polygons). When I connect to my database in ArcPro, I only see my points layer. Is there a way to display all geometry types in ArcPro? Do I need to make three connections to my table? Unfortunately because my database is managed by a separate application, I can not make any modifications to my database to only allow one geometry type.

Thanks in advance!

VinceAngelo · Answer

Paige -

Cool.  But don't forget to check if there are other, multi-part types in the table:

SELECT ST_GeometryType(geomcol), count(*) as nfeats
FROM   owner.geomtable
GROUP  BY ST_GeometryType(geomcol)
ORDER  BY 2 DESC

Note that the database name isn't required, because PostgreSQL doesn't support cross-database queries.

- V

Paige_Williams · Answer

Thanks @VinceAngelo ! This helped me. I ended up creating 3 query layers with the following SQL queries (I am using PostGIS) SELECT * FROM .. where ST_GeometryType() = 'ST_Point' SELECT * FROM .. where ST_GeometryType() = 'ST_LineString' SELECT * FROM .. where ST_GeometryType() = 'ST_Polygon'

VinceAngelo · Answer

You need to put a WHERE constraint on the QueryLayer definition to restrict by either GeometryType(geomcol) or ST_GeometryType(geomcol), then specify the desired type in the layer definition  Those two functions return different values (e.g. "POINT" and "ST_POINT"), and you may need to use an IN operator, because multi-part linestring and multi-part polygon geometeries are represented as different types in PostGIS. You may want to use ST_Dimension(geomcol) instead, since that just sorts POINT, LINESTRING, and POLYGON data into 0, 1, or 2 (but be careful, because MULTIPOINT also returns 0, and it's a different type as well, though not as common).

In a perfect world, the various geometry types wouldn't be smushed together like that (it's an antipattern, but some folks love to go down that route).  You'd get better performance on large tables (>100k features) if the dimensionality were also an attribute property capable of being indexed (e.g. 'WALL', 'WELL','PATIO') though I suppose a covering index on one of the differentiator functions could be effective (but only if you can get the data owners to construct that index).

- V