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.

Pingback: Postgres/Postgis Tips « memento

thanks very much for your explanation….. I solved my troubleshooting!!!

Thanks Ritesh. I solved my problems and understand other things. Great explanation!

thanks for this ๐

Good and practical blog post! Many thanks Ritesh!

Helped to me, thanks!

Postgres 9.3, PostGIS 2.1

3.

psql> select distinct ST_SRID() as srid, count(*) from group by srid

4.

psql> update set = ST_SetSRID(, ) where ST_SRID()

Helped to me, thanks!

Postgres 9.3, PostGIS 2.1

3.

psql> select distinct ST_SRID(<geometry_column>) as srid, count(*) from <table> group by srid

4.

psql> update <table> set <geometry_column> = ST_SetSRID(<geometry_column>, <SRID_Value>) where ST_SRID(<geometry_column>) <> <SRID_Value>

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

To fix this I did this:

ALTER TABLE ALTER COLUMN TYPE Geometry(, )