PostGIS Error: Operation on mixed geometries

PostGIS usually raises “operations on mixed geometries” while trying to use topological functions (such as st_intersects, st_within, etc). There can be many different reasons because of which one might encounter this error. Below are the steps that I found useful in resolving this error.

1. Validate Geometries: Make sure all the geometries are valid.
psql> select *, ST_IsValidReason(<geometry_column>) from <table> where ST_IsValid(<geometry_column>) = false

2. Simplify Invalid Geometries: Skip this step if you didn’t find any invalid geometries in the above step. If you did, then use the ST_Simplify or ST_SimplifyPreserveTopology functions to fix them.
psql> update <table> set <geometry_column> = ST_Simplify(<geometry_column>, <tolerance>) where ST_IsValid(<geometry_column>) = false

3. Check SRID: Then make sure that all the geometries have the same projection system. You can check all the different SRID values using the following query
psql> select distinct(SRID(<geometry_column>)) as srid, count(*) from <table> group by srid

4. Update SRID: Skip this step if you find only one SRID value in the above step. If not, then use the setSRID values to set SRID for geometries or ST_Transform to transform from one projection system to another. 
psql> update <table> set <geometry_column> = setSRID(<geometry_column>, <SRID_Value>)  where SRID(<geometry_column>) <> <SRID_Value>
5. Check Geometry_Columns Table: Postgis maintains basic information about any geometry column in any table in a separate table known as “Geometry_Columns”. Make sure that there is an entry for your table in the “Geometry_Columns” table.
psql> select * from geometry_columns where f_table_name = <table>

6. Insert geometry information in Geometry_Columns Table:
if in the above step (step 5), you didn’t find any entry for the geometry column, then manually add it to geometry_columns table
psql> INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") SELECT '', '<schema>', '<table>', '<geometry_column>', ST_CoordDim(<geometry_column>), ST_SRID(<geometry_column>), GeometryType(<geometry_column>) FROM <schema>.<table> where <geometry_column> is not null LIMIT 1

7. Use UpdateGeometryColumn function: if you performed step 6, then this step is optional. Use updateGeometryColumn to set SRID value
psql> select updateGeometryColumn(<schema>, <table>, <SRID_Value>) where f_table_name = <table>

8. Now try your original query. That’s all. Now try running your command and most likely you should be able to get it working.



8 thoughts on “PostGIS Error: Operation on mixed geometries

  1. Helped to me, thanks!
    Postgres 9.3, PostGIS 2.1
    psql> select distinct ST_SRID() as srid, count(*) from group by srid
    psql> update set = ST_SetSRID(, ) where ST_SRID()

  2. Helped to me, thanks!
    Postgres 9.3, PostGIS 2.1
    psql> select distinct ST_SRID(<geometry_column>) as srid, count(*) from <table> group by srid
    psql> update <table> set <geometry_column> = ST_SetSRID(<geometry_column>, <SRID_Value>) where ST_SRID(<geometry_column>) <> <SRID_Value>

  3. In my case the type of the geometry was not set correctly in the geometry_columns view.

    To fix this I did this:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s