[GIS] ERROR: Operation on two geometries with different SRIDs

postgispostgresql

I'm new when it comes to GIS and PostgreSQL. I have searched everywhere for the cause of this problem but I can't find the solution. I was following the documentation here

First i created a table:

CREATE TABLE tableA (id integer, polygon geometry);

I couldn't use st_polygon because that was causing a "type not exist" error though GIS is fully updated. (through Ubuntu repos)

Then I inserted a row. I couldn't do it like how it was described in the documentation because that was giving me a weird errors:

INSERT INTO tableA VALUES (1,
    st_polygon ('polygon ((40 40, 40 60, 60 60, 60 40, 40 40))', 0)
    );    

ERROR: lwline_deserialize: attempt to deserialize a line which is really a Invalid type
    SQL state: XX000
    Context: SQL function "st_polygon" statement 1


INSERT INTO tableA VALUES (1,
    st_polygon ('polygon ((40 40, 40 60, 60 60, 60 40, 40 40))', 0)
    );    

  ERROR: function polygon(unknown, integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 44

In the end I was able to insert it like this:

INSERT INTO tableA VALUES (
1,
ST_PolyFromText ('polygon ((-30.8947 138.6522,
-34.8951 138.6522,
-34.8952 138.6512,
-34.8947 138.6512,
-30.8947 138.6522))', 1)
);

Now when I perform 

SELECT * FROM tableA WHERE ST_OVERLAPS(ST_BUFFER(GeomFromText('POLYGON((-34.8947 138.6522,
-34.8951 138.6522,
-34.8952 138.6512,
-34.8947 138.6512,
-34.8947 138.6522 ))'),0), polygon);

It outputs the error:

ERROR: Operation on two geometries with different SRIDs
SQL state: XX000

Anybody got a clue what it could be now and how to fix it?

Best Answer

I couldn't use st_polygon because that was causing a "type not exist" error though GIS is fully updated. (through Ubuntu repos)

It looks like the cause of your problem is that, although you have installed PostGIS, you haven't imported it into your database.

Before you start using any of the geographic types, ensure you have done

CREATE EXTENSION postgis;

If you have more than one database, you will need to execute this in each database that you want to be PostGIS-enabled.

You can check what extensions are installed, by typing \dx in the psql client:

\dx
                                     List of installed extensions
  Name   | Version |   Schema   |                             Description                             
---------+---------+------------+---------------------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis | 2.1.7   | postgis    | PostGIS geometry, geography, and raster spatial types and functions

(hmm, looks like I need to update the extension to match the installed postgis package...)

Related Question