0

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?

3
3

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.

2
  • is it not possible to convert degrees into meters. so i could keep on using my query but using a transformed meter value?
    – dc10
    May 5 '14 at 10:12
  • Its possible, but non-trivial for longitude (X) values because of convergence. That is, 1 degree of longitude a the equator is many more metres than 1 degree of longitude near the poles.
    – BradHards
    May 5 '14 at 10:24
0

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

1
  • ST_DWithin() is working great (much faster). Thanks.
    – dc10
    May 6 '14 at 11:19

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