Hfrhyu

Why can't I install Tomboy in Ubuntu Mate 19.04?

What is the meaning of 'breadth' in breadth first search?

What does Turing mean by this statement?

Crossing US/Canada Border for less than 24 hours

Project Euler #1 in C++

What to do with repeated rejections for phd position

How does light 'choose' between wave and particle behaviour?

How to write capital alpha?

What is the home of the drow in Flanaess?

How to unroll a parameter pack from right to left

Why is it faster to reheat something than it is to cook it?

Why are vacuum tubes still used in amateur radios?

What happened to Thoros of Myr's flaming sword?

One-one communication

Amount of permutations on an NxNxN Rubik's Cube

A letter with no particular backstory

Google .dev domain strangely redirects to https

Dyck paths with extra diagonals from valleys (Laser construction)

How could we fake a moon landing now?

Significance of Cersei's obsession with elephants?

What order were files/directories output in dir?

Putting class ranking in CV, but against dept guidelines

How did Fremen produce and carry enough thumpers to use Sandworms as de facto Ubers?

Can a new player join a group only when a new campaign starts?

Find a point in a multipolygon geometry

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

2

I need to know which points from Table 1 are contained in Table 2.

In Table 1 I have longitude and latitude and I made points with ST_SetSRID(ST_MakePoint(d.longitude, d.latitude),4326) as geom

Table 2 has a collection of multipolygons: geom geometry(MultiPolygon,4326)

I know it is a collection because when I run
select ST_IsCollection(geom)from table2 it is TRUE, but when I want to know how many collections with SELECT ST_NumGeometries(geom)from table 2, it returns 1.

I know that since I have a collection I have to use st_INTERSECTS.

select TABLE1.*, TABLE2.*
from TABLE1, TABLE2
where ST_Intersects(TABLE2.geom, TABLE1.geom)

But it is empty. And I know from plotting the data that they intersect.

Do you know what might be wrong?

postgis point point-in-polygon st-intersects

share|improve this question

edited Apr 11 at 13:50

neuhausr

2,0731521

asked Apr 11 at 13:16

Florencia DiazFlorencia Diaz

112

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  you are missing aliases, e.g. TABLE1 AS T1, TABLE2 AS T2
  
  – Taras
  Apr 11 at 13:24
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes @BERA is postgis
  
  – Florencia Diaz
  Apr 11 at 13:29
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  and you tried to change tables in places? where ST_Intersects(TABLE1.geom,TABLE2.geom)
  
  – Cyril
  Apr 11 at 13:55
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  you're title is misleading -- its a multipolygon collection. If I were you I would dump the collection into an actual geometry. I think postgis recently allowed collections to be used in spatial functions such as st_intersects but that depends on what version of postgis you have
  
  – ziggy
  Apr 11 at 14:12
  

  
  
  
  

add a comment | 

2

I need to know which points from Table 1 are contained in Table 2.

In Table 1 I have longitude and latitude and I made points with ST_SetSRID(ST_MakePoint(d.longitude, d.latitude),4326) as geom

Table 2 has a collection of multipolygons: geom geometry(MultiPolygon,4326)

I know it is a collection because when I run
select ST_IsCollection(geom)from table2 it is TRUE, but when I want to know how many collections with SELECT ST_NumGeometries(geom)from table 2, it returns 1.

I know that since I have a collection I have to use st_INTERSECTS.

select TABLE1.*, TABLE2.*
from TABLE1, TABLE2
where ST_Intersects(TABLE2.geom, TABLE1.geom)

But it is empty. And I know from plotting the data that they intersect.

Do you know what might be wrong?

postgis point point-in-polygon st-intersects

share|improve this question

edited Apr 11 at 13:50

neuhausr

2,0731521

asked Apr 11 at 13:16

Florencia DiazFlorencia Diaz

112

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  you are missing aliases, e.g. TABLE1 AS T1, TABLE2 AS T2
  
  – Taras
  Apr 11 at 13:24
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes @BERA is postgis
  
  – Florencia Diaz
  Apr 11 at 13:29
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  and you tried to change tables in places? where ST_Intersects(TABLE1.geom,TABLE2.geom)
  
  – Cyril
  Apr 11 at 13:55
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  you're title is misleading -- its a multipolygon collection. If I were you I would dump the collection into an actual geometry. I think postgis recently allowed collections to be used in spatial functions such as st_intersects but that depends on what version of postgis you have
  
  – ziggy
  Apr 11 at 14:12
  

  
  
  
  

add a comment | 

I need to know which points from Table 1 are contained in Table 2.

