st_dwithin

[Pierre]

Hi,

I'd like to specify use_spheroid=False when calling web2py function st_dwithin

postgis ref :
boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters, boolean use_spheroid);

 Is it possible ?


if not can can I achieve it with  executesSQL ?

[DenesL]

Not with the current sources but they can be modified.
You can always use executesql to run a SQL command.
The actual command depends on what you are doing.

If you are running from source, modifying it is not that hard.
In \gluon\packages\dal\pydal\objects.py replace (at line 1328 in ver.2.12.03)

    def st_dwithin(self, value, distance):
        db = self.db
        return Query(db, db._adapter.ST_DWITHIN, self, (value, distance))

with

    def st_dwithin(self, value, distance, use_spheroid=True):
        db = self.db
        return Query(db, db._adapter.ST_DWITHIN, self, (value, distance, use_spheroid))

and in \gluon\packages\dal\pydal\adapters\postgres.py replace (at line 325)

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

with

    def ST_DWITHIN(self, geo1, tup):
        """
        http://postgis.org/docs/ST_DWithin.html
        """
        geo2, dist, use_spheroid = tup
        if use_spheroid:
            return 'ST_DWithin(%s,%s,%s)' %(self.expand(geo1),
                                            self.expand(geo2, geo1.type),
                                            self.expand(dist, 'double'))
        else:
            return 'ST_DWithin(%s,%s,%s,%s)' %(self.expand(geo1),
                                            self.expand(geo2, geo1.type),
                                            self.expand(dist, 'double'),
                                            self.expand(False, 'boolean'))

If you or someone else is willing to test the changes we can add them to the next version.

Denes

[Pierre]

Great !!

I'd prefer to use Web2py api instead of executesql but I've read the latter is faster (speed might be a concern in my case). I'll do the modifications you suggest and see if it works. I hope this won't corrupt Web2py magical harmony

thanks

[Pierre]

Hi DenesL,

I didn't test  st_dwithin  with use_spheroid because I changed my "strategy" to use geometry fields instead of geography's for GIS calculations. The function I really miss and am willing to test is st_transform ( st_interpolate could be good too). So far I did some calculations/queries with st_distance and the original st_dwithin and  results obtained were coherent. I really don't like having to executesql(queries) to compensate for the lack of st_transform and to "reinvent the wheel" anytime I need to validate_and_insert or update_or_insert GIS data. I like to use the DAL