[postgis-users] st_dist, st_sphere, st_spheroid - overhead/efficiency question

[Paul Ramsey]

Two key points:

- Spatial indexes can't operate once you wrap the column in a
function. The index is on 'value' not on 'f(value)'.
- You are transforming into geographic coordinates (4326 == units of
degree) and then running a dwithin test with a radius of 2414. The
world is only 360 degrees wide. This term is providing no selectivity
at all.

You are not understanding/interpreting your coordinates/srids
correctly is my guess. What are the units of the coordinates of
market_locations and customer_locations?

select st_srid(the_geom), st_astext(the_geom) from market_locations limit 1;

select st_srid(the_geom), st_astext(the_geom) from customer_locations limit 1;

P.


On Thu, Jul 9, 2009 at 8:46 AM, Bo Coughlin<bo at rekindl.com> wrote:
> Have over a million POINT (the_geom) records located in two separate tables
> - x.table & y.table.  x.table 50,000 records; y.table 995,000 records.
> Distances required are all less than 1.5 miles (2414 meters) : all locations
> are located within a specific region of the US.
> GiST index on the_geom
> Goal: distance from all y.the_geom to x.the_geom - (shortest distance). And
> then place this distance back into each respective y.table.record
>  (currently creating temp table to then update y.table)
> Current sql:
> SELECT m.id AS mid,
> m.building_i AS mb_id,
> c.id AS cid, c.building_i AS cb_id,
> m.streetaddr AS m_address,
> m.city AS m_city,
> m.state AS m_state,
> m.zip AS m_zip,
> m.zip4 AS m_zip4,
> round(CAST(
> ST_Distance_Sphere(
> ST_Transform(m.the_geom,4326),
> ST_Transform(c.the_geom,4326)
> )
> AS numeric
> )
> ,2) AS dist_meters
> FROM market_locations m, customer_locations c
> WHERE st_dwithin(ST_Transform(m.the_geom,4326),
> ST_Transform(c.the_geom,4326) ,2414.016)
> I tried ST_Spheroid as well but after 28 hours I killed it.  This current
> query (ST_Sphere) has been working for .... 14 hrs.  Question is, should
> this be expected in terms of overhead and time, are there areas I might be
> able to tighten to increase performance on the query (additional btree index
> etc.)
> System Information: PostgreSQL 8.3.7 on i386-apple-darwin8.11.1, compiled by
> GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build
> 5370)
> Thanks to any and all for assistance.
>  - bo
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>