title: SQL query for point-in-polygon using PostgreSQL
body: <p>I have the following simple table:</p>
  
  <pre><code>CREATE TABLE tbl_test
  (
    id serial NOT NULL,
    poly polygon NOT NULL
  )
  WITH (OIDS=FALSE);
  </code></pre>
  
  <p>I then try to insert a row with a polygon:</p>
  
  <pre><code>insert into tbl_test values(1, PolyFromText('POLYGON((0 0, 10 10, 10 0, 0 0))'))
  </code></pre>
  
  <p>And run into this error:</p>
  
  <blockquote>
    <p>column "poly" is of type polygon but expression is of type geometry</p>
  </blockquote>
  
  <p>Which is lame. So my first questions is:</p>
  
  <ol>
  <li>Do I really have to cast? </li>
  </ol>
  
  <p>Anyway, after casting it works. And now I'm trying to do a simple ST_Contains query:</p>
  
  <pre><code>select id, poly from tbl_test where ST_Contains(poly, Point(GeomFromText('POINT(9 2)')))
  </code></pre>
  
  <p>Which gives the error:</p>
  
  <pre><code>ERROR:  function st_contains(polygon, point) does not exist
  LINE 1: select id, poly from tbl_test where ST_Contains(poly, Point(...
                                              ^
  HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
  </code></pre>
  
  <p>What am I supposed to do?</p>
  
  <p>The following works: </p>
  
  <pre><code>select st_contains(st_geomfromtext('POLYGON((0 0, 10 10, 10 0, 0 0))'), st_geomfromtext('POINT(0 0)'))
  </code></pre>
  
  <p>But that's probably because both arguments are of type Geometry. The actual query against the table data doesn't work.</p>
  
  <p><strong>Answer:</strong></p>
  
  <p>Doi! The problem was that the DB I created was not based on the postgis template DB (and therefor did not have the relevant functions and geometry column tables, etc.).
  May I just remark, in conclusion, that the way PostGIS requires you to add hundreds of functions, rows and a few tables to your DB just so you'd have GIS support is completely lame. It makes backup of the schema that much more complex and is very error prone (heaven forbid if you neglect to call AddGeometryColumn and just add a geometry column yourself).</p>