2

I have two tables of polygons: Table_1and Table_2. All the polygons of T1 are inside the polygons of T2.

I want to SELECT the id of T1 and the name of the polygon from T2 where the polygon is located.

I am using a spatial join :

SELECT T1.id, T2.name
FROM Table_1 AS T1
JOIN Table_2 AS T2
ON ST_Contains(T1.geom, T2.geom)

The trouble I have is that the query does not return any value. I don't understand why. I have already used this method for other queries and it worked. Having already done this doesn't help for this case.

UPDATE 1:

I tried with ST_Intersects and nothing was returned neither.

UPDATE 2:

I checked the SRID for the Table_2, none was defined. I corrected it but the results are still the same.

UPDATE 3: Here is the geometry of the first row for Table_1

1; 01060000206A080000010000000103000000010000001F000000768C2BD8D7392441EBB09D7A7D1C5B410A497D15F03A24410BBC0F716C1C5B41366DF983803B24413246D86B5D1C5B4129CFBCC2DB3B2441D5616275491C5B416B1A9FE6863C244171AEA1B83D1C5B4157485EC7673D2441AF20131C3F1C5B41B76046F83A3E2441B02ED7F3391C5B413C64BBBEE93D244106E8ABEC1D1C5B414CF08D90B33D2441ECAE432D0A1C5B41C01F2375013E24414B069F9AF41B5B41B1E53CDA463D2441E057FA4EDF1B5B41987E1A55C83C244135ADDCA1DC1B5B417D513EA5E43A24410EF6856DBC1B5B41E6FEDBFB583A24414C69E48CB71B5B419B448F6F523A24417D139ABEB61B5B41D614CBE8DE382441DE529054A31B5B41FC1348DEF3362441A8D13B6B891B5B41CCC3BB8973352441194EE3937C1B5B412BBE50BBC9342441F232372E9C1B5B419782A0B82E3424419E9C1FE2D21B5B41F81E12BDAD33244166FE8D8F041C5B41FE3278D9AF3324412FDBB1FB231C5B4175D7441B2D332441878F829C391C5B4114C0D422DA3324415FA552FC491C5B412D03A336AE342441BEB77102521C5B41406F4356EC35244127E1BC3B5D1C5B410ACB77C47D362441BDC9A9145D1C5B4123FC9AA4A13724419EDFE6046A1C5B4134D3FF7E76382441B9F5A0997D1C5B41CA43370F16392441FCB951AD8A1C5B41768C2BD8D7392441EBB09D7A7D1C5B41

The geometries of my Table_2 have to many characters to fit in the update. The SRID is 2154.

Improve this question
9
  • 2
    Because the order matters. If all of the T1s are inside the T2s, then by definition, none of the T1s will contain any T2s, unless, they are alternating in size and getting progressively smaller. So, switch the order in ST_Contains.
    – John Powell
    Commented Jul 30, 2019 at 14:45
  • I had already done it and changed nothing, I retried on your advice but still nothing is returned
    – Basile
    Commented Jul 30, 2019 at 14:52
  • 2
    Silly question, but how do you know your polygon contains other ? if it's graphically, be carefull with projection system maybe. otherwise, your query should be ok
    – Maximilien jaffrès
    Commented Jul 30, 2019 at 15:04
  • the condition WHERE doesn't change anything
    – Basile
    Commented Jul 31, 2019 at 9:49
  • 1
    the ST_Dump() function returned this error when I tried to UPDATE my geometries. "Geometry type (Polygon) does not match column type (MultiPolygon)"
    – Basile
    Commented Jul 31, 2019 at 9:50

1 Answer 1

Reset to default
2

I found the solution, the source from which I had received Table_2 was supposed to give it in the SRID 2154 but it did in 4326. I had then imported the layer with the SRID as 2154 whereas it wasn't. After reprojection of the layer and re-importation under Postgis, the initial query worked.

ANSWER: ALWAYS CHECK SRID EVEN WHEN IT IS ASSUMED TO BE KNOWN

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.