Search code examples
postgresqlpostgis

Postgis: function st_contains does not exist

I am struggling to get a simple Postgresql/Postgis statement to work, I need all points within a polygon (in this case a rectangle)

SELECT * FROM points_table WHERE ST_Contains( ST_GEOMFROMTEXT('POLYGON((51.8121, 0.13712199999997665, 51.9078, 0.21444399999995767))'), points_table.geom)

The error reads 

ERROR:  function st_contains(geometry, geography) does not exist
LINE 1: SELECT * FROM points_table WHERE ST_Contains( ST_GEOMFRO...
                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

********** Error **********

ERROR: function st_contains(geometry, geography) 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: 38

The answer from this question amongst others suggests my statement is correct.

Solution

  • Seems you are comparing GEOMETRY and GEOGRAPHY

    As in your message error st_contains(geometry, geography)

    be sure that your column points_table.geom is a valid GEOMETRY data type and not a GEOGRAPHY data type .. for this chek also for the SR you are using and eventually convert you geomtext as a valid SR for geography

    eg assuming you using as SR 4326

    SELECT * 
FROM points_table 
WHERE ST_Contains(
  ST_Transform(
  ST_GEOMFROMTEXT('POLYGON((51.8121, 0.13712199999997665, 51.9078, 0.21444399999995767))')
  ,4326)
, points_table.geom)