[postgis-users] Fastest test for point (nearly) in polygon

[Ben Madin]

G'day all,

I'm sure I've seen a similar discussion to this question, but I can't find it now. During a bulk data submission process we have a large number of point locations and the code of the province in which they are 'reputed' to be occurring. Having put some basic checks through the system we are inclined to check this at upload time, but have to realistically anticipate that points within a certain limit will be adequately accurate (at the moment about 2km, but probably doesn't have to be strictly 2km)

There are any number of ways of doing this - all uploaded data (and geometries) are currently in EPSG 4283 (Australian Lat Long). 

Options would include using :

st_distance_sphere(province polygon, st_setsrid(st_makepoint(long, lat), 4283)) < 2000

st_dwithin (province polygon, st_setsrid(st_makepoint(long, lat), 4283), 0.02)

st_within (st_buffer(province polygon, 0.02), st_setsrid(st_makepoint(long, lat), 4283))

st_intersects(province polygon, st_buffer(st_setsrid(st_makepoint(long, lat), 4283)))

or any number of similar variants.

Our problem is that there are many rows, and even prior to these checks, other checks on other aspects of the data were inclined to take longer than we would like.

I can accept that trying to avoid to much transforming is probably better (and accepting some slightly inconsistent accuracy) from a speed perspective.

Can anyone suggest which of these checks (or others) might be fastest?

I'm leaning towards the latter two as they are mainly functions offered by GEOS, which I understand to be faster than PL functions. 

cheers

Ben