0

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 enter image description here

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:

  1. POIS table content

[POIS 3

  1. Gemeinden table content

[Gemeinden4

QGIS Screenshots:

  1. Both shape-files together:

[enter image description here5

  1. Only the POIS shape-file:

[enter image description here6

  1. Only the Gemeinden shape-file:

[enter image description here7

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!

S. Njegoš
  • 1
  • 4

2 Answers2

3

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);
lat long
  • 920
  • 6
  • 16
  • Update: 1. I've created a multi-polygon table: 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";, 2. Updated the SRID of both tables, using the command SELECT UpdateGeometrySRID('polygon_table','geom',4326); and SELECT UpdateGeometrySRID('POIS','geom',4326);. Sadly the command SELECT pois.* FROM public."POIS" AS pois JOIN public."polygon_table" AS gem ON ST_intersects(gem.geom, pois.geom); still returns an empty query. I'm very thankful for any further input! – S. Njegoš Dec 30 '17 at 12:56
  • Can you share the tables schema. I guess you also need to convert the multiplication into single polygon – lat long Jan 01 '18 at 08:47
  • Thank you for your addition! I have solved the problem by saving the shape-files from QGIS as the same SRID onto the desktop and opening them again. Afterwards, while importing into the database, I have selected the option that the Geometry files should be converted into single polygons. I am not sure which of these have resolved my problem, but everything works well now. Thank you again for sticking around! – S. Njegoš Jan 06 '18 at 14:28
0

I have resolved the problem by going back to QGIS and saving both previously imported shape-files as the same SRID. Apparently, my code that converted the SRID in the database did not work.

Right click on the shape-file

Selecting the SRID

Note: I have done this for both shape-files, just to be sure.

Another factor that may have influenced the outcome was the selection of the "convert to single polygon" box while importing into the database from QGIS.

The box that I ticked for both shape-files while importing

PS: I use the German version of QGIS

S. Njegoš
  • 1
  • 4