On 02/05/2013 09:01 PM, Ben Madin wrote:
> Thanks Adrian,
>
> On 2013-02-06, at 12:52 , Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
>> On 02/05/2013 08:24 PM, Ben Madin wrote:
>>> The full query is :
>>>
>>> {{{
>>> SELECT rep.id, res8.reportid, round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0',
post.the_point::geometry)/1000)as dist
>>> FROM reports rep
>>> LEFT JOIN users u ON rep.link = u.id
>>> LEFT JOIN postcodes post ON u.postcode::integer = post.postcode
>>> LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND spe.synonym = 0
>>> LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = 108 AND res8.del = false
>>> LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid = 108 AND rlu8.id = res8.resultvalue::int
>>> WHERE rep.del IS false AND rep.projectid = 51
>>> AND round(st_distance_sphere( '0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/1000) < '150'
ANDspe.id = '9465' AND rlu8.id = '935';
>>> }}}
>>>
>>
>>
>> Follow up questions:
Dim bulb moment.
What happens if you run a simplified version of the query?
One that just LEFT JOINS reports to results ON reportid=rep.id.
>
> cheers
>
> Ben
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
