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

[Bo Coughlin]

Thanks for the reply.  Both are set to 4326 (both meaning both tables and
all records). - bo


On Thu, Jul 9, 2009 at 12:04 PM, Paul Ramsey <pramsey at cleverelephant.ca>wrote:

> 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
> >
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090709/6143f9ab/attachment.html>