SQL query for point-in-polygon using PostgreSQL SQL query for point-in-polygon using PostgreSQL postgresql postgresql

SQL query for point-in-polygon using PostgreSQL


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.


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