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:

enter image description here

and we have a point that is clearly in Australia:

enter image description here

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.

  • I wonder if it's not a limit story... and if you tested the functions: st_Covers and st_CoveredBy?
    – fcka
    Jul 21 at 8:44
  • @fcka, tried these functions now too with the same result.
    – pavel
    Jul 21 at 9:24
  • 3
    It isn't for some reason - postgis operates that way postgis.net/docs/ST_MakePoint.html Jul 21 at 11:14
  • 3
    Your initial Point WKT is in (Lat Lon) order, which is simply not what PostGIS expects. Your edit also confuses the order again.
    – geozelot
    Jul 21 at 11:17
  • 1
    POINT(X,Y,Z,M) is the order for point coordinates in postgis. In case all your coordinates are flipped, you can use st_flipcoordinates, which will swap the x and y coordinates.
    – jbalk
    Aug 4 at 17:47


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.