How to create a table with one polygon and one point?

Question

using postgresql and postgis, i would like to create a table with one polygon and one point. then use st_contains to check if the point is inside the polygon or not.

so first, I should create a table :

CREATE TABLE my_schema.my_table (id serial);

then add

SELECT AddGeometryColumn( 'my_table', 'geo_polygon', 4326, 'POLYGON', 2);
UPDATE my_table 
 SET geo_polygon=GeomFromText('POLYGON((0 0,4 0,4 4,0 4,0 0))',4326)
 WHERE id='p1';


SELECT AddGeometryColumn( 'my_table', 'geo_point', 4326, 'POINT', 2);
UPDATE my_table 
 SET geo_point=GeomFromText('POINT((2 2))',4326)
 WHERE id='p2';

Finally, to check containing i should use :

SELECT ST_Contains(geo_polygon, geo_ponts, 4326)) 
    FROM my_table
    WHERE ???

I am not sure about the condition here. do I have to use the ids ? and how ?

Answer 1

I suspect you may have a slight issue with your data structure, rather than the query per se. It is uncommon to have two geometry columns in the same table that need to be queried in such a way as to return two or more records that share some geometric relationship. With queries of this sort, it is usual to have two tables and join them in the regular fashion.

For instance, building footprints are usually multipolygons and they sometimes have building seeds associated that are a single point within a single building, so there would be no need to test if buildings contain the seed of another building and so the data is neatly contained within each record. But what you appear to be asking is is a point associated with a polygon contained within a different polygon on the same "layer". While there may well be use cases that this is ok for, I can't think of any, so it may help if you can describe what you're trying to achieve.

Having said all of that, you can perform your query with aliases and a self join. The statement should look something like this, but I've not tested it:

SELECT a.id, b.id FROM my_table a, my_table b 
WHERE ST_Contains(a.geo_polygon, b.geo_points, 4326);