SQL query for point-in-polygon using Postgres

Question

I have the following simple table:

CREATE TABLE tbl_test
(
  id serial NOT NULL,
  poly polygon NOT NULL
)
WITH (OIDS=FALSE);

I then try to insert a row with a polygon:

insert into tbl_test values(1, PolyFromText('POLYGON((0 0, 10 10, 10 0, 0 0))'))

And run into this error:

column "poly" is of type polygon but expression is of type geometry

Which is lame. So my first questions is:

1. Do I really have to cast?

Anyway, after casting it works. And now I'm trying to do a simple ST_Contains query:

select id, poly from tbl_test where ST_Contains(poly, Point(GeomFromText('POINT(9 2)')))

Which gives the error:

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.

What am I supposed to do?

The following works:

select st_contains(st_geomfromtext('POLYGON((0 0, 10 10, 10 0, 0 0))'), st_geomfromtext('POINT(0 0)'))

But that's probably because both arguments are of type Geometry. The actual query against the table data doesn't work.

Answer:

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).

Answer 1

Ok, weird, I found out the following much simpler syntax works:

insert into tbl_test (poly) values ('(0,0),(0,10),(10, 10), (0, 0)')

select * from tbl_test where poly @> '(2, 8)'

But I'm struggling to figure out the difference between these sets of functions and operators. Does this shorter syntax (which isn't really OpenGIS compliant) take advantage of the same spatial indexes, etc.?

Answer 2

The polygon is a fundamental Postgres type which PostGIS builds on top of. You enable the geometry columns with the PostGIS function select AddGeometryColumn(...). Otherwise you are working with straight polygons:

=> create table gt (id int, space polygon);
=> insert into gt values (1, '((2,2),(3,4),(3,6),(1,1))');
INSERT 0 1
=> select point(space) from gt where id = 1;
    point    
-------------
 (2.25,3.25)
(1 row)

This is the center point of the polygon

=> select circle(space) from gt where id = 1;
             circle             
--------------------------------
 <(2.25,3.25),1.93994028704315>
(1 row)

This is the minimum bounding circle of the polygon, expressed as a Postgres circle type. All the geometric operators are documented here: http://www.postgresql.org/docs/8.3/interactive/functions-geometry.html The base polygon does not have any projection data, SRID, etc., so if it works with PostGIS it is probably just defaulting to presets and getting lucky. But of course there are tons of cases where you simply need geometry on a sub-geospatial scale.