0

I'm using postgis 9.1 and rgeo to develop an map application.

One of the features implies that I find all points that are XXX kms from the current point, and are also on the right of the Point.

I tried with the following query:

Environment.where{ st_dwithin(location, factory.point(170.0, 0), 300000 ) }.where('ST_X(location::geometry) > 170')

The corresponding SQL for the query: [EDITED] SELECT "environments".* FROM "environments" WHERE (st_dwithin("environments"."location", ST_PointFromText('POINT(170 0)', 4326), 300000)) AND (ST_X(location::geometry) > 170 );

Here I hardcoded the point to be in the 170 longitude.

The problem with this is that I can't match points in the [-170,-180] longitudes.

Is there a way to solve this using only postgis functions?

[EDIT] This is a around the world problem with postgis. In this application I'm allowed only to find points to the right and in a certain range.. I can't find a solution with pure SQL...

Thanks!

José da Mata
  • 11
  • 1
  • 1
    Please do not hard-code data in a binary format. I do not think anybody on SO can read HEXEWKB. Can you please edit to regular coordinates? – Patrick May 20 '14 at 02:43
  • Do you realize that you are effectively using Cartesian distance units in degrees? – Mike T May 20 '14 at 05:33
  • Patrick I'm using a ruby library, rgeo, which translated to the binary format you see. But this is the same for: SELECT "environments".* FROM "environments" WHERE (st_dwithin("environments"."location", ST_PointFromText('POINT(-170 0)', 4326), 150000)); – José da Mata May 21 '14 at 00:46

1 Answers1

0

Consider using the geography type rather than the geometry type.

Community
  • 1
  • 1
Mike T
  • 41,085
  • 18
  • 152
  • 203
  • Thanks Mike. I'm using geography type in the column location from relation Environment. The postgis function ST_X only works with geometries. – José da Mata May 21 '14 at 01:02
  • use `ST_X(geog::geometry)` or `ST_AsLatLonText(geog::geometry)` to get coordinate data from geography types. – Mike T May 21 '14 at 03:49