I want to get all points, that are in the same area. Therefore, I have the following query for my table check:

SELECT l.location_id, l.location 
FROM check l 
INNER JOIN check l2 ON ST_DWithin (l.location, l2.location, 500) 
                    AND l.location_id <> l2.location_id;

The column location is a Geometry(Point) data type.

The execution gives me an error and gives me more than 2 million result sets...from initial 1560 rows. The points are normally distributed through Europe and setting the threshold to 500 (m) seem to be fine. Is it may be a prolem with the data type?

  • 5
    I expect your points are in degrees and you've selected everything on the earth. – Ian Turton Mar 1 '15 at 17:47

It's a good bet that your SRID is 4326, and that you are testing if each geometry is within 500° Latitude and/or Longitude, which is a non-sense distance. Since everything is within 500° of each other, the expected return is 1560 * 1560 = 2433600 rows.

Try casting to a geography type, which uses distance units of meters (e.g., see ST_DWithin for geography inputs). Try this:

ST_DWithin (l.location::geography, l2.location::geography, 500)

It will run slower. If you want to speed it up, you can either make an GiST index on the geography cast, or convert the type to geography and make a regular GiST index there, or use_spheroid=false to approximate distances on a sphere rather than an ellipsoid of revolution.

Or if you don't really need exactly 500 m distance, you can approximate 500 m in degrees, which depends on your position. E.g., at 40° Latitude, 500 m is about 0.00450310° N-S and 0.00585522° E-W.

  • Thanks. You were absolutely right...Using Postgis, is it better to use JTS with Hibernate or postgis geo types? – Hans Müller Mar 1 '15 at 22:27
  • @HansMüller not sure, that sounds like a different question to me. – Mike T Mar 1 '15 at 22:41

Your Answer

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

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