PostgreSQL ST_Geom or PostGIS native ?

MarkChilcott

Hi Peoples,

Geodatabases in PostgreSQL use the ST_Geometry spatial type by default.  

To use the PostGIS geometry or geography type, you must install PostGIS in your PostgreSQL database cluster, and the database itself must be enabled to use PostGIS.

What are peoples thoughts on ST_Geometry vs PostGIS as the spatial type?

performance, reliability, bugs ...

Cheers,

Mark

MarkChilcott

Hi Peoples,

One our analysts provided his take on it here.  Some interesting points, so have provided below.  I have seen almost the same issues in the distant past when dealing with Oracle and Oracle Spatial.  As always, it comes back to what the primary business objective is.

There are three main issues we found when loading spatial data into PostgreSQL using the PostGIS geometry type (without ArcSDE).

 

1. Multiple Geometry Types vs Single Geometry Types

PostGIS allows for a mixture of different shape types (point,line,polygon) to be stored against the one feature/row. ArcGIS expects only one shape type per table, so a mixture of geometry types will cause an error or display nothing.

1. Invalid geometry

PostGIS allows the storage of invalid geometry. It is up to the application, or developer, to ensure the data is valid before inserting it into the table. Alternatively, all applications reading the data must be able to accommodate invalid geometry. ArcSDE, on the other hand, does not allow for the insertion of invalid geometry.

1. Curve Geometry Representation

The storage of curves (curved lines or curved polygons) is varied and complicated in PostGIS. Probably better to explain face-to-face.

We had to do a lot of work in FME to create a PostGIS Writer that will create data that is readable by ArcGIS applications. It was complicated, and I wouldn’t be surprised if more issues arise in the future.

Note that if only point geometry is being stored, then using the PostGIS geometry should be fine.

Consider ArcSDE installed on top of PostgreSQL and using PostGIS as the underlying geometry storage type. At face value this sounds like it should make everyone happy, ArcSDE ensures that only a single geometry type is used, validates the geometry, and converts the curves into a structure that ArcGIS can handle. This approach works for the “single geometry type” and “invalid geometry” issues, but it does something weird to curves – ArcGIS creates an additional binary column in the table and stores the curves in a way that other PostGIS clients can’t read, which actually defeats the whole purpose of using the PostGIS geometry type! We worked out that if we convert curves to straight line segments, and do a bit more data “massaging”, we can load the data in a way that can be read by ArcGIS and PostGIS clients (like QGIS).

Assuming we are using PostgreSQL as the underlying database consider :

• If the applications accessing a spatial database are purely ArcGIS applications, use ArcSDE with ST_Geometry.
• If ArcGIS is the primary application accessing a database, but other types of clients need access too: Use ArcSDE with PostGIS Geometry Type, but be aware of the curve geometry issues.
• If non-ArcGIS clients are the predominant software accessing the data, go with straight PostgreSQL/PostGIS, but be real careful with how you handle geometry.

 

PS:  There is a side note here – you can install the ST_geometry into PostgreSQL and use that.