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 <[email protected]>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<[email protected]> 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 <[email protected]> > > 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<[email protected]> 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 > >> > [email protected] > >> > http://postgis.refractions.net/mailman/listinfo/postgis-users > >> > > >> > > >> _______________________________________________ > >> postgis-users mailing list > >> [email protected] > >> http://postgis.refractions.net/mailman/listinfo/postgis-users > >> > > > > > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