In Table 1 I have longitude and latitude and I made points with ST_SetSRID(ST_MakePoint(d.longitude, d.latitude),4326) as geom

Table 2 has a collection of multipolygons: geom geometry(MultiPolygon,4326)

I know it is a collection because when I run
select ST_IsCollection(geom)from table2 it is TRUE, but when I want to know how many collections with SELECT ST_NumGeometries(geom)from table 2, it returns 1.

I know that since I have a collection I have to use st_INTERSECTS.

select TABLE1.*, TABLE2.*
from TABLE1, TABLE2
where ST_Intersects(TABLE2.geom, TABLE1.geom)

But it is empty. And I know from plotting the data that they intersect.

Do you know what might be wrong?

postgis point point-in-polygon st-intersects

share|improve this question

edited Apr 11 at 13:50

neuhausr

2,0731521

asked Apr 11 at 13:16

Florencia DiazFlorencia Diaz

112

select TABLE1.*, TABLE2.*
from TABLE1, TABLE2
where ST_Intersects(TABLE2.geom, TABLE1.geom)

share|improve this question

edited Apr 11 at 13:50

neuhausr

2,0731521

asked Apr 11 at 13:16

Florencia DiazFlorencia Diaz

112

share|improve this question

edited Apr 11 at 13:50

neuhausr

2,0731521

asked Apr 11 at 13:16

Florencia DiazFlorencia Diaz

112

edited Apr 11 at 13:50

neuhausr

2,0731521

edited Apr 11 at 13:50

neuhausr

2,0731521

asked Apr 11 at 13:16

Florencia DiazFlorencia Diaz

112

asked Apr 11 at 13:16

Florencia DiazFlorencia Diaz

112

1 Answer
1

active

oldest

votes

3

Have a look at St_Dump: https://postgis.net/docs/ST_Dump.html and Using multipolygon or polygon features for large intersect operations?

Assuming that Table2 are the polygons, this would be:

select T1.*, T2.*
from TABLE1 as T1, TABLE2 as T2
where ST_Intersects(St_Dump(T2.geom), T1.geom)

share|improve this answer

edited Apr 11 at 14:09

answered Apr 11 at 13:35

PieterBPieterB

3,1701128

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  No Pieter, thanks, but that is not the problem. I edited the question, I wrote T1 T2 for convenience.
  
  – Florencia Diaz
  Apr 11 at 13:40
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f318497%2ffind-a-point-in-a-multipolygon-geometry%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

Have a look at St_Dump: https://postgis.net/docs/ST_Dump.html and Using multipolygon or polygon features for large intersect operations?

Assuming that Table2 are the polygons, this would be:

select T1.*, T2.*
from TABLE1 as T1, TABLE2 as T2
where ST_Intersects(St_Dump(T2.geom), T1.geom)

share|improve this answer

edited Apr 11 at 14:09

answered Apr 11 at 13:35

PieterBPieterB

3,1701128

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  No Pieter, thanks, but that is not the problem. I edited the question, I wrote T1 T2 for convenience.
  
  – Florencia Diaz
  Apr 11 at 13:40
  

  
  
  
  

add a comment | 

3

Have a look at St_Dump: https://postgis.net/docs/ST_Dump.html and Using multipolygon or polygon features for large intersect operations?

Assuming that Table2 are the polygons, this would be:

select T1.*, T2.*
from TABLE1 as T1, TABLE2 as T2
where ST_Intersects(St_Dump(T2.geom), T1.geom)

share|improve this answer

edited Apr 11 at 14:09

answered Apr 11 at 13:35

PieterBPieterB

3,1701128

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  No Pieter, thanks, but that is not the problem. I edited the question, I wrote T1 T2 for convenience.
  
  – Florencia Diaz
  Apr 11 at 13:40
  

  
  
  
  

add a comment | 

Have a look at St_Dump: https://postgis.net/docs/ST_Dump.html and Using multipolygon or polygon features for large intersect operations?

Assuming that Table2 are the polygons, this would be:

select T1.*, T2.*
from TABLE1 as T1, TABLE2 as T2
where ST_Intersects(St_Dump(T2.geom), T1.geom)

share|improve this answer

edited Apr 11 at 14:09

answered Apr 11 at 13:35

PieterBPieterB

3,1701128

select T1.*, T2.*
from TABLE1 as T1, TABLE2 as T2
where ST_Intersects(St_Dump(T2.geom), T1.geom)

share|improve this answer

edited Apr 11 at 14:09

answered Apr 11 at 13:35

PieterBPieterB

3,1701128

answered Apr 11 at 13:35

PieterBPieterB

3,1701128

answered Apr 11 at 13:35

PieterBPieterB

3,1701128