I'm trying to do a spatial join between a table that stores locations of photos and the table with names and geometries of countries and the table containing locations of major cities.
For example here we have a multupolygon of Australia and I've confirmed that it's a valid geometry:
and we have a point that is clearly in Australia:
If I run a simple query:
select c."name"
from country c
where st_contains(c.geometry, st_geomfromtext('POINTZ(-37.56977080555556 144.89031980555555 265)', 4326))
...I get an empty return from the query. I've also tried st_within and st_intersects but neither of those return any result.
I tried a spatial join, and it works correctly if I only merge with the City table:
select i."path", i."timestamp", c."name"
from image i
join city c
on ST_DWithin(i."location", c."location", 50000, true)
but if I add Country table to the mix, I get only a handful of results and even those are all wrong:
select i."path", i."timestamp", c."name" , x."name"
from image i
join city c
on ST_DWithin(i."location", c."location", 50000, true)
join country x
on ST_Contains(x.geometry, i."location")
What am I doing wrong here?
P.S. As I said, all geometries are valid and DBeaver correctly interprets the geometry in SRID 4326.
EDIT: Ok I found the issue. For some reason it contains doesn't like the order of coordinates I have.
This (lat, lon):
'POINT(144.89031980555555 -37.56977080555556)'
doesn't work, but for some reason
'POINT(-37.56977080555556 144.89031980555555 )'
Gives a correct result.
