- sqlalchemy 2017-03-19T16:58:15+01:00 https://fadeit.dk/blog/tag/sqlalchemy.html SQLAlchemy with PostgreSQL earthdistance extension 2015-03-19T20:30:16+01:00 https://fadeit.dk/blog /2015/03/19/sqlalchemy-postgresql-earthdistance <p>The fact that Earth is round, but not a sphere makes calculating distances difficult. Fortunately PostgreSQL has an extension for just doing that - earthdistance. Doing the calculations in the database is superior as extensions are compiled and it enables us to build an index to reduce the amount of calculations needed to make at query time. I came across <a href="http://johanndutoit.net/searching-in-a-radius-using-postgres/">this</a> article to get me started, however considering I spent more time on making earthdistance work with SQLAlchemy, I decided to share my solution. The example below assumes that you already have earthdistance set up and working with a ORM model class ‘Company’ that has ‘lat’ and ‘lng’ fields present.</p> <div class="language-python highlighter-rouge"><pre class="highlight"><code><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">func</span> <span class="kn">from</span> <span class="nn">example.model</span> <span class="kn">import</span> <span class="n">Company</span> <span class="kn">from</span> <span class="nn">example.core</span> <span class="kn">import</span> <span class="n">db</span> <span class="k">def</span> <span class="nf">get_companies_from_amsterdam</span><span class="p">():</span> <span class="c">#Calculating each company's distance from Amsterdam.</span> <span class="n">loc_amsterdam</span> <span class="o">=</span> <span class="n">func</span><span class="o">.</span><span class="n">ll_to_earth</span><span class="p">(</span><span class="mf">52.3667</span><span class="p">,</span> <span class="mf">4.9000</span><span class="p">)</span> <span class="n">loc_company</span> <span class="o">=</span> <span class="n">func</span><span class="o">.</span><span class="n">ll_to_earth</span><span class="p">(</span><span class="n">Company</span><span class="o">.</span><span class="n">lat</span><span class="p">,</span> <span class="n">Company</span><span class="o">.</span><span class="n">lng</span><span class="p">)</span> <span class="n">distance_func</span> <span class="o">=</span> <span class="n">func</span><span class="o">.</span><span class="n">earth_distance</span><span class="p">(</span><span class="n">loc_amsterdam</span><span class="p">,</span> <span class="n">loc_company</span><span class="p">)</span> <span class="n">query</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Company</span><span class="p">,</span> <span class="n">distance_func</span><span class="p">)</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">distance_func</span><span class="p">)</span> <span class="c">#Resultset is no longer list of Company, but a list of tuples.</span> <span class="n">result</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="nb">all</span><span class="p">()</span> <span class="n">mapped</span> <span class="o">=</span> <span class="p">[]</span> <span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span> <span class="n">company</span> <span class="o">=</span> <span class="n">row</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span> <span class="n">company</span><span class="o">.</span><span class="n">distance</span> <span class="o">=</span> <span class="n">row</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span> <span class="n">mapped</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">company</span><span class="p">)</span> <span class="k">return</span> <span class="n">mapped</span> </code></pre> </div> <p>To speed up things, we can pre-compute <em>ll_to_earth(Company.lat, Company.lng)</em> using an index:</p> <div class="language-python highlighter-rouge"><pre class="highlight"><code><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">func</span><span class="p">,</span> <span class="n">Index</span> <span class="kn">from</span> <span class="nn">example.model</span> <span class="kn">import</span> <span class="n">Company</span> <span class="n">Index</span><span class="p">(</span><span class="s">'company_distance'</span><span class="p">,</span> <span class="n">func</span><span class="o">.</span><span class="n">ll_to_earth</span><span class="p">(</span><span class="n">Company</span><span class="o">.</span><span class="n">lat</span><span class="p">,</span> <span class="n">Company</span><span class="o">.</span><span class="n">lng</span><span class="p">),</span> <span class="n">postgresql_using</span><span class="o">=</span><span class="s">'gist'</span><span class="p">)</span> </code></pre> </div>