0

I am looking into using DreamFactory for my REST API.. I have a table called 'tags' that has a collumn called 'lat' and one called 'lon' for latitude and longitude of locations stored in my 'db'

Problem is i need to be able to pass a latitude and longitude along with a distance to my service call and filter the results to only return the rows where the location is <= the distance provided from the latitude and longitude i provided..

My SQL commands are a bit rusty but something like this:

SELECT  *,   3956 * 2 * ASIN(SQRT( POWER(SIN((42.347109 - abs(tags.lat)) * pi()/180 / 2),2) + 
COS(42.347109 * pi()/180 ) * COS(  abs (lat) *  pi()/180) * POWER(SIN((-71.075589 – tags.lon) *  
pi()/180 / 2), 2) ))   as distance FROM tags having distance <  2;

anyone have experience doing this sort of filter? especially with DreamFactory?

erik
  • 4,946
  • 13
  • 70
  • 120

1 Answers1

2

Sounds like you need to perform the distance calculation at runtime across the full set of records in your table. So from a performance perspective you probably want to have the database make this calculation using a stored procedure. See this post for more information on this approach.

latitude/longitude find nearest latitude/longitude - complex sql or complex calculation

Triggering stored procedures with server-side events is on the DreamFactory roadmap coming up here soon (not in version 1.6 mid-June but in the next release in the July timeframe). That feature will let you make a REST API call from the client that triggers DreamFactory to run your stored procedure and return the correct records in JSON (or XML) format back to the client.

DreamFactory currently supports MySQL, Postgres, and SQL Server, so as long as you're using one of those relational databases for stored procedures, the stored procedure approach will work (Oracle and DB2 are on the roadmap too).

Hope that helps.

Community
  • 1
  • 1
Ben
  • 91
  • 5
  • Ben, thanks.. The Dreamfactory Team has been ever so helpful. much appreciated. That said, i am using the DB built into Dreamfactory.. i assume since it is SQL that the ability to add stored procedures will be allowed for the default DB as well? – erik Jun 12 '14 at 19:10
  • Happy to help! As long as you install the DreamFactory open source package (from Bitnami or direct from GitHub), you can add stored procedures to the DB built into DreamFactory (which is MySQL). You can't use stored procedures in the free hosted edition of DreamFactory though. The free hosted edition is really a multi-tenant developer sandbox, and we don't want rogue processes like a sketchy stored procedure gone wild bringing the system down for others. – Ben Jun 12 '14 at 21:12
  • I am running it on Amazon web services.. it was a free installation.. is that ok? – erik Jun 13 '14 at 00:48
  • Absolutely, DreamFactory is an open source project licensed under Apache, so it's free to use and install wherever you like. AWS works great. If you need any help, just contact support at dreamfactory.com or community.dreamfactory.com – Ben Jun 13 '14 at 06:55
  • thanks again ben.. one last question.. i am running vs 1.4.0-2 on Ubuntu.. where/how do i check for updates.. – erik Jun 13 '14 at 11:43
  • The best way is to "watch" the [DreamFactory GitHub repo](https://github.com/dreamfactorysoftware/dsp-core/). Release notes for each release will also be on the [DreamFactory wiki](https://github.com/dreamfactorysoftware/dsp-core/wiki) soon too. If you're using a Bitnami package of DreamFactory it typically takes them a few days to release a new version after the new version is live on GitHub. – Ben Jun 13 '14 at 15:27
  • I tried installing directly from bitnami but aws gave me problems and i ended installing it directly from aws market – erik Jun 13 '14 at 16:53
  • AWS doesn't seem to have the latest in their market by the way.. and when i look for upgrades there is nothing :( – erik Jun 13 '14 at 17:06
  • Sorry about the mismatch between the version on AWS marketplace and Bitnami. The latest AWS installer is on Bitnami here https://bitnami.com/stack/dreamfactory/cloud/amazon. Working with Bitnami on updating the version that's on the AWS marketplace. – Ben Jun 13 '14 at 18:56