Thanks for the help - much appreciated.
Yes I'm using PostGIS, and with a simply join to a relating table I could get access to the geometry for these point
positions.Is using the GIST r-tree index faster than using the 2 b-tree indexes on the lat and long values? I guess
thisis a question for the PostGIS guys and a quick test could tell me anyway! My memory is that the GIST r-tree index
isslow for points at the moment, and that a good implementation of a kd-tree index over GIST is required for better
National Geodetic Office
Land Information New Zealand | Toitu te whenua
160 Lambton Quay | Private Box 5501 | Wellington 6145
DDI: 64 (0)4 498 3537 | Fax: 64 (0)4 498 3837 | www.linz.govt.nz
From: Tom Lane [mailto:]
Sent: Saturday, 10 July 2010 11:20 a.m.
To: Jeremy Palmer
Subject: Re: [PERFORM] Index usage with functions in where condition
Jeremy Palmer <> writes:
> This is the query that does not use the indexes:
> 6400000*radians(sqrt((coo.value1 - -41.0618)^2+((coo.value2 - 175.58461)*cos(radians(-41.0618)))^2)) as distance
> crs_coordinate coo
> coo.value1 between -41.0618-degrees(1200.0/6400000.0) and -41.0618+degrees(1200.0/6400000.0) and
> coo.value2 between 175.58461-degrees(1200.0/6400000.0)/(cos(radians(-41.0618))) and
Those expressions yield float8, not numeric, and numeric vs float8 isn't
an indexable operator for reasons we needn't get into here. You should
probably rethink whether numeric is really the best choice of datatype
for your columns, if this is the sort of value you expect to work with
--- you're paying a considerable price in speed and space for
perhaps-illusory precision gains. But if you insist on using numeric
then the solution is to cast the expression results to numeric
BTW I wonder whether you ought not be looking into postgis rather than
rolling-your-own coordinate arithmetic ...
regards, tom lane
This message contains information, which is confidential and may be subject to legal privilege.
If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message.
If you have received this message in error, please notify us immediately (Phone 0800 665 463 or ) and
destroythe original message.
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.