-5

I want to return all the houses for sale within 500 meters from my own house.

I have this query:

SELECT * FROM houses_for_sale WHERE postgis.ST_DWithin(coordinates, $1, 500);

It returns houses located anywhere -- not just within 500 meters. Both "coordinates" and $1 are PostGIS geometry types, using the SRID 4326.

After spending a million hours sweating and swearing at the usual total lack of clarity from the manual and every other kind of resource I can find, it eventually turned out that my coordinates use something called "degrees" instead of meters:

UNIT["degree",0.0174532925199433,

I have no idea what a "degree" is or how I turn them into meters. I want meters -- not "degrees".

I find all of this to be total insanity. Why can't maps and coordinates be simple and consistent? Why must every single person who has ever looked at a map invent their own completely incompatible and weird format for coordinates? I swear, there are more "map systems" than there are people on this planet. The variations are absolutely endless. I've never found two map/coordinate sources that used the same or even slightly compatible systems. In practice, this means that they are all useless because it's so difficult to convert between them.

Improve this question
6
  • 2
    Degrees are degrees of a spheroid, 500 is more than a complete circle so you will get everything. If you're prepared to accept a small inaccuracy convert 500m to degrees roughly, usna.edu/Users/oceano/pguth/md_help/html/approx_equivalents.htm should help if you're not on either pole or close to the equator. As for why maps can't be consistent there's books written on that subject, some projections work for small areas but can't be used for large areas, others are great for very large areas but not so fantastic at small areas, some retain distance, others retain bearing.
    – Michael Stimson
    Commented Sep 21, 2020 at 4:12
  • @MichaelStimson Are you actually suggesting that I have to manually look up and convert numbers all the time? There isn't even any converter function in PostGIS?
    – S. Fedore
    Commented Sep 21, 2020 at 4:21
  • No if you want to rough convert 500 metres to DD as an arbitrary search distance, if your figure of 500m changes then so does your rough distance, use the values in the link to derive your conversion factor. Alternately you should be able to project your circle into the same coordinate reference system as your data, which is quicker than projecting your data into the same CRS as your selection geometry. If accuracy is super important have a read of geodesic distance community.esri.com/groups/coordinate-reference-systems/blog/…
    – Michael Stimson
    Commented Sep 21, 2020 at 5:01
  • 2
    Because your units are decimal degrees, 0-360 around the equator, -90 is the south pole and 90 is the north pole. There is no direct conversion from degrees to metres as the physical distance a degree encompasses is smaller the further you move away from the equator and the distance north/south is different to east/west (have a good look at a globe and note the latitude/longitude bands).This XKCD is quite illustrative xkcd.com/977 showing the different methods of transforming a spheroid into a flat sheet.
    – Michael Stimson
    Commented Sep 21, 2020 at 5:21
  • 2
    It's somewhat disturbing that you don't understand the purpose of degrees when working with latitude/longitude angular measurements. Maybe if folks weren't distracted by the rant character of your question they would have pointed out that ST_DWithin will compute spherical distance in meters when its first two parameters are of the geography type. While "coordinates" is an awful name for something as sophisticated as a geometry object, all you need to do is cast it and the variable with ::geography and distance will be computed in meters.
    – Vince
    Commented Sep 21, 2020 at 11:24

1 Answer 1

Reset to default
4

When using the GEOMETRY data type, distances are computed by simply using Pythagoras' theorem on the coordinates, without considering what the coordinates actually mean. In the case of EPSG:4326, coordinates are latitudes and longitues (i.e. polar coordinates on an ellipsoid), and Euclidean distances between those don't make much sense. This means that the combination of GEOMETRY and EPSG:4326 is pretty much useless if you want to work with distances.

One solution is to use the GEOGRAPHY data type. It has some limitations compared to GEOMETRY, but it does handle global distance calculations.

Another option is to use some coordinate system where Euclidean distances do make sense, e.g. an appropriate UTM zone, or some national or state-wide coordinate system (most countries seem to have them).

The reason it is so complicated is because the Earth is, when you look at it carefully, quite a funny-shaped lump of rock, so everyone works with approximations. Which approximation works best depends on what area of the planet you are interested in, your needs for precision versus computational complexity, and other trade-offs. There are also many legacy coordinate systems, suffering from various degrees of modelling and measurement errors, that still need to be handled.

Improve this answer
2
  • I am new to this so please correct but even if you get euclidean distance in 4326 you can multiply the result by 113000 to get estimated distance in meters. So its not completely useless if you are ok with approximate results
    – Ajak6
    Commented May 6, 2021 at 23:07
  • 1
    @ajak6 That's going to give you some weird results depending on where on the planet you are. Here in Stockholm, one degree of longitude is about half as long as a degree of latitude. In Longyearbyen, it's one fourth!
    – Ture Pålsson
    Commented May 7, 2021 at 7:06

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