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

[Paul Ramsey]

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
>
>