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.


  

About these ads

About Ritesh Agrawal

I am a applied researcher who enjoys anything related to statistics, large data analysis, data mining, machine learning and data visualization.
This entry was posted in Database, postgis, Postgres, Tips. Bookmark the permalink.

5 Responses to PostGIS Error: Operation on mixed geometries

  1. Pingback: Postgres/Postgis Tips « memento

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

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

  4. DGuidi says:

    thanks for this :)

  5. Ines says:

    Good and practical blog post! Many thanks Ritesh!

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s