Spatial/GIS: find records within X distance of point?

[User]

I'm storing latitude/longitude coordinates in a geometry field (using PostgreSQL 9.1.10):

Field('point', 'geometry()')

I understand there is also the geography type but from my reading geometry is faster and is suitable for small distances (http://workshops.boundlessgeo.com/postgis-intro/geography.html#why-not-use-geography)

I want users to be able to specify a reference point and search for all records within X distance from the reference point.  Using raw SQL I would use  ST_DWithin doing something like

SELECT name, ST_AsText(point)

FROM mytable

WHERE ST_DWithin(point, ST_GeomFromText('POINT(40.47112 -76.33)',4326), 0.1)

But web2py does not seem to support ST_DWithin only st_within.  So how can I achieve a similar result in web2py?

[Massimo Di Pierro]

Added ST_Dwithin support in trunk. Please check it.

[User]

Thanks.  This is what I see at line 2983 in dal.py:

    def ST_DWITHIN(self, first, second):
        """
        http://postgis.org/docs/ST_Within.html
        """
        return 'ST_DWithin(%s,%s)' %(self.expand(first), self.expand(second, first.type))

The proper documentation is at http://postgis.org/docs/ST_DWithin.html (note the 'D').  Also looks like this is missing the 3rd argument to ST_DWithin:

boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);

[Massimo Di Pierro]

OK. This needs more work than anticipated. Looks like the Query object only supports unary and binary operators. Give me a little more time. ;-)

[User]

Forgive me because I don't understand anything about the internals of web2py but doesn't REPLACE take three parameters:

def REPLACE(self, first, (second, third)):
    return 'REPLACE(%s,%s,%s)' % (self.expand(first,'string'),
               self.expand(second,'string'),
               self.expand(third,'string'))

Also:

def ST_ASGEOJSON(self, first, second):
        """
        http://postgis.org/docs/ST_AsGeoJSON.html
        """
        return 'ST_AsGeoJSON(%s,%s,%s,%s)' %(second['version'],
            self.expand(first), second['precision'], second['options'])

Or do you mean something different?

[Massimo Di Pierro]

Actually you do understand a lot of about this. That's a trick. It works. We can use it in this case too, until we come up with a better design.

I used the same trick as in REPLACE, in trunk. Please give it a try.

Massimo

[User]

Thanks, on line 2987 I believe the format string is missing the 3rd '%s' parameter:

        return 'ST_DWithin(%s,%s)' %(self.expand(first), self.expand(second, first.type),
                                     self.expand(third, 'double'))

Should be:

        return 'ST_DWithin(%s,%s,%s)' %(self.expand(first), self.expand(second, first.type),
                                     self.expand(third, 'double'))

[Massimo Di Pierro]

You are right. Please check again.

[User]

This appears to be working correctly now thank you.  One more thing: the docstring on line 2985 is referencing the wrong PostGIS function (ST_Within), it should be:

        """
        http://postgis.org/docs/ST_DWithin.html
        """

(note the D)