ST_Expand return different results depending on meters [duplicate]

Question

I am very confused by getting different results from st_expand (srid 4326). I want to do a search within a bounding box, therefore I pass a point and I'd like to provide distance in meters in order to see if I will get a macht.

So this query is returning a result:

select "geo_name_cities".* FROM "geo_name_cities" WHERE (name like '%Ischgl%' AND ST_Expand(ST_GeomFromText('POINT (10.3298112 46.9809071)', 4326),5000) && lonlat)

my assumption here. Make a bounding box of 5000 meters around the point.

Now I increase to 6000m

select "geo_name_cities".* FROM "geo_name_cities" WHERE (name like '%Ischgl%' AND ST_Expand(ST_GeomFromText('POINT (10.3298112 46.9809071)', 4326),6000) && lonlat)

No result. Ok, I might assume that the place is not within this box, fine. BUT when I use 60000m I get a result again ?????

select "geo_name_cities".* FROM "geo_name_cities" WHERE (name like '%Ischgl%' AND ST_Expand(ST_GeomFromText('POINT (10.3298112 46.9809071)', 4326),60000) && lonlat)

So I increased the distance steadily but the results are different, how does this come?

Answer 1

As Vince pointed out, ST_Expand() is working in units of the spatial reference system you are using, which for EPSG:4326 is degrees. Some functions will have wrap-around effects and other aliasing artifacts when you ask for extreme numbers (like 5000 degrees).

Try projecting into a local coordinate system using metres. I'd use ST_Transform to convert from EPSG:4326 to EPSG:32633 (UTM Zone 33N), do the expand there, and transform back. That may lose some of the benefits of ST_Expand that you were hoping to achieve, so perhaps ST_Buffer might be more suitable. Depends on your specific situation.

Answer 2

The main problem is covered by brad and vince: you're mixing up units.

A second issue: consider using ST_DWithin() instead of a ST_Expand() and && combination. There's a lot of Q&As using ST_DWithin() around. E.g., Searching planet_osm_point by longitude and latitude