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

[Bo Coughlin]

Ooops. sorry thought I'd pasted that last time.

select st_srid(the_geom), st_astext(the_geom) from market_locations limit 1;
4326 POINT(-77.1042756692815 38.995397678429)

select st_srid(the_geom), st_astext(the_geom) from customer_locations limit
1;
4326 POINT(-100.000971435723 45.0003533555503)

- bo

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

> Could you run the two queries and show the results, just to be sure?
>
> 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;
>
> On Thu, Jul 9, 2009 at 9:24 AM, Bo Coughlin<bo at rekindl.com> wrote:
> > 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
> >>
> >
> >
> > _______________________________________________
> > 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/26de32f7/attachment.html>