POSTGIS "ST_Contains" returns a empty query

Question

I have imported two shape-files using QGIS into pgAdmin 4 (PostgreSQL), I use the PostGIS extension to be able to access spatial commands.

I want to check all the POIS (points) that are included in the Gemeinden (multipolygon) table. To accomplish that I use the spatial command "ST_Contains" ([postgis.net documentation regarding the command])1. Contrary to the fact that the points are actually in the polygons, the returned query is empty (see QGIS and output screenshot). What could be the issue? Any help would be appreciated. Thank you in advance!

Output - pgAdmin

The command I use:

SELECT * FROM public."POIS" AS pois INNER JOIN public."Gemeinden" AS gem
ON (1 = 1)
WHERE ST_Contains(gem.geom, pois.geom) = true;

My tables:

POIS table content

[ POIS 3

Gemeinden table content

[ Gemeinden 4

QGIS Screenshots:

Both shape-files together:

[ enter image description here 5

Only the POIS shape-file:

[ enter image description here 6

Only the Gemeinden shape-file:

[ enter image description here 7

Update:

I have created a polygon table out of the multi-polygons using the command

CREATE TABLE polygon_table AS 
SELECT id, public."Gemeinden".kg_nr, public."Gemeinden".kg, (ST_DUMP(geom)).geom::geometry(Polygon,4326) AS geom FROM public."Gemeinden";

Afterwards I've updated the SRID of both the POIS table and the newly created one using :

SELECT UpdateGeometrySRID('polygon_table','geom',4326);

and

SELECT UpdateGeometrySRID('POIS','geom',4326);

Sadly,

SELECT pois.* FROM public."POIS" AS pois JOIN public."polygon_table" AS 
gem
ON ST_intersects(gem.geom, pois.geom);

still returns a empty query.

Any ideas? Thank you in advance!

lat long lat long · Accepted Answer · 2017-12-29T06:40:07

First of all make sure the SRID of the both table must be same. If it is not same then spatial queries wont work. Secondly convert the multipolygons to single polygons. Following link may help

PostGIS - convert multipolygon to single polygon

Finally the following query would be enough if you just want to get the POIS that intersects the polygons

SELECT pois.* FROM public."POIS" AS pois JOIN public."Gemeinden" AS gem
ON  ST_intersects(gem.geom, pois.geom);

POSTGIS "ST_Contains" returns a empty query

2 Answers